package com.product.datasource.dao.impl;
|
|
import cn.hutool.core.date.DateUtil;
|
import cn.hutool.core.date.TimeInterval;
|
import cn.hutool.core.util.ArrayUtil;
|
import com.product.common.enums.IEnum;
|
import com.product.core.config.Global;
|
import com.product.core.entity.DataTableEntity;
|
import com.product.core.entity.FieldSetEntity;
|
import com.product.core.spring.context.SpringMVCContextHolder;
|
import com.product.datasource.config.DataBaseType;
|
import com.product.datasource.config.ErrorCode;
|
import com.product.datasource.connection.ConnectionManager;
|
import com.product.datasource.dao.ConnectionInterface;
|
import com.product.datasource.dao.Dao;
|
import com.product.datasource.entity.BatchResultEntity;
|
import com.product.datasource.entity.DataBaseEntity;
|
import com.product.datasource.entity.UpdateFilterEntity;
|
import com.product.datasource.utils.BatchUtil;
|
import org.apache.commons.lang3.StringUtils;
|
|
import javax.swing.*;
|
import java.sql.Connection;
|
import java.sql.PreparedStatement;
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
|
/**
|
* @Author cheng
|
* @Date 2022/7/6 16:43
|
* @Desc MySql 数据操作实现类
|
*/
|
public class MysqlDaoImpl implements Dao {
|
|
|
private Connection connection;
|
|
private boolean outLog;
|
|
private ConnectionInterface connectionInterface;
|
|
public MysqlDaoImpl(DataBaseEntity dataBaseEntity) {
|
if (!DataBaseType.MYSQL.equals(dataBaseEntity.getDataBaseType())) {
|
throw new BaseException(ErrorCode.INIT_DAO_IMPL_TYPE_ERROR);
|
}
|
this.outLog = "dev".equals(Global.getSystemConfig("spring.profiles.active", "prod"));
|
connectionInterface = () -> ConnectionManager.getConnection(dataBaseEntity);
|
}
|
|
public MysqlDaoImpl(ConnectionInterface connectionInterface) {
|
this.connectionInterface = connectionInterface;
|
}
|
|
private void info(String message) {
|
if (this.outLog) {
|
SpringMVCContextHolder.getSystemLogger().info(message);
|
}
|
}
|
|
|
/**
|
* 获取数据库类型
|
*
|
* @return
|
* @throws BaseException
|
*/
|
@Override
|
public DataBaseType getDataBaseType() throws com.product.core.exception.BaseException {
|
return DataBaseType.MYSQL;
|
}
|
|
public Connection getConnection() throws com.product.core.exception.BaseException {
|
try {
|
if (this.connection == null || this.connection.isClosed()) {
|
//初始化连接
|
this.connection = this.connectionInterface.getConnection();
|
}
|
return this.connection;
|
} catch (Exception e) {
|
e.printStackTrace();
|
throw new BaseException(ErrorCode.GET_CONNECTION_FAIL, e);
|
}
|
}
|
|
/**
|
* 新增单条记录
|
*
|
* @param fse
|
* @throws OracleDaoImpl.BaseException
|
*/
|
@Override
|
public void add(FieldSetEntity fse) throws com.product.core.exception.BaseException {
|
StringBuilder insertSql = BatchUtil.getInsertSql(fse.getMeta());
|
try (PreparedStatement pst = getConnection().prepareStatement(insertSql.toString())) {
|
Object[] fields = fse.getFields();
|
for (int i = 0; i < fields.length; i++) {
|
pst.setObject(i + 1, fse.getObject((String) fields[i]));
|
}
|
pst.execute();
|
} catch (Exception e) {
|
e.printStackTrace();
|
throw new BaseException(ErrorCode.ADD_RECORD_FAIL, e);
|
}
|
}
|
|
/**
|
* 查询列表数据
|
*
|
* @param sql
|
* @return
|
*/
|
@Override
|
public DataTableEntity getList(String sql) throws com.product.core.exception.BaseException {
|
return this.getList(sql, new Object[]{});
|
}
|
|
/**
|
* 查询列表数据
|
*
|
* @param sql sql
|
* @param params 参数
|
* @return
|
*/
|
@Override
|
public DataTableEntity getList(String sql, Object[] params) throws com.product.core.exception.BaseException {
|
try (PreparedStatement pst = getConnection().prepareStatement(sql)) {
|
info("执行SQL：\n" + sql);
|
if (params != null && params.length > 0) {
|
for (int i = 0; i < params.length; i++) {
|
info("param" + (i + 1) + ": " + params[i]);
|
pst.setObject(i + 1, params[i]);
|
}
|
}
|
ResultSet resultSet = pst.executeQuery();
|
return loaddingDataList(resultSet);
|
} catch (Exception e) {
|
SpringMVCContextHolder.getSystemLogger().error("EXECUTE_ERROR_SQL:\n" + sql);
|
throw new BaseException(ErrorCode.GET_LIST_FAIL, e);
|
}
|
}
|
|
/**
|
* 查询列表数据
|
*
|
* @param tableName
|
* @param filter
|
* @return
|
*/
|
@Override
|
public DataTableEntity getList(String tableName, String filter) throws com.product.core.exception.BaseException {
|
return getList(tableName, filter, new Object[]{});
|
}
|
|
/**
|
* 查询列表数据
|
*
|
* @param tableName 表名
|
* @param filter 条件
|
* @param params 参数
|
* @return
|
*/
|
@Override
|
public DataTableEntity getList(String tableName, String filter, Object[] params) throws com.product.core.exception.BaseException {
|
return this.getList(tableName, filter, null, params);
|
}
|
|
/**
|
* 查询列表数据
|
*
|
* @param tableName 表名
|
* @param filter 条件
|
* @param fields
|
* @param params 参数
|
* @return
|
*/
|
@Override
|
public DataTableEntity getList(String tableName, String filter, String[] fields, Object[] params) throws com.product.core.exception.BaseException {
|
StringBuilder sql = new StringBuilder("SELECT ");
|
if (ArrayUtil.isEmpty(fields)) {
|
sql.append(" * ");
|
} else {
|
for (int i = 0; i < fields.length; i++) {
|
sql.append(fields[i]);
|
if (i + 1 < fields.length) {
|
sql.append(",");
|
}
|
}
|
}
|
sql.append(" FROM ");
|
sql.append(tableName);
|
if (!StringUtils.isEmpty(filter)) {
|
sql.append(" WHERE (").append(filter).append(" ) ");
|
}
|
return this.getList(sql.toString(), params);
|
}
|
|
/**
|
* 查询列表数据
|
*
|
* @param tableName 表名
|
* @param filter 条件
|
* @param params 参数
|
* @param pageIndex 页数
|
* @param pageSize 条数
|
* @return
|
*/
|
@Override
|
public DataTableEntity getList(String tableName, String filter, Object[] params, int pageIndex, int pageSize) throws com.product.core.exception.BaseException {
|
StringBuilder sql = new StringBuilder();
|
sql.append(" SELECT * FROM ").append(tableName);
|
if (!StringUtils.isEmpty(filter)) {
|
sql.append(" WHERE (").append(filter).append(" )");
|
}
|
return getList(sql.toString(), params, pageIndex, pageSize);
|
}
|
|
|
@Override
|
public DataTableEntity getList(String tableName, String filter, Object[] params, String orderBy, int pageIndex, int pageSize) throws com.product.core.exception.BaseException {
|
StringBuilder sql = new StringBuilder();
|
sql.append(" SELECT * FROM ").append(tableName);
|
if (!StringUtils.isEmpty(filter)) {
|
sql.append(" WHERE (").append(filter).append(" )");
|
}
|
if (!StringUtils.isEmpty(orderBy)) {
|
sql.append(" ORDER BY ").append(orderBy);
|
}
|
return getList(sql.toString(), params, pageIndex, pageSize);
|
}
|
|
@Override
|
public DataTableEntity getList(String tableName, String[] fields, String filter, Object[] params, String orderBy, int pageIndex, int pageSize) throws com.product.core.exception.BaseException {
|
StringBuilder sql = new StringBuilder();
|
sql.append(" SELECT ");
|
if (ArrayUtil.isEmpty(fields)) {
|
sql.append("*");
|
} else {
|
for (int i = 0; i < fields.length; i++) {
|
sql.append(fields[i]);
|
if (i + 1 < fields.length) {
|
sql.append(",");
|
}
|
}
|
}
|
sql.append(" FROM ").append(tableName);
|
if (!StringUtils.isEmpty(filter)) {
|
sql.append(" WHERE (").append(filter).append(" )");
|
}
|
if (!StringUtils.isEmpty(orderBy)) {
|
sql.append(" ORDER BY ").append(orderBy);
|
}
|
return null;
|
}
|
|
public DataTableEntity getList(String sql, Object[] params, int pageIndex, int pageSize) throws com.product.core.exception.BaseException {
|
int number = getPageParam(pageIndex, pageSize);
|
StringBuilder sb = new StringBuilder(sql);
|
sb.append(" LIMIT ?,? ");
|
if (params == null) {
|
params = new Object[]{number, pageSize};
|
} else {
|
params = ArrayUtil.append(params, number, pageSize);
|
}
|
return this.getList(sb.toString(), params);
|
}
|
|
private int getPageParam(int pageIndex, int pageSize) {
|
return pageIndex <= 1 ? 0 : (pageIndex - 1) * pageSize;
|
}
|
|
/**
|
* 查询一条数据
|
*
|
* @param sql
|
* @return
|
*/
|
@Override
|
public FieldSetEntity getOne(String sql) throws com.product.core.exception.BaseException {
|
return this.getOne(sql, new Object[]{});
|
}
|
|
/**
|
* 查询一条数据
|
*
|
* @param sql sql
|
* @param params 参数
|
* @return
|
*/
|
@Override
|
public FieldSetEntity getOne(String sql, Object[] params) throws com.product.core.exception.BaseException {
|
DataTableEntity list = this.getList(sql, params);
|
if (DataTableEntity.isEmpty(list)) {
|
return null;
|
}
|
return list.getFieldSetEntity(0);
|
}
|
|
/**
|
* 查询一条数据
|
*
|
* @param tableName
|
* @param filter
|
* @return
|
*/
|
@Override
|
public FieldSetEntity getOne(String tableName, String filter) throws com.product.core.exception.BaseException {
|
return this.getOne(tableName, filter, null);
|
}
|
|
/**
|
* 查询一条数据
|
*
|
* @param tableName 表名
|
* @param filter 条件
|
* @param params 参数
|
* @return
|
*/
|
@Override
|
public FieldSetEntity getOne(String tableName, String filter, Object[] params) throws com.product.core.exception.BaseException {
|
return this.getOne(tableName, filter, null, params);
|
}
|
|
/**
|
* 查询一条数据
|
*
|
* @param tableName 表名
|
* @param filter 条件
|
* @param fields
|
* @param params 参数
|
* @return
|
*/
|
@Override
|
public FieldSetEntity getOne(String tableName, String filter, String[] fields, Object[] params) throws com.product.core.exception.BaseException {
|
StringBuilder sb = new StringBuilder();
|
sb.append(" SELECT ");
|
if (ArrayUtil.isEmpty(fields)) {
|
sb.append(" * ");
|
} else {
|
for (int i = 0; i < fields.length; i++) {
|
sb.append(fields[i]);
|
if (i + 1 < fields.length) {
|
sb.append(",");
|
}
|
}
|
}
|
sb.append(" FROM ").append(tableName);
|
sb.append(" WHERE ");
|
if (!StringUtils.isEmpty(filter)) {
|
sb.append(" (").append(filter).append(") ");
|
}
|
sb.append(" limit 1");
|
DataTableEntity list = this.getList(sb.toString(), params);
|
return DataTableEntity.isEmpty(list) ? null : list.getFieldSetEntity(0);
|
}
|
|
/**
|
* 批处理添加数据
|
*
|
* @param data 数据集
|
* @return
|
*/
|
@Override
|
public BatchResultEntity addBatch(DataTableEntity data) throws com.product.core.exception.BaseException {
|
info("开始批量新增数据");
|
if (DataTableEntity.isEmpty(data)) {
|
info("批量新增数据条数为0");
|
return new BatchResultEntity(0);
|
}
|
TimeInterval timer = DateUtil.timer();
|
info("批量新增数据条数为：" + data.getRows());
|
BatchResultEntity batchResultEntity = new BatchResultEntity(data.getRows());
|
// ConnectionInterface connection = ConnectionManager.getConnection(dataBaseEntity);
|
try {
|
StringBuilder insertSql = BatchUtil.getInsertSql(data.getMeta());
|
info("SQL:" + insertSql);
|
Connection connection = getConnection();
|
connection.setAutoCommit(false);
|
|
try (PreparedStatement pst = connection.prepareStatement(insertSql.toString())) {
|
Object[] fields = data.getMeta().getFields();
|
for (int i = 0; i < data.getRows(); i++) {
|
FieldSetEntity fse = data.getFieldSetEntity(i);
|
for (int j = 1; j <= fields.length; j++) {
|
if (fse.getObject(fields[j - 1].toString()) instanceof oracle.sql.TIMESTAMP) {
|
pst.setObject(j, fse.getObject(fields[j - 1].toString()).toString());
|
} else {
|
pst.setObject(j, fse.getObject(fields[j - 1].toString()));
|
}
|
}
|
pst.addBatch();
|
}
|
info("开始执行批量提交数据");
|
int[] ints = pst.executeBatch();
|
connection.commit();
|
info("执行批量提交数据完成，共提交 " + data.getRows() + " 条,耗时：" + timer.intervalSecond() + " 秒 ！");
|
pst.clearBatch();
|
} catch (Exception e) {
|
SpringMVCContextHolder.getSystemLogger().error(e);
|
throw e;
|
} finally {
|
connection.setAutoCommit(true);
|
}
|
} catch (Exception e) {
|
SpringMVCContextHolder.getSystemLogger().error(e);
|
throw new BaseException(ErrorCode.ADD_BATCH_ERROR, e);
|
}
|
return batchResultEntity;
|
}
|
|
/**
|
* 批处理添加数据
|
*
|
* @param data 数据集
|
* @param AutomaticallyPrimaryField
|
* @return
|
*/
|
@Override
|
@Deprecated
|
public BatchResultEntity addBatch(DataTableEntity data, String AutomaticallyPrimaryField) throws com.product.core.exception.BaseException {
|
return addBatch(data);
|
}
|
|
@Override
|
public int update(FieldSetEntity fse, UpdateFilterEntity updateFilter) throws com.product.core.exception.BaseException {
|
//todo 待实现
|
return -1;
|
}
|
|
/**
|
* 批处理更新数据
|
*
|
* @param data
|
* @param updateFilter 过滤条件
|
* @param isCommit 自动提交
|
* @return
|
*/
|
@Override
|
public BatchResultEntity updateBatch(DataTableEntity data, UpdateFilterEntity updateFilter, boolean isCommit) throws com.product.core.exception.BaseException {
|
info("开始批量更新数据");
|
if (DataTableEntity.isEmpty(data)) {
|
info("批量更新数据条数为0");
|
return new BatchResultEntity(0);
|
}
|
TimeInterval timer = DateUtil.timer();
|
info("批量更新数据条数为：" + data.getRows());
|
BatchResultEntity batchResultEntity = new BatchResultEntity(data.getRows());
|
// ConnectionInterface connection = ConnectionManager.getConnection(dataBaseEntity);
|
try {
|
StringBuilder updateSql = BatchUtil.getUpdateSql(data.getMeta(), updateFilter.getFilter());
|
info("SQL: " + updateSql);
|
Connection connection = getConnection();
|
connection.setAutoCommit(false);
|
try (PreparedStatement pst = connection.prepareStatement(updateSql.toString())) {
|
Object[] fields = data.getMeta().getFields();
|
for (int i = 0; i < data.getRows(); i++) {
|
FieldSetEntity fse = data.getFieldSetEntity(i);
|
int j = 1;
|
for (; j <= fields.length; j++) {
|
Object value = fse.getObject(fields[j - 1].toString());
|
pst.setObject(j, value);
|
info("参数： " + (fields[j - 1]) + " = " + value);
|
}
|
j--;
|
Object[] valueFields = updateFilter.getValueFields();
|
for (int i1 = 1; i1 <= valueFields.length; i1++) {
|
Object value = fse.getObject(valueFields[i1 - 1].toString());
|
pst.setObject(j + i1, value);
|
info("参数： " + (valueFields[i1 - 1]) + " = " + value);
|
}
|
pst.addBatch();
|
}
|
info("开始执行批量提交数据");
|
pst.executeBatch();
|
info("执行批量提交数据完成，共提交 " + data.getRows() + " 条,耗时：" + timer.intervalSecond() + " 秒 ！");
|
pst.clearBatch();
|
if (isCommit) {
|
connection.commit();
|
}
|
batchResultEntity.setConnection(connection);
|
return batchResultEntity;
|
} catch (Exception e) {
|
throw e;
|
} finally {
|
if (isCommit) {
|
connection.setAutoCommit(true);
|
}
|
// this.closeConnection();
|
}
|
} catch (Exception e) {
|
e.printStackTrace();
|
SpringMVCContextHolder.getSystemLogger().error(e);
|
throw new BaseException(ErrorCode.UPDATE_BATCH_ERROR, e);
|
}
|
}
|
|
/**
|
* 删除数据
|
*
|
* @param sql sql语句
|
* @param params 参数
|
* @return
|
*/
|
@Override
|
public Boolean delete(String sql, Object[] params) throws com.product.core.exception.BaseException {
|
return executeSql(sql, params);
|
}
|
|
/**
|
* 删除数据
|
*
|
* @param tableName 表名
|
* @param filter 条件
|
* @return
|
*/
|
@Override
|
public Boolean delete(String tableName, String filter) throws com.product.core.exception.BaseException {
|
return delete(tableName, filter, null);
|
}
|
|
/**
|
* 删除数据
|
*
|
* @param tableName 表名
|
* @param filter 条件
|
* @param params 参数
|
* @return
|
*/
|
@Override
|
public Boolean delete(String tableName, String filter, Object[] params) throws com.product.core.exception.BaseException {
|
StringBuilder sql = new StringBuilder();
|
sql.append(" DELETE FROM ").append(tableName);
|
if (!StringUtils.isEmpty(filter)) {
|
sql.append(" WHERE (").append(filter).append(" ) ");
|
}
|
return executeSql(sql.toString(), params);
|
}
|
|
public int deleteRInt(String tableName, String filter, Object[] params) throws com.product.core.exception.BaseException {
|
StringBuilder sql = new StringBuilder();
|
sql.append(" DELETE FROM ").append(tableName);
|
if (!StringUtils.isEmpty(filter)) {
|
sql.append(" WHERE (").append(filter).append(" ) ");
|
}
|
return executeSqlResult(sql.toString(), params);
|
}
|
|
|
/**
|
* 执行sql
|
*
|
* @param sql
|
* @return
|
*/
|
@Override
|
public boolean executeSql(String sql) throws com.product.core.exception.BaseException {
|
return executeSql(sql, null);
|
}
|
|
|
/**
|
* 执行sql
|
*
|
* @param sql
|
* @param params
|
* @return
|
*/
|
@Override
|
public boolean executeSql(String sql, Object[] params) throws com.product.core.exception.BaseException {
|
info("执行SQL：\n" + sql);
|
try (PreparedStatement pst = getConnection().prepareStatement(sql)) {
|
if (params != null && params.length > 0) {
|
for (int i = 0; i < params.length; i++) {
|
info("param" + (i + 1) + ": " + params[i]);
|
pst.setObject(i + 1, params[i].toString());
|
}
|
}
|
return pst.execute();
|
} catch (Exception e) {
|
e.printStackTrace();
|
info("ERROR_SQL：\n" + sql);
|
throw new BaseException(ErrorCode.EXECUTE_SQL_FAIL, e);
|
}
|
}
|
|
/**
|
* 执行sql
|
*
|
* @param sql
|
* @param params
|
* @return
|
*/
|
public int executeSqlResult(String sql, Object[] params) throws com.product.core.exception.BaseException {
|
try (PreparedStatement pst = getConnection().prepareStatement(sql)) {
|
info("执行SQL：\n" + sql);
|
if (params != null && params.length > 0) {
|
for (int i = 0; i < params.length; i++) {
|
info("param" + (i + 1) + ": " + params[i]);
|
pst.setObject(i + 1, params[i].toString());
|
}
|
}
|
return pst.executeUpdate();
|
} catch (Exception e) {
|
throw new BaseException(ErrorCode.EXECUTE_SQL_FAIL, e);
|
}
|
}
|
|
@Override
|
public void closeConnection() {
|
try {
|
if (this.connection != null && !this.connection.isClosed()) {
|
this.connection.close();
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
this.connection = null;
|
}
|
}
|
|
class BaseException extends com.product.core.exception.BaseException {
|
public BaseException(IEnum a) {
|
super(a);
|
}
|
|
|
public BaseException(IEnum iEnum, Throwable throwable) {
|
super(iEnum.getValue(), iEnum.getText() + (throwable.getMessage() != null ? "," + throwable.getMessage() : ""));
|
}
|
}
|
|
@Override
|
protected void finalize() throws Throwable {
|
this.closeConnection();
|
super.finalize();
|
}
|
}
|
