一区二区三区在线-一区二区三区亚洲视频-一区二区三区亚洲-一区二区三区午夜-一区二区三区四区在线视频-一区二区三区四区在线免费观看

服務器之家:專注于服務器技術及軟件下載分享
分類導航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|數據庫技術|

服務器之家 - 數據庫 - Oracle - Oracle下的Java分頁功能_動力節點Java學院整理

Oracle下的Java分頁功能_動力節點Java學院整理

2020-01-21 17:04mrr Oracle

分頁的時候返回的不僅包括查詢的結果集(List),而且還包括總的頁數(pageNum)、當前第幾頁(pageNo)等等信息,所以我們封裝一個查詢結果PageModel類,具體實現代碼,大家參考下本文

就如平時我們很在分頁中看到的,分頁的時候返回的不僅包括查詢的結果集(List),而且還包括總的頁數(pageNum)、當前第幾頁(pageNo)等等信息,所以我們封裝一個查詢結果PageModel類,代碼如下:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
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,代碼如下:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
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對象代碼如下:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
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;
 }
}

接著是業務邏輯層代碼,代碼如下:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
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層代碼實現,代碼如下: 

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
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;    

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
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分頁功能,希望對大家有所幫助,如果大家有任何疑問請給我留言,小編會及時回復大家的。在此也非常感謝大家對服務器之家網站的支持!

延伸 · 閱讀

精彩推薦
主站蜘蛛池模板: ck7788免费视频| girlfriend动漫在线播放 | www.87福利| 色噜噜视频影院 | 国产欧美视频在线观看 | 国产精品视频免费观看 | 动漫jk美女被爆羞羞漫画 | 国产精品va在线观看手机版 | 成年美女黄网色大观看全 | 精品一区二区免费视频蜜桃网 | 国产无限免费观看黄网站 | 好大~好爽~再进去一点 | 日本高清在线播放一区二区三区 | 日本黄大片影院一区二区 | 国产香蕉一区二区在线网站 | 亚洲网站在线 | 性做久久久久免费观看 | 国产精品调教 | 99精彩视频| 国产1广场舞丰满老女偷 | 肥胖女性大bbbbbb视频女厕 | 久草热在线 | 毛茸茸的大逼 | 久久99精品久久久久久园产越南 | 国产欧美日韩一区二区三区在线 | 风间由美被义子中文字幕 | 久久偷拍免费2017 | 草草视频人人爽 | 青青青草国产线观 | 日本欧美一二三区色视频 | 国产精品视频第一区二区 | jizzjizz3d动漫| 国产伦精品一区二区三区免费迷 | 饭冈加奈子黑人解禁在线播放 | 白丝爆动漫羞羞动漫网站 | 亚洲精品国产在线 | 久久久大香菇 | 99任你躁精品视频 | 欧美日韩久久中文字幕 | 四虎2021地址入口 | 欧美性一区二区三区 |