org.sagacity.sqltoy.dialect.utils.PostgreSqlDialectUtils Maven / Gradle / Ivy
/**
*
*/
package org.sagacity.sqltoy.dialect.utils;
import java.io.Serializable;
import java.sql.Connection;
import java.util.List;
import org.sagacity.sqltoy.SqlToyConstants;
import org.sagacity.sqltoy.SqlToyContext;
import org.sagacity.sqltoy.callback.DecryptHandler;
import org.sagacity.sqltoy.callback.GenerateSavePKStrategy;
import org.sagacity.sqltoy.callback.GenerateSqlHandler;
import org.sagacity.sqltoy.callback.ReflectPropsHandler;
import org.sagacity.sqltoy.config.model.EntityMeta;
import org.sagacity.sqltoy.config.model.FieldMeta;
import org.sagacity.sqltoy.config.model.OperateType;
import org.sagacity.sqltoy.config.model.PKStrategy;
import org.sagacity.sqltoy.config.model.SqlToyConfig;
import org.sagacity.sqltoy.config.model.SqlToyResult;
import org.sagacity.sqltoy.dialect.model.SavePKStrategy;
import org.sagacity.sqltoy.model.QueryExecutor;
import org.sagacity.sqltoy.model.QueryResult;
import org.sagacity.sqltoy.model.inner.QueryExecutorExtend;
import org.sagacity.sqltoy.utils.SqlUtilsExt;
import org.sagacity.sqltoy.utils.StringUtil;
/**
* @project sqltoy-orm
* @description 提供postgresql数据库共用的逻辑实现，便于今后postgresql不同版本之间共享共性部分的实现
* @author zhongxuchen
* @version v1.0,Date:2015年3月5日
* @modify Date:2020-06-12 修复10+版本对identity主键生成的策略
*/
public class PostgreSqlDialectUtils {
/**
* 判定为null的函数
*/
public static final String NVL_FUNCTION = "COALESCE";
/**
* @todo 提供随机记录查询
* @param sqlToyContext
* @param sqlToyConfig
* @param queryExecutor
* @param decryptHandler
* @param totalCount
* @param randomCount
* @param conn
* @param dbType
* @param dialect
* @param fetchSize
* @param maxRows
* @return
* @throws Exception
*/
public static QueryResult getRandomResult(SqlToyContext sqlToyContext, SqlToyConfig sqlToyConfig,
QueryExecutor queryExecutor, final DecryptHandler decryptHandler, Long totalCount, Long randomCount,
Connection conn, final Integer dbType, final String dialect, final int fetchSize, final int maxRows)
throws Exception {
StringBuilder sql = new StringBuilder();
String innerSql = sqlToyConfig.isHasFast() ? sqlToyConfig.getFastSql(dialect) : sqlToyConfig.getSql(dialect);
// sql中是否存在排序或union
boolean hasOrderOrUnion = DialectUtils.hasOrderByOrUnion(innerSql);
// 给原始sql标记上特殊的开始和结尾，便于sql拦截器快速定位到原始sql并进行条件补充
innerSql = SqlUtilsExt.markOriginalSql(innerSql);
if (sqlToyConfig.isHasFast()) {
sql.append(sqlToyConfig.getFastPreSql(dialect));
if (!sqlToyConfig.isIgnoreBracket()) {
sql.append(" (");
}
}
// 存在order 或union 则在sql外包裹一层
if (hasOrderOrUnion) {
sql.append("select " + SqlToyConstants.INTERMEDIATE_TABLE + ".* from (");
}
sql.append(innerSql);
if (hasOrderOrUnion) {
sql.append(") ");
sql.append(SqlToyConstants.INTERMEDIATE_TABLE);
sql.append(" ");
}
sql.append(" order by random() limit ");
sql.append(randomCount);
if (sqlToyConfig.isHasFast()) {
if (!sqlToyConfig.isIgnoreBracket()) {
sql.append(") ");
}
sql.append(sqlToyConfig.getFastTailSql(dialect));
}
SqlToyResult queryParam = DialectUtils.wrapPageSqlParams(sqlToyContext, sqlToyConfig, queryExecutor,
sql.toString(), null, null, dialect);
QueryExecutorExtend extend = queryExecutor.getInnerModel();
// 增加sql执行拦截器 update 2022-9-10
queryParam = DialectUtils.doInterceptors(sqlToyContext, sqlToyConfig,
(extend.entityClass == null) ? OperateType.random : OperateType.singleTable, queryParam,
extend.entityClass, dbType);
return DialectUtils.findBySql(sqlToyContext, sqlToyConfig, queryParam.getSql(), queryParam.getParamsValue(),
extend, decryptHandler, conn, dbType, 0, fetchSize, maxRows);
}
/**
* @todo 保存单条对象记录
* @param sqlToyContext
* @param entity
* @param conn
* @param dbType
* @param tableName
* @return
* @throws Exception
*/
public static Object save(SqlToyContext sqlToyContext, Serializable entity, Connection conn, final Integer dbType,
String tableName) throws Exception {
// 只支持sequence模式
EntityMeta entityMeta = sqlToyContext.getEntityMeta(entity.getClass());
String sequence = "nextval('" + entityMeta.getSequence() + "')";
// save行为根据主键是否赋值情况调整最终的主键策略
PKStrategy pkStrategy = DialectUtils.getSavePKStrategy(entityMeta, entity, dbType);
boolean isAssignPK = isAssignPKValue(pkStrategy);
String insertSql = DialectExtUtils.generateInsertSql(sqlToyContext.getUnifyFieldsHandler(), dbType, entityMeta,
pkStrategy, NVL_FUNCTION, sequence, isAssignPK, tableName);
return DialectUtils.save(sqlToyContext, entityMeta, pkStrategy, isAssignPK, insertSql, entity,
new GenerateSqlHandler() {
@Override
public String generateSql(EntityMeta entityMeta, String[] forceUpdateField) {
PKStrategy pkStrategy = entityMeta.getIdStrategy();
String sequence = "nextval('" + entityMeta.getSequence() + "')";
return DialectExtUtils.generateInsertSql(sqlToyContext.getUnifyFieldsHandler(), dbType,
entityMeta, pkStrategy, NVL_FUNCTION, sequence, isAssignPKValue(pkStrategy), null);
}
}, new GenerateSavePKStrategy() {
@Override
public SavePKStrategy generate(EntityMeta entityMeta) {
return new SavePKStrategy(entityMeta.getIdStrategy(),
isAssignPKValue(entityMeta.getIdStrategy()));
}
}, conn, dbType);
}
/**
* @todo 批量保存对象入数据库
* @param sqlToyContext
* @param entities
* @param batchSize
* @param reflectPropsHandler
* @param conn
* @param dbType
* @param autoCommit
* @param tableName
* @return
* @throws Exception
*/
public static Long saveAll(SqlToyContext sqlToyContext, List entities, final int batchSize,
ReflectPropsHandler reflectPropsHandler, Connection conn, final Integer dbType, final Boolean autoCommit,
String tableName) throws Exception {
EntityMeta entityMeta = sqlToyContext.getEntityMeta(entities.get(0).getClass());
PKStrategy pkStrategy = entityMeta.getIdStrategy();
String sequence = "nextval('" + entityMeta.getSequence() + "')";
boolean isAssignPK = isAssignPKValue(pkStrategy);
String insertSql = DialectExtUtils.generateInsertSql(sqlToyContext.getUnifyFieldsHandler(), dbType, entityMeta,
pkStrategy, NVL_FUNCTION, sequence, isAssignPK, tableName);
return DialectUtils.saveAll(sqlToyContext, entityMeta, pkStrategy, isAssignPK, insertSql, entities, batchSize,
reflectPropsHandler, conn, dbType, autoCommit);
}
/**
* @TODO postgresql15 开始支持merge into 语法
* @param sqlToyContext
* @param entities
* @param batchSize
* @param reflectPropsHandler
* @param forceUpdateFields
* @param conn
* @param dbType
* @param dialect
* @param autoCommit
* @param tableName
* @return
* @throws Exception
*/
public static Long saveOrUpdateAll(SqlToyContext sqlToyContext, List entities, final int batchSize,
ReflectPropsHandler reflectPropsHandler, String[] forceUpdateFields, Connection conn, final Integer dbType,
final String dialect, final Boolean autoCommit, final String tableName) throws Exception {
EntityMeta entityMeta = sqlToyContext.getEntityMeta(entities.get(0).getClass());
return DialectUtils.saveOrUpdateAll(sqlToyContext, entities, batchSize, entityMeta, forceUpdateFields,
new GenerateSqlHandler() {
@Override
public String generateSql(EntityMeta entityMeta, String[] forceUpdateFields) {
PKStrategy pkStrategy = entityMeta.getIdStrategy();
String sequence = "nextval('" + entityMeta.getSequence() + "')";
return DialectUtils.getSaveOrUpdateSql(sqlToyContext, sqlToyContext.getUnifyFieldsHandler(),
dbType, entityMeta, pkStrategy, forceUpdateFields, null, NVL_FUNCTION, sequence,
isAssignPKValue(pkStrategy), tableName);
}
}, reflectPropsHandler, conn, dbType, autoCommit);
}
/**
* @todo 组织merge into 语句中select 的字段，进行类型转换
* @param sql
* @param columnName
* @param fieldMeta
*/
public static void wrapSelectFields(StringBuilder sql, String columnName, FieldMeta fieldMeta) {
int jdbcType = fieldMeta.getType();
if (jdbcType == java.sql.Types.VARCHAR) {
sql.append("?");
} else if (jdbcType == java.sql.Types.CHAR) {
sql.append("?");
} else if (jdbcType == java.sql.Types.DATE) {
sql.append("cast(? as date)");
} else if (jdbcType == java.sql.Types.NUMERIC) {
sql.append("cast(? as numeric)");
} else if (jdbcType == java.sql.Types.DECIMAL) {
sql.append("cast(? as decimal)");
} else if (jdbcType == java.sql.Types.BIGINT) {
sql.append("cast(? as bigint)");
} else if (jdbcType == java.sql.Types.INTEGER || jdbcType == java.sql.Types.TINYINT) {
sql.append("cast(? as integer)");
} else if (jdbcType == java.sql.Types.TIMESTAMP) {
sql.append("cast(? as timestamp)");
} else if (jdbcType == java.sql.Types.DOUBLE) {
sql.append("cast(? as double)");
} else if (jdbcType == java.sql.Types.FLOAT) {
sql.append("cast(? as double)");
} else if (jdbcType == java.sql.Types.TIME) {
sql.append("cast(? as time)");
} else if (jdbcType == java.sql.Types.CLOB) {
sql.append("cast(? as text)");
} else if (jdbcType == java.sql.Types.BOOLEAN) {
sql.append("cast(? as boolean)");
} else if (jdbcType == java.sql.Types.BINARY) {
sql.append("cast(? as bytea)");
} else if (jdbcType == java.sql.Types.BLOB) {
sql.append("cast(? as bytea)");
} else {
// 数组、json等特殊类型
if (StringUtil.isNotBlank(fieldMeta.getNativeType())) {
sql.append("cast(? as " + fieldMeta.getNativeType() + ")");
} else {
sql.append("?");
}
}
sql.append(" as ");
sql.append(columnName);
}
/**
* @TODO 定义当使用sequence或identity时,是否允许自定义值(即不通过sequence或identity产生，而是由外部直接赋值)
* @param pkStrategy
* @return
*/
public static boolean isAssignPKValue(PKStrategy pkStrategy) {
if (pkStrategy == null) {
return true;
}
// postgresql10+ 支持identity，但不能直接赋值
if (pkStrategy.equals(PKStrategy.IDENTITY)) {
return false;
}
return true;
}
}
© 2015 - 2024 Weber Informatics LLC | Privacy Policy