条件分页查询

条件分页查询（参考版）

1.条件分页查询（标准版）

实现功能如下：

数据库页面：

浏览器页面：

控制台页面：

代码结构布局：

代码：

创建表：

#创建班级表
create table class
(
	classId int primary key auto_increment,
	className varchar(20)
);

insert into class
(className)
values 
('菜鸟1班'),
('菜鸟2班'),
('菜鸟3班'),
('菜鸟4班'),
('菜鸡1班'),
('菜鸡2班'),
('菜鸡3班'),
('菜鸡4班');

select * from class;

select count(*) from class;

BaseDAO：

package com.util;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class BaseDAO {

	//四大金刚
	//驱动类
	private static final String DRIVER="com.mysql.cj.jdbc.Driver";
	//连接地址
	private static final String URL="jdbc:mysql://localhost:3306/70730_db?useSSL=false&characterEncoding=utf8&serverTimezone=Asia/Shanghai";
	//用户名
	private static final String USER="root";
	//密码
	private static final String PASSWORD="123456";

	//获取连接
	public static Connection getConnection(){

		Connection con = null;

		try{
			//加载驱动类
			Class.forName(DRIVER);
			//获取连接
			con = DriverManager.getConnection(URL,USER,PASSWORD);
			
		}catch(Exception ex){
			ex.printStackTrace();
		}

		return con;
	}

	//关闭数据库对象
	public static void closeAll(Connection con,Statement st,ResultSet rs){
		
		if(rs!=null){
			try{
				rs.close();
			}catch(Exception ex){
				ex.printStackTrace();
			}
			
		}

		if(st!=null){

			try{
				st.close();
			}catch(Exception ex){
				ex.printStackTrace();
			}
			
		}

		if(con!=null){
			try{
				con.close();
			}catch(Exception ex){
				ex.printStackTrace();
			}
			
		}

	}


	//通用设置参数方法
	public static void setParams(PreparedStatement pst,Object[] params){

		if(params==null){
			return;
		}

		for(int i=0;i<params.length;i++){
			try{
				pst.setObject(i+1,params[i]);
			}catch(Exception ex){
				ex.printStackTrace();
			}
		}
	}

	//获取总记录数的查询 :select count(*) from ..
	public static int getTotal(String sql,Object[] params){
		int total=0;

		Connection con=null;
		PreparedStatement pst=null;
		ResultSet rs=null;


		try {                                  //手动添加try-catch: ctrl+alt+t
			con=getConnection();
			pst=con.prepareStatement(sql);

			setParams(pst,params);
			rs=pst.executeQuery();

			if(rs.next()){
				total=rs.getInt(1); //获取第一列的值（具体解释请看详细图解）
			}
		} catch (SQLException throwables) {
			throwables.printStackTrace();
		} finally {
			closeAll(con,pst,rs);
		}


		return total;
	}

	//通用增删改
	public static int executeUpdate(String sql,Object[] params){

		Connection con = null;
		PreparedStatement pst = null;
		
		int res = -1;
		
		try{

			//获取连接
			con = getConnection();
			//创建预编译命令执行对象
			pst = con.prepareStatement(sql);
			//设置参数
			setParams(pst,params);
			//执行
			res = pst.executeUpdate();

		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			closeAll(con,pst,null);
		}
		
		return res;
	}


	//通用查询
	public static List<Map<String,Object>> executeQuery(String sql,Object[] params) {

		List<Map<String,Object>> rows = new ArrayList<>();

		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;

		try{
			//获取连接	
			con = getConnection();			
			//获取命令对象
			pst = con.prepareStatement(sql);
			//设置参数
			setParams(pst,params);
			//执行查询
			rs = pst.executeQuery();

			//通过rs获取结果集的结构信息
			ResultSetMetaData rsmd =  rs.getMetaData();
			//获取结果集的列数
			int colCount = rsmd.getColumnCount();

			//遍历查询结果，并封装到List<Map>中
			while(rs.next()){
				//用Map存储当前行的各个列数据
				Map<String,Object> map = new HashMap<>();
				//循环获取每一列的信息
				for(int i=1;i<=colCount;i++){
					//获取列名(使用rsmd)
					String colName = rsmd.getColumnLabel(i);
					//获取列值(使用rs)
					Object colVal = rs.getObject(i);
					//将当前列存储到map中
					map.put(colName,colVal);								
				}
				
				//将遍历的当前行的数据存储到List中
				rows.add(map);
							
			}


		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			closeAll(con,pst,rs);
		}
		
		return rows;

	}

}

ClassInfo：

package com.entity;

public class ClassInfo {
    private Integer classId;
    private String className;

    public ClassInfo() {
    }

    public ClassInfo(String className) {
        this.className = className;
    }

    public ClassInfo(Integer classId, String className) {
        this.classId = classId;
        this.className = className;
    }

    public Integer getClassId() {
        return classId;
    }

    public void setClassId(Integer classId) {
        this.classId = classId;
    }

    public String getClassName() {
        return className;
    }

    public void setClassName(String className) {
        this.className = className;
    }

    @Override
    public String toString() {
        return "Class{" +
                "classId=" + classId +
                ", className='" + className + '\'' +
                '}';
    }
}

PageDate：

package com.entity;

public class PageDate {
    //当前页码
    private Integer pageNo;
    //每页条数
    private Integer pageSize;
    //总记录数
    private Integer totalCount;
    //总页数
    private Integer totalPage;
    //当前页的记录
    private Object data;

    public PageDate() {
    }

    public PageDate(Integer pageSize, Integer totalCount, Integer totalPage, Object data) {
        this.pageSize = pageSize;
        this.totalCount = totalCount;
        this.totalPage = totalPage;
        this.data = data;
    }

    public PageDate(Integer pageNo, Integer pageSize, Integer totalCount, Integer totalPage, Object data) {
        this.pageNo = pageNo;
        this.pageSize = pageSize;
        this.totalCount = totalCount;
        this.totalPage = totalPage;
        this.data = data;
    }

    public Integer getPageNo() {
        return pageNo;
    }

    public void setPageNo(Integer pageNo) {
        this.pageNo = pageNo;
    }

    public Integer getPageSize() {
        return pageSize;
    }

    public void setPageSize(Integer pageSize) {
        this.pageSize = pageSize;
    }

    public Integer getTotalCount() {
        return totalCount;
    }

    public void setTotalCount(Integer totalCount) {
        this.totalCount = totalCount;
    }

    public Integer getTotalPage() {
        return totalPage;
    }

    public void setTotalPage(Integer totalPage) {
        this.totalPage = totalPage;
    }

    public Object getData() {
        return data;
    }

    public void setData(Object data) {
        this.data = data;
    }

    @Override
    public String toString() {
        return "PageDate{" +
                "pageNo=" + pageNo +
                ", pageSize=" + pageSize +
                ", totalCount=" + totalCount +
                ", totalPage=" + totalPage +
                ", data=" + data +
                '}';
    }
}

IClassDAO：

package com.dao;

import com.entity.ClassInfo;
import com.entity.PageDate;

import java.util.List;
import java.util.Map;

public interface IClassDAO {
    List<Map<String,Object>> listAll();

    ClassInfo getByClassId(Integer classId);

    List<Map<String,Object>> listByCon(String searchClassName);

    PageDate listByPage(Integer pageNo,Integer pageSize,String searchClassName);

    int insert(ClassInfo classInfo);
    int update(ClassInfo classInfo);
    int delete(Integer classId);
}

ClassDAOImpl：

package com.dao.impl;

import com.dao.IClassDAO;
import com.entity.ClassInfo;
import com.entity.PageDate;
import com.mysql.cj.util.StringUtils;
import com.util.BaseDAO;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class ClassDAOImpl implements IClassDAO {
    @Override
    public PageDate listByPage(Integer pageNo, Integer pageSize,String searchClassName) {
        StringBuilder whereSql=new StringBuilder(" where 1=1 ");
        List<Object> params=new ArrayList<>();

        if(!StringUtils.isNullOrEmpty(searchClassName)){
            whereSql.append(" and className like ? ");
            params.add("%"+searchClassName+"%");
        }

        //查询总记录数
        String totalSql="select count(*) from class"+whereSql;
        Integer totalCount=BaseDAO.getTotal(totalSql,params.toArray());

        //获取总页数
        Integer totalPage=totalCount%pageSize==0?totalCount/pageSize:totalCount/pageSize+1;

        //limit ?,?; 从哪一条开始，取若干条
        String sql="select * from class "+whereSql+
                "  order by classId "+
                "  limit ?,? ";
        //计算分页开始的位置
        int start=(pageNo-1)*pageSize;
        //构建分页参数
//        Object[] params={
//                start,
//                pageSize
//        };
        params.add(start);
        params.add(pageSize);


        //分页查询：查询当前页若干条的记录数
        List<Map<String,Object>> rows=BaseDAO.executeQuery(sql,params.toArray());

        //将5项数据封装到PageData中
        PageDate pd=new PageDate();
        pd.setPageNo(pageNo);
        pd.setPageSize(pageSize);
        pd.setTotalCount(totalCount);
        pd.setTotalPage(totalPage);
        pd.setData(rows);

        return pd;
    }

    @Override
    public List<Map<String, Object>> listByCon(String searchClassName) {
        StringBuilder whereSql=new StringBuilder();

        List<Object> params=new ArrayList<>();

        if(!StringUtils.isNullOrEmpty(searchClassName)){
            whereSql.append(" where className like ? ");
            params.add("%"+searchClassName+"%");
        }

        String sql="select classId,className " +
                "  from class "+whereSql;
        System.out.println("sql:"+sql);
        System.out.println(params);
        System.out.println("sparams.toArray():"+params.toArray());

        return BaseDAO.executeQuery(sql,params.toArray());

    }

    @Override
    public ClassInfo getByClassId(Integer classId) {
        String sql="select classId,className from class"+
                "   where classId=?";
        Object[] params={classId};
        List<Map<String,Object>> rows = BaseDAO.executeQuery(sql,params);
        if(rows.size()>0){
            Map<String,Object> map= rows.get(0);

            ClassInfo classInfo=new ClassInfo(
                    (Integer) map.get("classId"), //注意强转化 get得到的是Object类型（鼠标放在get上看）
                    (String) map.get("className")
            );
            System.out.println(classInfo);
            return classInfo;
        }
        return null;
    }

    @Override
    public List<Map<String, Object>> listAll() {
        String sql="select * from class";
        return BaseDAO.executeQuery(sql,null);
    }

    @Override
    public int insert(ClassInfo classInfo) {
        String sql="insert into class"+
                "   (className)"+
                "   values"+
                "   (?)";
        Object[] params={classInfo.getClassName()};
        return BaseDAO.executeUpdate(sql,params);
    }

    @Override
    public int update(ClassInfo classInfo) {
        String sql="update class"+
                "   set className=?"+
                "   where classId=?";
        Object[] params={
                classInfo.getClassName(),
                classInfo.getClassId()
        };
        return BaseDAO.executeUpdate(sql,params);
    }

    @Override
    public int delete(Integer classId) {
        String sql="delete from class"+  //注意delete写法
                "   where classId=?";
        Object[] params={classId};
        return BaseDAO.executeUpdate(sql,params);
    }
}

IClassService：

package com.service;

import com.entity.ClassInfo;
import com.entity.PageDate;

import java.util.List;
import java.util.Map;

public interface IClassService {
    List<Map<String,Object>> listAll();

    ClassInfo getByClassId(Integer classId);

    List<Map<String,Object>> listByCon(String searchClassName);

    PageDate listByPage(Integer pageNo,Integer pageSize,String searchClassName);

    int insert(ClassInfo classInfo);
    int update(ClassInfo classInfo);
    int delete(Integer classId);
}

ClassServiceImpl：

package com.service.impl;

import com.dao.IClassDAO;
import com.dao.impl.ClassDAOImpl;
import com.entity.ClassInfo;
import com.entity.PageDate;
import com.service.IClassService;

import java.util.List;
import java.util.Map;

public class ClassServiceImpl implements IClassService {

    IClassDAO classDAO=new ClassDAOImpl();

    @Override
    public PageDate listByPage(Integer pageNo, Integer pageSize,String searchClassName) {
        return classDAO.listByPage(pageNo,pageSize,searchClassName);
    }

    @Override
    public List<Map<String, Object>> listByCon(String searchClassName) {
        return classDAO.listByCon(searchClassName);
    }

    @Override
    public ClassInfo getByClassId(Integer classId) {
        return classDAO.getByClassId(classId);
    }

    @Override
    public List<Map<String, Object>> listAll() {
        return classDAO.listAll();
    }

    @Override
    public int insert(ClassInfo classInfo) {
        return classDAO.insert(classInfo);
    }

    @Override
    public int update(ClassInfo classInfo) {
        return classDAO.update(classInfo);
    }

    @Override
    public int delete(Integer classId) {
        return classDAO.delete(classId);
    }
}

ClassInfoServlet：

package com.servlet;

import com.entity.ClassInfo;
import com.entity.PageDate;
import com.mysql.cj.util.StringUtils;
import com.service.IClassService;
import com.service.impl.ClassServiceImpl;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;
import java.util.Map;

@WebServlet(urlPatterns = "/ClassInfoServlet/*")
public class ClassInfoServlet extends HttpServlet {
    IClassService classService=new ClassServiceImpl();

    @Override
    protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        req.setCharacterEncoding("utf-8");
        resp.setContentType("text/html;charset=utf-8");

        String uri=req.getRequestURI();
        String process=uri.substring(uri.lastIndexOf("/")+1);
        System.out.println("截取字段："+process);

        switch (process){
            case "query":
                this.query(req,resp);
                break;
            case "queryByPage":
                this.queryByPage(req,resp);
                break;
            case "toAdd":
                this.toAdd(req,resp);
                break;
            case "add":
                this.add(req,resp);
                break;
            case "toUpdate":
                this.toUpdate(req,resp);
                break;
            case "update":
                this.update(req,resp);
                break;
            case "delete":
                this.delete(req,resp);
                break;
        }
    }

    private void queryByPage(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        Integer pageNo=1;
        Integer pageSize=3;

        if(!StringUtils.isNullOrEmpty(req.getParameter("pageNo"))){
            pageNo=Integer.parseInt(req.getParameter("pageNo"));
        }
        if(!StringUtils.isNullOrEmpty(req.getParameter("pageSize"))){
            pageSize= Integer.parseInt(req.getParameter("pageSize"));  //Integet.valueOf()和Integet.parseInt()
        }

//        if(req.getParameter("pageNo")!=null){
//            pageNo=Integer.parseInt(req.getParameter("pageNo"));
//        }
//        if(req.getParameter("pageSize")!=null){
//            pageSize= Integer.parseInt(req.getParameter("pageSize"));  //Integet.valueOf()和Integet.parseInt()
//        }

        //获取查询条件
        String searchClassName=req.getParameter("searchClassName");
        System.out.println("searchClassName:"+searchClassName);
        System.out.println("pageNo:"+pageNo);
        System.out.println("pageSize:"+pageSize);

        //调用分页查询
        PageDate pageDate=classService.listByPage(pageNo,pageSize,searchClassName);
        System.out.println("pageDate"+pageDate);

        //存储到req,跳转到页面
        req.setAttribute("pd",pageDate);
        req.getRequestDispatcher("/classList.jsp").forward(req,resp);
    }

//    private void query(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
//        List<Map<String,Object>> classList=classService.listAll();
//
//        req.setAttribute("classList",classList);
//        req.getRequestDispatcher("/classList.jsp").forward(req,resp);
//    }


    private void query(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String searchClassName=req.getParameter("searchClassName");
        List<Map<String,Object>> classList=classService.listByCon(searchClassName);
        //用的特别巧妙，如果searchClassName为空，将查询所有；如果不为空，将模糊查询得相关的数据
        System.out.println(classList);

        req.setAttribute("classList",classList);
        req.getRequestDispatcher("/classList.jsp").forward(req,resp);
    }

    private void toAdd(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        req.getRequestDispatcher("/classAdd.jsp").forward(req,resp);
    }

    private void add(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String className=req.getParameter("className"); //注意 设置值关键字 从输入框获取值的关键字
        ClassInfo ci=new ClassInfo(className);

        int count=classService.insert(ci);

        if(count==1){
            System.out.println("插入成功！");
            this.query(req,resp);
        }else{
            System.out.println("插入失败！");
        }
    }

    private void toUpdate(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        Integer classId = Integer.parseInt(req.getParameter("classId"));
        //根据编号，查询要修改的班级对象
        ClassInfo classInfo =  classService.getByClassId(classId);
        //将要修改的班级对象，存储到request中，带到页面上，呈现出来
        req.setAttribute("classInfo",classInfo);
        //跳转到修改页面
        req.getRequestDispatcher("/classUpdate.jsp").forward(req,resp);

        req.setAttribute("classInfo",classInfo);
        req.getRequestDispatcher("/classUpdate.jsp").forward(req,resp);
    }

    private void update(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        Integer classId = Integer.parseInt(req.getParameter("classId"));
        String className = req.getParameter("className");
        ClassInfo classInfo = new ClassInfo(classId,className);
        //调用修改
        int count = classService.update(classInfo);
        if(count==1){
            //修改成功，则重新显示
            this.query(req,resp);
        }else{
            //跳转到失败
        }

    }

    private void delete(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        Integer classId= Integer.parseInt(req.getParameter("classId"));
        int count =classService.delete(classId);
        if(count==1){
            System.out.println("删除成功！");
            this.query(req,resp);
        }else{
            System.out.println("删除失败！");
        }
    }

}

index.jsp:

<%--
  Created by IntelliJ IDEA.
  User: 33154
  Date: 2022/8/1
  Time: 20:26
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
  <head>
    <title>$Title$</title>
  </head>
  <body>
  $END$
  <a href="${pageContext.request.contextPath}/StudentInfoServlet ">学生信息</a>
  <a href="${pageContext.request.contextPath}/ClassInfoServlet/query">班级信息</a>
  <a href="${pageContext.request.contextPath}/ClassInfoServlet/queryByPage">分页信息</a>
 
  </body>
</html>

classList.jsp:

<%--
  Created by IntelliJ IDEA.
  User: 33154
  Date: 2022/8/2
  Time: 1:18
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<html>
<head>
    <title>Title</title>
<%--    <%=request.getAttribute("classList")%>--%>
</head>
<body>
    班级数据：${classList}
    <table border="1" align="center" width="50%">
        <tr>
            <td colspan="3">
                <a href="${pageContext.request.contextPath}/ClassInfoServlet/toAdd">添加</a>
            </td>
        </tr>
        <tr>
            <td colspan="3">
                <form id="searchFrm" action="${pageContext.request.contextPath}/ClassInfoServlet/queryByPage" method="get">
                    班级：<input type="text" id="searchCls" name="searchClassName" value="${param.searchClassName}"/> <%--value--%>
                    <input type="submit" value="搜索" />
                    <input type="hidden" name="pageNo" id="pageNo" />
                    <input type="hidden" name="pageSize" id="pageSize" />
                </form>
            </td>
        </tr>
        <tr>
            <th>班级编号</th>
            <th>班级名称</th>
            <th>操作</th>
        </tr>
        <c:forEach items="${pd.data}" var="pdd">
            <tr>
                <td>${pdd.classId}</td>
                <td>${pdd.className}</td>
                <td>
                    <a href="${pageContext.request.contextPath}/ClassInfoServlet/toUpdate?classId=${pdd.classId}">修改</a>
                    <a href="${pageContext.request.contextPath}/ClassInfoServlet/delete?classId=${pdd.classId}"
                    onclick="return confirm('你确定要删除吗？')">
                        删除
                    </a>
                </td>
            </tr>
        </c:forEach>
        <%--生成分页信息--%>
        <tr>
            <td colspan="3">
                当前页${pd.pageNo}/共${pd.totalPage}页 |
                总记录数：${pd.totalCount} |
                <c:forEach begin="1" end="${pd.totalPage}" var="i">
                    <c:if test="${pd.pageNo==i}">
                        <span>${i}</span>
                    </c:if>
                    <c:if test="${pd.pageNo!=i}">
                        <a href="javascript:jumpPage(${i})">${i}</a>
                    </c:if>
                    &nbsp;&nbsp;
                </c:forEach>
                每页
                    <select name="pageSize" id="ps" onchange="jumpPage(1)"> <%--onchange--%>
                        <option value="2" ${pd.pageSize==2?'selected':''}>2</option>  <%--option value="2"和2哪个在起作用？--%>
                        <option value="3" ${pd.pageSize==3?'selected':''}>3</option>    <%--注意：代码运行到这里出错了--%>
                        <option value="5" ${pd.pageSize==5?'selected':''}>5</option>
                    </select>
                条
            </td>
        </tr>
    </table>
    <script type="text/javascript">
        //切换每页记录数
        <%--function changePageSize(size){--%>
        <%--    window.location.href="${pageContext.request.contextPath}/ClassInfoServlet/queryByPage?pageSize="+size;--%>
        <%--}--%>

        //跳转分页
        function jumpPage(no){
            document.getElementById("pageNo").value=no;
            document.getElementById("pageSize").value=ps.value;  //ps在 （每页。。条） 下一行的位置
            searchFrm.submit();
        }
    </script>
</body>
</html>

2.扩展

思路图：

// A code block
var foo = 'bar';

// A code block
var foo = 'bar';

// A code block
var foo = 'bar';

// A code block
var foo = 'bar';

// A code block
var foo = 'bar';

// A code block
var foo = 'bar';

// A code block
var foo = 'bar';

// A code block
var foo = 'bar';

// A code block
var foo = 'bar';

// A code block
var foo = 'bar';

// A code block
var foo = 'bar';

// A code block
var foo = 'bar';

// A code block
var foo = 'bar';

// A code block
var foo = 'bar';

// A code block
var foo = 'bar';

// A code block
var foo = 'bar';

// A code block
var foo = 'bar';

// A code block
var foo = 'bar';

// A code block
var foo = 'bar';