java mysql ajax分页查询,mysql——分页技术实现(基于layui框架)

一.分页技术概述

分页技术：

JSP页面，用来显示数据！ 如果数据有多条，分页显示，每页显示10条，多页; 好处: 利于页面布局，且显示的效率高！

分页关键点：

1.分页SQL语句 SELECT * FROM car LIMIT [(当前页-1)*每页显示的行数],[每页显示的行数];；

2.后台处理： dao/service/servlet/JSP

实现步骤：

环境准备

a) 引入jar文件

i. 数据库驱动包

ii. BaseDao引用

qr.update(sql);

b) 公用类: JdbcUtils.java

先设计：PageBean.java

Dao接口设计/实现： 2个方法

Service/servlet

JSP

效果展示

二.实现思路：

1.通过jsp页面发送请求到servlet 然后servlet将调用service层处理将结果放在pageBean中

2.service层计算并数据调用dao层

3.dao层访问数据库通过pageBean中已知的数据查找相应的数据

三.实现代码

工具类：PageBean.java

package com.huan.pool.bean;

import java.util.List;

/**封装分页的参数

* Created by 马欢欢 on 2017/5/29.

*/

public class PageBean {

private int currentPage=1;//当前页

private int pageCount=10;//每页显示的行数(查询返回的行数)默认每页显示4行

private int totalCount;//总记录数

private List pageData;//分页查询到的数据

private int totalPage;//总页数=总记录数、每页显示的行数(有余数+1)

public int getCurrentPage() {

if(totalCount % pageCount == 0){

totalPage = totalCount / pageCount;

}else {

totalPage = totalCount / pageCount +1;

}

return currentPage;

}

public void setCurrentPage(int currentPage) {

this.currentPage = currentPage;

}

public int getPageCount() {

return pageCount;

}

public void setPageCount(int pageCount) {

this.pageCount = pageCount;

}

public int getTotalCount() {

return totalCount;

}

public void setTotalCount(int totalCount) {

this.totalCount = totalCount;

}

public int getTotalPage() {

return totalPage;

}

public void setTotalPage(int totalPage) {

this.totalPage = totalPage;

}

public List getPageData() {

return pageData;

}

public void setPageData(List pageData) {

this.pageData = pageData;

}

}

Dao层实现代码：

protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {

resp.setContentType("text/html;charset=utf-8");

req.setCharacterEncoding("utf-8");

String method = req.getParameter("method");

if ("tronClasstype".equals(method)) {

tronClasstypeFind(req, resp);

} else if ("save".equals(method)) {

save(req, resp);

} else if ("pool".equals(method)) {

pool(req, resp);

} else if ("pages".equals(method)) {

pages(req, resp);

}

}

//查询总数量

public int getTotalCount() throws Exception {

PreparedStatement pstmt = null;

Connection conn = null;

conn = ConnUtil.getConnextion();

String sql = " SELECT count(*) FROM eu_tronclass ; ";

pstmt = conn.prepareStatement(sql);

ResultSet rs = pstmt.executeQuery();

while(rs.next()) {

return rs.getInt(1);

}

ConnUtil.close(rs, pstmt, conn);

return 0;

}

//@Test

//public void aaa(){

// PageBean pageBean = new PageBean();

// pageBean.setPageCurrent(2);

// try {

// getAll(pageBean);

// } catch (Exception e) {

// e.printStackTrace();

// }

//}

public void getAll(PageBean pageBean) throws Exception {

int totalCount = this.getTotalCount();//总记录数

pageBean.setTotalCount(totalCount);//填充总记录数

/*

* 问题： jsp页面，如果当前页为首页，再点击上一页报错！

* 如果当前页为末页，再点下一页显示有问题！

* 解决：

* 1. 如果当前页 <= 0; 当前页设置当前页为1;

* 2. 如果当前页 > 最大页数； 当前页设置为最大页数

*/

// 判断

if (pageBean.getPageCurrent() <=0) {

pageBean.setPageCurrent(1); // 把当前页设置为1

} else if (pageBean.getPageCurrent() > pageBean.getTotalPage()){

pageBean.setPageCurrent(pageBean.getTotalPage()); // 把当前页设置为最大页数

}

int pageCurrent = pageBean.getPageCurrent();//当前页

int index = (pageCurrent -1)*pageBean.getPageCount();//起始查询位置

int count = pageBean.getPageCount();//每页显示的数量

String sql = "select * from eu_tronclass limit ?,?";

Object[] paramsValue = {index,count};

List list = super.query(sql,paramsValue,TronClasstype.class);

pageBean.setPageData(list);

JSONArray jsonArray = JSONArray.fromObject(list);

System.out.println("getAll"+jsonArray);

}

servlet层

/**

* 查询分页显示

*/

protected void pool(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {

PageBean pageBean = new PageBean();

int pageCurrent = Integer.parseInt(req.getParameter("curr"));

// int pageCurrent=1;

pageBean.setPageCurrent(pageCurrent);

tronClassService.getAll(pageBean);

List TronClasstypelist = pageBean.getPageData();

JSONArray jsonArray = JSONArray.fromObject(TronClasstypelist);

resp.getWriter().print(jsonArray);

System.out.println("servlet" + jsonArray);

}

/**

* 将总页数分封装在session

*/

protected void pages(HttpServletRequest req, HttpServletResponse resp) {

PageBean pageBean = new PageBean();

tronClassService.getAll(pageBean);

System.out.println("99999:"+pageBean.getTotalPage());

req.getSession().setAttribute("pages",pageBean.getTotalPage());

try {

resp.getWriter().print(pageBean.getTotalPage());

} catch (IOException e) {

e.printStackTrace();

}

}

jsp页面中第一次先创建Session 在servlet的pages方法中获取分页数放入session

var url ="http://localhost:8080/";

$.post(url+"/TronClassServlet?method=pages",

function (data, status) {

}

);

jsp中js的核心代码：使用layui自带分页插件

1.从session域中得到总页数

2.用ajax访问servlet层查出数据并拼接到页面显示

$(document).ready(function () {

var tronClasstype = [];

var dataLength = [];

//分页代码

layui.use(['laypage', 'layer'], function() {

var laypage = layui.laypage

// , layer = layui.layer;

laypage({

cont: 'demo1'

, groups: 5, //连续显示分页数

jump: function(obj, first){

//得到了当前页，用于向服务端请求对应数据

var curr = obj.curr;

$.post("${pageContext.request.contextPath}/TronClassServlet?method=pool",

{

curr:curr

},

function (data, status) {

$(".tr_1").html("");

for (var i = 0; i < data.length; i++) {

dataLength.push(data[i]);

tronClasstype = data[i];

$(".tr_1").append("

" +

"

" + (i + 1) + "" +

"

" + tronClasstype['year'] + "" +

"

" + tronClasstype['tron_month'] + "" +

"

" + tronClasstype['eu_rj'] + "" +

"

" + tronClasstype['eu_xin'] + "" +

"

" + tronClasstype['eu_rw'] + "" +

"

" + tronClasstype['eu_ts'] + "" +

"

" + tronClasstype['eu_xiu'] + "" +

"

" + tronClasstype['eu_gz'] + "" +

"

" + tronClasstype['eu_kuai'] + "" +

"

" + tronClasstype['eu_ad'] + "" +

"

" + tronClasstype['eu_wc'] + "" +

"

" + tronClasstype['eu_wu'] + "" +

"

" + tronClasstype['eu_jr'] + "" +

"

" +

"编辑" +

"删除" +

"

"

);

}

},

"json"

);

}

, pages: //总页数

});

});

});