作者:N3verL4nd
来源:CSDN
原文:https://blog.csdn.net/x_iya/article/details/77370161
package org.apache.commons.dbutils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.sql.DataSource;
/**
* 使用可插拔的策略执行SQL查询,并处理 ResultSet
* 这个类是线程安全的
*/
public class QueryRunner extends AbstractQueryRunner {
/**
* QueryRunner 类的构造函数
*/
public QueryRunner() {
super();
}
/**
* 用于控制是否使用 ParameterMetaData 的 QueryRunner 的构造函数
* pmdKnownBroken:有些数据库驱动不支持 java.sql.ParameterMetaData#getParameterType(int)
* 如果 pmdKnownBroken设置为 true, 我们不去尝试; 如果设置为 false, 我们去尝试,如果测试不能使用,我们就不再去使用它
*/
public QueryRunner(boolean pmdKnownBroken) {
super(pmdKnownBroken);
}
/**
* 使用数据源 DataSource 的 QueryRunner 的构造函数
* 不带有 Connection 参数的方法将会从 DataSource 中获取
*/
public QueryRunner(DataSource ds) {
super(ds);
}
/**
* QueryRunner 的构造函数
*/
public QueryRunner(DataSource ds, boolean pmdKnownBroken) {
super(ds, pmdKnownBroken);
}
/**
* 批处理(一批INSERT、UPDATE、DELETE操作)
* conn:用于执行 SQL 操作的 Connection 对象
* Connection 由调用者负责关闭
* sql:SQL语句
* params:一组查询替换参数,该数组中的每一行都是一组批量替换值
* 返回每个语句更新的行数组成的int数组
* 数据库访问出错则抛出 SQLException 异常
*/
public int[] batch(Connection conn, String sql, Object[][] params) throws SQLException {
return this.batch(conn, false, sql, params);
}
/**
* 批处理操作
* Connection 从 构造函数中的 DataSource 参数中获取
* 该 Connection 必须处于自动提交模式,否则更新将不会被保存
*/
public int[] batch(String sql, Object[][] params) throws SQLException {
Connection conn = this.prepareConnection();
return this.batch(conn, true, sql, params);
}
/**
* 同上
*/
private int[] batch(Connection conn, boolean closeConn, String sql, 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 (params == null) {
if (closeConn) {
close(conn);
}
throw new SQLException("Null parameters. If parameters aren't need, pass an empty array.");
}
PreparedStatement stmt = null;
int[] rows = null;
try {
stmt = this.prepareStatement(conn, sql);
for (int i = 0; i < params.length; i++) {
this.fillStatement(stmt, params[i]);
stmt.addBatch();
}
rows = stmt.executeBatch();
} catch (SQLException e) {
this.rethrow(e, sql, (Object[])params);
} finally {
close(stmt);
if (closeConn) {
close(conn);
}
}
return rows;
}
/**
* 执行一个带有替换参数的SQL SELECT查询
* 调用者负责关闭连接
* T:处理器返回对象的类型
* conn:执行查询的 Connection 对象
* sql:执行的查询语句
* rsh:将ResultSet 转换为其他对象的处理器
* params:可变参数列表,用于替换sql语句中的占位符
* 由 ResultSetHandler 负责返回相应对象
* 数据库访问出错则抛出 SQLException 异常
*/
public <T> T query(Connection conn, String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException {
return this.<T>query(conn, false, sql, rsh, params);
}
/**
* 同上,只是不带有可变参数列表
*/
public <T> T query(Connection conn, String sql, ResultSetHandler<T> rsh) throws SQLException {
return this.<T>query(conn, false, sql, rsh, (Object[]) null);
}
/**
* 同上,只是不带有 Connection 参数,它会从构造函数的 DataSource 参数中获取
*/
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);
}
/**
* 同上
*/
public <T> T query(String sql, ResultSetHandler<T> rsh) throws SQLException {
Connection conn = this.prepareConnection();
return this.<T>query(conn, true, sql, rsh, (Object[]) null);
}
/**
* 在检查参数后调用查询,以确保没有任何参数值为空。
* closeConn:True:该方法替我们关闭;false:调用者负责处理
*/
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 {
//获得 PreparedStatement 对象
stmt = this.prepareStatement(conn, sql);
//填充参数
this.fillStatement(stmt, params);
//获得ResultSet
rs = this.wrap(stmt.executeQuery());
//将 ResultSet 转换为指定对象
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;
}
/**
* 执行 SQL INSERT, UPDATE, 或者 DELETE 操作
* 返回更新的行数
*/
public int update(Connection conn, String sql) throws SQLException {
return this.update(conn, false, sql, (Object[]) null);
}
/**
* 同上,带有一个参数
*/
public int update(Connection conn, String sql, Object param) throws SQLException {
return this.update(conn, false, sql, new Object[]{param});
}
/**
* 同上,带有可变参数
*/
public int update(Connection conn, String sql, Object... params) throws SQLException {
return update(conn, false, sql, params);
}
/**
* 同上
*/
public int update(String sql) throws SQLException {
Connection conn = this.prepareConnection();
return this.update(conn, true, sql, (Object[]) null);
}
/**
* 同上
*/
public int update(String sql, Object param) throws SQLException {
Connection conn = this.prepareConnection();
return this.update(conn, true, sql, new Object[]{param});
}
/**
* 同上
*/
public int update(String sql, Object... params) throws SQLException {
Connection conn = this.prepareConnection();
return this.update(conn, true, sql, params);
}
/**
* 同上
*/
private int update(Connection conn, boolean closeConn, String sql, 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");
}
PreparedStatement stmt = null;
int rows = 0;
try {
stmt = this.prepareStatement(conn, sql);
this.fillStatement(stmt, params);
rows = stmt.executeUpdate();
} catch (SQLException e) {
this.rethrow(e, sql, params);
} finally {
close(stmt);
if (closeConn) {
close(conn);
}
}
return rows;
}
/**
* 执行给定的SQL插入语句
*/
public <T> T insert(String sql, ResultSetHandler<T> rsh) throws SQLException {
return insert(this.prepareConnection(), true, sql, rsh, (Object[]) null);
}
/**
* 执行给定的SQL插入语句
*/
public <T> T insert(String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException {
return insert(this.prepareConnection(), true, sql, rsh, params);
}
/**
* 执行给定的SQL插入语句
*/
public <T> T insert(Connection conn, String sql, ResultSetHandler<T> rsh) throws SQLException {
return insert(conn, false, sql, rsh, (Object[]) null);
}
/**
* 执行给定的SQL插入语句
*/
public <T> T insert(Connection conn, String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException {
return insert(conn, false, sql, rsh, params);
}
/**
* 执行给定的SQL插入语句
*/
private <T> T insert(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;
T generatedKeys = null;
try {
stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
this.fillStatement(stmt, params);
stmt.executeUpdate();
//获取自增主键值
ResultSet resultSet = stmt.getGeneratedKeys();
generatedKeys = rsh.handle(resultSet);
} catch (SQLException e) {
this.rethrow(e, sql, params);
} finally {
close(stmt);
if (closeConn) {
close(conn);
}
}
return generatedKeys;
}
/**
* 执行给定的多个插入SQL语句
*/
public <T> T insertBatch(String sql, ResultSetHandler<T> rsh, Object[][] params) throws SQLException {
return insertBatch(this.prepareConnection(), true, sql, rsh, params);
}
/**
* 执行给定的多个插入SQL语句
*/
public <T> T insertBatch(Connection conn, String sql, ResultSetHandler<T> rsh, Object[][] params) throws SQLException {
return insertBatch(conn, false, sql, rsh, params);
}
/**
* 执行给定的多个插入SQL语句
*/
private <T> T insertBatch(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 (params == null) {
if (closeConn) {
close(conn);
}
throw new SQLException("Null parameters. If parameters aren't need, pass an empty array.");
}
PreparedStatement stmt = null;
T generatedKeys = null;
try {
stmt = this.prepareStatement(conn, sql, Statement.RETURN_GENERATED_KEYS);
for (int i = 0; i < params.length; i++) {
this.fillStatement(stmt, params[i]);
stmt.addBatch();
}
stmt.executeBatch();
ResultSet rs = stmt.getGeneratedKeys();
generatedKeys = rsh.handle(rs);
} catch (SQLException e) {
this.rethrow(e, sql, (Object[])params);
} finally {
close(stmt);
if (closeConn) {
close(conn);
}
}
return generatedKeys;
}
}
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/**
* 测试 QueryRunner 类的 update 方法
*/
@Test
public void testQueryRunnerUpdate() {
Connection connection = null;
String sql = "DELETE FROM persons WHERE id in (?, ?)";
try {
connection = JDBCTools.getConnection();
//返回受影响的行数
int rows = queryRunner.update(connection, sql, 41, 39);
System.out.println(rows);
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
/**
* 测试 QueryRunner 类的 insert 方法
*/
@Test
public void testQueryRunnerInsert() {
Connection connection = null;
String sql = "INSERT INTO persons(name, age, birth, email) VALUES(?, ?, ?, ?)";
try {
connection = JDBCTools.getConnection();
Object[] args= {"测试", 99, new java.sql.Date(new Date().getTime()), "tmd@qq.com"};
//返回插入数据的主键
Long id = queryRunner.insert(connection, sql, new ScalarHandler<Long>(), args);
System.out.println(id);
} catch (SQLException e) {
e.printStackTrace();
} finally {
DbUtils.closeQuietly(connection);
}
}
//自定义 ResultSetHandler
@Test
public void testQueryRunnerQuery() {
Connection connection = null;
try {
connection = JDBCTools.getConnection();
String sql = "SELECT * FROM persons";
List<Person> list = queryRunner.query(connection, sql, rs -> {
List<Person> list1 = new ArrayList<>();
while (rs.next()) {
Person person = new Person(rs.getInt(1), rs.getString(2),
rs.getInt(3), rs.getDate(4), rs.getString(5));
list1.add(person);
}
return list1;
});
list.forEach(System.out::println);
} catch (SQLException e) {
e.printStackTrace();
} finally {
DbUtils.closeQuietly(connection);
}
}
查询实例
@Override
public User queryByName(String name) {
//1.获取数据库操作对象
QueryRunner run = JDBCUtils.getQueryRunner();
//2.执行
String sql = "SELECT * FROM t_user WHERE USERNAME = ?";
User user = null;
try {
user = run.query(sql, new BeanHandler<>(User.class), name);
} catch (SQLException e) {
e.printStackTrace();
}
return user;
}
---------------------
作者:N3verL4nd
来源:CSDN
原文:https://blog.csdn.net/x_iya/article/details/77370161