分頁技術(shù)原理與實(shí)現(xiàn)之Java+Oracle代碼實(shí)現(xiàn)分頁(二)

更新時(shí)間：2016年06月12日 09:55:32 作者：KaneTing

這篇文章主要介紹了分頁技術(shù)原理與實(shí)現(xiàn)的第二篇：Java+Oracle代碼實(shí)現(xiàn)分頁，感興趣的小伙伴們可以參考一下

緊接著上篇—分頁技術(shù)原理與實(shí)現(xiàn)之分頁的意義及方法（一），本篇繼續(xù)分析分頁技術(shù)。上篇講的是分頁技術(shù)的簡單原理與介紹，這篇深入分析一下分頁技術(shù)的代碼實(shí)現(xiàn)。
上篇最后講到了分頁的最佳實(shí)現(xiàn)是在數(shù)據(jù)庫層進(jìn)行分頁，而且不同的數(shù)據(jù)庫有不同的分頁實(shí)現(xiàn)，比如Oracle是用三層sql嵌套實(shí)現(xiàn)分頁的、MySQL是用limit關(guān)鍵字實(shí)現(xiàn)的(上篇已講到)。
這篇以Java+Oracle為基礎(chǔ)，講解代碼層的實(shí)現(xiàn)。
就如平時(shí)我們很在分頁中看到的，分頁的時(shí)候返回的不僅包括查詢的結(jié)果集(List)，而且還包括總的頁數(shù)(pageNum)、當(dāng)前第幾頁(pageNo)等等信息，所以我們封裝一個(gè)查詢結(jié)果PageModel類，代碼如下：

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

 public List<E> getList() {
 return list;
 }

 public void setList(List<E> 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 "";
 }
}

其中使用泛型是為了能使的該分頁類能進(jìn)行重用，比如在查詢用戶時(shí)可以封裝User對(duì)象、在查詢財(cái)務(wù)中的流向單時(shí)可以封裝流向單FlowCard類。
我們以查詢用戶為例，用戶選擇查詢條件，首先調(diào)用Servlet獲取查詢參數(shù)，然后請(qǐng)求業(yè)務(wù)邏輯層取得分頁封裝結(jié)果類。業(yè)務(wù)邏輯調(diào)用Dao層取得結(jié)果集、取得中記錄數(shù)封裝成分頁類。最后Servlet將結(jié)果設(shè)置到j(luò)sp頁面顯示。
首先來講解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.取得頁面參數(shù)并構(gòu)造參數(shù)對(duì)象
 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.調(diào)用業(yè)務(wù)邏輯取得結(jié)果集
 UserInfoManage userInfoManage = new UserInfoManage();
 PageModel<UserInfo> pagination = userInfoManage.userBasicSearch(u,
 pageNo, pageSize);
 List<UserInfo> userList = pagination.getList();

 // 3.封裝返回結(jié)果
 StringBuffer resultXML = new StringBuffer();
 try {
 resultXML.append("<?xml version='1.0' encoding='gb18030'?>/n");
 resultXML.append("<root>/n");
 for (Iterator<UserInfo> iterator = userList.iterator(); iterator
 .hasNext();) {
 UserInfo userInfo = iterator.next();
 resultXML.append("<data>/n");
 resultXML.append("/t<id>" + userInfo.getId() + "</id>/n");
 resultXML.append("/t<truename>" + userInfo.getTruename()
 + "</ truename >/n");
 resultXML.append("/t<sex>" + userInfo.getSex() + "</sex>/n");
 resultXML.append("/t<home>" + userInfo.getHome() + "</home>/n");
 resultXML.append("</data>/n");
 }
 resultXML.append("<pagination>/n");
 resultXML.append("/t<total>" + pagination.getTotalPage()
 + "</total>/n");
 resultXML.append("/t<start>" + pagination.getFirstPage()
 + "</start>/n");
 resultXML.append("/t<end>" + pagination.getLastPage() + "</end>/n");
 resultXML.append("/t<pageno>" + pagination.getPageNo()
 + "</pageno>/n");
 resultXML.append("</pagination>/n");

 resultXML.append("</root>/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對(duì)象代碼如下：

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;
 }
}

接著是業(yè)務(wù)邏輯層代碼，代碼如下：

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<UserInfo> userBasicSearch(UserInfo u, int pageNo,
 int pageSize) throws Exception {
 Connection connection = null;
 PageModel<UserInfo> pagination = new PageModel<UserInfo>();
 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為數(shù)據(jù)庫的連接封裝類。
最后是Dao層代碼實(shí)現(xiàn)，代碼如下：

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<UserInfo> getUserList(UserInfo userInfo, int pageNo,
 int pageSize) throws Exception {
 PreparedStatement pstmt = null;
 ResultSet rs = null;
 List<UserInfo> 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<UserInfo>();
 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將得到的結(jié)果返回給jsp頁面顯示出來。
注：其中DBUtility代碼是封裝數(shù)據(jù)庫連接操作的代碼，如下：

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<Connection> threadLocal = new ThreadLocal<Connection>();

 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;
 }

 // 封裝設(shè)置Connection自動(dòng)提交
 public static void setAutoCommit(Connection conn, Boolean flag) {
 try {
 conn.setAutoCommit(flag);
 } catch (SQLException e) {
 e.printStackTrace();
 }
 }

 // 設(shè)置事務(wù)提交
 public static void commit(Connection conn) {
 try {
 conn.commit();
 } catch (SQLException e) {
 e.printStackTrace();
 }
 }

 // 封裝設(shè)置Connection回滾
 public static void rollBack(Connection conn) {
 try {
 conn.rollback();
 } catch (SQLException e) {
 e.printStackTrace();
 }
 }

 // 封裝關(guān)閉Connection、PreparedStatement、ResultSet的函數(shù)
 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是為了保證事務(wù)的一致，使得同一個(gè)線程的所有數(shù)據(jù)庫操作使用同一個(gè)Connection。
到此一個(gè)簡單的代碼實(shí)現(xiàn)就完成了。

以上就是本文的全部內(nèi)容，希望對(duì)大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。

您可能感興趣的文章: