1.官网
https://commons.apache.org/proper/commons-dbutils/
2.概述
1.coomons-dbutils是apache旗下的一个数据库连接的工具
2.DbUtils 解决的最核心的问题就是结果集的映射，可以把 ResultSet 封装成JavaBean。
3.结果集映射
3.1.结果集映射概述
1.首先 DbUtils 提供了一个 QueryRunner 类，它对数据库的增删改查的方法进行了封装，那么我们操作数据库就可以直接使用它提供的方法。
2.在 QueryRunner 的构造函数里面，我们又可以传入一个数据源，比如在这里我们Hikari，这样我们就不需要再去写各种创建和释放连接的代码了。
queryRunner = new QueryRunner(dataSource);
3.那我们怎么把结果集转换成对象呢？比如实体类 Bean 或者 List 或者 Map？
在DbUtils 里面提供了一系列的支持泛型的 ResultSetHandler
3.2.结果集映射样例
创建数据库
略
CommonsDbutilsDemo测试类
public class CommonsDbutilsDemo {
public static void main(String[] args) throws Exception {
//初始化数据库的连接池的操作
HikariUtil.init();
BlogDao.selectBlog(1);
// BlogDao.selectList();
}
}
HikariUtil 使用Hikari进行数据库连接池的统一管理
package com.gaoxinfu.demo.commons.dbutils;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.sql.Connection;
import java.sql.SQLException;
public class HikariUtil {
private static final String PROPERTY_PATH = "/hikari.properties";
private static final Logger LOGGER = LoggerFactory.getLogger(HikariUtil.class);
private static HikariDataSource dataSource;
private static QueryRunner queryRunner;
public static void init() {
HikariConfig config = new HikariConfig(PROPERTY_PATH);
dataSource = new HikariDataSource(config);
queryRunner = new QueryRunner(dataSource);
}
public static QueryRunner getQueryRunner() {
check();
return queryRunner;
}
public static Connection getConnection() {
check();
try {
Connection connection = dataSource.getConnection();
return connection;
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
public static void close(Connection connection) {
try {
if (connection != null && !connection.isClosed()) {
connection.close();
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
private static void check() {
if (dataSource == null || queryRunner == null) {
throw new RuntimeException("DataSource has not been init");
}
}
}
具体表操作
package com.gaoxinfu.demo.commons.dbutils.dao;
import com.gaoxinfu.demo.commons.dbutils.HikariUtil;
import com.gaoxinfu.demo.commons.dbutils.dto.BlogDto;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import java.sql.SQLException;
import java.util.List;
public class BlogDao {
private static QueryRunner queryRunner;
static {
queryRunner = HikariUtil.getQueryRunner();
}
// 返回单个对象，通过new BeanHandler<>(Class<?> clazz)来设置封装
public static void selectBlog(Integer bid) throws SQLException {
String sql = "select * from blog where bid = ? ";
Object[] params = new Object[]{bid};
BlogDto blogDto = queryRunner.query(sql, new BeanHandler<>(BlogDto.class), params);
System.out.println(blogDto);
}
//返回列表，通过new BeanListHandler<>(Class<?> clazz)来设置List的泛型
public static void selectList() throws SQLException {
String sql = "select * from blog";
List<BlogDto> list = queryRunner.query(sql, new BeanListHandler<>(BlogDto.class));
//list.forEach(System.out::println);
}
}
QueryRunner.query 源码分析
public <T> T query(String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException {
Connection conn = this.prepareConnection();
//见下面的方法
return this.<T>query(conn, true, sql, rsh, params);
}
/**
* Calls query after checking the parameters to ensure nothing is null.
* @param conn The connection to use for the query call.
* @param closeConn True if the connection should be closed, false otherwise.
* @param sql The SQL statement to execute.
* @param params An array of query replacement parameters. Each row in
* this array is one set of batch replacement values.
* @return The results of the query.
* @throws SQLException If there are database or parameter errors.
*/
private <T> T query(Connection conn, boolean closeConn, String sql, ResultSetHandler<T> rsh, Object... params)
throws SQLException {
if (conn == null) {
throw new SQLException("Null connection");
}
if (sql == null) {
if (closeConn) {
close(conn);
}
throw new SQLException("Null SQL statement");
}
if (rsh == null) {
if (closeConn) {
close(conn);
}
throw new SQLException("Null ResultSetHandler");
}
PreparedStatement stmt = null;
ResultSet rs = null;
T result = null;
try {
//创建 根据给定的连接和SQL创建PreparedStatement，实际上是组装SQL
stmt = this.prepareStatement(conn, sql);
//将Params填充上面组装的SQL中预留的参数变量，进行变量替换
this.fillStatement(stmt, params);
//stmt.executeQuery() 执行SQL返回查询结果
//this.wrap 使用包装方法，对返回结果进行包装(当然这里没有做任何操作)
rs = this.wrap(stmt.executeQuery());
//对放回结果进行映射，将数据映射到DTO(entity)对象中，见下面的代码
result = rsh.handle(rs);
} catch (SQLException e) {
this.rethrow(e, sql, params);
} finally {
try {
close(rs);
} finally {
close(stmt);
if (closeConn) {
close(conn);
}
}
}
return result;
}
BeanHandler.handle(ResultSet rs)
@Override
public T handle(ResultSet rs) throws SQLException {
return rs.next() ? this.convert.toBean(rs, this.type) : null;
}
BasicRowProcessor.toBean
@Override
public <T> T toBean(ResultSet rs, Class<? extends T> type) throws SQLException {
return this.convert.toBean(rs, type);
}
BeanProcessor.toBean(ResultSet rs, Class<? extends T> type)
public <T> T toBean(ResultSet rs, Class<? extends T> type) throws SQLException {
T bean = this.newInstance(type);
return this.populateBean(rs, bean);
}
public <T> T populateBean(ResultSet rs, T bean) throws SQLException {
PropertyDescriptor[] props = this.propertyDescriptors(bean.getClass());
ResultSetMetaData rsmd = rs.getMetaData();
int[] columnToProperty = this.mapColumnsToProperties(rsmd, props);
return populateBean(rs, bean, props, columnToProperty);
}
/**
* This method populates a bean from the ResultSet based upon the underlying meta-data.
* populateBean 渲染构造Bean对象
* @param <T> The type of bean
* @param rs The result set.
* @param bean The bean to be populated.
* @param props The property descriptors.
* @param columnToProperty The column indices in the result set.
* @return An initialized object.
* @throws SQLException if a database error occurs.
*/
private <T> T populateBean(ResultSet rs, T bean,
PropertyDescriptor[] props, int[] columnToProperty)
throws SQLException {
for (int i = 1; i < columnToProperty.length; i++) {
if (columnToProperty[i] == PROPERTY_NOT_FOUND) {
continue;
}
PropertyDescriptor prop = props[columnToProperty[i]];
Class<?> propType = prop.getPropertyType();
Object value = null;
if(propType != null) {
//获取某一列字段的值
value = this.processColumn(rs, i, propType);
if (value == null && propType.isPrimitive()) {
value = primitiveDefaults.get(propType);
}
}
//将值设置到对象的某个字段上
this.callSetter(bean, prop, value);
}
return bean;
}
