Oracle 分页查询实例

祝湉湉

/**
* 返回分页相关信息
* @param list 每页显示的记录
* @param pageNo 当前的页数
* @return PageModel
*/

public PageModel<User> findUserList(int pageNo,int pageSize){

PageModel<User> pageModel = null;
String sql = "select count(*) from t_user where user_id <>'root'";
StringBuffer bufSql = new StringBuffer();
bufSql.append("select user_id,user_name,password,telephone,email,creat_date ");
bufSql.append("from ");
bufSql.append("( ");
bufSql.append("select rownum rn,user_id,user_name,password,telephone,email,creat_date ");
bufSql.append("from ");
bufSql.append("( ");
bufSql.append("select rownum rn,user_id,user_name,password,telephone,email,creat_date from t_user where user_id <> 'root' order by user_id ");
bufSql.append(") ");
bufSql.append("where rn <= ? ");
bufSql.append(") ");
bufSql.append("where rn > ? ");

Connection conn = null;
PreparedStatement pstmt = null;
Statement stmt = null;
ResultSet rs = null;
int totalRecords =0;
int totalPage  = 0;
try {
conn = DbUtil.getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
rs.next();
totalRecords = rs.getInt(1);

pstmt = conn.prepareStatement(bufSql.toString());
pstmt.setInt(1, pageNo*pageSize);
pstmt.setInt(2, (pageNo-1)*pageSize);
rs = pstmt.executeQuery();
List<User> userList = new ArrayList<User>();

while(rs.next()){
User user = new User();
user.setUserId(rs.getString(1));
user.setUserName(rs.getString(2));
user.setPassword(rs.getString(3));
user.setContackTel(rs.getString(4));
user.setEmail(rs.getString(5));
user.setCreateDate(rs.getTimestamp("creat_date"));
userList.add(user);
}
pageModel = new PageModel<User>();
pageModel.setList(userList);
pageModel.setPageNo(pageNo);
pageModel.setPageSize(pageSize);
pageModel.setTotalPage((totalRecords+pageSize-1)/pageSize);
} catch (SQLException e) {
e.printStackTrace();
}finally{
DbUtil.close(rs);
DbUtil.close(pstmt);
DbUtil.close(conn);
DbUtil.close(stmt);
}

return pageModel;
}