欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2024-03-12 17:52:38
紧接着上篇— ,本篇继续分析分页技术。上篇讲的是分页技术的简单原理与介绍,这篇深入分析一下分页技术的代码实现。 上篇最后讲到了分页的最佳实现是在数据库层进行分页,而且不同...

紧接着上篇— ,本篇继续分析分页技术。上篇讲的是分页技术的简单原理与介绍,这篇深入分析一下分页技术的代码实现。
上篇最后讲到了分页的最佳实现是在数据库层进行分页,而且不同的数据库有不同的分页实现,比如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。
到此一个简单的代码实现就完成了。

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