分页技术原理与实现之Java+Oracle代码实现分页(二)
紧接着上篇—分页技术原理与实现之分页的意义及方法（一），本篇继续分析分页技术。上篇讲的是分页技术的简单原理与介绍，这篇深入分析一下分页技术的代码实现。
上篇最后讲到了分页的最佳实现是在数据库层进行分页，而且不同的数据库有不同的分页实现，比如Oracle是用三层sql嵌套实现分页的、MySQL是用limit关键字实现的(上篇已讲到)。
这篇以Java+Oracle为基础，讲解代码层的实现。
就如平时我们很在分页中看到的，分页的时候返回的不仅包括查询的结果集(List)，而且还包括总的页数(pageNum)、当前第几页(pageNo)等等信息，所以我们封装一个查询结果PageModel类，代码如下：
packagekane;
importjava.util.List;
publicclassPageModel<E>{
privateList<E>list;
privateintpageNo;
privateintpageSize;
privateinttotalNum;
privateinttotalPage;
publicList<E>getList(){
returnlist;
}
publicvoidsetList(List<E>list){
this.list=list;
}
publicintgetPageNo(){
returnpageNo;
}
publicvoidsetPageNo(intpageNo){
this.pageNo=pageNo;
}
publicintgetPageSize(){
returnpageSize;
}
publicvoidsetPageSize(intpageSize){
this.pageSize=pageSize;
}
publicintgetTotalNum(){
returntotalNum;
}
publicvoidsetTotalNum(inttotalNum){
this.totalNum=totalNum;
setTotalPage((getTotalNum()%pageSize)==0?(getTotalNum()/pageSize)
:(getTotalNum()/pageSize+1));
}
publicintgetTotalPage(){
returntotalPage;
}
publicvoidsetTotalPage(inttotalPage){
this.totalPage=totalPage;
}
//获取第一页
publicintgetFirstPage(){
return1;
}
//获取最后页
publicintgetLastPage(){
returntotalPage;
}
//获取前页
publicintgetPrePage(){
if(pageNo>1)
returnpageNo-1;
return1;
}
//获取后页
publicintgetBackPage(){
if(pageNo<totalPage)
returnpageNo+1;
returntotalPage;
}
//判断'首页'及‘前页'是否可用
publicStringisPreable(){
if(pageNo==1)
return"disabled";
return"";
}
//判断'尾页'及‘下页'是否可用
publicStringisBackable(){
if(pageNo==totalPage)
return"disabled";
return"";
}
}
其中使用泛型是为了能使的该分页类能进行重用，比如在查询用户时可以封装User对象、在查询财务中的流向单时可以封装流向单FlowCard类。
我们以查询用户为例，用户选择查询条件，首先调用Servlet获取查询参数，然后请求业务逻辑层取得分页封装结果类。业务逻辑调用Dao层取得结果集、取得中记录数封装成分页类。最后Servlet将结果设置到jsp页面显示。
首先来讲解Servlet，代码如下：
packagekane;
importjava.io.*;
importjava.util.*;
importjavax.servlet.ServletConfig;
importjavax.servlet.ServletException;
importjavax.servlet.http.HttpServlet;
importjavax.servlet.http.HttpServletRequest;
importjavax.servlet.http.HttpServletResponse;
importkane.UserInfo;
importkane.UserInfoManage;
importkane.PageModel;
publicclassUserBasicSearchServletextendsHttpServlet{
privatestaticfinallongserialVersionUID=1L;
privateintpageSize=0;
@Override
publicvoidinit(ServletConfigconfig)throwsServletException{
pageSize=Integer.parseInt(config.getInitParameter("pageSize"));
}
@Override
protectedvoiddoGet(HttpServletRequestreq,HttpServletResponseresp)
throwsServletException,IOException{
doPost(req,resp);
}
@Override
protectedvoiddoPost(HttpServletRequestreq,HttpServletResponseresp)
throwsServletException,IOException{
//1.取得页面参数并构造参数对象
intpageNo=Integer.parseInt(req.getParameter("pageNo"));
Stringsex=req.getParameter("gender");
Stringhome=req.getParameter("newlocation");
Stringcolleage=req.getParameter("colleage");
Stringcomingyear=req.getParameter("ComingYear");
UserInfou=newUserInfo();
u.setSex(sex);
u.setHome(home);
u.setColleage(colleage);
u.setCy(comingyear);
//2.调用业务逻辑取得结果集
UserInfoManageuserInfoManage=newUserInfoManage();
PageModel<UserInfo>pagination=userInfoManage.userBasicSearch(u,
pageNo,pageSize);
List<UserInfo>userList=pagination.getList();
//3.封装返回结果
StringBufferresultXML=newStringBuffer();
try{
resultXML.append("<?xmlversion='1.0'encoding='gb18030'?>/n");
resultXML.append("<root>/n");
for(Iterator<UserInfo>iterator=userList.iterator();iterator
.hasNext();){
UserInfouserInfo=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(Exceptione){
e.printStackTrace();
}
writeResponse(req,resp,resultXML.toString());
}
publicvoidwriteResponse(HttpServletRequestrequest,
HttpServletResponseresponse,Stringresult)throwsIOException{
response.setContentType("text/xml");
response.setHeader("Cache-Control","no-cache");
response.setHeader("Content-Type","text/xml;charset=gb18030");
PrintWriterpw=response.getWriter();
pw.write(result);
pw.close();
}
}
其中User对象代码如下：
packagekane;
importjava.util.Date;
publicclassUserInfo{
privateintid;
privateStringusername;
privateStringpassword;
privateStringtruename;
privateStringsex;
privateDatebirthday;
privateStringhome;
privateStringcolleage;
privateStringcomingYear;
publicintgetId(){
returnid;
}
publicvoidsetId(intid){
this.id=id;
}
publicStringgetUsername(){
returnusername;
}
publicvoidsetUsername(Stringusername){
this.username=username;
}
publicStringgetPassword(){
returnpassword;
}
publicvoidsetPassword(Stringpassword){
this.password=password;
}
publicStringgetTruename(){
returntruename;
}
publicvoidsetTruename(Stringtruename){
this.truename=truename;
}
publicStringgetSex(){
returnsex;
}
publicvoidsetSex(Stringsex){
this.sex=sex;
}
publicDategetBirthday(){
returnbirthday;
}
publicvoidsetBirthday(Datebirthday){
this.birthday=birthday;
}
publicStringgetHome(){
returnhome;
}
publicvoidsetHome(Stringhome){
this.home=home;
}
publicStringgetColleage(){
returncolleage;
}
publicvoidsetColleage(Stringcolleage){
this.colleage=colleage;
}
publicStringgetCy(){
returncomingYear;
}
publicvoidsetCy(Stringcy){
this.comingYear=cy;
}
}
接着是业务逻辑层代码，代码如下：
packagekane;
importjava.sql.Connection;
importkane.DBUtility;
importkane.PageModel;
publicclassUserInfoManage{
privateUserInfoDaouserInfoDao=null;
publicUserInfoManage(){
userInfoDao=newUserInfoDao();
}
publicPageModel<UserInfo>userBasicSearch(UserInfou,intpageNo,
intpageSize)throwsException{
Connectionconnection=null;
PageModel<UserInfo>pagination=newPageModel<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(Exceptione){
DBUtility.rollBack(connection);
e.printStackTrace();
thrownewException();
}finally{
DBUtility.closeConnection();
}
returnpagination;
}
}
其中DBUtility为数据库的连接封装类。
最后是Dao层代码实现，代码如下：
packagekane;
importjava.sql.Connection;
importjava.sql.PreparedStatement;
importjava.sql.ResultSet;
importjava.sql.SQLException;
importjava.util.ArrayList;
importjava.util.List;
importkane.UserInfo;
importkane.DBUtility;
publicclassUserInfoDao{
publicList<UserInfo>getUserList(UserInfouserInfo,intpageNo,
intpageSize)throwsException{
PreparedStatementpstmt=null;
ResultSetrs=null;
List<UserInfo>userList=null;
try{
Stringsql="select*from(selectrownumnum,u.*from(select*fromuser_infowheresex=?andhomelike'"
+userInfo.getHome()
+"%"
+"'andcolleagelike'"
+userInfo.getColleage()
+"%"
+"'andcomingyearlike'"
+userInfo.getCy()
+"%"
+"'orderbyid)uwhererownum<=?)wherenum>=?";
userList=newArrayList<UserInfo>();
Connectionconn=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()){
UserInfouser=newUserInfo();
user.setId(rs.getInt("id"));
user.setTruename(rs.getString("truename"));
user.setSex(rs.getString("sex"));
user.setHome(rs.getString("home"));
userList.add(user);
}
}catch(SQLExceptione){
e.printStackTrace();
thrownewException(e);
}finally{
DBUtility.closeResultSet(rs);
DBUtility.closePreparedStatement(pstmt);
}
returnuserList;
}
publicintgetTotalNum(UserInfouserInfo)throwsException{
PreparedStatementpstmt=null;
ResultSetrs=null;
intcount=0;
try{
Stringsql="selectcount(*)fromuser_infowheresex=?andhomelike'"
+userInfo.getHome()
+"%"
+"'andcolleagelike'"
+userInfo.getColleage()
+"%"
+"'andcomingyearlike'"
+userInfo.getCy()+"%"+"'";
Connectionconn=DBUtility.getConnection();
pstmt=conn.prepareStatement(sql);
pstmt.setString(1,userInfo.getSex());
rs=pstmt.executeQuery();
if(rs.next()){
count=rs.getInt(1);
}
}catch(SQLExceptione){
e.printStackTrace();
thrownewException(e);
}finally{
DBUtility.closeResultSet(rs);
DBUtility.closePreparedStatement(pstmt);
}
returncount;
}
}
最后就是servlet将得到的结果返回给jsp页面显示出来。
注：其中DBUtility代码是封装数据库连接操作的代码，如下：
packagekane;
importjava.sql.Connection;
importjava.sql.DriverManager;
importjava.sql.PreparedStatement;
importjava.sql.ResultSet;
importjava.sql.SQLException;
publicclassDBUtility{
privatestaticThreadLocal<Connection>threadLocal=newThreadLocal<Connection>();
publicstaticConnectiongetConnection(){
Connectionconn=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(ClassNotFoundExceptione){
e.printStackTrace();
}catch(SQLExceptione){
e.printStackTrace();
}
}
returnconn;
}
//封装设置Connection自动提交
publicstaticvoidsetAutoCommit(Connectionconn,Booleanflag){
try{
conn.setAutoCommit(flag);
}catch(SQLExceptione){
e.printStackTrace();
}
}
//设置事务提交
publicstaticvoidcommit(Connectionconn){
try{
conn.commit();
}catch(SQLExceptione){
e.printStackTrace();
}
}
//封装设置Connection回滚
publicstaticvoidrollBack(Connectionconn){
try{
conn.rollback();
}catch(SQLExceptione){
e.printStackTrace();
}
}
//封装关闭Connection、PreparedStatement、ResultSet的函数
publicstaticvoidcloseConnection(){
Connectionconn=threadLocal.get();
try{
if(conn!=null){
conn.close();
conn=null;
threadLocal.remove();
}
}catch(SQLExceptione){
e.printStackTrace();
}
}
publicstaticvoidclosePreparedStatement(PreparedStatementpstmt){
try{
if(pstmt!=null){
pstmt.close();
pstmt=null;
}
}catch(SQLExceptione){
e.printStackTrace();
}
}
publicstaticvoidcloseResultSet(ResultSetrs){
try{
if(rs!=null){
rs.close();
rs=null;
}
}catch(SQLExceptione){
e.printStackTrace();
}
}
}
使用ThreadLocal是为了保证事务的一致，使得同一个线程的所有数据库操作使用同一个Connection。
到此一个简单的代码实现就完成了。
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持毛票票。