之前写过一个JDBC通用的DAO工具类,感觉不完美的地方是用到了第三方的BeanUtils的jar包,那就没什么意思了。现在改进一下,模拟一个Spring的JdbcTemplate工具类。使用的时候,传入一个数据源就可以了。
最近又对这个工具类进行了升级,做到了更多的功能,比如:多表查询,封装成Map,返回自增的主键。
package com.newboy.util;
import javax.sql.DataSource;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
/**
* 通用的JDBC工具类
* @author NewBoy
* @since 2024-1-12
* @version 2.0
*/
public class JdbcTemplate<T> {
//数据源
private DataSource dataSource;
/**
* 传入一个数据源创建对象
*/
public JdbcTemplate(DataSource dataSource) {
this.dataSource = dataSource;
}
/**
* 获取连接对象
*/
public Connection getConnection() {
try {
return dataSource.getConnection();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
/**
* 关闭所有的资源
*/
public void close(Connection connection, Statement statement, ResultSet resultSet) {
//先关闭结果集
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
//关闭语句对象
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
//关闭连接对象
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 关闭所有的资源
*/
public void close(Connection connection, Statement statement) {
close(connection, statement, null);
}
/**
* 实现增删改
*
* @param sql 要执行的SQL语句
* @param params 占位符的值
*/
public int update(String sql, Object... params) {
Connection connection = null;
PreparedStatement statement = null;
int row = 0;
try {
//获取连接对象
connection = getConnection();
//创建预编译的语句对象
statement = connection.prepareStatement(sql);
//替换占位符:占位符位置,要替换的值
setParameter(statement, params);
//执行SQL语句
row = statement.executeUpdate();
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
//释放资源
close(connection, statement);
}
return row;
}
private void setParameter(PreparedStatement statement, Object... params) throws SQLException {
//1.获取参数元数据
ParameterMetaData parameterMetaData = statement.getParameterMetaData();
//2.获取参数的个数
int parameterCount = parameterMetaData.getParameterCount();
//3.给每个占位符赋值
for (int i = 0; i < parameterCount; i++) {
statement.setObject(i + 1, params[i]);
}
}
/**
* 添加记录
*
* @return 返回新增的主键值
*/
public int save(String sql, Object... params) {
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
int primaryKey = 0;
try {
//1.获取连接对象
connection = getConnection();
//2.创建预编译的语句对象,第2个参数:要设置获取主键
statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
//3.设置占位符
setParameter(statement, params);
//4.执行添加操作,返回影响的行数
int row = statement.executeUpdate();
//添加成功再获取主键
if (row > 0) {
resultSet = statement.getGeneratedKeys();
//如果有结果集,则获取数据
if (resultSet.next()) {
//只有一列数据,直接封装给Student对象的id属性
primaryKey = resultSet.getInt(1);
}
}
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
//5.释放资源
close(connection, statement, resultSet);
}
return primaryKey;
}
/**
* 查询多条记录
*
* @param sql 要查询的SQL语句
* @param type 要封装的实体类对象
* @param params 占位符的值
*/
public List<T> query(String sql, Class<T> type, Object... params) {
List<T> list = new ArrayList<>();
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
//1.获取连接对象
connection = getConnection();
//2.创建语句对象
statement = connection.prepareStatement(sql);
setParameter(statement, params);
//3.执行SQL语句
resultSet = statement.executeQuery();
//4.获取结果集,并且封装
while (resultSet.next()) {
//调用方法,封装一个对象
T entity = encapsulate(type, resultSet);
list.add(entity);
}
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
//5.释放资源
close(connection, statement, resultSet);
}
return list;
}
/**
* 查询一条记录
*/
public T queryForObject(String sql, Class<T> type, Object... params) {
//直接调用上面的方法
List<T> list = query(sql, type, params);
if (!list.isEmpty()) {
return list.get(0);
} else {
return null;
}
}
/**
* 将结果集封装成一个实体对象
*
* @param resultSet 结果集
* @return 封装好的实体对象
*/
private T encapsulate(Class<T> type, ResultSet resultSet) {
T obj = null;
try {
//创建实体类对象
obj = type.getConstructor().newInstance();
//获取结果集元数据
ResultSetMetaData metaData = resultSet.getMetaData();
//获取总列数
int columnCount = metaData.getColumnCount();
//给每个成员变量赋值
for (int i = 1; i <= columnCount; i++) {
//获取列名或别名
String columnLabel = metaData.getColumnLabel(i);
//获取结果集中数据
Object value = resultSet.getObject(i);
//通过反射给属性赋值
Field field = type.getDeclaredField(columnLabel);
//设置暴力反射
field.setAccessible(true);
//给属性赋值
field.set(obj, value);
}
} catch (Exception e) {
throw new RuntimeException(e);
}
return obj;
}
/**
* 查询多条封装成List,其中每个元素是Map
*/
public List<Map<String, Object>> queryForList(String sql, Object... params) {
List<Map<String, Object>> list = new ArrayList<>();
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
//1.获取连接对象
connection = getConnection();
//2.创建语句对象
statement = connection.prepareStatement(sql);
setParameter(statement, params);
//3.执行SQL语句
resultSet = statement.executeQuery();
//4.获取结果集,并且封装
while (resultSet.next()) {
LinkedHashMap<String, Object> map = new LinkedHashMap<>();
//获取结果集元数据
ResultSetMetaData metaData = resultSet.getMetaData();
//获取总列数
int columnCount = metaData.getColumnCount();
//给每个成员变量赋值
for (int i = 1; i <= columnCount; i++) {
//获取列名或别名
String columnLabel = metaData.getColumnLabel(i);
//获取结果集中数据
Object value = resultSet.getObject(i);
//给Map赋值
map.put(columnLabel, value);
}
list.add(map);
}
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
//5.释放资源
close(connection, statement, resultSet);
}
return list;
}
/**
* 查询一条记录封装成Map
*/
public Map<String, Object> queryForMap(String sql, Object... params) {
List<Map<String, Object>> list = queryForList(sql, params);
if (!list.isEmpty()) {
return list.get(0);
} else {
return null;
}
}
}