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

2024-08-29 14:00:27

字体：大中小

来源：转载

供稿：网友

就如平时我们很在分页中看到的，分页的时候返回的不仅包括查询的结果集(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分页功能，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对VeVb武林网网站的支持！

注：相关教程知识阅读请移步到oracle教程频道。

上一篇：Win7 64位下PowerDesigner连接64位Oracle11g数据库

下一篇：Oracle addBatch()用法实例详解