配置文件db.properties
db.driver=com.mysql.cj.jdbc.Driver
db.url=jdbc:mysql://localhost:3305/db?useUnicode=true&characterEncoding=utf8&serverTimezone=PRC
db.username=root
db.password=
封装工具类
package com.nnzb;
import java.io.InputStream;
import java.io.StringReader;
import java.sql.*;
import java.util.*;
import java.util.stream.Collectors;
/**
* author:nnzb
* email:xymhxxqw@163.com
* time:17:02
*/
public class DbUtil {
private String driver;
private String url;
private String username;
private String password;
private Connection conn = null;
private int currpage = 1;
private int pagesize = 10;
private int recordcount = 0;
private int pagecount = 1;
public DbUtil() {
connect();
}
public DbUtil(String url, String username, String password) {
connect(url, username, password);
}
public DbUtil(String host, int port, String dbname, String username, String password) {
this.url = String.format("jdbc:mysql://%s:%d/%s?useUnicode=true&characterEncoding=utf8&serverTimezone=PRC", host, port, dbname);
this.username = username;
this.password = password;
connect(url, username, password);
}
public List<Map<String, Object>> page(String sql, Object... params) {
List<Map<String, Object>> list = new ArrayList<>();
// 统计并计算
String sc = "select count(*) ".concat(sql.substring(sql.indexOf("from")));
try {
PreparedStatement psc = conn.prepareStatement(sc);
for (int i = 0; i < params.length; i++) {
psc.setObject(i + 1, params[i]);
}
ResultSet rsc = psc.executeQuery();
rsc.next();
this.recordcount = rsc.getInt(1);
this.pagecount = this.recordcount % this.pagesize == 0 ? this.recordcount / this.pagesize : this.recordcount / this.pagesize + 1;
if (this.currpage < 1) this.currpage = 1;
if (this.currpage > this.pagecount) this.currpage = this.pagecount;
rsc.close();
psc.close();
} catch (Exception e) {
e.printStackTrace();
}
// 查询当前页的数据结果,并返回
String sok = sql.concat(" limit ?,?");
try {
PreparedStatement pst = conn.prepareStatement(sok);
for (int i = 0; i < params.length; i++) {
pst.setObject(i + 1, params[i]);
}
pst.setInt(params.length + 1, this.currpage * this.pagesize - this.pagesize);
pst.setInt(params.length + 2, this.pagesize);
ResultSet rs = pst.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
while (rs.next()) {
Map<String, Object> map = new HashMap<>();
for (int i = 1; i <= rsmd.getColumnCount(); i++) {
String cn = rsmd.getColumnLabel(i);
map.put(cn, rs.getObject(cn));
}
list.add(map);
}
} catch (Exception e) {
e.printStackTrace();
}
// 将分页信息保存到list
StringBuilder sbu = new StringBuilder();
sbu.append(String.format("第%d页/共%d页 每页%d条/共%d条",this.currpage,this.pagecount,this.pagesize,this.recordcount));
Map<String,Object> pinfo = new HashMap<>();
pinfo.put("currpage",this.currpage);
pinfo.put("pagesize",this.pagesize);
pinfo.put("pagecount",this.pagecount);
pinfo.put("recordcount",this.recordcount);
pinfo.put("info",sbu.toString());
list.add(pinfo);
return list;
}
public List<Map<String, Object>> query(String sql, Object... params) {
List<Map<String, Object>> list = new ArrayList<>();
try {
PreparedStatement pst = conn.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
pst.setObject(i + 1, params[i]);
}
ResultSet rs = pst.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
while (rs.next()) {
Map<String, Object> map = new HashMap<>();
for (int i = 1; i <= rsmd.getColumnCount(); i++) {
String cn = rsmd.getColumnLabel(i);
map.put(cn, rs.getObject(cn));
}
list.add(map);
}
} catch (Exception e) {
e.printStackTrace();
}
return list;
}
// 执行sql语句 insert update delete 相关的语句,有响应行
public int update(String sql, Object... params) {
int rows = 0;
try {
PreparedStatement pst = conn.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
pst.setObject(i + 1, params[i]);
}
rows = pst.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}
return rows;
}
public int deleteAll(String tn) {
return deleteByWhere(tn, "1=1");
}
public int count(String tn, String wh, Object... param) {
int rows = 0;
try {
String sql = String.format("select count(*) from %s where %s", tn, wh);
PreparedStatement pst = conn.prepareStatement(sql);
for (int i = 0; i < param.length; i++) {
pst.setObject(i + 1, param[i]);
}
ResultSet rs = pst.executeQuery();
rs.next();
rows = rs.getInt(1);
} catch (Exception e) {
e.printStackTrace();
}
return rows;
}
public int count(String tn) {
int rows = 0;
try {
String sql = String.format("select count(*) from %s", tn);
PreparedStatement pst = conn.prepareStatement(sql);
ResultSet rs = pst.executeQuery();
rs.next();
rows = rs.getInt(1);
} catch (Exception e) {
e.printStackTrace();
}
return rows;
}
public int deleteByWhere(String tn, String wh) {
int rows = 0;
String sql = String.format("delete from %s where %s", tn, wh);
try {
PreparedStatement pst = conn.prepareStatement(sql);
rows = pst.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}
return rows;
}
public int insert(String tn, Map<String, Object> values) {
int rows = 0;
List<String> ks = new ArrayList<>();
List<Object> vs = new ArrayList<>();
values.forEach((k, v) -> {
ks.add(k);
vs.add(v);
});
String[] ww = new String[ks.size()];
Arrays.fill(ww, "?");
System.out.println();
System.out.println();
String sql = String.format("insert into %s(%s) value(%s)", tn, ks.stream().collect(Collectors.joining(",")), Arrays.stream(ww).collect(Collectors.joining(",")));
try {
PreparedStatement pst = conn.prepareStatement(sql);
for (int i = 0; i < vs.size(); i++) {
pst.setObject(i + 1, vs.get(i));
}
rows = pst.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}
return rows;
}
public Set<String> dbs() {
Set<String> set = new HashSet<>();
try {
PreparedStatement pst = this.conn.prepareStatement("show databases");
ResultSet rs = pst.executeQuery();
while (rs.next()) {
set.add(rs.getString(1));
}
rs.close();
pst.close();
} catch (Exception e) {
e.printStackTrace();
}
return set;
}
public Set<String> tbs() {
Set<String> set = new HashSet<>();
List<Map<String, Object>> list = query("show tables");
for (Map<String, Object> mm : list) {
for (String k : mm.keySet()) {
set.add(mm.get(k).toString());
}
}
return set;
}
public Set<String> tbs(String dbname) {
Set<String> set = new HashSet<>();
List<Map<String, Object>> list = query("show tables from " + dbname);
for (Map<String, Object> mm : list) {
for (String k : mm.keySet()) {
set.add(mm.get(k).toString());
}
}
return set;
}
public void connect() {
// 加载src/main/resources/db.properties
try {
InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream("db.properties");
Properties prop = new Properties();
prop.load(is);
this.driver = prop.getProperty("db.driver", "com.mysql.cj.jdbc.Driver");
this.url = prop.getProperty("db.url", "jdbc:mysql://localhost:3306/");
this.username = prop.getProperty("db.username", "root");
this.password = prop.getProperty("db.password", "");
Class.forName(this.driver);
connect(this.url, this.username, this.password);
} catch (Exception e) {
System.out.println("数据库连接失败:" + e.getMessage());
}
}
public void connect(String url, String username, String password) {
try {
this.conn = DriverManager.getConnection(url, username, password);
} catch (Exception e) {
System.out.println("数据库连接失败:" + e.getMessage());
}
}
public void close() {
try {
if (this.conn != null) {
this.conn.close();
}
} catch (Exception e) {
System.out.println("数据库连接关闭失败:" + e.getMessage());
}
}
public Connection getConn() {
return conn;
}
public void setConn(Connection conn) {
this.conn = conn;
}
public String getDriver() {
return driver;
}
public void setDriver(String driver) {
this.driver = driver;
}
public String getUrl() {
return url;
}
public void setUrl(String url) {
this.url = url;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
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 getRecordcount() {
return recordcount;
}
public void setRecordcount(int recordcount) {
this.recordcount = recordcount;
}
public int getPagecount() {
return pagecount;
}
public void setPagecount(int pagecount) {
this.pagecount = pagecount;
}
}
测试代码
package com.nnzb;
import org.junit.jupiter.api.Test;
import javax.management.Query;
import javax.swing.*;
import java.time.LocalDate;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;
/**
* author:nnzb
* email:xymhxxqw@163.com
* time:20:29
*/
public class DbUtilDemo {
@Test
void t2(){
DbUtil du = new DbUtil();
//int rows = du.update("insert into t_student(name,birth,gender,score) values(?,?,?,?)","张三丽", LocalDate.now(),"女",80);
//System.out.println(rows);
// Map<String,Object> map = new HashMap<>();
// map.put("name","张磊");
// map.put("score",88);
// int rows = du.insert("t_student",map);
// System.out.println(rows);
// int rows = du.deleteByWhere("t_student","name like '张%'");
// System.out.println(rows);
// System.out.println(du.count("t_student","name like '周%'"));
// List<Map<String, Object>> list = du.query("select * from t_student order by id desc");
// System.out.println(list);
// 测试分页效果
// t_student 6条记录
du.setPagesize(4);
du.setCurrpage(2);
List<Map<String,Object>> list = du.page("select id,name,score from t_student order by id desc");
// System.out.println(du.getPagecount());
// System.out.println(list);
Map<String,Object> info = list.remove(list.size() - 1);
System.out.println(info.get("info"));
System.out.println(list);
}
@Test
public void t1(){
DbUtil du = new DbUtil("localhost",3306,"dbshop","root","");
System.out.println(du.dbs());
System.out.println(du.tbs());
System.out.println(du.tbs("db"));
System.out.println("********************************");
du.connect();
System.out.println(du.dbs());
System.out.println(du.tbs());
System.out.println(du.tbs("db"));
du.close();
}
}