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

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

紧接着上篇—分页技术原理与实现之分页的意义及方法（一） ，本篇继续分析分页技术。上篇讲的是分页技术的简单原理与介绍，这篇深入分析一下分页技术的代码实现。

上篇最后讲到了分页的最佳实现是在数据库层进行分页，而且不同的数据库有不同的分页实现，比如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()

+ " truename >/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 zKujUt(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。

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

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。