mysqlforumdao.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.ForumDAO;
import lightningboard.bean.ForumBean;
import lightningboard.bean.Topic;
import lightningboard.util.StringUtil;


public class MysqlForumDAO implements ForumDAO {
  private DBConnectionManager dbcm = DBConnectionManager.getInstance();

  public ForumBean execute(int f_id,int start, int range
                           ,String keyword)
      throws ActionException{
    Connection connection=null;
    try {
      ForumBean forumBean=new ForumBean();
      forumBean.setKeyword(keyword);
      connection=dbcm.getConnection();
      DBAccess dba=new DBAccess(connection);
      String sql="SELECT f_name,f_u_id,u_name,f_info,f_topics,f_messages FROM"
                +" lb_forum,lb_user WHERE f_u_id=u_id AND f_id=" + f_id;
      ResultSet rs=dba.openSelect(sql);
      //check result
      if (!rs.next()){
        rs.close();
        dba.closeSelect();
        return null;
      }
      rs.beforeFirst();

      while (rs.next()){
        forumBean.setF_id(f_id);
        forumBean.setF_name(rs.getString("f_name"));
        forumBean.setF_u_id(rs.getInt("f_u_id"));
        forumBean.setU_name(rs.getString("u_name"));
        forumBean.setF_info(rs.getString("f_info"));
        forumBean.setF_topics(rs.getInt("f_topics"));
        forumBean.setF_messages(rs.getInt("f_messages"));
      }
      rs.close();
      dba.closeSelect();
      //------------------------
      String condition= " t_f_id=" + f_id;
      if (!StringUtil.nullOrBlank(keyword)){
        condition+=" AND t_name LIKE '%"+keyword+"%'";
      }
      sql="SELECT count(*) as cnt FROM lb_topic,lb_user"
         +" WHERE t_u_id=u_id AND "+condition;
      rs = dba.openSelect(sql);
      int count = 0;
      while(rs.next()){
        count = rs.getInt("cnt");
      }
      rs.close();
      forumBean.setStart(start);
      forumBean.setRange(range);
      forumBean.setCount(count);
      LinkedList topicList=new LinkedList();
      if (count>0){
        if(start > count){
          rs.close();
          dba.closeSelect();
          return null;
        }
      sql="SELECT t_order,t_locked,t_id,t_name,t_u_id,t_iconid"
         +",user1.u_name as u_name"
         + ",t_replies,t_views,t_lasttime,t_u_id2,user2.u_name as u_name2 "
         +"FROM lb_topic,lb_user user1,lb_user user2  WHERE t_u_id=user1.u_id "
         +"AND t_u_id2=user2.u_id AND " + condition + " ORDER BY "
         +"t_order DESC,t_lasttime DESC LIMIT "+(start-1)+","+range;
      rs = dba.openSelect(sql);
        while (rs.next()){
          Topic topic=new Topic();
          topic.setT_order(rs.getInt("t_order"));
          topic.setT_locked(rs.getInt("t_locked"));
          topic.setT_iconid(rs.getInt("t_iconid"));
          topic.setT_id(rs.getInt("t_id"));
          topic.setT_name(rs.getString("t_name"));
          topic.setT_u_id(rs.getInt("t_u_id"));
          topic.setU_name(rs.getString("u_name"));
          topic.setT_replies(rs.getInt("t_replies"));
          topic.setT_views(rs.getString("t_views"));
          topic.setT_lasttime(rs.getTimestamp("t_lasttime"));
          topic.setT_u_id2(rs.getInt("t_u_id2"));
          topic.setU_name2(rs.getString("u_name2"));
          topicList.add(topic);
        }
      }
      forumBean.setTopics(topicList);
      rs.close();
      dba.closeSelect();
      //jump
      CommonDAO commonDAO=new CommonDAO();
      forumBean.setForumMap(commonDAO.getForumMap(dba));
      return forumBean;
    } catch (Exception ex) {
      throw new ActionException(ex);
    }finally{
      try {
        dbcm.freeConnection(connection);
      } catch (SQLException ex) {
        //
      }
    }
  }
}