mysql模糊查询后分页_mysql笔记十——数据库分页技术（再分页，模糊查询）

1.什么是数据分页：数据分页就是将很多条记录像书本一样分页，每页显示多少行记录；

2.为什么要数据分页：当我们进行sql语句查询时，假如数据有成千上万行记录，如果在同一个页面去显示，那这个页面得有多大，数据就要很多，而我们所需的记录又很少，不使用分页，查看起来那么繁琐，而且一不小心容易看着眼花。使用数据分页，就行书本一样，有页数，一目了然。相当简洁。

3.核心sql语句：SELECT * FROM stud LIMIT m,n ————m表示要显示的页数，n表示显示的记录行数

4.核心思想：

总行数(rows): select count(1) from stud;每页显示的行数(PAGE_SIZE): 固定值---已知的一个常量页数: pageSize= num/n + (num%n==0)?0:1 当前页号: currentPage当前要显示的页面数据的起始行号和终止行号 :startRow: (currentPage-1)*pageSize如何显示从startN开始的pageSize条记录 select * from stud limit startN, pageSize;当前显示的开始页号：showStart=currentPage-showSize/2;当前显示的结束页号：showEnd=showStart+showSize-1;模糊查询：select count(*) from stud where 1=1 and........

5.成果图：

6.代码实现

需要的包和配置文件：

myConUtil.jar----自己写的c3p0pool工具类

commons-dbutils-1.4.jar

mysql-connector-java-5.1.34-bin.jar

c3p0-0.9.1.2.jar

c3p0-config.xml

jsp/jstl/core">

table.css

table{

color: green;

border: 1px solid blue;

border-collapse: collapse;

width:500px;

margin: auto;

}

td{

border: 1px solid blue;

}

th{

border: 1px solid blue;

}

body{

text-align: center;

}

PageServlet.java

package cn.hncu.page1.servlet;

import java.io.IOException;

import java.sql.SQLException;

import java.util.Map;

import javax.servlet.ServletException;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import cn.hncu.page1.domain.Stud;

import cn.hncu.page1.service.IPageService;

import cn.hncu.page1.service.PageService;

public class PageServlet extends HttpServlet {

private IPageService service=new PageService();

public void doGet(HttpServletRequest request, HttpServletResponse response)

throws ServletException, IOException {

doPost(request, response);

}

public void doPost(HttpServletRequest request, HttpServletResponse response)

throws ServletException, IOException {

request.setCharacterEncoding("utf-8");

//这里是搜索区域的操作

Stud stud=null;

if(request.getMethod().equals("POST")){

if (stud==null) {

stud = new Stud();

}

String serachId = request.getParameter("serachId");

String serachName = request.getParameter("serachName");

stud.setId(serachId);

stud.setName(serachName);

request.getSession().setAttribute("stud", stud);

}else{

stud=(Stud) request.getSession().getAttribute("stud");

if (stud==null) {

stud = new Stud();

}

}

//封装studs对象

int currentPage=1;

try {

currentPage = Integer.parseInt(request.getParameter("page"));

} catch (NumberFormatException e) {

currentPage=1;

}

Map map=null;

try {

map=service.query(currentPage,stud);

} catch (SQLException e) {

e.printStackTrace();

}

map.put("currentPage", currentPage);

//显示滚动页号

int showStart=0;//从第几个页号开始显示

int showEnd=0;//从第几个页号结束显示

int showSize=10;//显示多少页数

int pageCount=Integer.parseInt(""+map.get("pageCount"));

if(showSize>pageCount){//显示页数大于于总页数

showStart=1;

showEnd=pageCount;

}else{

if(currentPage<=showSize/2){

showStart=1;

showEnd=showSize;

}else{

showStart=currentPage-showSize/2;

showEnd=showStart+showSize-1;

}

}

if(showEnd>pageCount){

showEnd=pageCount;

showStart=showEnd-showSize;

}

map.put("showStart", showStart);

map.put("showEnd", showEnd);

request.setAttribute("map", map);

request.getRequestDispatcher("/jsps/show.jsp").forward(request, response);

}

}

IPageService.java

package cn.hncu.page1.service;

import java.sql.SQLException;

import java.util.Map;

import cn.hncu.page1.domain.Stud;

public interface IPageService {

public Map query(int currentPage, Stud stud) throws SQLException;

}

PageService.java

package cn.hncu.page1.service;

import java.sql.SQLException;

import java.util.Map;

import cn.hncu.page1.dao.PageDao;

import cn.hncu.page1.dao.PageDaoJdbc;

import cn.hncu.page1.domain.Stud;

public class PageService implements IPageService{

private PageDao dao=new PageDaoJdbc();

@Override

public Map query(int currentPage, Stud stud)

throws SQLException {

return dao.query(currentPage,stud);

}

}

PageDao.java

package cn.hncu.page1.dao;

import java.sql.SQLException;

import java.util.Map;

import cn.hncu.page1.domain.Stud;

public interface PageDao {

public Map query(int currentPage, Stud stud) throws SQLException;

}

PageDaoJdbc.java

package cn.hncu.page1.dao;

import java.sql.SQLException;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

import javax.sql.DataSource;

import org.apache.commons.dbutils.QueryRunner;

import org.apache.commons.dbutils.handlers.MapListHandler;

import org.apache.commons.dbutils.handlers.ScalarHandler;

import org.junit.Test;

import cn.hncu.page1.domain.Stud;

import cn.hncu.page1.service.IPageService;

import cn.hncu.pool.C3p0Pool;

public class PageDaoJdbc implements PageDao{

private static final int PAGE_SIZE=10;

@Override

public Map query(int currentPage, Stud stud) throws SQLException {

Map map=new HashMap();

DataSource pool=C3p0Pool.getPool();

QueryRunner qr=new QueryRunner(pool);

String sql="select count(*) from stud where 1=1 ";

if(stud.getId()!=null&&stud.getId().trim().length()>0){

sql+="and id like '%"+stud.getId()+"%'";

}

if(stud.getName()!=null&&stud.getName().trim().length()>0){

sql+="and name like '%"+stud.getName()+"%'";

}

int rows=Integer.parseInt(""+ qr.query(sql, new ScalarHandler()));

int pageCount=rows/PAGE_SIZE+((rows%PAGE_SIZE==0)?0:1);

map.put("pageCount", pageCount);

int startRow=(currentPage-1)*PAGE_SIZE;

map.put("startRow", startRow);

String sql2="select * from stud where 1=1 ";//这种判断方法，很不错

if(stud.getId()!=null&&stud.getId().trim().length()>0){

sql2+="and id like '%"+stud.getId()+"%'";

}

if(stud.getName()!=null&&stud.getName().trim().length()>0){

sql2+="and name like '%"+stud.getName()+"%' ";

}

sql2+="limit "+startRow+" , "+PAGE_SIZE;

List> datas=qr.query(sql2, new MapListHandler());

map.put("datas", datas);

return map;

}

}

Stud.java

package cn.hncu.page1.domain;

public class Stud {

private String id;

private String name;

public String getId() {

return id;

}

public void setId(String id) {

this.id = id;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

@Override

public String toString() {

return "Stud [id=" + id + ", name=" + name + "]";

}

}

%@taglib>%@>