servlet mysql 分页_Java基础94 分页查询（以MySQL数据库为例，Servlet技术）

1、概述

分页查询，也可叫做分批查询，基于数据库的分页语句(不同数据库是不同的)。

本文使用的事MySql数据库。

假设：每页显示10条数据.

Select * from contact  limit M,N;

M：开始记录的索引。第一条数据的索引为0 (页数)

N：一次查询几条记录(每页显示的数据的条数)

则：

第一页：select * from contact limit 0,10;

第二页：select * from contact limit 10,10

............

第n页：select * from contact limit(M-1)*N,N;

2、实例演示

db.properties 配置文件

1 url=jdbc:mysql://localhost:3306/school

2 user=root3 password=123456

4 driverClass=com.mysql.jdbc.Driver

JdbcUtil.java 封装文件(连接数据库)

1 packagecom.shore.util;2

3 importjava.io.FileInputStream;4 importjava.io.FileNotFoundException;5 importjava.io.IOException;6 importjava.io.InputStream;7 importjava.sql.Connection;8 importjava.sql.DriverManager;9 importjava.sql.ResultSet;10 importjava.sql.SQLException;11 importjava.sql.Statement;12 importjava.util.Properties;13

14 public classJdbcUtil {15 //连接数据库的URL

16 private static String url=null;17 private static String user=null;//用户名

18 private static String password=null;//密码

19 private static String driverClass=null;20 //静态代码块中(只加载一次)

21 static{22 try{23 //读取db.properties

24 Properties props=newProperties();25 InputStream in=JdbcUtil.class.getResourceAsStream("/db.properties");26 //加载文件

27 props.load(in);28 url=props.getProperty("url");29 user=props.getProperty("user");30 password=props.getProperty("password");31 driverClass=props.getProperty("driverClass");32 //注册驱动

33 Class.forName(driverClass);34 } catch(FileNotFoundException e) {35 e.printStackTrace();36 } catch(IOException e) {37 e.printStackTrace();38 } catch(ClassNotFoundException e) {39 e.printStackTrace();40 System.out.println("注册驱动失败");41 }42 }43 /*

44 * 获取连接45 **/

46 public staticConnection getConnection(){47 try{48 Connection conn=DriverManager.getConnection(url, user, password);49 returnconn;50 } catch(SQLException e) {51 e.printStackTrace();52 throw newRuntimeException();53 }54 }55 /*

56 * 释放资源57 **/

58 public static voidclose(Connection conn,Statement stmt,ResultSet rs){59 try{60 if(stmt!=null) stmt.close();61 if(conn!=null) conn.close();62 if(rs!=null) rs.close();63 } catch(SQLException e) {64 e.printStackTrace();65 }66 }67 }

Page 实体类

1 packagecom.shore.entity;2

3 importjava.util.List;4

5 //封装与分页有关的所有信息

6 public classPage {7 private List records;//要显示的分页记录

8 private int currentPageNum;//当前页码；可由用户指定(用于输入页码，点击跳转到指定页)*

9 private int pageSize = 10;//每页显示的记录条数(这里是没页显示10条数据) *

10 private int totalPageNum;//总页数*

11 private int prePageNum;//上一页的页码*

12 private int nextPageNum;//下一页的页码*

13

14 private int startIndex;//数据库每页开始记录的索引(比如第2页是从11开始，第三页从21开始...)*

15 private int totalRecords;//总记录的条数*16 //扩展的

17 private int startPage;//开始页码

18 private int endPage;//结束页码

19

20 private String url;//查询分页的请求servlet的地址21

22 //currentPageNum：用户要看的页码23 //totalRecords:总记录条数

24 public Page(int currentPageNum,inttotalRecords){25 this.currentPageNum =currentPageNum;26 this.totalRecords =totalRecords;27 //计算总页数

28 totalPageNum = totalRecords%pageSize==0?totalRecords/pageSize:(totalRecords/pageSize+1);29 //计算每页开始的索引

30 startIndex = (currentPageNum-1)*pageSize;31 //计算开始和结束页码:9个页码

32 if(totalPageNum > 9){33 //超过9页

34 startPage = currentPageNum - 4;35 endPage = currentPageNum + 4;36 if(startPage < 1){37 startPage = 1;38 endPage = 9;39 }40 if(endPage>totalPageNum){41 endPage =totalPageNum;42 startPage = endPage - 8;43 }44 }else{45 //没有9页

46 startPage = 1;47 endPage =totalPageNum;48 }49 }50 publicList getRecords() {51 returnrecords;52 }53 public voidsetRecords(List records) {54 this.records =records;55 }56 public intgetCurrentPageNum() {57 returncurrentPageNum;58 }59 public void setCurrentPageNum(intcurrentPageNum) {60 this.currentPageNum =currentPageNum;61 }62 public intgetPageSize() {63 returnpageSize;64 }65 public void setPageSize(intpageSize) {66 this.pageSize =pageSize;67 }68 public intgetTotalPageNum() {69 returntotalPageNum;70 }71 public void setTotalPageNum(inttotalPageNum) {72 this.totalPageNum =totalPageNum;73 }74 //不能无限上一页(假如当前页是第1页，那么“上一页”这个按钮变为灰色，再点击，则 无反应)

75 public intgetPrePageNum() {76 prePageNum = currentPageNum-1;77 if(prePageNum < 1){78 prePageNum = 1;79 }80 returnprePageNum;81 }82 public void setPrePageNum(intprePageNum) {83 this.prePageNum =prePageNum;84 }85 //不能无限下一页(假如当前页是最后一页，那么“下一页”这个按钮变为灰色，再点击，则 无反应)

86 public intgetNextPageNum() {87 nextPageNum = currentPageNum + 1;88 if(nextPageNum >totalPageNum){89 if(nextPageNum >totalPageNum){90 nextPageNum =totalPageNum;91 }92 returnnextPageNum;93 }94 public void setNextPageNum(intnextPageNum) {95 this.nextPageNum =nextPageNum;96 }97 public intgetStartIndex() {98 returnstartIndex;99 }100 public void setStartIndex(intstartIndex) {101 this.startIndex =startIndex;102 }103 public intgetTotalRecords() {104 returntotalRecords;105 }106 public void setTotalRecords(inttotalRecords) {107 this.totalRecords =totalRecords;108 }109 public intgetStartPage() {110 returnstartPage;111 }112 public void setStartPage(intstartPage) {113 this.startPage =startPage;114 }115 public intgetEndPage() {116 returnendPage;117 }118 public void setEndPage(intendPage) {119 this.endPage =endPage;120 }121 publicString getUrl() {122 returnurl;123 }124 public voidsetUrl(String url) {125 this.url =url;126 }127 }

ContactDAOMySqlImpl 实现类

1 packagecom.shore.dao.impl;2

3 importjava.sql.Connection;4 importjava.sql.PreparedStatement;5 importjava.sql.ResultSet;6 importjava.util.ArrayList;7 importjava.util.List;8

9 importcom.shore.dao.ContactDAO;10 importcom.shore.entity.Contact;11 importcom.shore.util.JdbcUtil;12

13 public class ContactDAOMySqlImpl implementsContactDAO{14

15 //总记录数

16 public intgetTotalRecordsNum() {17 Connection conn = null;18 PreparedStatement stmt = null;19 ResultSet rs = null;20 try{21 //获取数据库的连接

22 conn =JdbcUtil.getConnection();23 //准备sql

24 String sql = "select count(*) from contact";25 //执行预编译的sql语句(检查语法)

26 stmt =conn.prepareStatement(sql);27 //执行sql语句

28 rs =stmt.executeQuery();29 if(rs.next()){//把查到的结果返回给调用者

30 return rs.getInt(1);31 }32 return 0;33 }catch(Exception e){34 throw newRuntimeException(e);35 }finally{//关闭资源

36 JdbcUtil.close(conn, stmt, rs);37 }38 }39

40 //每页的记录数

41 public List getPageRecords(int startIndex, intoffset) {42 Connection conn = null;43 PreparedStatement stmt = null;44 ResultSet rs = null;45 try{46 //获取数据库的连接

47 conn =JdbcUtil.getConnection();48 //执行预编译的sql语句(检查语法)

49 stmt = conn.prepareStatement("select * from contact limit ?,?");50 //设置参数

51 stmt.setInt(1, startIndex);52 stmt.setInt(2, offset);53 //发送参数,执行sql

54 rs =stmt.executeQuery();55 List cs = new ArrayList();56 while(rs.next()){57 Contact c=newContact();58 c.setId(rs.getString("id"));59 c.setName(rs.getString("name"));60 c.setSex(rs.getString("sex"));61 c.setAge(rs.getInt("age"));62 c.setPhone(rs.getString("phone"));63 c.setEmail(rs.getString("email"));64 c.setQq(rs.getString("qq"));65 cs.add(c);66 }67 returncs;68 }catch(Exception e){69 throw newRuntimeException(e);70 }finally{//关闭资源

71 JdbcUtil.close(conn, stmt, rs);72 }73 }74 }

ContactServiceimpl 实现类

1 packagecom.shore.service.impl;2

3 importjava.util.List;4

5 importcom.shore.dao.ContactDAO;6 importcom.shore.dao.impl.ContactDAOMySqlImpl;7 importcom.shore.entity.Page;8 importcom.shore.service.ContactService;9

10 public class ContactServiceimpl implementsContactService{11 ContactDAO dao=newContactDAOMySqlImpl();12

13 publicPage findPage(String pageNum) {14 int num = 1;//用户要看的页码,默认是1

15 if(pageNum!=null&&!pageNum.trim().equals("")){//解析用户要看的页码

16 num =Integer.parseInt(pageNum);17 }18 int totalRecords = dao.getTotalRecordsNum();//得到总记录的条数

19 Page page = new Page(num, totalRecords);//对象创建出来后,很多的参数就已经计算完毕20 //查询分页的记录(当前页显示的记录)

21 List records =dao.getPageRecords(page.getStartIndex(), page.getPageSize());22 page.setRecords(records);23 returnpage;24 }25 }

ListContactServlet 类

1 packagecom.shore.servlet;2

3 importjava.io.IOException;4

5 importjavax.servlet.ServletException;6 importjavax.servlet.http.HttpServlet;7 importjavax.servlet.http.HttpServletRequest;8 importjavax.servlet.http.HttpServletResponse;9

10 importcom.shore.entity.Page;11 importcom.shore.service.ContactService;12 importcom.shore.service.impl.ContactServiceimpl;13

14 public class ListContactServlet extendsHttpServlet {15 /*

16 * 显示所有联系人的逻辑17 **/

18 public voiddoGet(HttpServletRequest request, HttpServletResponse response)19 throwsServletException, IOException {20 ContactService service=newContactServiceimpl();21 String num=request.getParameter("num");22 Page page=service.findPage(num);23 page.setUrl("/ListContactServlet");24 request.setAttribute("page",page);25 request.getRequestDispatcher("/listContact.jsp").forward(request, response);26 }27

28 public voiddoPost(HttpServletRequest request, HttpServletResponse response)29 throwsServletException, IOException {30 doGet(request, response);31 }32 }

listContact.jsp 查询页面

1

2

3

4

5

6

7

查询所有联系人

8

9 table td{

10 /*文字居中*/

11 text-align:center;

12 }

13

14 /*合并表格的边框*/

15 table{

16 border-collapse:collapse;

17 }

18

19

20

21

22

查询所有联系人

23

24

25

编号

26

姓名

27

性别

28

年龄

29

电话

30

邮箱

31

QQ

32

操作

33

34

35

36

${varSta.count }

37

${con.name }

38

${con.sex }

39

${con.age }

40

${con.phone }

41

${con.email }

42

${con.qq }

43

修改 删除

44

45

46

47 [添加联系人]

48

49

50

51

52