设计
一般情况下,直接用 jdbc
连接数据库要写一堆代码,非常麻烦。
//1.加载驱动程序
Class.forName("com.mysql.cj.jdbc.Driver");
//2. 获得数据库连接
Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
//3.操作数据库,实现增删改查
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT user_name, age FROM imooc_goddess");
//如果有数据,rs.next()返回true
while(rs.next()){
System.out.println(rs.getString("user_name")+" 年龄:"+rs.getInt("age"));
}
于是,可以实现一个工具类:
public class DBUtil {
private static final String URL = "db_url";
private static final String USER = "db_user";
private static final String PASS = "db_pass";
// 查询操作
public List<List<Object>> query() {
//1.加载驱动程序
Class.forName("com.mysql.cj.jdbc.Driver");
//2. 获得数据库连接
Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
//3.操作数据库,实现增删改查
PreparedStatement statement = conn.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
Object o = args[i];
if (o instanceof Integer) {
statement.setInt(i + 1, (Integer) o);
} else if (o instanceof Long) {
statement.setLong(i + 1, (Long) o);
} else if (o instanceof Float) {
statement.setFloat(i + 1, (Float) o);
} else if (o instanceof Double) {
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);
}
}
ResultSet resultSet = statement.executeQuery();
List<List<Object>> result = new ArrayList<>();
while (resultSet.next()) {
List<Object> line = new ArrayList<>();
result.add(line);
for (int i = 0; i < resultNumber; i++) {
line.add(resultSet.getObject(i));
}
}
return result;
}
}
但是这样查询数据库,每次查询都需要重新连接数据库。于是,可以通过配置类和工厂类的方式生成一个单例 DBUtil
。
DBUtil.java
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
/**
* @author 鸭园园长
* @date 2021/7/24 15:54
* @description 数据库连接工具类
*/
public class DBUtil {
private final Connection conn;
DBUtil(DBUtilProperties properties) {
Connection connection = null;
try {
connection = DriverManager.getConnection(properties.getUrl(), properties.getUser(), properties.getPass());
} catch (SQLException e) {
e.printStackTrace();
}
this.conn = connection;
}
/**
* 查询
* @param sql sql 语句
* @param resultNumber 结果数量
* @param args 参数
* @return 按行返回数据
*/
public List<List<Object>> query(String sql, int resultNumber, Object... args) throws SQLException {
PreparedStatement statement = conn.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
Object o = args[i];
if (o instanceof Integer) {
statement.setInt(i + 1, (Integer) o);
} else if (o instanceof Long) {
statement.setLong(i + 1, (Long) o);
} else if (o instanceof Float) {
statement.setFloat(i + 1, (Float) o);
} else if (o instanceof Double) {
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);
}
}
ResultSet resultSet = statement.executeQuery();
List<List<Object>> result = new ArrayList<>();
while (resultSet.next()) {
List<Object> line = new ArrayList<>();
result.add(line);
for (int i = 0; i < resultNumber; i++) {
line.add(resultSet.getObject(i));
}
}
return result;
}
/**
* 执行
* @param sql sql 语句
* @param args 参数
* @return statement.execute 返回值
*/
public boolean execute(String sql, Object... args) throws SQLException {
PreparedStatement statement = conn.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
Object o = args[i];
if (o instanceof Integer) {
statement.setInt(i + 1, (Integer) o);
} else if (o instanceof Long) {
statement.setLong(i + 1, (Long) o);
} else if (o instanceof Float) {
statement.setFloat(i + 1, (Float) o);
} else if (o instanceof Double) {
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"),
map.get("summary4"),
map.get("summary5"),
map.get("deadline1"),
map.get("deadline2"),
map.get("deadline3"),
map.get("deadline4"),
map.get("deadline5"),
map.get("overview"),
yearWeek);
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 获取用户身份
* @return 返回 null 代表没有此用户,否则返回用户身份
*/
public int getIdentityByUsername(String username) {
int identity = -1;
DBUtil dbUtil = dbUtilFactory.getDBUtil();
String sql = "select identity from user where username = ?";
try {
List<List<Object>> query = dbUtil.query(sql, 1, username);
identity = (int) query.get(0).get(0);
} catch (SQLException e) {
e.printStackTrace();
}
return identity;
}
/**
* 获取用户本周报表
* @param username 用户名
* @return 包含报表信息的 map
*/
public Map<String, String> getReportByUsername(String username) {
Calendar instance = Calendar.getInstance();
int currentWeekYear = instance.getWeekYear();
int currentWeeksInWeekYear = instance.getWeeksInWeekYear();
int currentYearWeek = currentWeekYear * 100 + currentWeeksInWeekYear;
Map<String, String> result = null;
DBUtil dbUtil = dbUtilFactory.getDBUtil();
String sql = "select complete1, " +
"complete2, " +
"complete3, " +
"complete4, " +
"complete5," +
"summary1, " +
"summary2, " +
"summary3, " +
"summary4, " +
"summary5, " +
"deadline1, " +
"deadline2, " +
"deadline3, " +
"deadline4," +
"deadline5, " +
"overview, " +
"year_n_week as yearWeek " +
"from reports " +
"where username = ? " +
"order by year_n_week desc " +
"limit 2";
try {
List<List<Object>> query = dbUtil.query(sql, 17, username);
result = new HashMap<>();
if (query.size() == 0) {
for (int i = 0; i < 5; i++) {
result.put("complete" + (i + 1), "");
result.put("summary" + (i + 1), "");
result.put("deadline" + (i + 1), "");
result.put("lastSummary" + (i + 1), "");
result.put("lastDeadline" + (i + 1), "");
}
} else {
List<Object> row = query.get(0);
if (row.get(16).equals(currentYearWeek)) { // 第一条数据是本周
for (int i = 0; i < 5; i++) {
result.put("complete" + (i + 1), (String) row.get(1 + i));
result.put("summary" + (i + 1), (String) row.get(5 + i));
result.put("deadline" + (i + 1), (String) row.get(10 + i));
}
if (query.size() > 1) {
List<Object> lastRow = query.get(1);
for (int i = 0; i < 5; i++) {
result.put("lastSummary" + (i + 1), (String) lastRow.get(5 + i));
result.put("lastDeadline" + (i + 1), (String) lastRow.get(10 + i));
}
} else {
for (int i = 0; i < 5; i++) {
result.put("lastSummary" + (i + 1), "");
result.put("lastDeadline" + (i + 1), "");
}
}
} else { // 第一条数据不是本周
for (int i = 0; i < 5; i++) {
result.put("complete" + (i + 1), "");
result.put("summary" + (i + 1), "");
result.put("deadline" + (i + 1), "");
result.put("lastSummary" + (i + 1), (String) row.get(5 + i));
result.put("lastDeadline" + (i + 1), (String) row.get(10 + i));
}
}
}
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
private String emptyIfNull(Object data) {
return data != null ? (String) data : "";
}
}