本文共 10987 字,大约阅读时间需要 36 分钟。
作者:N3verL4nd
来源:CSDN 原文:https://blog.csdn.net/x_iya/article/details/77370161package 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