Oracle下的Java分页功能_动力节点Java学院整理

就如平时我们很在分页中看到的，分页的时候返回的不仅包括查询的结果集(list)，而且还包括总的页数(pagenum)、当前第几页(pageno)等等信息，所以我们封装一个查询结果pagemodel类，代码如下：

package com.bjpowernode.test; 
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 com.bjpowernode.test; 
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 com.bjpowernode.test; 
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 com.bjpowernode.test; 
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 com.bjpowernode.test; 
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代码是封装数据库连接操作的代码，如下：

1.package com.bjpowernode.test;    

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。

到此一个简单的代码实现就完成了。

总结

以上所述是小编给大家介绍的oracle下的java分页功能，希望对大家有所帮助