mysqltopicdao.java

雷霆论坛是用J2EE技术开发的功能强大、扩展性强、易于定制

package lightningboard.dao.mysql;

import java.util.LinkedList;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import lightningboard.db.DBConnectionManager;
import lightningboard.db.DBAccess;
import lightningboard.ActionException;
import lightningboard.dao.TopicDAO;
import lightningboard.bean.TopicBean;
import lightningboard.bean.Message;

public class MysqlTopicDAO implements TopicDAO {
  private DBConnectionManager dbcm = DBConnectionManager.getInstance();

  public TopicBean execute(int t_id,int start,int range)throws ActionException{
    Connection connection=null;
    try {
      TopicBean topicBean=new TopicBean();
      connection=dbcm.getConnection();
      DBAccess dba=new DBAccess(connection);
      String sql="SELECT t_f_id,f_name,f_info,f_u_id,u_name,f_topics"
                +",f_messages,t_name,t_replies,t_views,t_order,t_locked"
                +" FROM lb_forum,lb_topic,lb_user  WHERE t_f_id=f_id AND"
                +" f_u_id=u_id  AND t_id=" + t_id;
      ResultSet rs=dba.openSelect(sql);
      //check result
      if (!rs.next()){
        rs.close();
        dba.closeSelect();
       return null;
      }
      rs.beforeFirst();
      while (rs.next()){
        topicBean.setF_id(rs.getInt("t_f_id"));
        topicBean.setF_name(rs.getString("f_name"));
        topicBean.setF_info(rs.getString("f_info"));
        topicBean.setF_u_id(rs.getInt("f_u_id"));
        topicBean.setU_name(rs.getString("u_name"));
        topicBean.setF_topics(rs.getInt("f_topics"));
        topicBean.setF_messages(rs.getInt("f_messages"));
        topicBean.setT_id(t_id);
        topicBean.setT_name(rs.getString("t_name"));
        topicBean.setT_replies(rs.getInt("t_replies"));
        topicBean.setT_views(rs.getString("t_views"));
        topicBean.setT_order(rs.getInt("t_order"));
        topicBean.setT_locked(rs.getInt("t_locked"));
      }
      rs.close();
      dba.closeSelect();
      sql="SELECT count(*) as cnt FROM lb_message  WHERE  m_t_id=" + t_id;
      rs = dba.openSelect(sql);
      int count = 0;
      while(rs.next()){
        count = rs.getInt("cnt");
      }
      topicBean.setStart(start);
      topicBean.setRange(range);
      topicBean.setCount(count);
      rs.close();
      if(start > count){
        rs.close();
        dba.closeSelect();
        return null;
      }

      //------------------------
      sql="SELECT m_id,m_iconid,m_content,m_time,m_u_id,u_name,u_regtime,u_address"
         +",u_post,u_sign,u_avatar_id,r_name FROM lb_message,lb_user,lb_role "
         +" WHERE m_u_id=u_id AND u_r_id=r_id AND m_t_id=" + t_id
         +" ORDER BY m_time LIMIT "+(start-1)+","+range;
      rs = dba.openSelect(sql);
      LinkedList messageList=new LinkedList();
      while(rs.next()){
        Message message=new Message();
        message.setM_id(rs.getInt("m_id"));
        message.setM_iconid(rs.getInt("m_iconid"));
        message.setM_content(rs.getString("m_content"));
        message.setM_time(rs.getTimestamp("m_time"));
        message.setM_u_id(rs.getInt("m_u_id"));
        message.setU_name(rs.getString("u_name"));
        message.setU_regtime(rs.getTimestamp("u_regtime"));
        message.setU_address(rs.getString("u_address"));
        message.setU_post(rs.getInt("u_post"));
        message.setU_sign(rs.getString("u_sign"));
        message.setU_avatar_id(rs.getInt("u_avatar_id"));
        message.setR_name(rs.getString("r_name"));
        messageList.add(message);
      }
      topicBean.setMessages(messageList);
      rs.close();
      dba.closeSelect();
      //view
      dba.runSql("UPDATE lb_topic SET t_views=t_views+1 WHERE t_id="+t_id);
      //jump
      CommonDAO commonDAO=new CommonDAO();
      topicBean.setForumMap(commonDAO.getForumMap(dba));
      return topicBean;
    } catch (Exception ex) {
      throw new ActionException(ex);
    }finally{
      try {
        dbcm.freeConnection(connection);
      } catch (SQLException ex) {
        //
      }
    }
  }
}