Please wait. This can take some minutes ...
Many resources are needed to download a project. Please understand that we have to compensate our server costs. Thank you in advance.
Project price only 1 $
You can buy this project and download/modify it how often you want.
com.bixuebihui.r2dbc.sql.DbHelper Maven / Gradle / Ivy
package com.bixuebihui.r2dbc.sql;
import com.bixuebihui.DbException;
import com.bixuebihui.jdbc.ParamsIterator;
import io.r2dbc.spi.*;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import reactor.core.publisher.Flux;
import reactor.core.publisher.Mono;
import jakarta.validation.constraints.NotNull;
import java.sql.Date;
import java.sql.Timestamp;
import java.time.LocalDateTime;
import java.util.*;
import java.util.concurrent.atomic.AtomicBoolean;
/**
* DbHelper class.
*
* @author xingwx
* @version $Id: $Id
*/
public class DbHelper implements IDbHelper {
private static final Logger LOG = LoggerFactory.getLogger(DbHelper.class);
private ConnectionFactory dataSource = null;
/**
* Constructor for DbHelper.
*/
public DbHelper() {
// use this constructor
}
/**
* freeConnection.
*
* @param cn a {@link Connection} object.
*/
public static void freeConnection(Mono cn) {
cn.flatMapMany(Connection::close).subscribe();
}
/**
* close.
*
* @param conn a {@link Connection} object.
*/
public static void close(Mono conn) {
if (conn != null) {
freeConnection(conn);
}
}
private static void dumpSql(Exception ex, String sql, Object[] params) {
LOG.warn(ex.getMessage());
LOG.warn("===" + sql);
if (params != null) {
for (Object o : params) {
LOG.warn("===" + o);
}
}
}
private static void dumpSql(Exception ex, String[] sql) {
LOG.warn(ex.getMessage());
for (String s : sql) {
LOG.warn("==" + s);
}
}
public static String clob2Str(final Clob source) {
return Mono.from(source.stream()).map(CharSequence::toString).block();
}
/**
* {@inheritDoc}
*/
@Override
public ConnectionFactory getDataSource() {
return dataSource;
}
/**
* {@inheritDoc}
*/
@Override
public void setDataSource(ConnectionFactory dataSource) {
this.dataSource = dataSource;
}
// 用于事务处理
/**
* Getter for the field connection
.
*
* @return a {@link Connection} object.
*/
@Override
public Mono getConnection() {
return Mono.from(dataSource.create());
}
// 用于事务处理
/**
* {@inheritDoc}
*
* this method for subclass override
*
* @return
*/
@Override
public Mono getConnection(boolean readOnly) {
return getConnection();
}
/**
* {@inheritDoc}
*/
@Override
public void close() {
}
/**
* do not use this if you don't know what to do...
* because it leading to resource leaking
*
* @param sql sql
* @param cn connection
* @return Flux
*/
@Deprecated
public Flux executeQuery(String sql, @NotNull Connection cn) {
return Flux.from(cn.createStatement(sql).execute());
}
/**
* executeNoQuery.
*
* @param sql a {@link String} object.
* @return a int.
*/
@Override
public Mono executeNoQuery(String sql) {
return Mono.usingWhen(getConnection(false), cn -> {
Flux v = Flux.from(cn.createStatement(sql).execute())
.flatMap(Result::getRowsUpdated);
return Mono.from(v);
}, Connection::close);
}
/**
* executeNoQuery.
*
* @param sql a {@link String} object.
* @param cn a {@link Connection} object.
* @return a int.
*/
public Mono executeNoQuery(String sql, @NotNull Connection cn) {
Flux v = Flux.from(cn.createStatement(sql).execute())
.flatMap(Result::getRowsUpdated);
return Mono.from(v);
}
/**
* {@inheritDoc}
*/
@Override
public Mono executeNoQueryBatch(String strSql, Iterable params) {
return executeNoQueryBatch(strSql, params, null);
}
/**
* {@inheritDoc}
*/
@Override
public Mono executeNoQueryBatch(String strSql, int total, ParamsIterator.CurrentParameters cur, Mono cn) {
return executeNoQueryBatch(strSql, new ParamsIterator(total, cur), cn);
}
/**
* {@inheritDoc}
*/
@Override
public Mono executeScalar(String strSql) {
return executeScalarSession(strSql, Mono.empty());
}
/**
* executeNoQuery.
*
* @param sql an array of {@link String} objects.
* @return a count of influenced records.
*/
@Override
public Mono executeNoQuery(String[] sql) {
return Mono.usingWhen(getConnection(false), connection -> {
connection.beginTransaction();
Batch batch = connection.createBatch();
for (String s : sql) {
if (StringUtils.isNotBlank(s)) {
batch.add(s);
}
}
return Flux.from(batch.execute()).flatMap(Result::getRowsUpdated).reduceWith(() -> 0, Integer::sum);
}, conn -> Mono.from(conn.commitTransaction()).then(Mono.from(conn.close())));
}
/**
* 批量在事条里执行SQL语句数组
*
* @param sqls 批量在事条里执行SQL语句数组
* @return 每一个SQL执行结果的数组
*/
public Mono> executeUpdate(String[] sqls) {
if (sqls == null || sqls.length < 1) {
return Mono.just(Collections.emptyList());
}
return Mono.usingWhen(getConnection(false), connection -> {
Batch batch = connection.createBatch();
for (String sql : sqls) {
batch.add(sql);
}
return Flux.from(batch.execute()).flatMap(Result::getRowsUpdated).collectList();
}, Connection::close);
}
/**
* {@inheritDoc}
*/
@Override
public @NotNull
Flux> exeQuery(String sql) {
return Flux.usingWhen(getConnection(true), cn ->
Flux.from(cn.createStatement(sql).execute())
.flatMap(DbHelper::resultSet2Vector),
Connection::close);
}
/**
* executeNoQuery.
*
* @param sqls an array of {@link String} objects.
* @param cn a {@link Connection} object.
* @return a int.
*/
@Override
public Mono executeNoQuery(String[] sqls, @NotNull Mono cn) {
return Mono.from(cn.flatMapMany(connection -> {
Batch batch = connection.createBatch();
for (String sql : sqls) {
if (!StringUtils.isNotBlank(sql)) {
batch.add(sql);
}
}
return Flux.from(batch.execute()).flatMap(Result::getRowsUpdated).reduceWith(() -> 0, Integer::sum);
}));
}
/**
* {@inheritDoc}
*/
@Override
public Mono executeNoQuery(String sql, Object[] params, Connection cn) {
Statement p = cn.createStatement(sql);
fillParameter(p, params, null);
return Mono.from(Flux.from(p.execute()).flatMap(Result::getRowsUpdated));
}
/**
* {@inheritDoc}
*
* 批处里数组, 用于Insert, delete或update
*/
@Override
public Mono executeNoQueryBatch(String sql, Iterable params,
Mono cn) {
return Mono.from(cn.flatMapMany(connection -> {
Statement p = connection.createStatement(sql);
params.forEach(it -> {
fillParameter(p, it, null);
p.add();
});
return Flux.from(p.execute()).flatMap(Result::getRowsUpdated).reduceWith(() -> 0, Integer::sum);
}));
}
/**
* {@inheritDoc}
*/
@Override
public Mono executeQuery(String sql, RowCallbackHandler handle) {
return Mono.usingWhen(getConnection(true), connection -> {
Statement stmt = connection.createStatement(sql);
return Flux.from(stmt.execute()).map(handle::processRow).then();
}, Connection::close);
}
/**
* (non-Javadoc)
*
* @param cn connection
* @param sql sql语句
* @return single object, may be integer or string
* @see IDbHelper#executeScalar(String)
*/
public Mono executeScalarSession(String sql, Mono cn) {
AtomicBoolean localCn = new AtomicBoolean(true);
return Mono.usingWhen(
cn.flatMap(cn1 -> {
localCn.set(false);
return Mono.just(cn1);
}).switchIfEmpty(getConnection(true)),
c -> Flux.from(c.createStatement(sql).execute())
.log()
.map(result -> result.map((row, meta) -> row.get(0))
).flatMap(Mono::from).next(), //.doOnNext(data -> LOG.info("value: {}", data)),
c -> {
if (localCn.get()) {
c.close();
}
return Mono.empty();
}
);
}
/**
* resultSet2Vector.
*
* @param rs a {@link Result} object.
* @return a {@link List} object.
*/
public static Flux> resultSet2Vector(Result rs) {
return Flux.from(rs.map((row, rowMetadata) -> {
int iColumnNumber = rowMetadata.getColumnMetadatas().size();
Map mapColumn = new HashMap<>(32);
for (int i = 0; i < iColumnNumber; i++) {
ColumnMetadata colMeta = rowMetadata.getColumnMetadata(i);
String colName = colMeta.getName();
if (mapColumn.containsKey(colName) && row.get(i) == null) {
//同一列名的已存在且不为空,则保留当前值,用于多表联合查询,但后表无记录的情况
continue;
}
mapColumn.put(colName, row.get(i));//, colMeta.getJavaType()));
}
return mapColumn;
}));
}
/**
* executeScalar.
*
* @param sql a {@link String} object.
* @param params an array of {@link Object} objects.
* @return a {@link Object} object.
*/
@Override
public Mono executeScalar(String sql, Object[] params) {
return executeScalar(sql, params, Mono.empty());
}
/**
* fillParameter.
*
* @param p a {@link Statement} object.
* @param params an array of {@link Object} objects.
*/
public static void fillParameter(Statement p, Object[] params) {
fillParameter(p, params, null);
}
/**
* {@inheritDoc}
*/
@Override
public Mono executeScalar(String sql, Object[] params, Mono cn) {
if (params == null || params.length == 0) {
return executeScalarSession(sql, cn);
}
return Mono.usingWhen(cn != null ? cn : getConnection(), connection -> {
Statement p = connection.createStatement(sql);
fillParameter(p, params, null);
return Mono.from(p.execute()).map(result ->
result.map((row, meta) -> row.get(0))).single();
}
, conn -> (cn == null) ? conn.close() : Mono.empty());
}
/**
* fillParameter.
*
* @param p a {@link Statement} object.
* @param params an array of {@link Object} objects.
* @param targetSqlTypes an array of int objects.
*/
public static void fillParameter(Statement p, Object[] params,
Class[] targetSqlTypes) {
if (ArrayUtils.isEmpty(params)) {
return;
}
if (ArrayUtils.isNotEmpty(targetSqlTypes) && targetSqlTypes.length != params.length) {
throw new DbException(
"Intenal error: The params and the targetSqlTypes must have same length!");
}
if (ArrayUtils.isNotEmpty(targetSqlTypes)) {
for (int i = 0; i < params.length; i++) {
if (params[i] == null) {
p.bindNull(i, targetSqlTypes[i]);
} else {
p.bind(i, params[i]);
}
}
} else {
for (int i = 0; i < params.length; i++) {
Object pa = params[i];
if (pa == null) {
p.bindNull(i, String.class);
// LOG.debug("不支持空数据(参数["
// + i
// + "])! Null is not support in DbHelper.executeNoQuery(String sql, Object[] params), there may be error!");
} else if (pa instanceof Integer) {
p.bind(i, pa);
} else if (pa instanceof Long) {
p.bind(i, pa);
} else if (pa instanceof Timestamp) {
p.bind(i, ((Timestamp) pa).toLocalDateTime());
} else if (pa instanceof LocalDateTime) {
p.bind(i, pa);
} else if (pa instanceof Date) {
p.bind(i, pa);
} else if (pa instanceof java.sql.Time) {
p.bind(i, pa);
} else if (pa instanceof java.util.Date) {
p.bind(i, pa);
} else if (pa instanceof Double) {
p.bind(i, pa);
} else if (pa instanceof String) {
p.bind(i, pa);
} else if (pa instanceof ClobString) {
p.bind(i, pa.toString());
} else {
p.bind(i, pa);
}
}
}
}
/**
* 用于执行带参数的非查询语句
*
* @param sql 参数化sql语句
* @param params 参数
* @return (字段)) 结果集
*/
@Override
public Mono>> executeQuery(String sql, Object[] params) {
return Mono.usingWhen(getConnection(true), cn -> {
Statement p = cn.createStatement(sql);
if (params != null && params.length > 0) {
fillParameter(p, params);
}
return Mono.from(p.execute()).map(DbHelper::resultSet2Vector).flatMap(Flux::collectList);
}, Connection::close);
}
/**
* executeNoQuery.
*
* @param sql a {@link String} object.
* @param params an array of {@link Object} objects.
* @param targetSqlTypes an array of int objects.
* @return a int.
*/
@Override
public Mono executeNoQuery(String sql, Object[] params, Class[] targetSqlTypes) {
return executeNoQuery(sql, params, targetSqlTypes, null);
}
/**
* {@inheritDoc}
*/
@Override
public Mono executeNoQuery(String sql, Object[] params,
Class[] targetSqlTypes, Mono cn) {
return Mono.usingWhen(cn != null ? cn : getConnection(), connection -> {
Statement p = connection.createStatement(sql);
fillParameter(p, params, targetSqlTypes);
p.add();
return Flux.from(p.execute()).flatMap(Result::getRowsUpdated).reduceWith(() -> 0, Integer::sum);
}, conn -> (cn == null) ? conn.close() : Mono.empty());
}
/**
* {@inheritDoc}
*/
@Override
public Mono insertAndFetchLastId(String sql, Object[] params,
Class[] targetSqlTypes, Mono cn) {
return Mono.usingWhen(cn != null ? cn : getConnection(),
connection -> {
Statement p = connection.createStatement(sql);
if (ArrayUtils.isNotEmpty(params)) {
fillParameter(p, params, targetSqlTypes);
}
return Mono.from(p.returnGeneratedValues().execute()).flatMap(result ->
Mono.from(result.map((row, meta) ->
Long.parseLong(Objects.requireNonNull(row.get(0))
.toString())))
);
}, conn -> cn == null ? conn.close() : Mono.empty());
}
/**
* executeNoQuery.
*
* @param sql a {@link String} object.
* @param params an array of {@link Object} objects.
* @return a int.
*/
@Override
public Mono executeNoQuery(String sql, Object[] params) {
return executeNoQuery(sql, params, new Class[0]);
}
/**
* executeQuery.
*
* @param sql a {@link String} object.
* @param params an array of {@link Object} objects.
* @param handle a {@link RowCallbackHandler} object.
*/
@Override
public Mono> executeQuery(String sql, Object[] params,
RowCallbackHandler handle) {
return Mono.usingWhen(getConnection(true),
cn -> {
Statement p = cn.createStatement(sql);
if (params != null && params.length > 0) {
fillParameter(p, params, null);
}
return Mono.from(p.execute()).flatMap(handle::processRow);
},
Connection::close
);
}
/**
* {@inheritDoc}
*/
@Override
public @NotNull Mono> executeQuery(String sql, Object[] params,
@NotNull RowMapperResultReader handle) {
return Mono.usingWhen(getConnection(true),
cn -> {
Statement p = cn.createStatement(sql);
if (ArrayUtils.isNotEmpty(params)) {
fillParameter(p, params, null);
}
return Mono.from(p.execute())
.flatMap(handle::processResult);
},
Connection::close
);
}
@Override
public @NotNull Mono> executeQuery(String sql, Object[] params,
@NotNull RowMapper handle) {
return executeQuery(sql, params,
new RowMapperResultReader<>(handle));
}
}