用 jdbc 实现简单数据库查询工具类

statement.setDouble(i + 1, (Double) o);

} else if (o instanceof String) {

statement.setString(i + 1, (String) o);

} else if (o instanceof Date) {

statement.setDate(i + 1, (Date) o);

} else if (o instanceof java.util.Date) {

java.util.Date date = (java.util.Date) o;

statement.setDate(i + 1, new Date(date.getTime()));

} else {

statement.setObject(i + 1, o);

}

}

return statement.execute();

}

}

配置类

---

/**

• @author 鸭园园长

• @date 2021/7/24 16:24

• @description DBUtil 属性配置类

*/

public class DBUtilProperties {

private String url = “jdbc:mysql://localhost:3306/your_schema_name?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC”;

private String user = “your_username”;

private String pass = “your_password”;

public DBUtilProperties() {

}

public DBUtilProperties(String url, String user, String pass) {

this.url = url;

this.user = user;

this.pass = pass;

}

public DBUtilProperties setUrl(String url) {

this.url = url;

return this;

}

public DBUtilProperties setUser(String user) {

this.user = user;

return this;

}

public DBUtilProperties setPass(String pass) {

this.pass = pass;

return this;

}

public String getUrl() {

return url;

}

public String getUser() {

return user;

}

public String getPass() {

return pass;

}

}

工厂类

---

/**

• @author 鸭园园长

• @date 2021/7/24 17:21

• @description DBUtil 工厂类

*/

public class DBUtilFactory {

private static final String DRIVER = “com.mysql.cj.jdbc.Driver”;

private static DBUtil dbUtil;

private final DBUtilProperties properties;

static {

try {

Class.forName(DRIVER);

} catch (ClassNotFoundException e) {

e.printStackTrace();

}

}

public DBUtilFactory(DBUtilProperties properties) {

this.properties = properties;

}

/**

• 获取 DBUtil 实例

*/

public DBUtil getDBUtil() {

if (dbUtil == null) {

dbUtil = new DBUtil(properties);

}

return dbUtil;

}

}

实例

============================================================

数据库设计

---

数据库 sql

---

SET FOREIGN_KEY_CHECKS=0;

---

– Table structure for reports

---

DROP TABLE IF EXISTS reports;

CREATE TABLE reports (

id int NOT NULL AUTO_INCREMENT COMMENT ‘主键’,

username varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT ‘用户id’,

complete1 varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT ‘’,

complete2 varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT ‘’,

complete3 varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT ‘’,

complete4 varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT ‘’,

complete5 varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT ‘’,

summary1 varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT ‘’,

summary2 varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT ‘’,

summary3 varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT ‘’,

summary4 varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT ‘’,

summary5 varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT ‘’,

deadline1 varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT ‘’,

deadline2 varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT ‘’,

deadline3 varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT ‘’,

deadline4 varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT ‘’,

deadline5 varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT ‘’,

overview varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT ‘’,

year_n_week int NOT NULL,

PRIMARY KEY (id)

) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

---

– Records of reports

---

---

– Table structure for user

---

DROP TABLE IF EXISTS user;

CREATE TABLE user (

id int NOT NULL COMMENT ‘主键’,

username varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT ‘用户名’,

password varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT ‘密码’,

identity int NOT NULL COMMENT ‘身份（1/学生、2/老师）’,

PRIMARY KEY (id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

---

– Records of user

---

INSERT INTO user VALUES (‘0’, ‘ahy231’, ‘asdf’, ‘2’);

测试类

---

import java.lang.reflect.Proxy;

import java.sql.SQLException;

import java.util.Calendar;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

/**

• @author 鸭园园长

• @date 2021/7/24 18:06

• @description

*/

public class DBUtilTest {

private final DBUtilFactory dbUtilFactory = new DBUtilFactory(new DBUtilProperties()

.setUrl(“jdbc:mysql://localhost:3306/weekly_report?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC”)

.setUser(“common_user”)

.setPass(“123456”));

public static void main(String[] args) {

DBUtilTest test = new DBUtilTest();

System.out.println(“输出 ahy231 的身份”);

System.out.println(test.getIdentityByUsername(“ahy231”));

Map<String, Object> map = new HashMap<>();

map.put(“username”, “ahy231”);

map.put(“complete1”, “完成complete1”);

map.put(“complete2”, “完成complete2”);

map.put(“complete3”, “完成complete3”);

map.put(“complete4”, “完成complete4”);

map.put(“complete5”, “完成complete5”);

map.put(“summary1”, “完成summary1”);

map.put(“summary2”, “完成summary2”);

map.put(“summary3”, “完成summary3”);

map.put(“summary4”, “完成summary4”);

map.put(“summary5”, “完成summary5”);

map.put(“deadline1”, “2021-07-25 22:00:00”);

map.put(“deadline2”, “2021-07-25 22:00:00”);

map.put(“deadline3”, “2021-07-25 22:00:00”);

map.put(“deadline4”, “2021-07-25 22:00:00”);

map.put(“deadline5”, “2021-07-25 22:00:00”);

map.put(“overview”, “完成所有任务”);

test.sendReportByUsername(map);

System.out.println(“输出 ahy231 的本周周报”);

Map<String, String> resultMap = test.getReportByUsername(“ahy231”);

resultMap.forEach((k, v) -> System.out.println(k + ": " + v));

}

/**

• 发送报告

*/

@SuppressWarnings(“unchecked”)

public void sendReportByUsername(Map<String, Object> data) {

Map<String, Object> map = (Map<String, Object>) Proxy.newProxyInstance(data.getClass().getClassLoader(),

data.getClass().getInterfaces(), (proxy, method, args) -> {

if (args[0] instanceof String) {

return emptyIfNull(method.invoke(data, args));

} else {

return method.invoke(data, args);

}

});

Calendar instance = Calendar.getInstance();

int weekYear = instance.getWeekYear();

int weeksInWeekYear = instance.getWeeksInWeekYear();

int yearWeek = weekYear * 100 + weeksInWeekYear;

DBUtil dbUtil = dbUtilFactory.getDBUtil();

String sql = “insert into reports (” +

"username, " +

"complete1, " +

"complete2, " +

"complete3, " +

"complete4, " +

"complete5, " +

"summary1, " +

"summary2, " +

"summary3, " +

"summary4, " +

"summary5, " +

"deadline1, " +

"deadline2, " +

"deadline3, " +

"deadline4, " +

"deadline5, " +

"overview, " +

"year_n_week) " +

“values (” +

“?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)”;

try {

dbUtil.execute(sql, map.get(“username”),

map.get(“complete1”),

map.get(“complete2”),

map.get(“complete3”),

map.get(“complete4”),

map.get(“complete5”),

map.get(“summary1”),

map.get(“summary2”),

map.get(“summary3”),

小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。

深知大多数初中级Java工程师，想要提升技能，往往是自己摸索成长，但自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

因此收集整理了一份《2024年最新Java开发全套学习资料》送给大家，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。

由于文件比较大，这里只是将部分目录截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频

如果你觉得这些内容对你有帮助，可以添加下面V无偿领取！（备注Java）

线程、数据库、算法、JVM、分布式、微服务、框架、Spring相关知识

一线互联网P7面试集锦+各种大厂面试集锦

学习笔记以及面试真题解析

024年最新Java开发全套学习资料》送给大家，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。**
[外链图片转存中…(img-2KhcYvmj-1710428273396)]
[外链图片转存中…(img-yFJeBK3f-1710428273397)]
[外链图片转存中…(img-YBL1GFMp-1710428273398)]

由于文件比较大，这里只是将部分目录截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频

如果你觉得这些内容对你有帮助，可以添加下面V无偿领取！（备注Java）
[外链图片转存中…(img-h97oS2Tc-1710428273398)]

线程、数据库、算法、JVM、分布式、微服务、框架、Spring相关知识

[外链图片转存中…(img-XhtoP0dC-1710428273399)]

一线互联网P7面试集锦+各种大厂面试集锦

[外链图片转存中…(img-9g25wAYp-1710428273399)]

学习笔记以及面试真题解析

[外链图片转存中…(img-DtbwOxP2-1710428273400)]

本文已被CODING开源项目：【一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码】收录