mysqluserlistdao.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.UserListDAO;
import lightningboard.bean.UserListBean;
import lightningboard.bean.User;
import lightningboard.util.StringUtil;

public class MysqlUserListDAO implements UserListDAO {
  private DBConnectionManager dbcm = DBConnectionManager.getInstance();
  public UserListBean execute(int start, int range,String keyword)throws ActionException{
    Connection connection=null;
    try {
      UserListBean userListBean=new UserListBean();
      userListBean.setKeyword(keyword);
      connection=dbcm.getConnection();
      DBAccess dba=new DBAccess(connection);

      String condition=" u_name LIKE '%"+keyword+"%'";

      String sql="SELECT count(*) as cnt FROM lb_user WHERE "+condition;
      ResultSet rs = dba.openSelect(sql);
      int count = 0;
      while(rs.next()){
        count = rs.getInt("cnt");
      }
      rs.close();
      if(start > count){
        rs.close();
        dba.closeSelect();
        return null;
      }
      userListBean.setStart(start);
      userListBean.setRange(range);
      userListBean.setCount(count);
      sql="SELECT u_id,u_name,u_avatar_id,u_email,u_regtime,u_address"
         +",u_post,u_lasttime,u_name,r_name FROM lb_user,lb_role"
         +" WHERE u_r_id=r_id AND "+condition+"ORDER BY u_post DESC"
         +" LIMIT "+(start-1)+","+range;
      rs = dba.openSelect(sql);
      LinkedList userList=new LinkedList();
      while(rs.next()){
        User user=new User();
        user.setU_id(rs.getInt("u_id"));
        user.setU_name(rs.getString("u_name"));
        user.setU_avatar_id(rs.getInt("u_avatar_id"));
        user.setU_email(rs.getString("u_email"));
        user.setU_regtime(rs.getTimestamp("u_regtime"));
        user.setU_address(rs.getString("u_address"));
        user.setU_post(rs.getInt("u_post"));
        user.setU_lasttime(rs.getTimestamp("u_lasttime"));
        user.setR_name(rs.getString("r_name"));
        userList.add(user);
      }
      userListBean.setUsers(userList);
      rs.close();
      dba.closeSelect();
      return userListBean;
    } catch (Exception ex) {
      throw new ActionException(ex);
    }finally{
      try {
        dbcm.freeConnection(connection);
      } catch (SQLException ex) {
        //
      }
    }
  }
}