org.sagacity.sqltoy.dialect.impl.PostgreSqlDialect Maven / Gradle / Ivy
/**
*
*/
package org.sagacity.sqltoy.dialect.impl;
import java.io.Serializable;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import org.sagacity.sqltoy.SqlExecuteStat;
import org.sagacity.sqltoy.SqlToyContext;
import org.sagacity.sqltoy.callback.DecryptHandler;
import org.sagacity.sqltoy.callback.GenerateSqlHandler;
import org.sagacity.sqltoy.callback.ReflectPropsHandler;
import org.sagacity.sqltoy.callback.UpdateRowHandler;
import org.sagacity.sqltoy.config.model.EntityMeta;
import org.sagacity.sqltoy.config.model.PKStrategy;
import org.sagacity.sqltoy.config.model.SqlToyConfig;
import org.sagacity.sqltoy.config.model.SqlType;
import org.sagacity.sqltoy.dialect.Dialect;
import org.sagacity.sqltoy.dialect.utils.DefaultDialectUtils;
import org.sagacity.sqltoy.dialect.utils.DialectExtUtils;
import org.sagacity.sqltoy.dialect.utils.DialectUtils;
import org.sagacity.sqltoy.dialect.utils.PostgreSqlDialectUtils;
import org.sagacity.sqltoy.model.ColumnMeta;
import org.sagacity.sqltoy.model.LockMode;
import org.sagacity.sqltoy.model.QueryExecutor;
import org.sagacity.sqltoy.model.QueryResult;
import org.sagacity.sqltoy.model.StoreResult;
import org.sagacity.sqltoy.model.TableMeta;
import org.sagacity.sqltoy.model.inner.QueryExecutorExtend;
import org.sagacity.sqltoy.utils.DataSourceUtils.DBType;
import org.sagacity.sqltoy.utils.SqlUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
* @project sqltoy-orm
* @description 基于postgresql9.5+版本的方言实现,9.5开始insert into [ON CONFLICT DO
* NOTHING/UPDATE]功能生效
* @author zhongxuchen
* @version v1.0,Date:2015年8月10日
* @modify Date:2019-3-12
* 修复saveOrUpdate的缺陷,改为先update后saveIgnore，因为其跟mysql一样存在bug
* @modify Date:2020-06-12 修复10+版本对identity主键生成的策略
*/
@SuppressWarnings({ "rawtypes" })
public class PostgreSqlDialect implements Dialect {
/**
* 定义日志
*/
protected final Logger logger = LoggerFactory.getLogger(PostgreSqlDialect.class);
/**
* 判定为null的函数
*/
public static final String NVL_FUNCTION = "COALESCE";
@Override
public boolean isUnique(SqlToyContext sqlToyContext, Serializable entity, String[] paramsNamed, Connection conn,
final Integer dbType, String tableName) {
return DialectUtils.isUnique(sqlToyContext, entity, paramsNamed, conn, dbType, tableName,
(entityMeta, realParamNamed, table, topSize) -> {
String queryStr = DialectExtUtils.wrapUniqueSql(entityMeta, realParamNamed, dbType, table);
return queryStr + " limit " + topSize;
});
}
/*
* (non-Javadoc)
*
* @see org.sagacity.sqltoy.dialect.Dialect#getRandomResult(org.sagacity.sqltoy.
* SqlToyContext, org.sagacity.sqltoy.config.model.SqlToyConfig,
* org.sagacity.sqltoy.model.QueryExecutor, java.lang.Long, java.lang.Long,
* java.sql.Connection)
*/
@Override
public 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 {
return PostgreSqlDialectUtils.getRandomResult(sqlToyContext, sqlToyConfig, queryExecutor, decryptHandler,
totalCount, randomCount, conn, dbType, dialect, fetchSize, maxRows);
}
/*
* (non-Javadoc)
*
* @see org.sagacity.sqltoy.dialect.Dialect#findPageBySql(org.sagacity.sqltoy.
* SqlToyContext, org.sagacity.sqltoy.config.model.SqlToyConfig,
* org.sagacity.sqltoy.model.QueryExecutor, java.lang.Long, java.lang.Integer,
* java.sql.Connection)
*/
@Override
public QueryResult findPageBySql(SqlToyContext sqlToyContext, SqlToyConfig sqlToyConfig,
QueryExecutor queryExecutor, final DecryptHandler decryptHandler, Long pageNo, Integer pageSize,
Connection conn, final Integer dbType, final String dialect, final int fetchSize, final int maxRows)
throws Exception {
return DefaultDialectUtils.findPageBySql(sqlToyContext, sqlToyConfig, queryExecutor, decryptHandler, pageNo,
pageSize, conn, dbType, dialect, fetchSize, maxRows);
}
/*
* (non-Javadoc)
*
* @see org.sagacity.sqltoy.dialect.Dialect#findTopBySql(org.sagacity.sqltoy.
* SqlToyContext, org.sagacity.sqltoy.config.model.SqlToyConfig,
* org.sagacity.sqltoy.model.QueryExecutor, double, java.sql.Connection)
*/
@Override
public QueryResult findTopBySql(SqlToyContext sqlToyContext, SqlToyConfig sqlToyConfig, QueryExecutor queryExecutor,
final DecryptHandler decryptHandler, Integer topSize, Connection conn, final Integer dbType,
final String dialect, final int fetchSize, final int maxRows) throws Exception {
return DefaultDialectUtils.findTopBySql(sqlToyContext, sqlToyConfig, queryExecutor, decryptHandler, topSize,
conn, dbType, dialect, fetchSize, maxRows);
}
/*
* (non-Javadoc)
*
* @see org.sagacity.sqltoy.dialect.Dialect#findBySql(org.sagacity.sqltoy.
* SqlToyContext, org.sagacity.sqltoy.config.model.SqlToyConfig,
* java.lang.String, java.lang.Object[],
* org.sagacity.sqltoy.callback.RowCallbackHandler, java.sql.Connection)
*/
@Override
public QueryResult findBySql(SqlToyContext sqlToyContext, SqlToyConfig sqlToyConfig, String sql,
Object[] paramsValue, QueryExecutorExtend queryExecutorExtend, final DecryptHandler decryptHandler,
final Connection conn, final LockMode lockMode, final Integer dbType, final String dialect,
final int fetchSize, final int maxRows) throws Exception {
String realSql = sql.concat(getLockSql(sql, dbType, lockMode));
return DialectUtils.findBySql(sqlToyContext, sqlToyConfig, realSql, paramsValue, queryExecutorExtend,
decryptHandler, conn, dbType, 0, fetchSize, maxRows);
}
/*
* (non-Javadoc)
*
* @see org.sagacity.sqltoy.dialect.Dialect#getCountBySql(org.sagacity.sqltoy.
* SqlToyContext, java.lang.String, java.lang.Object[], boolean,
* java.sql.Connection)
*/
@Override
public Long getCountBySql(SqlToyContext sqlToyContext, final SqlToyConfig sqlToyConfig, String sql,
Object[] paramsValue, boolean isLastSql, Connection conn, final Integer dbType, final String dialect)
throws Exception {
return DialectUtils.getCountBySql(sqlToyContext, sqlToyConfig, sql, paramsValue, isLastSql, conn, dbType);
}
/*
* (non-Javadoc)
*
* @see org.sagacity.sqltoy.dialect.Dialect#load(org.sagacity.sqltoy.
* SqlToyContext, java.io.Serializable, java.util.List,
* org.sagacity.sqltoy.lock.LockMode, java.sql.Connection)
*/
@Override
public Serializable load(SqlToyContext sqlToyContext, Serializable entity, boolean onlySubTables,
List cascadeTypes, LockMode lockMode, Connection conn, final Integer dbType, final String dialect,
final String tableName) throws Exception {
EntityMeta entityMeta = sqlToyContext.getEntityMeta(entity.getClass());
// 获取loadsql(loadsql 可以通过@loadSql进行改变，所以需要sqltoyContext重新获取)
SqlToyConfig sqlToyConfig = sqlToyContext.getSqlToyConfig(entityMeta.getLoadSql(tableName), SqlType.search,
dialect, null);
String loadSql = sqlToyConfig.getSql(dialect);
loadSql = loadSql.concat(getLockSql(loadSql, dbType, lockMode));
return (Serializable) DialectUtils.load(sqlToyContext, sqlToyConfig, loadSql, entityMeta, entity, onlySubTables,
cascadeTypes, conn, dbType);
}
/*
* (non-Javadoc)
*
* @see org.sagacity.sqltoy.dialect.Dialect#loadAll(org.sagacity.sqltoy.
* SqlToyContext, java.util.List, java.util.List,
* org.sagacity.sqltoy.lock.LockMode, java.sql.Connection)
*/
@Override
public List loadAll(SqlToyContext sqlToyContext, List entities, boolean onlySubTables,
List cascadeTypes, LockMode lockMode, Connection conn, final Integer dbType, final String dialect,
final String tableName, final int fetchSize, final int maxRows) throws Exception {
return DialectUtils.loadAll(sqlToyContext, entities, onlySubTables, cascadeTypes, lockMode, conn, dbType,
tableName, (sql, dbTypeValue, lockedMode) -> {
return getLockSql(sql, dbTypeValue, lockedMode);
}, fetchSize, maxRows);
}
/*
* (non-Javadoc)
*
* @see org.sagacity.sqltoy.dialect.Dialect#save(org.sagacity.sqltoy.
* SqlToyContext, java.io.Serializable, java.sql.Connection)
*/
@Override
public Object save(SqlToyContext sqlToyContext, Serializable entity, Connection conn, final Integer dbType,
final String dialect, final String tableName) throws Exception {
return PostgreSqlDialectUtils.save(sqlToyContext, entity, conn, dbType, tableName);
}
/*
* (non-Javadoc)
*
* @see org.sagacity.sqltoy.dialect.Dialect#saveAll(org.sagacity.sqltoy.
* SqlToyContext, java.util.List,
* org.sagacity.sqltoy.callback.ReflectPropsHandler, java.sql.Connection)
*/
@Override
public Long saveAll(SqlToyContext sqlToyContext, List entities, final int batchSize,
ReflectPropsHandler reflectPropsHandler, Connection conn, final Integer dbType, final String dialect,
final Boolean autoCommit, final String tableName) throws Exception {
return PostgreSqlDialectUtils.saveAll(sqlToyContext, entities, batchSize, reflectPropsHandler, conn, dbType,
autoCommit, tableName);
}
/*
* (non-Javadoc)
*
* @see org.sagacity.sqltoy.dialect.Dialect#update(org.sagacity.sqltoy.
* SqlToyContext, java.io.Serializable, java.lang.String[], boolean,
* java.lang.Class[], java.util.HashMap, java.sql.Connection)
*/
@Override
public Long update(SqlToyContext sqlToyContext, Serializable entity, String[] forceUpdateFields, boolean cascade,
Class[] forceCascadeClasses, HashMap subTableForceUpdateProps, Connection conn,
final Integer dbType, final String dialect, final String tableName) throws Exception {
return DialectUtils.update(sqlToyContext, entity, NVL_FUNCTION, forceUpdateFields, cascade,
(cascade == false) ? null : new GenerateSqlHandler() {
@Override
public String generateSql(EntityMeta entityMeta, String[] forceUpdateFields) {
PKStrategy pkStrategy = entityMeta.getIdStrategy();
String sequence = "nextval('" + entityMeta.getSequence() + "')";
boolean isAssignPK = PostgreSqlDialectUtils.isAssignPKValue(pkStrategy);
// update 级联操作过程中会自动判断postgresql15，而采用不同策略(这里统一按15的规则提供，14之前版本并不用到)
return DialectUtils.getSaveOrUpdateSql(sqlToyContext, sqlToyContext.getUnifyFieldsHandler(),
dbType, entityMeta, pkStrategy, forceUpdateFields, null, NVL_FUNCTION, sequence,
isAssignPK, null);
}
}, forceCascadeClasses, subTableForceUpdateProps, conn, dbType, tableName);
}
/*
* (non-Javadoc)
*
* @see org.sagacity.sqltoy.dialect.Dialect#updateAll(org.sagacity.sqltoy.
* SqlToyContext, java.util.List, java.lang.String[],
* org.sagacity.sqltoy.callback.ReflectPropsHandler, java.sql.Connection)
*/
@Override
public Long updateAll(SqlToyContext sqlToyContext, List entities, final int batchSize,
final String[] uniqueFields, String[] forceUpdateFields, ReflectPropsHandler reflectPropsHandler,
Connection conn, final Integer dbType, final String dialect, final Boolean autoCommit,
final String tableName) throws Exception {
return DialectUtils.updateAll(sqlToyContext, entities, batchSize, forceUpdateFields, reflectPropsHandler,
NVL_FUNCTION, conn, dbType, autoCommit, tableName, false);
}
@Override
public Serializable updateSaveFetch(SqlToyContext sqlToyContext, Serializable entity,
UpdateRowHandler updateRowHandler, String[] uniqueProps, Connection conn, Integer dbType, String dialect,
String tableName) throws Exception {
return DefaultDialectUtils.updateSaveFetch(sqlToyContext, entity, updateRowHandler, uniqueProps, conn, dbType,
dialect, tableName);
}
// postgres的ON CONFLICT ON CONSTRAINT() DO UPDATE SET特性跟mysql一样存在bug
/*
* (non-Javadoc)
*
* @see org.sagacity.sqltoy.dialect.Dialect#saveOrUpdate(org.sagacity.sqltoy.
* SqlToyContext, java.io.Serializable, java.lang.String[], java.sql.Connection,
* java.lang.Boolean)
*/
@Override
public Long saveOrUpdate(SqlToyContext sqlToyContext, Serializable entity, String[] forceUpdateFields,
Connection conn, final Integer dbType, final String dialect, final Boolean autoCommit,
final String tableName) throws Exception {
List entities = new ArrayList();
entities.add(entity);
return saveOrUpdateAll(sqlToyContext, entities, sqlToyContext.getBatchSize(), null, forceUpdateFields, conn,
dbType, dialect, autoCommit, tableName);
}
/*
* (non-Javadoc)
*
* @see org.sagacity.sqltoy.dialect.Dialect#saveOrUpdateAll(org.sagacity.sqltoy.
* SqlToyContext, java.util.List,
* org.sagacity.sqltoy.callback.ReflectPropsHandler, java.lang.String[],
* java.sql.Connection, java.lang.Boolean)
*/
@Override
public 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 {
// 暂时不开放，postgresql类型太多,merge 语句中需要case(? as type) as columnName
// 目前type类型无法完整适配支持
// postgresql15 支持merge into
if (dbType.equals(DBType.POSTGRESQL15)) {
return PostgreSqlDialectUtils.saveOrUpdateAll(sqlToyContext, entities, batchSize, reflectPropsHandler,
forceUpdateFields, conn, dbType, dialect, autoCommit, tableName);
}
Long updateCnt = DialectUtils.updateAll(sqlToyContext, entities, batchSize, forceUpdateFields,
reflectPropsHandler, NVL_FUNCTION, conn, dbType, autoCommit, tableName, true);
// 如果修改的记录数量跟总记录数量一致,表示全部是修改
if (updateCnt >= entities.size()) {
SqlExecuteStat.debug("修改记录", "修改记录量:" + updateCnt + " 条,等于entities集合长度,不再做insert操作!");
return updateCnt;
}
Long saveCnt = saveAllIgnoreExist(sqlToyContext, entities, batchSize, reflectPropsHandler, conn, dbType,
dialect, autoCommit, tableName);
SqlExecuteStat.debug("新增记录", "新建记录数量:" + saveCnt + " 条!");
return updateCnt + saveCnt;
}
/*
* (non-Javadoc)
*
* @see org.sagacity.sqltoy.dialect.Dialect#saveAllNotExist(org.sagacity.sqltoy.
* SqlToyContext, java.util.List,
* org.sagacity.sqltoy.callback.ReflectPropsHandler, java.sql.Connection,
* java.lang.Boolean)
*/
@Override
public Long saveAllIgnoreExist(SqlToyContext sqlToyContext, List entities, final int batchSize,
ReflectPropsHandler reflectPropsHandler, 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.saveAllIgnoreExist(sqlToyContext, entities, batchSize, entityMeta,
new GenerateSqlHandler() {
@Override
public String generateSql(EntityMeta entityMeta, String[] forceUpdateFields) {
PKStrategy pkStrategy = entityMeta.getIdStrategy();
String sequence = "nextval('" + entityMeta.getSequence() + "')";
boolean isAssignPK = PostgreSqlDialectUtils.isAssignPKValue(pkStrategy);
if (dbType == DBType.POSTGRESQL15) {
return DialectExtUtils.mergeIgnore(sqlToyContext.getUnifyFieldsHandler(), dbType,
entityMeta, pkStrategy, null, NVL_FUNCTION, sequence, isAssignPK, tableName);
}
return DialectExtUtils.insertIgnore(sqlToyContext.getUnifyFieldsHandler(), dbType, entityMeta,
pkStrategy, NVL_FUNCTION, sequence, isAssignPK, tableName);
}
}, reflectPropsHandler, conn, dbType, autoCommit);
}
/*
* (non-Javadoc)
*
* @see org.sagacity.sqltoy.dialect.Dialect#delete(org.sagacity.sqltoy.
* SqlToyContext, java.io.Serializable, java.sql.Connection)
*/
@Override
public Long delete(SqlToyContext sqlToyContext, Serializable entity, Connection conn, final Integer dbType,
final String dialect, final String tableName) throws Exception {
return DialectUtils.delete(sqlToyContext, entity, conn, dbType, tableName);
}
/*
* (non-Javadoc)
*
* @see org.sagacity.sqltoy.dialect.Dialect#deleteAll(org.sagacity.sqltoy.
* SqlToyContext, java.util.List, java.sql.Connection)
*/
@Override
public Long deleteAll(SqlToyContext sqlToyContext, List entities, final int batchSize, Connection conn,
final Integer dbType, final String dialect, final Boolean autoCommit, final String tableName)
throws Exception {
return DialectUtils.deleteAll(sqlToyContext, entities, batchSize, conn, dbType, autoCommit, tableName);
}
/*
* (non-Javadoc)
*
* @see org.sagacity.sqltoy.dialect.Dialect#updateFetch(org.sagacity.sqltoy.
* SqlToyContext, org.sagacity.sqltoy.config.model.SqlToyConfig,
* java.lang.String, java.lang.Object[],
* org.sagacity.core.database.callback.UpdateRowHandler, java.sql.Connection)
*/
@Override
public QueryResult updateFetch(SqlToyContext sqlToyContext, SqlToyConfig sqlToyConfig, String sql,
Object[] paramValues, UpdateRowHandler updateRowHandler, Connection conn, final Integer dbType,
final String dialect, final LockMode lockMode, final int fetchSize, final int maxRows) throws Exception {
String realSql = sql.concat(getLockSql(sql, dbType, (lockMode == null) ? LockMode.UPGRADE : lockMode));
return DialectUtils.updateFetchBySql(sqlToyContext, sqlToyConfig, realSql, paramValues, updateRowHandler, conn,
dbType, 0, fetchSize, maxRows);
}
@Override
public StoreResult executeStore(SqlToyContext sqlToyContext, final SqlToyConfig sqlToyConfig, final String sql,
final Object[] inParamsValue, final Integer[] outParamsType, final boolean moreResult,
final Connection conn, final Integer dbType, final String dialect, final int fetchSize) throws Exception {
return DialectUtils.executeStore(sqlToyConfig, sqlToyContext, sql, inParamsValue, outParamsType, moreResult,
conn, dbType, fetchSize);
}
@Override
public List getTableColumns(String catalog, String schema, String tableName, Connection conn,
Integer dbType, String dialect) throws Exception {
return DefaultDialectUtils.getTableColumns(catalog, schema, tableName, conn, dbType, dialect);
}
@Override
public List getTables(String catalog, String schema, String tableName, Connection conn, Integer dbType,
String dialect) throws Exception {
return DefaultDialectUtils.getTables(catalog, schema, tableName, conn, dbType, dialect);
}
private String getLockSql(String sql, Integer dbType, LockMode lockMode) {
// 判断是否已经包含for update
if (lockMode == null || SqlUtil.hasLock(sql, dbType)) {
return "";
}
if (lockMode == LockMode.UPGRADE_NOWAIT) {
return " for update nowait ";
}
if (lockMode == LockMode.UPGRADE_SKIPLOCK) {
return " for update skip locked";
}
return " for update ";
}
}
© 2015 - 2024 Weber Informatics LLC | Privacy Policy