cool.scx.jdbc.sql.SQLRunner Maven / Gradle / Ivy
package cool.scx.jdbc.sql;
import cool.scx.functional.ScxConsumer;
import cool.scx.functional.ScxFunction;
import cool.scx.functional.ScxRunnable;
import cool.scx.jdbc.JDBCContext;
import cool.scx.jdbc.result_handler.ResultHandler;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.Callable;
import java.util.concurrent.CompletableFuture;
import java.util.concurrent.atomic.AtomicLong;
import static cool.scx.util.ScxExceptionHelper.wrap;
import static java.sql.ResultSet.CONCUR_READ_ONLY;
import static java.sql.ResultSet.TYPE_FORWARD_ONLY;
import static java.sql.Statement.RETURN_GENERATED_KEYS;
/**
* SQLRunner 执行 cool.scx.sql 语句
*
* @author scx567888
* @version 0.0.1
*/
public final class SQLRunner {
/**
* a
*/
private static final InheritableThreadLocal CONNECTION_THREAD_LOCAL = new InheritableThreadLocal<>();
private final AtomicLong threadNumber = new AtomicLong(0);
private final JDBCContext jdbcContext;
/**
* 根据数据源构建一个 SQLRunner
*
* @param jdbcContext a DataSource object
*/
public SQLRunner(JDBCContext jdbcContext) {
this.jdbcContext = jdbcContext;
}
/**
* 同 autoTransaction
*
* @param con 连接对象
* @param handler handler
* @throws Exception e
*/
public static void autoTransaction(Connection con, ScxConsumer handler) throws Exception {
con.setAutoCommit(false);
try {
handler.accept(con);
con.commit();
} catch (Exception e) {
con.rollback();
throw e;
}
}
/**
* a
*
* @param con a
* @param handler a
* @param a
* @return a
* @throws Exception a
*/
public static T autoTransaction(Connection con, ScxFunction handler) throws Exception {
con.setAutoCommit(false);
try {
T result = handler.apply(con);
con.commit();
return result;
} catch (Exception e) {
con.rollback();
throw e;
}
}
/**
* getUpdateResult.
*
* @param preparedStatement a PreparedStatement object
* @return a {@link UpdateResult} object
* @throws SQLException if any.
*/
private static List getGeneratedKeys(PreparedStatement preparedStatement) throws SQLException {
try (var resultSet = preparedStatement.getGeneratedKeys()) {
var ids = new ArrayList();
while (resultSet.next()) {
ids.add(resultSet.getLong(1));
}
return ids;
}
}
/**
* a
*
* @param con a
* @param sql a
* @param resultHandler a
* @return a
* @throws SQLException a
*/
public T query(Connection con, SQL sql, ResultHandler resultHandler) throws SQLException {
try (var preparedStatement = con.prepareStatement(sql.sql(), TYPE_FORWARD_ONLY, CONCUR_READ_ONLY)) {
sql.fillParams(preparedStatement, jdbcContext.dialect());
jdbcContext.dialect().beforeExecuteQuery(preparedStatement);
var resultSet = preparedStatement.executeQuery();
return resultHandler.apply(resultSet, jdbcContext.dialect());
}
}
/**
* a
*
* @param con a
* @param sql a
* @return a
* @throws SQLException a
*/
public long execute(Connection con, SQL sql) throws SQLException {
try (var preparedStatement = con.prepareStatement(sql.sql(), RETURN_GENERATED_KEYS)) {
sql.fillParams(preparedStatement, jdbcContext.dialect());
preparedStatement.execute();
return preparedStatement.getLargeUpdateCount();
}
}
/**
* a
*
* @param con a
* @param sql a
* @return a
* @throws SQLException a
*/
public UpdateResult update(Connection con, SQL sql) throws SQLException {
try (var preparedStatement = con.prepareStatement(sql.sql(), RETURN_GENERATED_KEYS)) {
sql.fillParams(preparedStatement, jdbcContext.dialect());
var affectedItemsCount = preparedStatement.executeLargeUpdate();
var generatedKeys = getGeneratedKeys(preparedStatement);
return new UpdateResult(affectedItemsCount, generatedKeys);
}
}
/**
* 批量执行更新语句
*
* @param sql cool.scx.sql
* @param con a Connection object
* @return r
* @throws SQLException if any.
*/
public UpdateResult updateBatch(Connection con, SQL sql) throws SQLException {
try (var preparedStatement = con.prepareStatement(sql.sql(), RETURN_GENERATED_KEYS)) {
sql.fillParams(preparedStatement, jdbcContext.dialect());
var affectedItemsCount = preparedStatement.executeLargeBatch().length;
var generatedKeys = getGeneratedKeys(preparedStatement);
return new UpdateResult(affectedItemsCount, generatedKeys);
}
}
/**
* a
*
* @param sql a
* @param resultHandler a
* @param a
* @return a
*/
public T query(SQL sql, ResultHandler resultHandler) {
return wrap(() -> {
//我们根据 CONNECTION_THREAD_LOCAL.get() 是否为 null 来判断是否处于 autoTransaction 中
var connection = CONNECTION_THREAD_LOCAL.get();
if (connection != null) {
return query(connection, sql, resultHandler);
} else {
try (var con = getConnection()) {
return query(con, sql, resultHandler);
}
}
});
}
/**
* 执行 cool.scx.sql 语句
*
* @param sql a {@link String} object.
* @return a 执行结果
*/
public long execute(SQL sql) {
return wrap(() -> {
//我们根据 CONNECTION_THREAD_LOCAL.get() 是否为 null 来判断是否处于 autoTransaction 中
var connection = CONNECTION_THREAD_LOCAL.get();
if (connection != null) {
return execute(connection, sql);
} else {
try (var con = getConnection()) {
return execute(con, sql);
}
}
});
}
/**
* a
*
* @param sql a
* @return a
*/
public UpdateResult update(SQL sql) {
return wrap(() -> {
//我们根据 CONNECTION_THREAD_LOCAL.get() 是否为 null 来判断是否处于 autoTransaction 中
var connection = CONNECTION_THREAD_LOCAL.get();
if (connection != null) {
return update(connection, sql);
} else {
try (var con = getConnection()) {
return update(con, sql);
}
}
});
}
/**
* 批量执行更新语句
*
* @param sql a
* @return a
*/
public UpdateResult updateBatch(SQL sql) {
return wrap(() -> {
//我们根据 CONNECTION_THREAD_LOCAL.get() 是否为 null 来判断是否处于 autoTransaction 中
var connection = CONNECTION_THREAD_LOCAL.get();
if (connection != null) {
return updateBatch(connection, sql);
} else {
try (var con = getConnection()) {
return updateBatch(con, sql);
}
}
});
}
/**
* 自动处理事务并在产生异常时进行自动回滚
* 注意 其中的操作会在另一个线程中执行 所以需要注意线程的操作
* 当抛出异常时 请使用 {@link cool.scx.util.ScxExceptionHelper#getRootCause(Throwable)} 来获取真正的异常
* 用法
* {@code
* 假设有以下结构的数据表
* create table test (
* name varchar(32) null unique,
* );
* 在连接消费者中传入要执行的操作
* SQLRunner sqlRunner = xxx;
* try {
* sqlRunner.autoTransaction(() -> {
* // 这句代码会正确执行
* sqlRunner.execute(NoParametersSQL.of("insert into test(name) values('uniqueName') "));
* // 这句会产生异常 这时上一个语句会进行回滚 (rollback) 同时将异常抛出
* sqlRunner.execute(NoParametersSQL.of("insert into test(name) values('uniqueName') "));
* });
* } catch (Exception e) {
* //这里会捕获 getConnection 可能产生的 SQLException 和 autoTransaction 代码块中产生的所有异常
* //因为会进行多层包裹 所以建议使用 ScxExceptionHelper.getRootCause(e); 来获取真正的异常
* ScxExceptionHelper.getRootCause(e).printStackTrace();
* }
* }
*
* @param handler 连接消费者
*/
public void autoTransaction(ScxRunnable handler) {
var promise = new CompletableFuture();
Thread.ofVirtual().name("scx-auto-transaction-thread-", threadNumber.getAndIncrement()).start(() -> {
try (var con = getConnection(false)) {
CONNECTION_THREAD_LOCAL.set(con);
try {
handler.run();
con.commit();
promise.complete(null);
} catch (Exception e) {
con.rollback();
throw e;
} finally {
CONNECTION_THREAD_LOCAL.remove();
}
} catch (Exception e) {
promise.completeExceptionally(e);
}
});
wrap(() -> promise.get());
}
/**
* 同上 {@link SQLRunner#autoTransaction(ScxRunnable)} 但是有返回值
*
* @param handler a
* @param a
* @return a
*/
public T autoTransaction(Callable handler) {
var promise = new CompletableFuture();
Thread.ofVirtual().name("scx-auto-transaction-thread-", threadNumber.getAndIncrement()).start(() -> {
try (var con = getConnection(false)) {
CONNECTION_THREAD_LOCAL.set(con);
try {
T result = handler.call();
con.commit();
promise.complete(result);
} catch (Exception e) {
con.rollback();
throw e;
} finally {
CONNECTION_THREAD_LOCAL.remove();
}
} catch (Exception e) {
promise.completeExceptionally(e);
}
});
return wrap(() -> promise.get());
}
/**
* a
*
* @return a
* @throws SQLException a
*/
private Connection getConnection() throws SQLException {
return getConnection(true);
}
/**
* a
*
* @param autoCommit a
* @return a
* @throws SQLException a
*/
private Connection getConnection(boolean autoCommit) throws SQLException {
var con = jdbcContext.dataSource().getConnection();
con.setAutoCommit(autoCommit);
return con;
}
}
© 2015 - 2024 Weber Informatics LLC | Privacy Policy