All Downloads are FREE. Search and download functionalities are using the official Maven repository.

com.bixuebihui.r2dbc.sql.DbHelper Maven / Gradle / Ivy

The newest version!
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)); } }