一、最低级分页
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;
}
}</string,></map<string,></string></string></string,></map<string,></string></string></string,></map<string,></string></=></string,></string,></map<string,></map<string,></map<string,></string,></map<string,></object></map<string,>
fy.jsp
<%@ page import="java.util.Map" %>
<%@ page import="cn.zcx.DbUtil" %>
<%@ page import="java.util.List" %><%-- 3 4 2022 created by intellij idea. user: lenovo date: time: 19:25 to change this template use file | settings 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>
<% 0 1 dbutil du="new" dbutil(); system.out.println(du.show("t_member")); int pagesize="du.getPageSize();" recordcount="du.count("t_member");" pagecount="recordcount" % ? : + 1; currpage="request.getParameter("p")" =="null" integer.parseint(request.getparameter("p").tostring()); if(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%>页 每页<%=pagesize%>条/共<%=recordcount%>页</%=recordcount%></%=pagesize%></%=pagecount%></%=currpage%></span>
<a href="?p=1">首页</a>  
<a href="?p=<%=currpage-1%>">上页</a>  
<a href="?p=<%=currpage+1%>">下页</a>  
<a href="?p=<%=pagecount%>">末页</a>  
</div>
</string,></%></%></body>
</html>
</%@></%--></%@></%@></%@>
二、百度分页
<%@ page import="cn.zcx.Demo2" %>
<%@ page import="java.util.Map" %>
<%@ page import="java.util.List" %>
<%@ page import="cn.zcx.DbUtil" %><%-- 3 4 2022 created by intellij idea. user: lenovo date: time: 20:53 to change this template use file | settings 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];" pagesize="2;" pagecount="a[1];" currpage="request.getParameter("p")==null?" 1:integer.parseint(request.getparameter("p").tostring()); if(currpage <1)>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;" 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\"">下一页</pagecount){></d<></a>",currpage+1));}
out.print(String.format("<a href="\"?p=%d\"">尾页</a>",pageCount));
%>
</=ee;i++){></%></div>
</h3></h3></string,object></map<string,></%></body>
</html>
</%@></%--></%@></%@></%@></%@>
三、bootstrap分页
项目结构
<%@ page import="cn.zcx.Demo2" %>
<%@ page import="cn.zcx.DbUtil" %>
<%@ page import="java.util.Map" %>
<%@ page import="java.util.List" %><%-- 3 5 2022 created by intellij idea. user: lenovo date: time: 9:41 to change this template use file | settings 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];" pagesize="2;" pagecount="a[1];" currpage="request.getParameter("p")==null?" 1:integer.parseint(request.getparameter("p").tostring()); if(currpage <1)>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;" 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\"">上一页</span></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</span></li>",i));
continue;
}
out.print(String.format("<li class="\"page-item\""><a class="\"page-link\"" href="\"?p=%d\"">%<d< a></d<></a></li>",i));
}
if(currpage<pagecount) { out.print(string.format("<li class="\"page-item\""><a class="\"page-link\"" href="\"?p=%d\"">下一页</a>",currpage+1));
}else{
out.print(String.format("<li class="\"page-item" disabled\"><span class="\"page-link\"">下一页</span></li>"));
}
%>
</pagecount)></=ee;i++){></%></ul>
</div>
</h3></h3></string,object></map<string,></%></body>
</html>
</%@></%--></%@></%@></%@></%@>
Original: https://www.cnblogs.com/zcxxcvbn/p/15967146.html
Author: 一份人间烟火
Title: jdbc分页
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/590765/
转载文章受原作者版权保护。转载请注明原作者出处！