緊接著上篇—分頁技術原理與實現之分頁的意義及方法（一） ，本篇繼續分析分頁技術。上篇講的是分頁技術的簡單原理與介紹，這篇深入分析一下分頁技術的代碼實現。
上篇最后講到了分頁的最佳實現是在數據庫層進行分頁，而且不同的數據庫有不同的分頁實現，比如Oracle是用三層sql嵌套實現分頁的、MySQL是用limit關鍵字實現的(上篇已講到)。
這篇以Java+Oracle為基礎，講解代碼層的實現。
就如平時我們很在分頁中看到的，分頁的時候返回的不僅包括查詢的結果集(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
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
|
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，代碼如下：
|
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
84
85
86
87
88
89
90
91
92
93
94
95
|
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對象代碼如下：
|
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
84
85
|
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; }} |
接著是業務邏輯層代碼，代碼如下：
|
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
|
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層代碼實現，代碼如下：
|
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
84
85
|
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代碼是封裝數據庫連接操作的代碼，如下：
|
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
84
85
86
87
88
89
90
91
92
93
94
|
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。
到此一個簡單的代碼實現就完成了。
以上就是本文的全部內容，希望對大家的學習有所幫助，也希望大家多多支持服務器之家。
