JdbcUtils工具类

//me.muphy.util

import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
/**
* JdbcUtils
*
* @className: JdbcUtils
* @author: 若非
* @date: 2021-09-24 14:57
*/
public class JdbcUtils {
public static Connection getConnection() {
Connection conn = null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql:///dbpipe", "root", "root");
} catch (Exception e) {
LogUtils.e(JdbcUtils.class.getSimpleName(), e);
}
return conn;
}
/**
* 增删改
*
* @param sql    预编译SQL语句
* @param params 参数
* @return 受影响的记录数目
*/
public static int executeUpdate(String sql, List<Object> params) {
int result = -1;
if (StringUtils.isEmpty(sql)) {
return result;
}
Connection connection = null;
PreparedStatement ps = null;
try {
connection = getConnection();
ps = connection.prepareStatement(sql);
if (params != null) {
for (int i = 0; i < params.size(); i++) {
ps.setObject(i + 1, params.get(i));
}
}
result = ps.executeUpdate();
} catch (SQLException e) {
LogUtils.e(JdbcUtils.class.getSimpleName(), e);
} finally {
release(ps, connection);
}
return result;  // 更新数据失败
    }
/**
* 查
*
* @param sql    预编译SQL语句
* @param params 参数
*/
public static ResultSet executeQuery(String sql, List<Object> params) {
if (StringUtils.isEmpty(sql)) {
return null;
}
Connection connection = null;
PreparedStatement ps = null;
try {
connection = getConnection();
ps = connection.prepareStatement(sql);
if (params != null) {
for (int i = 0; i < params.size(); i++) {
ps.setObject(i + 1, params.get(i));
}
}
ResultSet resultSet = ps.executeQuery();
return resultSet;
} catch (SQLException e) {
LogUtils.e(JdbcUtils.class.getSimpleName(), e);
} finally {
release(ps, connection);
}
return null;  // 更新数据失败
    }
/**
* 查
*
* @param sql    预编译SQL语句
* @param params 参数
*/
public static <T> List<T> executeQuery(String sql, List<Object> params, Class<T> tClass) {
List<T> ts = new ArrayList<>();
if (StringUtils.isEmpty(sql)) {
return ts;
}
ResultSet rs = executeQuery(sql, params);
if (rs == null) {
return ts;
}
List<Field> list = ReflectUtils.getAllFieldList(tClass);
Map<String, Field> fieldMap = toMap(list, field -> field.getName());
//for (Field field : list) {
//Column annotation = field.getAnnotation(Column.class);
//if (annotation == null || StringUtils.isEmpty(annotation.name())) {
//continue;
            }
//String camelCase = StringUtils.getLowerCamelCase(annotation.name());
//fieldMap.put(camelCase, field);
//}
// 获取数据库表结构
        ResultSetMetaData meta;
try {
meta = rs.getMetaData();
while (rs.next()) {
try {
T t = tClass.newInstance();
// 循环获取指定行的每一列的信息
for (int i = 1; i <= meta.getColumnCount(); i++) {
// 当前列名
String colName = meta.getColumnLabel(i);
colName = StringUtils.getLowerCamelCase(colName);
// 获取当前位置的值，返回Object类型
Object value = rs.getObject(i);
ReflectUtils.setData(fieldMap.get(colName), t, value);
}
ts.add(t);
} catch (Exception e) {
LogUtils.e(JdbcUtils.class.getSimpleName(), e);
}
}
} catch (SQLException e) {
e.printStackTrace();
}
return ts;  // 更新数据失败
    }
public static void release(Statement stmt, Connection conn) {
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
LogUtils.w(JdbcUtils.class.getSimpleName(), e);
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
LogUtils.w(JdbcUtils.class.getSimpleName(), e);
}
}
}
public static <K, T> Map<K, T> toMap(Collection<T> collection, Callback<K, T> callback) {
Map<K, T> map = new HashMap<>();
if (collection == null) {
return map;
}
for (T t : collection) {
K k = callback.call(t);
map.put(k, t);
}
return map;
}
interface Callback<K, T> {
K call(T t);
}
}