mysqlhomedao.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.HomeDAO;
import lightningboard.bean.HomeBean;
import lightningboard.bean.Forum;

public class MysqlHomeDAO implements HomeDAO  {
  private DBConnectionManager dbcm = DBConnectionManager.getInstance();
  public HomeBean execute() throws ActionException{
    Connection connection=null;
    try {
      HomeBean homeBean=new HomeBean();
      connection=dbcm.getConnection();
      DBAccess dba=new DBAccess(connection);
      String sql="SELECT f_id,f_name,f_info,f_u_id,user1.u_name as u_name"
                +",f_topics,f_messages,f_u_id2,user2.u_name as u_name2"
                +",f_lasttime FROM lb_forum,lb_user user1 ,lb_user user2 "
                +"WHERE f_u_id=user1.u_id AND f_u_id2=user2.u_id ORDER BY "
                +"f_order";
      ResultSet rs=dba.openSelect(sql);
//check result
      if (!rs.next()){
        rs.close();
       dba.closeSelect();
       return null;
      }
      rs.beforeFirst();
      LinkedList forums=new LinkedList();
      int topicSum=0;
      int messageSum=0;
      while(rs.next()){
        Forum forum=new Forum();
        forum.setF_id(rs.getInt("f_id"));
        forum.setF_name(rs.getString("f_name"));
        forum.setF_info(rs.getString("f_info"));
        forum.setF_u_id(rs.getInt("f_u_id"));
        forum.setU_name(rs.getString("u_name"));
        forum.setF_topics(rs.getInt("f_topics"));
        forum.setF_messages(rs.getInt("f_messages"));
        forum.setF_u_id2(rs.getInt("f_u_id2"));
        forum.setU_name2(rs.getString("u_name2"));
        forum.setF_lasttime(rs.getTimestamp("f_lasttime"));
        forums.add(forum);
        topicSum+=rs.getInt("f_topics");
        messageSum+=rs.getInt("f_messages");
      }
      homeBean.setForums(forums);
      rs.close();
      dba.closeSelect();
      //topicsSum
       homeBean.setTopicSum(topicSum);
      //messagesSum
       homeBean.setMessageSum(messageSum);
      //usersSum
      rs=dba.openSelect("select count(*) as cnt from lb_user");
      if (rs.next()){
        homeBean.setUserSum(rs.getString(1));
      }
      rs.close();
      dba.closeSelect();

      return homeBean;
    } catch (Exception ex) {
      throw new ActionException(ex);
    }finally{
     try {
        dbcm.freeConnection(connection);
      } catch (SQLException ex) {
        //
      }
    }
  }
}