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

紧接着上篇— ，本篇继续分析分页技术。上篇讲的是分页技术的简单原理与介绍，这篇深入分析一下分页技术的代码实现。
上篇最后讲到了分页的最佳实现是在数据库层进行分页，而且不同的数据库有不同的分页实现，比如oracle是用三层sql嵌套实现分页的、mysql是用limit关键字实现的(上篇已讲到)。
这篇以java+oracle为基础，讲解代码层的实现。
就如平时我们很在分页中看到的，分页的时候返回的不仅包括查询的结果集(list)，而且还包括总的页数(pagenum)、当前第几页(pageno)等等信息，所以我们封装一个查询结果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 "";
 }
}

其中使用泛型是为了能使的该分页类能进行重用，比如在查询用户时可以封装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<userinfo> pagination = userinfomanage.userbasicsearch(u,
 pageno, pagesize);
 list<userinfo> userlist = pagination.getlist();

 // 3.封装返回结果
 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对象代码如下：

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<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为数据库的连接封装类。
最后是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<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将得到的结果返回给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<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;
 }

 // 封装设置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。
到此一个简单的代码实现就完成了。

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