对慕课网分页感悟二
上一次我們使用list集合里面的方法sublist實(shí)現(xiàn)了分頁功能，但是這一種方法雖然能直接的實(shí)現(xiàn)分頁，具體需要直接將所有的數(shù)據(jù)取出來然后再進(jìn)行分頁使用，如果數(shù)據(jù)較多的話，我們就不便使用這種方法更適合使用sql語句里面的limit關(guān)鍵字就能比較迅速的實(shí)現(xiàn)分頁的功能，話不多說。
首先，看看連接數(shù)據(jù)庫的JdbcUtil.java，這一個和上一次的并沒有不同。
package com.imooc.page.util;import java.io.InputStream; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.Properties;public class JdbcUtil {// 表示定義數(shù)據(jù)庫的用戶名private static String USERNAME ;// 定義數(shù)據(jù)庫的密碼private static String PASSWORD;// 定義數(shù)據(jù)庫的驅(qū)動信息private static String DRIVER;// 定義訪問數(shù)據(jù)庫的地址private static String URL;// 定義數(shù)據(jù)庫的鏈接private Connection connection;// 定義sql語句的執(zhí)行對象private PreparedStatement pstmt;// 定義查詢返回的結(jié)果集合private ResultSet resultSet;static{//加載數(shù)據(jù)庫配置信息，并給相關(guān)的屬性賦值 loadConfig();}/*** 加載數(shù)據(jù)庫配置信息，并給相關(guān)的屬性賦值*/public static void loadConfig() {try {InputStream inStream = JdbcUtil.class.getResourceAsStream("/jdbc.properties");Properties prop = new Properties();prop.load(inStream);USERNAME = prop.getProperty("jdbc.username");PASSWORD = prop.getProperty("jdbc.password");DRIVER= prop.getProperty("jdbc.driver");URL = prop.getProperty("jdbc.url");} catch (Exception e) {throw new RuntimeException("讀取數(shù)據(jù)庫配置文件異常！", e);}}public JdbcUtil() {}/*** 獲取數(shù)據(jù)庫連接* * @return 數(shù)據(jù)庫連接*/public Connection getConnection() {try {Class.forName(DRIVER); // 注冊驅(qū)動connection = DriverManager.getConnection(URL, USERNAME, PASSWORD); // 獲取連接} catch (Exception e) {throw new RuntimeException("get connection error!", e);}return connection;}/*** 執(zhí)行更新操作* * @param sql* sql語句* @param params* 執(zhí)行參數(shù)* @return 執(zhí)行結(jié)果* @throws SQLException*/public boolean updateByPreparedStatement(String sql, List<?> params)throws SQLException {boolean flag = false;int result = -1;// 表示當(dāng)用戶執(zhí)行添加刪除和修改的時候所影響數(shù)據(jù)庫的行數(shù)pstmt = connection.prepareStatement(sql);int index = 1;// 填充sql語句中的占位符if (params != null && !params.isEmpty()) {for (int i = 0; i < params.size(); i++) {pstmt.setObject(index++, params.get(i));}}result = pstmt.executeUpdate();flag = result > 0 ? true : false;return flag;}/*** 執(zhí)行查詢操作* * @param sql* sql語句* @param params* 執(zhí)行參數(shù)* @return* @throws SQLException*/public List<Map<String, Object>> findResult(String sql, List<?> params)throws SQLException {List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();int index = 1;pstmt = connection.prepareStatement(sql);if (params != null && !params.isEmpty()) {for (int i = 0; i < params.size(); i++) {pstmt.setObject(index++, params.get(i));}}resultSet = pstmt.executeQuery();ResultSetMetaData metaData = resultSet.getMetaData();int cols_len = metaData.getColumnCount();while (resultSet.next()) {Map<String, Object> map = new HashMap<String, Object>();for (int i = 0; i < cols_len; i++) {String cols_name = metaData.getColumnName(i + 1);Object cols_value = resultSet.getObject(cols_name);if (cols_value == null) {cols_value = "";}map.put(cols_name, cols_value);}list.add(map);}return list;}/*** 釋放資源*/public void releaseConn() {if (resultSet != null) {try {resultSet.close();} catch (SQLException e) {e.printStackTrace();}}if (pstmt != null) {try {pstmt.close();} catch (SQLException e) {e.printStackTrace();}}if (connection != null) {try {connection.close();} catch (SQLException e) {e.printStackTrace();}}}public static void main(String[] args) {JdbcUtil jdbcUtil = new JdbcUtil();jdbcUtil.getConnection();try {List<Map<String, Object>> result = jdbcUtil.findResult("select * from t_student", null);for (Map<String, Object> m : result) {System.out.println(m);}} catch (SQLException e) {e.printStackTrace();} finally {jdbcUtil.releaseConn();}} }
------------------------------------------------------------JdbcUtil.java-----------------------------------------------
jdbc.username=root jdbc.password=123456 jdbc.driver=com.mysql.jdbc.Driver jdbc.url=jdbc:mysql://127.0.0.1:3306/imooc
好，看完連接我們就來看一下實(shí)體類層：
-------------------------------------------------------------Student.java-------------------------------------------------------
package com.imooc.page.model;import java.io.Serializable; import java.util.Map;public class Student implements Serializable {private static final long serialVersionUID = -7476381137287496245L;private int id; //學(xué)生記錄idprivate String stuName;//學(xué)生姓名private int age; //學(xué)生年齡private int gender; //學(xué)生性別private String address;//學(xué)生住址public Student() {super();}public Student(int id, String stuName, int age, int gender, String address) {super();this.id = id;this.stuName = stuName;this.age = age;this.gender = gender;this.address = address;}public Student(Map<String, Object> map){this.id = (int)map.get("id");this.stuName = (String)map.get("stu_name");this.age = (int)map.get("age");this.gender = (int)map.get("gender");this.address = (String)map.get("address");}public int getId() {return id;}public void setId(int id) {this.id = id;}public String getStuName() {return stuName;}public void setStuName(String stuName) {this.stuName = stuName;}public int getAge() {return age;}public void setAge(int age) {this.age = age;}public int getGender() {return gender;}public void setGender(int gender) {this.gender = gender;}public String getAddress() {return address;}public void setAddress(String address) {this.address = address;}@Overridepublic String toString() {return "Student [id=" + id + ", stuName=" + stuName + ", age=" + age+ ", gender=" + gender + ", address=" + address + "]";}}
------------------------------------------------------Pager<T>.java-----------------------------------------------
package com.imooc.page.model;import java.io.Serializable; import java.util.List;public class Pager<T> implements Serializable {private static final long serialVersionUID = -8741766802354222579L;private int pageSize; // 每頁顯示多少條記錄private int currentPage; //當(dāng)前第幾頁數(shù)據(jù)private int totalRecord; // 一共多少條記錄private int totalPage; // 一共多少頁記錄private List<T> dataList; //要顯示的數(shù)據(jù)public Pager(int pageNum, int pageSize, List<T> sourceList){if(sourceList == null || sourceList.isEmpty()){return;}// 總記錄條數(shù)this.totalRecord = sourceList.size();// 每頁顯示多少條記錄this.pageSize = pageSize;//獲取總頁數(shù)this.totalPage = this.totalRecord / this.pageSize;if(this.totalRecord % this.pageSize !=0){this.totalPage = this.totalPage + 1;}// 當(dāng)前第幾頁數(shù)據(jù)this.currentPage = this.totalPage < pageNum ? this.totalPage : pageNum;// 起始索引int fromIndex = this.pageSize * (this.currentPage -1);// 結(jié)束索引int toIndex = this.pageSize * this.currentPage > this.totalRecord ? this.totalRecord : this.pageSize * this.currentPage;this.dataList = sourceList.subList(fromIndex, toIndex);}public Pager(){}public Pager(int pageSize, int currentPage, int totalRecord, int totalPage,List<T> dataList) {super();this.pageSize = pageSize;this.currentPage = currentPage;this.totalRecord = totalRecord;this.totalPage = totalPage;this.dataList = dataList;}public int getPageSize() {return pageSize;}public void setPageSize(int pageSize) {this.pageSize = pageSize;}public int getCurrentPage() {return currentPage;}public void setCurrentPage(int currentPage) {this.currentPage = currentPage;}public int getTotalRecord() {return totalRecord;}public void setTotalRecord(int totalRecord) {this.totalRecord = totalRecord;}public int getTotalPage() {return totalPage;}public void setTotalPage(int totalPage) {this.totalPage = totalPage;}public List<T> getDataList() {return dataList;}public void setDataList(List<T> dataList) {this.dataList = dataList;}}
?
看完上面的代碼我們可以比較明顯的看出來兩者并沒有什么不同。
不多說，我們直接看控制層servlet：
---------------------------------------------------------JdbcSqlServlet.java------------------------------------------------
package com.imooc.page.servlet;import java.io.IOException;import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse;import com.imooc.page.Constant; import com.imooc.page.model.Pager; import com.imooc.page.model.Student; import com.imooc.page.service.JdbcSqlStudentServiceImpl; import com.imooc.page.service.StudentService; import com.imooc.page.util.StringUtil;public class JdbcSqlServlet extends HttpServlet {private static final long serialVersionUID = -318134993070614515L;private StudentService studentService = new JdbcSqlStudentServiceImpl();public void doGet(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {doPost(request, response);}public void doPost(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {// 接收request里的參數(shù)String stuName = request.getParameter("stuName"); //學(xué)生姓名// 獲取學(xué)生性別int gender = Constant.DEFAULT_GENDER;String genderStr = request.getParameter("gender");if(genderStr!=null && !"".equals(genderStr.trim())){gender = Integer.parseInt(genderStr);}// 校驗(yàn)pageNum參數(shù)輸入合法性String pageNumStr = request.getParameter("pageNum"); if(pageNumStr !=null && !StringUtil.isNum(pageNumStr)){request.setAttribute("errorMsg", "參數(shù)傳輸錯誤");request.getRequestDispatcher("jdbcSqlStudent.jsp").forward(request, response);return;}int pageNum = Constant.DEFAULT_PAGE_NUM; //顯示第幾頁數(shù)據(jù)if(pageNumStr!=null && !"".equals(pageNumStr.trim())){pageNum = Integer.parseInt(pageNumStr);}int pageSize = Constant.DEFAULT_PAGE_SIZE; // 每頁顯示多少條記錄String pageSizeStr = request.getParameter("pageSize");if(pageSizeStr!=null && !"".equals(pageSizeStr.trim())){pageSize = Integer.parseInt(pageSizeStr);}// 組裝查詢條件Student searchModel = new Student(); searchModel.setStuName(stuName);searchModel.setGender(gender);//調(diào)用service 獲取查詢結(jié)果Pager<Student> result = studentService.findStudent(searchModel,pageNum, pageSize);// 返回結(jié)果到頁面request.setAttribute("result", result);request.setAttribute("stuName", stuName);request.setAttribute("gender", gender);request.getRequestDispatcher("jdbcSqlStudent.jsp").forward(request, response);}}
servlce層也能看到是完全的一致。
-----------------------------------------------------------JdbcSqlStudentServiceImpl.java-------------------------------------------
package com.imooc.page.service;import com.imooc.page.dao.JdbcSqlStudentDaoImpl; import com.imooc.page.dao.StudentDao; import com.imooc.page.model.Pager; import com.imooc.page.model.Student;public class JdbcSqlStudentServiceImpl implements StudentService {private StudentDao studentDao;public JdbcSqlStudentServiceImpl(){studentDao = new JdbcSqlStudentDaoImpl();}@Overridepublic Pager<Student> findStudent(Student searchModel, int pageNum,int pageSize) {Pager<Student> result = studentDao.findStudent(searchModel, pageNum,pageSize);return result;}}
我們前面一直在說是使用了sql語句里面的關(guān)鍵字limit，可想而知，在Dao層肯定是有所不同的。
-----------------------------------------------------JdbcSqlStudentDaoImpl.java----------------------------------------
package com.imooc.page.dao;import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import java.util.Map;import com.imooc.page.Constant; import com.imooc.page.model.Pager; import com.imooc.page.model.Student; import com.imooc.page.util.JdbcUtil;/*** 使用mysql數(shù)據(jù)庫limit關(guān)鍵字實(shí)現(xiàn)分頁* * @author lenjey* */ public class JdbcSqlStudentDaoImpl implements StudentDao {@Overridepublic Pager<Student> findStudent(Student searchModel, int pageNum,int pageSize) {Pager<Student> result = null;// 存放查詢參數(shù)List<Object> paramList = new ArrayList<Object>();String stuName = searchModel.getStuName();int gender = searchModel.getGender();StringBuilder sql = new StringBuilder("select * from t_student where 1=1");StringBuilder countSql = new StringBuilder("select count(id) as totalRecord from t_student where 1=1 ");if (stuName != null && !stuName.equals("")) {sql.append(" and stu_name like ?");countSql.append(" and stu_name like ?");paramList.add("%" + stuName + "%");}if (gender == Constant.GENDER_FEMALE || gender == Constant.GENDER_MALE) {sql.append(" and gender = ?");countSql.append(" and gender = ?");paramList.add(gender);}// 起始索引//這里的話，個人覺得并沒有處理好int fromIndex = pageSize * (pageNum -1);// System.out.println(fromIndex);// 使用limit關(guān)鍵字，實(shí)現(xiàn)分頁sql.append(" limit " + fromIndex + ", " + pageSize );// 存放所有查詢出的學(xué)生對象List<Student> studentList = new ArrayList<Student>();JdbcUtil jdbcUtil = null;try {jdbcUtil = new JdbcUtil();jdbcUtil.getConnection(); // 獲取數(shù)據(jù)庫鏈接// 獲取總記錄數(shù)List<Map<String, Object>> countResult = jdbcUtil.findResult(countSql.toString(), paramList);//在獲取總數(shù)的時候，我們需要使用這種方法來進(jìn)行獲取Map<String, Object> countMap = countResult.get(0);int totalRecord = ((Number)countMap.get("totalRecord")).intValue();// 獲取查詢的學(xué)生記錄List<Map<String, Object>> studentResult = jdbcUtil.findResult(sql.toString(), paramList);if (studentResult != null) {for (Map<String, Object> map : studentResult) {Student s = new Student(map);studentList.add(s);}}//獲取總頁數(shù)int totalPage = totalRecord / pageSize;if(totalRecord % pageSize !=0){totalPage++;}// 組裝pager對象result = new Pager<Student>(pageSize, pageNum, totalRecord, totalPage, studentList);} catch (SQLException e) {throw new RuntimeException("查詢所有數(shù)據(jù)異常！", e);} finally {if (jdbcUtil != null) {jdbcUtil.releaseConn(); // 一定要釋放資源 }}return result;}}
不知道又沒有看出來，兩個dao層調(diào)用pager里面的方法是不一樣的，一個是調(diào)用在modal直接獲取分頁需要的數(shù)據(jù)
而后者則是直接給之賦值即可。
好，后臺我們看完了，剩下的我們看前臺。
前臺里面呢？我們用了一個插件。
這個插件能比較形象的將分頁功能顯示出來，比以往我們所用的感覺更好，用戶體驗(yàn)更好。
具體這里就不再贅述了。
<%@ page language="java" contentType="text/html; charset=UTF-8"pageEncoding="UTF-8"%> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%> <%@taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions"%> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <title>學(xué)生信息</title> </head> <%// 獲取請求的上下文String context = request.getContextPath(); %> <link href="../css/pagination.css" rel="stylesheet" type="text/css" /> <script type="text/javascript" src="../js/jquery-1.11.3.js"></script> <script type="text/javascript" src="../js/jquery.pagination.js"></script> <script type="text/javascript">// 點(diǎn)擊分頁按鈕以后觸發(fā)的動作 function handlePaginationClick(new_page_index, pagination_container) {$("#stuForm").attr("action", "<%=context%>/jdbcSql/JdbcSqlServlet?pageNum=" + (new_page_index+1));$("#stuForm").submit();return false; }$(function(){$("#News-Pagination").pagination(${result.totalRecord}, {items_per_page : ${result.pageSize}, // 每頁顯示多少條記錄current_page:${result.currentPage} - 1, // 當(dāng)前顯示第幾頁數(shù)據(jù)num_display_entries:8, // 分頁顯示的條目數(shù)next_text:"下一頁",prev_text:"上一頁",num_edge_entries:2, // 連接分頁主體，顯示的條目數(shù) callback:handlePaginationClick}); // 設(shè)置學(xué)生默認(rèn)性別$("#gender").val("${gender}"); }); </script> <body><div style="margin-left: 100px; margin-top: 100px;"><div><font color="red">${errorMsg }</font></div><div><form action="<%=context%>/jdbcSql/JdbcSqlServlet" id="stuForm"method="post">姓名 <input type="text" name="stuName" id="stu_name"style="width: 120px" value="${stuName }"> 性別 <selectname="gender" id="gender" style="width: 80px"><option value="0">全部</option><option value="1">男</option><option value="2">女</option></select> <input type="submit" value="查詢"></form></div><br> 學(xué)生信息列表：<br> <br><!-- 后臺返回結(jié)果為空 --><c:if test="${fn:length(result.dataList) eq 0 }"><span>查詢的結(jié)果不存在</span></c:if><!-- 后臺返回結(jié)果不為空 --><c:if test="${fn:length(result.dataList) gt 0 }"><table border="1px" cellspacing="0px"style="border-collapse: collapse"><thead><tr height="30"><th width="130">姓名</th><th width="130">性別</th><th width="130">年齡</th><th width="190">家庭地址</th></tr></thead><c:forEach items="${result.dataList }" var="student"><tr><td><c:out value="${student.stuName }"></c:out></td><td><c:if test="${ student.gender eq 1}">男</c:if> <c:iftest="${ student.gender eq 2}">女</c:if></td><td><c:out value="${student.age }"></c:out></td><td><c:out value="${student.address }"></c:out></td></tr></c:forEach></table><br><div id="News-Pagination"></div></c:if></div> </body> </html>
好了，這個就到這里了。
?
轉(zhuǎn)載于:https://www.cnblogs.com/strator/p/7637718.html
總結(jié)
- 上一篇： 【kindeditor】KindEdit
- 下一篇： 没排卵能做试管婴儿