分页技术原理与实现之Java+Oracle代码实现分页(二)

顺亿

2026-09-15 • 👁 0人浏览过

紧接着上篇—分页技术原理与实现之分页的意义及方法（一）

，本篇继续分析分页技术。上篇讲的是分页技术的简单原理与介绍，这篇深入分析一下分页技术的代码实现。

上篇最后讲到了分页的最佳实现是在数据库层进行分页，而且不同的数据库有不同的分页实现，比如Oracle是用三层sql嵌套实现分页的、MySQL是用limit关键字实现的(上篇已讲到)。

这篇以Java+Oracle为基础，讲解代码层的实现。

就如平时我们很在分页中看到的，分页的时候返回的不仅包括查询的结果集(List)，而且还包括总的页数(pageNum)、当前第几页(pageNo)等等信息，所以我们封装一个查询结果PageModel类，代码如下：

package kane;import java.util.List;public class PageModel {private List list;private int pageNo;private int pageSize;private int totalNum;private int totalPage;


public List getList() {return list;}
public void setList(List list) {this.list = list;}
public int getPageNo() {return pageNo;}
public void setPageNo(int pageNo) {this.pageNo = pageNo;}
public int getPageSize() {return pageSize;}
public void setPageSize(int pageSize) {this.pageSize = pageSize;}
public int getTotalNum() {return totalNum;}
public void setTotalNum(int totalNum) {this.totalNum = totalNum;setTotalPage((getTotalNum() % pageSize) == 0 ? (getTotalNum() / pageSize): (getTotalNum() / pageSize + 1));}
public int getTotalPage() {return totalPage;}
public void setTotalPage(int totalPage) {this.totalPage = totalPage;}
// 获取第一页public int getFirstPage() {return 1;}
// 获取最后页public int getLastPage() {return totalPage;}
// 获取前页public int getPrePage() {if (pageNo > 1) return pageNo - 1;return 1;}
// 获取后页public int getBackPage() {if (pageNo < totalPage) return pageNo + 1;return totalPage;}
// 判断'首页'及‘前页'是否可用public String isPreable() {if (pageNo == 1) return "disabled";return "";}
// 判断'尾页'及‘下页'是否可用public String isBackable() {if (pageNo == totalPage) return "disabled";return "";}}

其中使用泛型是为了能使的该分页类能进行重用，比如在查询用户时可以封装User对象、在查询财务中的流向单时可以封装流向单FlowCard类。

我们以查询用户为例，用户选择查询条件，首先调用Servlet获取查询参数，然后请求业务逻辑层取得分页封装结果类。业务逻辑调用Dao层取得结果集、取得中记录数封装成分页类。最后Servlet将结果设置到jsp页面显示。

首先来讲解Servlet，代码如下：

package kane;import java.io.*;import java.util.*;


import javax.servlet.ServletConfig;import javax.servlet.ServletException;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;
import kane.UserInfo;import kane.UserInfoManage;import kane.PageModel;
public class UserBasicSearchServlet extends HttpServlet {private static final long serialVersionUID = 1L;private int pageSize = 0;
@Override public void init(ServletConfig config) throws ServletException {pageSize = Integer.parseInt(config.getInitParameter("pageSize"));}
@Override protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {doPost(req, resp);}
@Override protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {// 1.取得页面参数并构造参数对象int pageNo = Integer.parseInt(req.getParameter("pageNo"));String sex = req.getParameter("gender");String home = req.getParameter("newlocation");String colleage = req.getParameter("colleage");String comingyear = req.getParameter("ComingYear");
UserInfo u = new UserInfo();u.setSex(sex);u.setHome(home);u.setColleage(colleage);u.setCy(comingyear);
// 2.调用业务逻辑取得结果集UserInfoManage userInfoManage = new UserInfoManage();PageModel pagination = userInfoManage.userBasicSearch(u,pageNo, pageSize);List userList = pagination.getList();
// 3.封装返回结果StringBuffer resultXML = new StringBuffer();try {resultXML.append("/n");resultXML.append("/n");for (Iterator iterator = userList.iterator(); iterator.hasNext();) {UserInfo userInfo = iterator.next();resultXML.append("/n");resultXML.append("/t" + userInfo.getId() + "/n");resultXML.append("/t" + userInfo.getTruename()+ "/n");resultXML.append("/t" + userInfo.getSex() + "/n");resultXML.append("/t" + userInfo.getHome() + "/n");resultXML.append("/n");}resultXML.append("/n");resultXML.append("/t" + pagination.getTotalPage()+ "/n");resultXML.append("/t" + pagination.getFirstPage()+ "/n");resultXML.append("/t" + pagination.getLastPage() + "/n");resultXML.append("/t" + pagination.getPageNo()+ "/n");resultXML.append("/n");
resultXML.append("/n");} catch (Exception e) {e.printStackTrace();}writeResponse(req, resp, resultXML.toString());
}

public void writeResponse(HttpServletRequest request,HttpServletResponse response, String result) throws IOException {response.setContentType("text/xml");response.setHeader("Cache-Control", "no-cache");response.setHeader("Content-Type", "text/xml; charset=gb18030");PrintWriter pw = response.getWriter();pw.write(result);pw.close();}}

其中User对象代码如下：

package kane;import java.util.Date;public class UserInfo {private int id;private String username;private String password;private String truename;private String sex;private Date birthday;private String home;private String colleage;private String comingYear;


public int getId() {return id;}
public void setId(int id) {this.id = id;}
public String getUsername() {return username;}
public void setUsername(String username) {this.username = username;}
public String getPassword() {return password;}
public void setPassword(String password) {this.password = password;}
public String getTruename() {return truename;}
public void setTruename(String truename) {this.truename = truename;}
public String getSex() {return sex;}
public void setSex(String sex) {this.sex = sex;}
public Date getBirthday() {return birthday;}
public void setBirthday(Date birthday) {this.birthday = birthday;}
public String getHome() {return home;}
public void setHome(String home) {this.home = home;}
public String getColleage() {return colleage;}
public void setColleage(String colleage) {this.colleage = colleage;}
public String getCy() {return comingYear;}

public void setCy(String cy) {this. comingYear= cy;}}接着是业务逻辑层代码，代码如下：

package kane;import java.sql.Connection;


import kane.DBUtility;import kane.PageModel;
public class UserInfoManage {private UserInfoDao userInfoDao = null;
public UserInfoManage () {userInfoDao = new UserInfoDao();}

public PageModel userBasicSearch(UserInfo u, int pageNo,int pageSize) throws Exception {Connection connection = null;PageModel pagination = new PageModel();try {connection = DBUtility.getConnection();DBUtility.setAutoCommit(connection, false);pagination.setList(userInfoDao.getUserList(u, pageNo, pageSize));pagination.setPageNo(pageNo);pagination.setPageSize(pageSize);pagination.setTotalNum(userInfoDao.getTotalNum(u));DBUtility.commit(connection);} catch (Exception e) {DBUtility.rollBack(connection);e.printStackTrace();throw new Exception();} finally {DBUtility.closeConnection();}return pagination;}}其中DBUtility为数据库的连接封装类。

最后是Dao层代码实现，代码如下：

package kane;


import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;
import kane.UserInfo;import kane.DBUtility;
public class UserInfoDao {
public List getUserList(UserInfo userInfo, int pageNo,int pageSize) throws Exception {PreparedStatement pstmt = null;ResultSet rs = null;List userList = null;try {String sql = "select * from(select rownum num,u.* from(select * from user_info where sex = ? and home like '"+ userInfo.getHome()+ "%"+ "' and colleage like '"+ userInfo.getColleage()+ "%"+ "' and comingyear like '"+ userInfo.getCy()+ "%"+ "' order by id) u where rownum<=?) where num>=?";userList = new ArrayList();Connection conn = DBUtility.getConnection();pstmt = conn.prepareStatement(sql);pstmt.setString(1, userInfo.getSex());pstmt.setInt(2, pageNo * pageSize);pstmt.setInt(3, (pageNo - 1) * pageSize + 1);rs = pstmt.executeQuery();while (rs.next()) {UserInfo user = new UserInfo();user.setId(rs.getInt("id"));user.setTruename(rs.getString("truename"));user.setSex(rs.getString("sex"));user.setHome(rs.getString("home"));userList.add(user);}} catch (SQLException e) {e.printStackTrace();throw new Exception(e);} finally {DBUtility.closeResultSet(rs);DBUtility.closePreparedStatement(pstmt);}return userList;}

public int getTotalNum(UserInfo userInfo) throws Exception {PreparedStatement pstmt = null;ResultSet rs = null;int count = 0;try {String sql = "select count(*) from user_info where sex=? and home like '"+ userInfo.getHome()+ "%"+ "' and colleage like '"+ userInfo.getColleage()+ "%"+ "' and comingyear like '"+ userInfo.getCy()+ "%" + "'";Connection conn = DBUtility.getConnection();pstmt = conn.prepareStatement(sql);pstmt.setString(1, userInfo.getSex());rs = pstmt.executeQuery();if (rs.next()) {count = rs.getInt(1);}} catch (SQLException e) {e.printStackTrace();throw new Exception(e);} finally {DBUtility.closeResultSet(rs);DBUtility.closePreparedStatement(pstmt);}return count;}}最后就是servlet将得到的结果返回给jsp页面显示出来。

注：

其中DBUtility代码是封装数据库连接操作的代码，如下：

package kane;


import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;
public class DBUtility {private static ThreadLocal threadLocal = new ThreadLocal();
public static Connection getConnection() {Connection conn = null;conn = threadLocal.get();if (conn == null) {try {Class.forName("oracle.jdbc.driver.OracleDriver");conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:oracle", "admin","admin");threadLocal.set(conn);} catch (ClassNotFoundException e) {e.printStackTrace();} catch (SQLException e) {e.printStackTrace();}}return conn;}
// 封装设置Connection自动提交public static void setAutoCommit(Connection conn, Boolean flag) {try {conn.setAutoCommit(flag);} catch (SQLException e) {e.printStackTrace();}}
// 设置事务提交public static void commit(Connection conn) {try {conn.commit();} catch (SQLException e) {e.printStackTrace();}}
// 封装设置Connection回滚public static void rollBack(Connection conn) {try {conn.rollback();} catch (SQLException e) {e.printStackTrace();}}
// 封装关闭Connection、PreparedStatement、ResultSet的函数public static void closeConnection() {Connection conn = threadLocal.get();try {if (conn != null) {conn.close();conn = null;threadLocal.remove();}} catch (SQLException e) {e.printStackTrace();}
}
public static void closePreparedStatement(PreparedStatement pstmt) {try {if (pstmt != null) {pstmt.close();pstmt = null;}} catch (SQLException e) {e.printStackTrace();}}
public static void closeResultSet(ResultSet rs) {try {if (rs != null) {rs.close();rs = null;}} catch (SQLException e) {e.printStackTrace();}}}

使用ThreadLocal是为了保证事务的一致，使得同一个线程的所有数据库操作使用同一个Connection。

到此一个简单的代码实现就完成了。

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持脚本之家。

您可能感兴趣的文章:oracle,mysql,SqlServer三种数据库的分页查询的实例Oracle实现分页查询的SQL语法汇总Oracle中使用Rownum分页详细例子Oracle row_number() over()解析函数高效实现分页oracle 分页很棒的sql语句浅析Oracle和Mysql分页的区别Oracle与Mysql主键、索引及分页的区别小结Oracle、MySQL和SqlServe三种数据库分页查询语句的区别介绍oracle分页存储过程 oracle存储过程实例oracle实现一对多数据分页查询筛选示例代码

其中User对象代码如下：

注：

相关文章