查看: 1635|回复: 0

[Oracle数据库] Oracle下的Java分页功能_动力节点Java学院整理

发表于 2017-10-10 08:00:04
句号论坛

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

  1. package com.bjpowernode.test;
  2. import java.util.List;
  3. public class PageModel<E> {
  4. private List<E> list;
  5. private int pageNo;
  6. private int pageSize;
  7. private int totalNum;
  8. private int totalPage;
  9. public List<E> getList() {
  10. return list;
  11. }
  12. public void setList(List<E> list) {
  13. this.list = list;
  14. }
  15. public int getPageNo() {
  16. return pageNo;
  17. }
  18. public void setPageNo(int pageNo) {
  19. this.pageNo = pageNo;
  20. }
  21. public int getPageSize() {
  22. return pageSize;
  23. }
  24. public void setPageSize(int pageSize) {
  25. this.pageSize = pageSize;
  26. }
  27. public int getTotalNum() {
  28. return totalNum;
  29. }
  30. public void setTotalNum(int totalNum) {
  31. this.totalNum = totalNum;
  32. setTotalPage((getTotalNum() % pageSize) == 0 ? (getTotalNum() / pageSize)
  33. : (getTotalNum() / pageSize + 1));
  34. }
  35. public int getTotalPage() {
  36. return totalPage;
  37. }
  38. public void setTotalPage(int totalPage) {
  39. this.totalPage = totalPage;
  40. }
  41. // 获取第一页
  42. public int getFirstPage() {
  43. return 1;
  44. }
  45. // 获取最后页
  46. public int getLastPage() {
  47. return totalPage;
  48. }
  49. // 获取前页
  50. public int getPrePage() {
  51. if (pageNo > 1)
  52. return pageNo - 1;
  53. return 1;
  54. }
  55. // 获取后页
  56. public int getBackPage() {
  57. if (pageNo < totalPage)
  58. return pageNo + 1;
  59. return totalPage;
  60. }
  61. // 判断'首页'及‘前页'是否可用
  62. public String isPreable() {
  63. if (pageNo == 1)
  64. return "disabled";
  65. return "";
  66. }
  67. // 判断'尾页'及‘下页'是否可用
  68. public String isBackable() {
  69. if (pageNo == totalPage)
  70. return "disabled";
  71. return "";
  72. }
  73. }
复制代码

其中使用泛型是为了能使的该分页类能进行重用,比如在查询用户时可以封装User对象、在查询财务中的流向单时可以封装流向单FlowCard类。

我们以查询用户为例,用户选择查询条件,首先调用Servlet获取查询参数,然后请求业务逻辑层取得分页封装结果类。业务逻辑调用Dao层取得结果集、取得中记录数封装成分页类。最后Servlet将结果设置到jsp页面显示。

首先来讲解Servlet,代码如下:

  1. package com.bjpowernode.test;
  2. import java.io.*;
  3. import java.util.*;
  4. import javax.servlet.ServletConfig;
  5. import javax.servlet.ServletException;
  6. import javax.servlet.http.HttpServlet;
  7. import javax.servlet.http.HttpServletRequest;
  8. import javax.servlet.http.HttpServletResponse;
  9. import kane.UserInfo;
  10. import kane.UserInfoManage;
  11. import kane.PageModel;
  12. public class UserBasicSearchServlet extends HttpServlet {
  13. private static final long serialVersionUID = 1L;
  14. private int pageSize = 0;
  15. @Override
  16. public void init(ServletConfig config) throws ServletException {
  17. pageSize = Integer.parseInt(config.getInitParameter("pageSize"));
  18. }
  19. @Override
  20. protected void doGet(HttpServletRequest req, HttpServletResponse resp)
  21. throws ServletException, IOException {
  22. doPost(req, resp);
  23. }
  24. @Override
  25. protected void doPost(HttpServletRequest req, HttpServletResponse resp)
  26. throws ServletException, IOException {
  27. // 1.取得页面参数并构造参数对象
  28. int pageNo = Integer.parseInt(req.getParameter("pageNo"));
  29. String sex = req.getParameter("gender");
  30. String home = req.getParameter("newlocation");
  31. String colleage = req.getParameter("colleage");
  32. String comingyear = req.getParameter("ComingYear");
  33. UserInfo u = new UserInfo();
  34. u.setSex(sex);
  35. u.setHome(home);
  36. u.setColleage(colleage);
  37. u.setCy(comingyear);
  38. // 2.调用业务逻辑取得结果集
  39. UserInfoManage userInfoManage = new UserInfoManage();
  40. PageModel<UserInfo> pagination = userInfoManage.userBasicSearch(u,
  41. pageNo, pageSize);
  42. List<UserInfo> userList = pagination.getList();
  43. // 3.封装返回结果
  44. StringBuffer resultXML = new StringBuffer();
  45. try {
  46. resultXML.append("<?xml version='1.0' encoding='gb18030'?>/n");
  47. resultXML.append("<root>/n");
  48. for (Iterator<UserInfo> iterator = userList.iterator(); iterator
  49. .hasNext();) {
  50. UserInfo userInfo = iterator.next();
  51. resultXML.append("<data>/n");
  52. resultXML.append("/t<id>" + userInfo.getId() + "</id>/n");
  53. resultXML.append("/t<truename>" + userInfo.getTruename()
  54. + "</ truename >/n");
  55. resultXML.append("/t<sex>" + userInfo.getSex() + "</sex>/n");
  56. resultXML.append("/t<home>" + userInfo.getHome() + "</home>/n");
  57. resultXML.append("</data>/n");
  58. }
  59. resultXML.append("<pagination>/n");
  60. resultXML.append("/t<total>" + pagination.getTotalPage()
  61. + "</total>/n");
  62. resultXML.append("/t<start>" + pagination.getFirstPage()
  63. + "</start>/n");
  64. resultXML.append("/t<end>" + pagination.getLastPage() + "</end>/n");
  65. resultXML.append("/t<pageno>" + pagination.getPageNo()
  66. + "</pageno>/n");
  67. resultXML.append("</pagination>/n");
  68. resultXML.append("</root>/n");
  69. } catch (Exception e) {
  70. e.printStackTrace();
  71. }
  72. writeResponse(req, resp, resultXML.toString());
  73. }
  74. public void writeResponse(HttpServletRequest request,
  75. HttpServletResponse response, String result) throws IOException {
  76. response.setContentType("text/xml");
  77. response.setHeader("Cache-Control", "no-cache");
  78. response.setHeader("Content-Type", "text/xml; charset=gb18030");
  79. PrintWriter pw = response.getWriter();
  80. pw.write(result);
  81. pw.close();
  82. }
  83. }
复制代码

其中User对象代码如下:

  1. package com.bjpowernode.test;
  2. import java.util.Date;
  3. public class UserInfo {
  4. private int id;
  5. private String username;
  6. private String password;
  7. private String truename;
  8. private String sex;
  9. private Date birthday;
  10. private String home;
  11. private String colleage;
  12. private String comingYear;
  13. public int getId() {
  14. return id;
  15. }
  16. public void setId(int id) {
  17. this.id = id;
  18. }
  19. public String getUsername() {
  20. return username;
  21. }
  22. public void setUsername(String username) {
  23. this.username = username;
  24. }
  25. public String getPassword() {
  26. return password;
  27. }
  28. public void setPassword(String password) {
  29. this.password = password;
  30. }
  31. public String getTruename() {
  32. return truename;
  33. }
  34. public void setTruename(String truename) {
  35. this.truename = truename;
  36. }
  37. public String getSex() {
  38. return sex;
  39. }
  40. public void setSex(String sex) {
  41. this.sex = sex;
  42. }
  43. public Date getBirthday() {
  44. return birthday;
  45. }
  46. public void setBirthday(Date birthday) {
  47. this.birthday = birthday;
  48. }
  49. public String getHome() {
  50. return home;
  51. }
  52. public void setHome(String home) {
  53. this.home = home;
  54. }
  55. public String getColleage() {
  56. return colleage;
  57. }
  58. public void setColleage(String colleage) {
  59. this.colleage = colleage;
  60. }
  61. public String getCy() {
  62. return comingYear;
  63. }
  64. public void setCy(String cy) {
  65. this. comingYear= cy;
  66. }
  67. }
复制代码

接着是业务逻辑层代码,代码如下:

  1. package com.bjpowernode.test;
  2. import java.sql.Connection;
  3. import kane.DBUtility;
  4. import kane.PageModel;
  5. public class UserInfoManage {
  6. private UserInfoDao userInfoDao = null;
  7. public UserInfoManage () {
  8. userInfoDao = new UserInfoDao();
  9. }
  10. public PageModel<UserInfo> userBasicSearch(UserInfo u, int pageNo,
  11. int pageSize) throws Exception {
  12. Connection connection = null;
  13. PageModel<UserInfo> pagination = new PageModel<UserInfo>();
  14. try {
  15. connection = DBUtility.getConnection();
  16. DBUtility.setAutoCommit(connection, false);
  17. pagination.setList(userInfoDao.getUserList(u, pageNo, pageSize));
  18. pagination.setPageNo(pageNo);
  19. pagination.setPageSize(pageSize);
  20. pagination.setTotalNum(userInfoDao.getTotalNum(u));
  21. DBUtility.commit(connection);
  22. } catch (Exception e) {
  23. DBUtility.rollBack(connection);
  24. e.printStackTrace();
  25. throw new Exception();
  26. } finally {
  27. DBUtility.closeConnection();
  28. }
  29. return pagination;
  30. }
  31. }
复制代码

其中DBUtility为数据库的连接封装类。

最后是Dao层代码实现,代码如下:

  1. package com.bjpowernode.test;
  2. import java.sql.Connection;
  3. import java.sql.PreparedStatement;
  4. import java.sql.ResultSet;
  5. import java.sql.SQLException;
  6. import java.util.ArrayList;
  7. import java.util.List;
  8. import kane.UserInfo;
  9. import kane.DBUtility;
  10. public class UserInfoDao {
  11. public List<UserInfo> getUserList(UserInfo userInfo, int pageNo,
  12. int pageSize) throws Exception {
  13. PreparedStatement pstmt = null;
  14. ResultSet rs = null;
  15. List<UserInfo> userList = null;
  16. try {
  17. String sql = "select * from(select rownum num,u.* from(select * from user_info where sex = ? and home like '"
  18. + userInfo.getHome()
  19. + "%"
  20. + "' and colleage like '"
  21. + userInfo.getColleage()
  22. + "%"
  23. + "' and comingyear like '"
  24. + userInfo.getCy()
  25. + "%"
  26. + "' order by id) u where rownum<=?) where num>=?";
  27. userList = new ArrayList<UserInfo>();
  28. Connection conn = DBUtility.getConnection();
  29. pstmt = conn.prepareStatement(sql);
  30. pstmt.setString(1, userInfo.getSex());
  31. pstmt.setInt(2, pageNo * pageSize);
  32. pstmt.setInt(3, (pageNo - 1) * pageSize + 1);
  33. rs = pstmt.executeQuery();
  34. while (rs.next()) {
  35. UserInfo user = new UserInfo();
  36. user.setId(rs.getInt("id"));
  37. user.setTruename(rs.getString("truename"));
  38. user.setSex(rs.getString("sex"));
  39. user.setHome(rs.getString("home"));
  40. userList.add(user);
  41. }
  42. } catch (SQLException e) {
  43. e.printStackTrace();
  44. throw new Exception(e);
  45. } finally {
  46. DBUtility.closeResultSet(rs);
  47. DBUtility.closePreparedStatement(pstmt);
  48. }
  49. return userList;
  50. }
  51. public int getTotalNum(UserInfo userInfo) throws Exception {
  52. PreparedStatement pstmt = null;
  53. ResultSet rs = null;
  54. int count = 0;
  55. try {
  56. String sql = "select count(*) from user_info where sex=? and home like '"
  57. + userInfo.getHome()
  58. + "%"
  59. + "' and colleage like '"
  60. + userInfo.getColleage()
  61. + "%"
  62. + "' and comingyear like '"
  63. + userInfo.getCy()+ "%" + "'";
  64. Connection conn = DBUtility.getConnection();
  65. pstmt = conn.prepareStatement(sql);
  66. pstmt.setString(1, userInfo.getSex());
  67. rs = pstmt.executeQuery();
  68. if (rs.next()) {
  69. count = rs.getInt(1);
  70. }
  71. } catch (SQLException e) {
  72. e.printStackTrace();
  73. throw new Exception(e);
  74. } finally {
  75. DBUtility.closeResultSet(rs);
  76. DBUtility.closePreparedStatement(pstmt);
  77. }
  78. return count;
  79. }
  80. }
复制代码

最后就是servlet将得到的结果返回给jsp页面显示出来。

注:其中DBUtility代码是封装数据库连接操作的代码,如下:

1.package com.bjpowernode.test;

  1. import java.sql.Connection;
  2. import java.sql.DriverManager;
  3. import java.sql.PreparedStatement;
  4. import java.sql.ResultSet;
  5. import java.sql.SQLException;
  6. public class DBUtility {
  7. private static ThreadLocal<Connection> threadLocal = new ThreadLocal<Connection>();
  8. public static Connection getConnection() {
  9. Connection conn = null;
  10. conn = threadLocal.get();
  11. if (conn == null) {
  12. try {
  13. Class.forName("oracle.jdbc.driver.OracleDriver");
  14. conn = DriverManager.getConnection(
  15. "jdbc:oracle:thin:@localhost:1521:oracle", "admin",
  16. "admin");
  17. threadLocal.set(conn);
  18. } catch (ClassNotFoundException e) {
  19. e.printStackTrace();
  20. } catch (SQLException e) {
  21. e.printStackTrace();
  22. }
  23. }
  24. return conn;
  25. }
  26. // 封装设置Connection自动提交
  27. public static void setAutoCommit(Connection conn, Boolean flag) {
  28. try {
  29. conn.setAutoCommit(flag);
  30. } catch (SQLException e) {
  31. e.printStackTrace();
  32. }
  33. }
  34. // 设置事务提交
  35. public static void commit(Connection conn) {
  36. try {
  37. conn.commit();
  38. } catch (SQLException e) {
  39. e.printStackTrace();
  40. }
  41. }
  42. // 封装设置Connection回滚
  43. public static void rollBack(Connection conn) {
  44. try {
  45. conn.rollback();
  46. } catch (SQLException e) {
  47. e.printStackTrace();
  48. }
  49. }
  50. // 封装关闭Connection、PreparedStatement、ResultSet的函数
  51. public static void closeConnection() {
  52. Connection conn = threadLocal.get();
  53. try {
  54. if (conn != null) {
  55. conn.close();
  56. conn = null;
  57. threadLocal.remove();
  58. }
  59. } catch (SQLException e) {
  60. e.printStackTrace();
  61. }
  62. }
  63. public static void closePreparedStatement(PreparedStatement pstmt) {
  64. try {
  65. if (pstmt != null) {
  66. pstmt.close();
  67. pstmt = null;
  68. }
  69. } catch (SQLException e) {
  70. e.printStackTrace();
  71. }
  72. }
  73. public static void closeResultSet(ResultSet rs) {
  74. try {
  75. if (rs != null) {
  76. rs.close();
  77. rs = null;
  78. }
  79. } catch (SQLException e) {
  80. e.printStackTrace();
  81. }
  82. }
  83. }
复制代码

使用ThreadLocal是为了保证事务的一致,使得同一个线程的所有数据库操作使用同一个Connection。

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

总结

以上所述是小编给大家介绍的Oracle下的Java分页功能,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对程序员之家网站的支持!



回复

使用道具 举报

关闭

站长推荐上一条 /1 下一条