java 分页查询实例_JavaWeb学习之分页查询实例-CSDN博客

本文链接：https://blog.csdn.net/weixin_33028765/article/details/114113259

一、环境搭建

客户端(发送请求)=> Servlet(处理请求，1、指定处理Service 2、指定对应页面) => Service(业务处理。1、调用具体Dao并对返回数据做对应处理) => Dao(数据库操作。单一增删改查)

基本结构如下图

二、代码实现分页

1、Sql语句(Sql Server 2012及以上)

SELECT * FROM 表名 order by 排序字段 OFFSET (当前页-1)*页容量 ROWS FETCH next 页容量 rows only

2、domain包用于存储数据实体结构

2.1、PageBean.java

1 /**

2 * PageBean,泛型可以提高其复用性3 * 用于分页：4 * 1、页容量(PageSize)5 * 2、当前页面(PageIndex)6 * 3、总页数(PageCount)7 * 4、总条数(PageTotal)8 * 5、分页数据集合(List)9 */

10 public class PageBean{11 private intPageSize;12 private intPageIndex;13 private intPageCount;14 private intPageTotal;15 private Listlist;16 public intgetPageSize() {17 returnPageSize;18 }19 public void setPageSize(intpageSize) {20 PageSize =pageSize;21 }22 public intgetPageIndex() {23 returnPageIndex;24 }25 public void setPageIndex(intpageIndex) {26 PageIndex =pageIndex;27 }28 public intgetPageCount() {29 returnPageCount;30 }31 public void setPageCount(intpageCount) {32 PageCount =pageCount;33 }34 public intgetPageTotal() {35 returnPageTotal;36 }37 public void setPageTotal(intpageTotal) {38 PageTotal =pageTotal;39 }40 public ListgetList() {41 returnlist;42 }43 public void setList(Listlist) {44 this.list =list;45 }46 }

2.2、UserInfo

1 public classUserInfo {2 privateString UName;3 private Date SubTime;//java.util.Date

4 privateString Remark;5 publicString getUName() {6 returnUName;7 }8 public voidsetUName(String uname) {9 UName =uname;10 }11 publicDate getSubTime() {12 returnSubTime;13 }14 public voidsetSubTime(Date subTime) {15 SubTime =subTime;16 }17 publicString getRemark() {18 returnRemark;19 }20 public voidsetRemark(String remark) {21 Remark =remark;22 }23 }

3、dao.impl包实现dao包中的接口

1 public class UserInfoDaoImpl implementsIUserInfoDao {2

3 /**

4 * 获取分页数据5 */

6 @Override7 public List getUserInfoByPageIndex(int p_intPageIdex) throwsSQLException {8 QueryRunner runner= newQueryRunner(JDBCUtil.getDataSource());9 return runner.query("select * from UserInfo order by Id offset ? row fetch next ? row only",10 new BeanListHandler(UserInfo.class), (p_intPageIdex-1)*PAGE_SIZE,PAGE_SIZE);11 }12

13 /**

14 * 获取总记录数15 */

16 @Override17 public int getUserInfoCount() throwsSQLException {18 QueryRunner runner= newQueryRunner(JDBCUtil.getDataSource());19 Long countLong= (Long) runner.query("select Count(*) from UserInfo",newScalarHandler());20 return countLong.intValue();//Long类型获取int类型的值

21 }22 }

4、service.impl包实现service包中的接口

1 public class UserInfoServiceImpl implementsIUserInfoService {2

3 @Override4 public PageBean getPageList(int p_intPageIndex) throwsSQLException {5 PageBean bean=new PageBean();6 bean.setPageIndex(p_intPageIndex);7 int pageSize=IUserInfoDao.PAGE_SIZE;8 bean.setPageSize(pageSize);9 IUserInfoDao dao=newUserInfoDaoImpl();10 bean.setList(dao.getUserInfoByPageIndex(p_intPageIndex));11 int count=dao.getUserInfoCount();12 bean.setPageCount(count);13

14 //200,10 2015 //201,10 11

16 bean.setPageTotal(count % pageSize == 0 ? count / pageSize : (count / pageSize) + 1);17 returnbean;18 }19 }

5、servlet调用service.impl中的方法

1 public class UserInfoServlect extendsHttpServlet {2

3 @Override4 protected void doGet(HttpServletRequest req, HttpServletResponse resp) throwsServletException, IOException {5 try{6

7 int intPageIndex = 1;8 if(req.getParameter("pageIndex")!=null) {9 intPageIndex=Integer.parseInt(req.getParameter("pageIndex"));10 }11 IUserInfoService service=newUserInfoServiceImpl();12 PageBean bean=service.getPageList(intPageIndex);13 req.setAttribute("bean", bean);14 req.getRequestDispatcher("Index.jsp").forward(req, resp);15 } catch(SQLException e) {16

17 e.printStackTrace();18 }19 }20

21 @Override22 protected void doPost(HttpServletRequest req, HttpServletResponse resp) throwsServletException, IOException {23

24 doGet(req, resp);25 }26 }

6、创建jsp文件

6.1、导入标签库