分页查询的实现多种多样,今天介绍一种在Java代码里面封装的分页方法。
封装分页类:PageModel
package com.xxjstgb.drp.util;
import java.util.List;
/**
* 封装分页信息
* @author liuzhengquan
*
*/
public class PageModel<E> {
//结果集
private List<E> list;
//查询记录数
private int totalRecords;
//每页多少条数据
private int pageSize;
//第几页
private int pageNo;
/**
* 总页数
* @return
*/
public int getTotalPages(){
return(totalRecords+pageSize-1)/pageSize;
}
/**
* 取得首页
* @return
*/
public int getTopPageNo(){
return 1;
}
public List<E> getList() {
return list;
}
/**
* 上一页
* @return
*/
public int getPreviousPageNo(){
if(pageNo<=1){
return 1;
}
return pageNo-1;
}
/**
* 下一页
* @return
*/
public int getNextPageNo(){
if(pageNo>=getBottomPageNo()){
return getBottomPageNo();
}
return pageNo+1;
}
/**
* 取得尾页
* @return
*/
public int getBottomPageNo(){
return getTotalPages();
}
public void setList(List<E> list) {
this.list = list;
}
public int getTotalRecords() {
return totalRecords;
}
public void setTotalRecords(int totalRecords) {
this.totalRecords = 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;
}
}
分页查询的方法:使用拼接SQL语句,查询出分页的结果,并将查询结果返回给封装好的PageModel。
package com.xxjstgb.drp.sysmgr.manager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.ArrayList;
import com.xxjstgb.drp.sysmgr.domain.User;
import com.xxjstgb.drp.util.Dbutil;
import com.xxjstgb.drp.util.PageModel;
/**
* 分页查询信息
* @author liuzhengquan
*/
public class testUser {
/**
* 分页查询用户列表
* @param pageNo 第几页
* @param pageSize 每页多少条数据
* @return pageModel
* @throws SQLException
*/
public PageModel<User> testUserList(int pageNo, int pageSize) throws SQLException {
StringBuffer sbStr=new StringBuffer();
sbStr
.append("select * from ")
.append("( ")
.append("select rownum rn, user_id,user_name,password from ")
.append("( ")
.append("select user_id,user_name,password from t_user order by user_id ")
.append(") ")
.append("where rownum <= ? ")
.append(") ")
.append("where rn> ? ");
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
//User 为实体,包含userId、UserName、password三个属性
PageModel<User> pageModel = null;
try {
conn = Dbutil.getConnection();
pstmt = conn.prepareStatement(sbStr.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("user_id"));
user.setUserName(rs.getString("user_name"));
user.setPassword(rs.getString("password"));
userList.add(user);
}
pageModel = new PageModel<User>();
pageModel.setList(userList);
pageModel.setTotalRecords(getTotalRecords(conn));
pageModel.setPageSize(pageSize);
pageModel.setPageNo(pageNo);
} catch (SQLException e) {
e.printStackTrace();
} finally {
rs.close();
pstmt.close();
conn.close();
}
return pageModel;
}
/**
* 取得总记录数
* @param conn 连接
* @return 返回记录数
* @throws SQLException
*/
private int getTotalRecords(Connection conn) throws SQLException {
String strSql = "select count(*) from t_user";
PreparedStatement pstmt = null;
ResultSet rs = null;
int count = 0;
try {
pstmt = conn.prepareStatement(strSql);
rs = pstmt.executeQuery();
rs.next(); // 找不到时为0,找到为相应的值
count = rs.getInt(1);
} catch (SQLException e) {
e.printStackTrace();
} finally {
rs.close();
pstmt.close();
}
return count;
}
}
将每一页面容纳信息的条数配置在配置在context-param里面,取的时候通过ServletContext取得。
<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.4"
xmlns="http://java.sun.com/xml/ns/j2ee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee
http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd">
<context-param>
<param-name>page-size</param-name>
<param-value>3</param-value>
</context-param>
</web-app>
调用:
<%@ page import="com.xxjstgb.drp.sysmgr.domain.*" %>
<%@ page import="com.xxjstgb.drp.sysmgr.manager.*" %>
<%@ page import="com.xxjstgb.drp.util.*" %>
<%@ page import="java.util.*" %>
<%@ page import="java.text.*" %>
<%
int pageNo = 1;
String pageNoString = request.getParameter("pageNo");
int pageSize = Integer.parseInt(this.getServletContext()
.getInitParameter("page-size"));
if(pageNoString !=null){
pageNo=Integer.parseInt(pageNoString);
}
testUser tUser=new testUser();
PageModel<User> pageModel=tUser.testUserList(pageNo,pageSize);
%>
上下翻页:
<script type="text/javascript">
//首页
function topPage() {
window.self.location="user_maint.jsp?pageNo=<%=pageModel.getTopPageNo()%>";
//user_maint.jsp?pageNo=<%=pageModel.getTopPageNo()%>
//alert("");
} //=<%=pageModel.getTopPageNo()%>";
//上一页
function previousPage() {
window.self.location="user_maint.jsp?pageNo=<%=pageModel.getPreviousPageNo()%>";
}
//下一页
function nextPage() {
window.self.location="user_maint.jsp?pageNo=<%=pageModel.getNextPageNo()%>";
}
//尾页
function bottomPage() {
//getBottomPageNo
window.self.location="user_maint.jsp?pageNo=<%=pageModel.getBottomPageNo()%>";
}
</script>
至此,分页查询就完成了。使用分页查询的地方非常多,这种封装的思想特别好。如果我们不进行封装,一些代码就需要我们一遍一遍的重复去写。
好的代码需要我们用心推敲,用“不将就”的心态写代码、思考代码,我相信也只有这样,才能提升我们的编码水平。