hsqldbuserlistdao.java

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

package lightningboard.dao.hsqldb;

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;
public class HsqldbUserListDAO 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 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";
      ResultSet rs = dba.openSelect(sql);
      rs.last();
      int count = rs.getRow();
      userListBean.setStart(start);
      userListBean.setRange(range);
      userListBean.setCount(count);
      LinkedList userList=new LinkedList();
      if (count>0){
        if(start > count){
          rs.close();
          dba.closeSelect();
          return null;
        }
        rs.absolute(start);
        for(int i=0; i < range && !rs.isAfterLast();i++ ){
          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);
          rs.next();
        }
      }
      userListBean.setUsers(userList);
      rs.close();
      dba.closeSelect();
      return userListBean;
    } catch (Exception ex) {
      throw new ActionException(ex);
    }finally{
      try {
        dbcm.freeConnection(connection);
      } catch (SQLException ex) {
        //
      }
    }
  }
}