关于分页查询,大家并不陌生,easyui中封装的datagrid将表格的一系列操作运用的淋漓尽致,分页查询便是其中之一。然而,我们却疏于了解分页查询的基本实现原理,它究竟是如何实现的,今天就带大家一起来探个究竟!
看一下界面的实现效果:
看似很复杂的事情,其实实现它最核心的东西有两个:
一、实现分页查询的SQL语句
-
select user_id,user_name,password,contact_Tel,email,create_date
-
from(
-
select rownum rn,user_id,user_name,password,contact_tel,email,create_date
-
from (
-
select user_id,user_name,password,contact_tel,email,create_date
-
from t_user where user_id <> 'root' order by user_id")
-
where rownum <= ? )
-
-
where rn> ?
无论做什么这些最底层的东西还是离不开的,所以最原始的SQL语句我们还是应该了解的。
二、实现分页查询的实体
-
package com.bjpowernode.drp.util;
-
-
import java.util.List;
-
-
public class PageModel<E> {
-
private List<E> list;
-
private int totalRecords;
-
private int pageSize;
-
private int pageNo;
-
public List<E> getList() {
-
return list;
-
}
-
public void setList(List<E> list) {
-
this.list = list;
-
}
-
-
-
-
public int getTotalPages() {
-
return (totalRecords+pageSize -1)/pageSize;
-
}
-
-
-
-
-
public int getTopPageNo() {
-
return 1;
-
}
-
-
-
-
-
-
public int getBottomPageNo(){
-
return getTotalPages();
-
}
-
-
-
-
-
-
public int getPreviousPageNo(){
-
if(pageNo<=1){
-
return 1;
-
}
-
return pageNo-1;
-
-
}
-
-
public int getNextPageNo(){
-
if(pageNo>=getBottomPageNo()){
-
return getBottomPageNo();
-
}
-
return pageNo+1;
-
}
-
public void setTotalRecords(int totalRecords) {
-
this.totalRecords = totalRecords;
-
}
-
-
public int getTotalPages(int totalRecords){
-
return totalRecords;
-
}
-
public int getPageSize() {
-
return pageSize;
-
}
-
public void setPageSize(int pageSize) {
-
this.pageSize = pageSize;
-
}
-
public int getPageNo() {
-
return pageNo;
-
}
-
-
public void setPageNo(int pageNo) {
-
this.pageNo = pageNo;
-
}
-
-
}
以上已经把分页查询的核心介绍完了,接下来只需要我们把我们的业务逻辑和我们上面的这两个核心结合成一条线,这样,分页查询的功能就可以实现了!
三、与业务逻辑结合
业务逻辑实现:
-
-
-
-
-
-
-
public PageModel<User> findUserList(int pageNo,int pageSize){
-
StringBuffer sbSql=new StringBuffer();
-
sbSql.append("select user_id,user_name,password,contact_Tel,email,create_date")
-
.append(" from")
-
.append("(")
-
.append("select rownum rn,user_id,user_name,password,contact_tel,email,create_date")
-
.append(" from")
-
.append("(")
-
.append("select user_id,user_name,password,contact_tel,email,create_date from t_user where user_id <> 'root' order by user_id")
-
.append(") where rownum <= ? ")
-
.append(") where rn> ? ");
-
-
PageModel<User> pageModel=null;
-
-
Connection conn=null;
-
PreparedStatement pstmt=null;
-
ResultSet rs=null;
-
User user=null;
-
try{
-
conn=DbUtil.getConnection();
-
pstmt = conn.prepareStatement(sbSql.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=new User();
-
user.setUserId(rs.getString("user_Id"));
-
user.setUserName(rs.getString("user_name"));
-
user.setPassword(rs.getString("password"));
-
user.setContactTel(rs.getString("contact_Tel"));
-
user.setEmail(rs.getString("email"));
-
user.setCreateDate(rs.getTimestamp("create_date"));
-
userList.add(user);
-
}
-
pageModel=new PageModel();
-
pageModel.setList(userList);
-
pageModel.setTotalRecords(getTotalRecords(conn));
-
pageModel.setPageSize(pageSize);
-
pageModel.setPageNo(pageNo);
-
-
}catch(SQLException e){
-
e.printStackTrace();
-
}finally{
-
-
DbUtil.close(rs);
-
DbUtil.close(pstmt);
-
DbUtil.close(conn);
-
-
}
-
-
return pageModel;
-
}
-
-
-
private int getTotalRecords(Connection conn)
-
throws SQLException{
-
String sql="select count(*) from t_user where user_Id <>'root'";
-
PreparedStatement pstmt=null;
-
ResultSet rs=null;
-
int count=0;
-
try{
-
pstmt=conn.prepareStatement(sql);
-
rs=pstmt.executeQuery();
-
rs.next();
-
count=rs.getInt(1);
-
-
}finally{
-
-
DbUtil.close(rs);
-
DbUtil.close(pstmt);
-
}
-
return count;
-
}
前端调用:
-
<%
-
int pageNo=1;
-
int pageSize=3;
-
String pageNoString=request.getParameter("pageNo");
-
if(pageNoString !=null){
-
pageNo=Integer.parseInt(pageNoString);
-
}
-
PageModel<User> pageModel=UserManager.getInstance().findUserList(pageNo, pageSize);
-
-
%>
至此,分页查询就基本上已经实现了,通过这次学习,维修网让我再次认识了分页查询的基本实现原理,以后再用easyui封装的datagrid的时候再也不用迷茫了啊!
文章来源:http://blog.csdn.net/wangyy130/article/details/48138695
阅读(1038) | 评论(0) | 转发(0) |