分页技术原理与实现之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。
到此一个简单的代码实现就完成了。
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持。
上一篇: PHP构造二叉树算法示例