jdbc分页

一、最低级分页

resources下的db.properties

db.driver=com.mysql.cj.jdbc.Driver
db.url=jdbc:mysql://localhost:3305/db
db.user=root
db.password=
db.pageSize=3

Demo2.java

package cn.zcx;

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Map;

/**
 * description:
 * author:zcx
 * lenovo
 * 时间：2022.03.04.19.52
 */
public class Demo2 {
    DbUtil du = new DbUtil();
    //获取查询数量
    public int m1(String sql){
        int recordcount=du.count(sql);//查询的总数
          return recordcount;
        }
        //返回一个数组存放当前总数，页数，设置每页数量
        public int[] m2(int pageSize, String sql ){
            int recordcount=m1(sql);//当前总数
            int pagecount=recordcount/ pageSize ==0? recordcount/ pageSize :recordcount/ pageSize +1;//页数
            int[] l=new int[]{recordcount,pagecount,pageSize};
            return l;
        }
}

DbUtil工具类

package cn.zcx;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.*;
import java.util.stream.Collectors;

/**
 * description:
 * author:zcx
 * lenovo
 * 时间：2022.03.04.14.14
 */
public class DbUtil {
    private String mysqlversion = "8.0.28";
    private String version = "1.0";
    private String dname = "db";
    private String user = "root";
    private String password = "";
    private String url = "jdbc:mysql://localhost:3306";
    private String driver = "com.mysql.cj.jdbc.Driver";
    private Connection conn = null;
    private int currPage = 1;
    private int pageSize = 10;
    private int pageCount = 0;
    private int recordCount = 0;
    private InputStream is;

    public DbUtil() {
        is = Thread.currentThread().getContextClassLoader().getResourceAsStream("db.properties");
        if (is == null) {
            try {
                Class.forName(this.driver);
                this.conn = DriverManager.getConnection(this.url, this.user, this.password);
                this.mysqlversion = this.conn.getMetaData().getDatabaseProductVersion();
            } catch (Exception e) {
                e.printStackTrace();
            }
        } else {
            init();
        }
    }
//数据库连接
    public void init() {
        Properties prop = new Properties();
        try {
            prop.load(is);
            this.driver = prop.getProperty("db.driver", "com.mysql.cj.jdbc.Driver");
            this.url = prop.getProperty("db.url", "jdbc:mysql://localhost:3305/");
            this.user = prop.getProperty("db.username", "root");
            this.password = prop.getProperty("db.password", "");
            this.pageSize = Integer.parseInt(prop.getProperty("db.pageSize", "3"));
            Class.forName(this.driver);
            this.conn = DriverManager.getConnection(this.url, this.user, this.password);
            this.mysqlversion = this.conn.getMetaData().getDatabaseProductVersion();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void connect(String host, String db, String user, String password, int port) {
        try {
            this.url = String.format("jdbc:mysql://%s:%d/%s?useUnicode=true&characterEncoding=utf8&serverTimezone=PRC", host, port, db);
            this.user = user;
            this.password = password;
            Class.forName(this.driver);
            this.conn = DriverManager.getConnection(this.url, this.user, this.password);
            this.mysqlversion = this.conn.getMetaData().getDatabaseProductVersion();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public int count(String sql, Object... params) {
        int rows = 0;
        List<Map<String, Object>> list = this.query(sql, params);
        Collection<Object> col = list.get(0).values();
        for (Object o : col) {
            rows = Integer.parseInt(o.toString());
        }
        return rows;
    }
//给一个表名查询所有数量
    public int count(String tn) {
        int rows = 0;
        String sql = String.format("select count(0) from `%s` where 1=1", tn);
        try {
            PreparedStatement p = this.conn.prepareStatement(sql);
            ResultSet rs = p.executeQuery();
            if (rs.next()) {
                rows = rs.getInt(1);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return rows;
    }

    public void execute(String sql) {
        try {
            PreparedStatement p = this.conn.prepareStatement(sql);
            p.execute();
            p.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public String show(String t) {
        List<Map<String, Object>> list = this.query(String.format("show create table `%s`", t));
        return list.get(0).get("Create Table").toString();
    }

    public int save(String sql, Object... params) {
        return update(sql, params);
    }

    public int save(Map<String, Object> values) {
        String t = values.get("table").toString();
        values.remove("table");
        String[] vs = new String[values.size()];
        int index = 0;
        for (String s : values.keySet()) {
            vs[index++] = String.format("%s='%s'", s, values.get(s));
        }
        String sql = String.format("insert into `%s` set %s", t, Arrays.stream(vs).collect(Collectors.joining(",")));
        System.out.println(sql);
        return save(sql);
    }

    public int delete(String sql, Object... params) {
        return update(sql, params);
    }

    public int update(String sql, Object... params) {
        int rows = 0;
        try {
            PreparedStatement p = this.conn.prepareStatement(sql);
            int index = 0;
            for (Object param : params) {
                p.setObject(++index, param);
            }
            rows = p.executeUpdate();
            p.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return 0;
    }
//查询分页数据放到list
    public List<Map<String, Object>> query(String sql, Object... params) {
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        try {
            PreparedStatement p = this.conn.prepareStatement(sql);
            int index = 0;
            for (Object param : params) {
                p.setObject(++index, param);
            }
            ResultSet rs = p.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            while (rs.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                    String t = rsmd.getColumnLabel(i);
                    map.put(t, rs.getObject(t));
                }
                list.add(map);
            }
            rs.close();
            p.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    public List<String> dbs() {
        List<String> list = new ArrayList<>();
        List<Map<String, Object>> ds = this.query("show databases");
        for (Map<String, Object> d : ds) {
            list.add(d.get("Database").toString());
        }
        return list;
    }

    public List<String> tbs(String db) {
        List<String> list = new ArrayList<>();
        List<Map<String, Object>> ds = this.query(String.format("show tables from `%s`", db));
        for (Map<String, Object> d : ds) {
            list.add(d.get(String.format("Tables_in_%s", db)).toString());
        }
        return list;
    }

    public List<String> tbs() {
        List<String> list = new ArrayList<>();
        List<Map<String, Object>> ds = this.query("show tables");
        for (Map<String, Object> d : ds) {
            list.add(d.get(String.format("Tables_in_%s", this.dname)).toString());
        }
        return list;
    }


    public String getVersion() {
        return version;
    }

    public void setVersion(String version) {
        this.version = version;
    }

    public String getDname() {
        return dname;
    }

    public void setDname(String dname) {
        try {
            this.dname = this.conn.getCatalog();
        } catch (Exception e) {
            e.printStackTrace();
        }
        this.dname = dname;
    }

    public String getUser() {
        return user;
    }

    public void setUser(String user) {
        this.user = user;
    }

    public String getUrl() {
        return url;
    }

    public void setUrl(String url) {
        this.url = url;
    }

    public Connection getConn() {
        return conn;
    }

    public void setConn(Connection conn) {
        this.conn = conn;
    }

    public int getCurrPage() {
        return currPage;
    }

    public void setCurrPage(int currPage) {
        this.currPage = currPage;
    }

    public int getPageSize() {
        return pageSize;
    }

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

    public int getPageCount() {
        return pageCount;
    }

    public void setPageCount(int pageCount) {
        this.pageCount = pageCount;
    }

    public int getRecordCount() {
        return recordCount;
    }

    public void setRecordCount(int recordCount) {
        this.recordCount = recordCount;
    }

    public String getDriver() {
        return driver;
    }

    public void setDriver(String driver) {
        this.driver = driver;
    }

    public InputStream getIs() {
        return is;
    }

    public void setIs(InputStream is) {
        this.is = is;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public void close() {
        try {
            if (this.conn != null) {
                this.conn.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public String getMysqlversion() {
        return mysqlversion;
    }

    public void setMysqlversion(String mysqlversion) {
        this.mysqlversion = mysqlversion;
    }
}

fy.jsp

<%@ page import="java.util.Map" %>
<%@ page import="cn.zcx.DbUtil" %>
<%@ page import="java.util.List" %><%--
  Created by IntelliJ IDEA.
  User: lenovo
  Date: 2022/3/4
  Time: 19:25
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>Title</title>
    <style>

        h3{
            padding: 3px;
            border-bottom: 1px dashed gray;
            text-align: center;
            width: 500px;
        }
        img{
            height: 150px;
        }
    </style>
</head>
<body>
<%
    DbUtil du = new DbUtil();
    System.out.println(du.show("t_member"));
    int pagesize = du.getPageSize();
    int recordcount = du.count("t_member");
    int pagecount = recordcount % pagesize == 0 ? recordcount / pagesize : recordcount / pagesize + 1;
    int currpage = request.getParameter("p") == null ? 1 : Integer.parseInt(request.getParameter("p").toString());
    if(currpage<1) currpage=1;
    if(currpage>pagecount) currpage = pagecount;
%>
<hr>
<%
    List<Map<String, Object>> list = du.query("select * from t_member order by id desc limit ?,?",currpage*pagesize-pagesize,pagesize);
    for (Map<String, Object> m : list) {
        out.print("<h3>" + m.get("truename") + "</h3>");

    }
%>
<hr>
<div class="page">
    <span>第<%=currpage%>页/共<%=pagecount%>页&nbsp;每页<%=pagesize%>条/共<%=recordcount%>页</span>
    <a href="?p=1">首页</a> &nbsp;
    <a href="?p=<%=currpage-1%>">上页</a> &nbsp;
    <a href="?p=<%=currpage+1%>">下页</a> &nbsp;
    <a href="?p=<%=pagecount%>">末页</a> &nbsp;
</div>
</body>
</html>

二、百度分页

<%@ page import="cn.zcx.Demo2" %>
<%@ page import="java.util.Map" %>
<%@ page import="java.util.List" %>
<%@ page import="cn.zcx.DbUtil" %><%--
  Created by IntelliJ IDEA.
  User: lenovo
  Date: 2022/3/4
  Time: 20:53
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>Title</title>
  <style>

      h3{
          padding:3px;
          border-bottom: 1px dashed gray;
          width: 700px;
      }
      .page a{
          text-decoration: none;
          padding:  2px;
          margin: 2px;
          border: 1px solid gray;
      }
      .page span{
          text-decoration: none;
          padding:  2px;
          margin: 2px;
          background-color: aqua;
          border: 1px solid gray;
      }
  </style>
</head>
<body>
<%
    Demo2 d=new Demo2();
    DbUtil du=new DbUtil();
    int[] a=d.m2(2,"t_member");
    int num=a[0];
    int pageSize=2;
    int pageCount=a[1];
    int currpage=request.getParameter("p")==null? 1:Integer.parseInt(request.getParameter("p").toString());
    if(currpage <1) currpage=1;
    if(currpage>pageCount) currpage=pageCount;
    List<Map<String, Object>> list = du.query("select *from t_member order by id desc limit ?,?",currpage*pageSize-pageSize,pageSize);
    for (Map<String,Object> m:list) {
        out.print("<h3>"+m.get("truename")+"<h3>");
        out.print("<hr>");
    }
%>
<hr>
<div class="page">
    <%
        int ss=1;
        int ee=10;
        if(currpage>5){
            ss=currpage-5;
            ee=currpage+5;
        }
        out.print("<a href=\"?p=1\">首页</a>");
        if(currpage>1){
        out.print(String.format("<a href=\"?p=%d\">上一页</a>",currpage-1));}
        for(int i=ss;i<=ee;i++){
            if(i>pageCount){
                break;
            }
            if(i==currpage){
                out.print(String.format("<span>%d</span>",i));
                continue;
            }
            out.print(String.format("<a href=\"?p=%d\">%<d</a>",i));
        }
        if(currpage<pageCount){
            out.print(String.format("<a href=\"?p=%d\">下一页</a>",currpage+1));}
        out.print(String.format("<a href=\"?p=%d\">尾页</a>",pageCount));
    %>
</div>

</body>
</html>

三、bootstrap分页

项目结构

<%@ page import="cn.zcx.Demo2" %>
<%@ page import="cn.zcx.DbUtil" %>
<%@ page import="java.util.Map" %>
<%@ page import="java.util.List" %><%--
  Created by IntelliJ IDEA.
  User: lenovo
  Date: 2022/3/5
  Time: 9:41
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>Title</title>
<link rel="stylesheet" href="bootstrap/css/bootstrap.css">
    <style>

        h3{
            padding:3px;
            border-bottom: 1px dashed gray;
            width: 700px;
        }
        .page a{
            text-decoration: none;
            padding:  2px;
            margin: 2px;
            border: 1px solid gray;
        }
        .page span{
            text-decoration: none;
            padding: 2px;
            margin: 2px;
            background-color: rgba(34, 104, 232, 0.54);
            color:#fff;
            border:1px solid gray;
        }
    </style>
</head>
<body>
<%
    Demo2 d=new Demo2();
    DbUtil du=new DbUtil();
    int[] a=d.m2(2,"t_member");
    int num=a[0];
    int pageSize=2;
    int pageCount=a[1];
    int currpage=request.getParameter("p")==null? 1:Integer.parseInt(request.getParameter("p").toString());
    if(currpage <1) currpage=1;
    if(currpage>pageCount) currpage=pageCount;
    List<Map<String, Object>> list = du.query("select *from t_member order by id desc limit ?,?",currpage*pageSize-pageSize,pageSize);
    for (Map<String,Object> m:list) {
        out.print("<h3>"+m.get("truename")+"<h3>");
        out.print("<hr>");
    }
%>
<hr>
<div class="page">
    <ul class="pagination pagination-sm justify-content-center">
        <%
            int ss = 1;
            int ee = 10;
            if(currpage>5){
                ss = currpage-5;
                ee = currpage+5;
            }
            if(currpage>1) {
                out.print(String.format("<li class=\"page-item\"><a class=\"page-link\" href=\"?p=%d\">上一页</a></li>",currpage-1));
            }else{
                out.print(String.format("<li class=\"page-item disabled\"><span class=\"page-link\">上一页</a></li>"));
            }
            for(int i = ss;i<=ee;i++){
                if(i>pageCount) break;
                if(i == currpage){
                    out.print(String.format("<li class=\"page-item active\"><span class=\"page-link\">%d</a></li>",i));
                    continue;
                }
                out.print(String.format("<li class=\"page-item\"><a class=\"page-link\" href=\"?p=%d\">%<d</a></li>",i));
            }
            if(currpage<pageCount) {
                out.print(String.format("<li class=\"page-item\"><a class=\"page-link\" href=\"?p=%d\">下一页</a></li>",currpage+1));
            }else{
                out.print(String.format("<li class=\"page-item disabled\"><span class=\"page-link\">下一页</a></li>"));
            }
        %>
    </ul>
</div>
</body>
</html>