org.sagacity.sqltoy.dialect.impl.SqlServerDialect 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 java.util.Map;
import java.util.regex.Pattern;
import org.sagacity.sqltoy.SqlToyConstants;
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.SqlConfigParseUtils;
import org.sagacity.sqltoy.config.model.EntityMeta;
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.config.model.SqlType;
import org.sagacity.sqltoy.config.model.SqlWithAnalysis;
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.SqlServerDialectUtils;
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.SqlUtil;
import org.sagacity.sqltoy.utils.SqlUtilsExt;
import org.sagacity.sqltoy.utils.StringUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
* @project sqltoy-orm
* @description sqlserver2012以及更新版本的数据库操作实现
* @author zhongxuchen
* @version v1.0,Date:2013-3-21
* @modify Date:2020-2-5 废弃对sqlserver2008 的支持,最低版本为2012版
*/
@SuppressWarnings({ "rawtypes" })
public class SqlServerDialect implements Dialect {
/**
* 定义日志
*/
protected final Logger logger = LoggerFactory.getLogger(SqlServerDialect.class);
/**
* 判定为null的函数
*/
private static final String NVL_FUNCTION = "isnull";
// order by 匹配
private static final Pattern ORDER_BY = Pattern.compile("(?i)\\Worder\\s*by\\W");
@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.replaceFirst("(?i)select ", "select top " + 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 SqlServerDialectUtils.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 {
QueryExecutorExtend extend = queryExecutor.getInnerModel();
StringBuilder sql = new StringBuilder();
boolean isNamed = sqlToyConfig.isNamedParam();
String innerSql = sqlToyConfig.isHasFast() ? sqlToyConfig.getFastSql(dialect) : sqlToyConfig.getSql(dialect);
// update 2021-10-20 提前试算一下实际sql,便于判断最终sql中是否包含order by
String judgeOrderSql = innerSql;
// 给原始sql标记上特殊的开始和结尾，便于sql拦截器快速定位到原始sql并进行条件补充
innerSql = SqlUtilsExt.markOriginalSql(innerSql);
// 存在@fast() 快速分页
if (sqlToyConfig.isHasFast()) {
sql.append(sqlToyConfig.getFastPreSql(dialect));
if (!sqlToyConfig.isIgnoreBracket()) {
sql.append(" (");
}
}
sql.append(innerSql);
// 避免条件用?模式,导致实际参数位置不匹配,因此只针对:name模式进行处理
if (isNamed) {
SqlToyResult tmpResult = SqlConfigParseUtils.processSql(judgeOrderSql, extend.getParamsName(),
extend.getParamsValue(sqlToyContext, sqlToyConfig), dialect);
judgeOrderSql = tmpResult.getSql();
}
// order by位置
int orderByIndex = StringUtil.matchIndex(judgeOrderSql, ORDER_BY);
// 存在order by，继续判断order by 是否在子查询内
if (orderByIndex > 0) {
// 剔除select 和from 之间内容，剔除sql中所有()之间的内容,即剔除所有子查询，再判断是否有order by
orderByIndex = StringUtil.matchIndex(DialectUtils.clearDisturbSql(judgeOrderSql), ORDER_BY);
}
// 不存在order by或order by存在于子查询中
if (orderByIndex < 0) {
sql.append(" order by NEWID() ");
}
// 增加分页语句
sql.append(" offset ");
sql.append(isNamed ? ":" + SqlToyConstants.PAGE_FIRST_PARAM_NAME : "?");
sql.append(" rows fetch next ");
sql.append(isNamed ? ":" + SqlToyConstants.PAGE_LAST_PARAM_NAME : "?");
sql.append(" rows only");
if (sqlToyConfig.isHasFast()) {
if (!sqlToyConfig.isIgnoreBracket()) {
sql.append(") ");
}
sql.append(sqlToyConfig.getFastTailSql(dialect));
}
SqlToyResult queryParam = DialectUtils.wrapPageSqlParams(sqlToyContext, sqlToyConfig, queryExecutor,
sql.toString(), (pageNo - 1) * pageSize, Long.valueOf(pageSize), dialect);
// 增加sql执行拦截器 update 2022-9-10
queryParam = DialectUtils.doInterceptors(sqlToyContext, sqlToyConfig,
(extend.entityClass == null) ? OperateType.page : OperateType.singleTable, queryParam,
extend.entityClass, dbType);
return findBySql(sqlToyContext, sqlToyConfig, queryParam.getSql(), queryParam.getParamsValue(), extend,
decryptHandler, conn, null, 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 {
StringBuilder sql = new StringBuilder();
String innerSql = sqlToyConfig.isHasFast() ? sqlToyConfig.getFastSql(dialect) : sqlToyConfig.getSql(dialect);
if (sqlToyConfig.isHasFast()) {
sql.append(sqlToyConfig.getFastPreSql(dialect));
if (!sqlToyConfig.isIgnoreBracket()) {
sql.append(" (");
}
}
String partSql = " select top " + topSize + " ";
if (sqlToyConfig.isHasWith()) {
SqlWithAnalysis sqlWith = new SqlWithAnalysis(innerSql);
sql.append(sqlWith.getWithSql());
innerSql = sqlWith.getRejectWithSql();
}
boolean hasUnion = false;
if (sqlToyConfig.isHasUnion()) {
hasUnion = SqlUtil.hasUnion(innerSql, false);
}
// 给原始sql标记上特殊的开始和结尾，便于sql拦截器快速定位到原始sql并进行条件补充
innerSql = SqlUtilsExt.markOriginalSql(innerSql);
if (hasUnion) {
sql.append(partSql);
sql.append(" " + SqlToyConstants.INTERMEDIATE_TABLE + ".* from (");
sql.append(innerSql);
sql.append(") as " + SqlToyConstants.INTERMEDIATE_TABLE + " ");
} else {
sql.append(innerSql.replaceFirst("(?i)select ", partSql));
}
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.top : OperateType.singleTable, queryParam,
extend.entityClass, dbType);
return findBySql(sqlToyContext, sqlToyConfig, queryParam.getSql(), queryParam.getParamsValue(), extend,
decryptHandler, conn, null, dbType, dialect, fetchSize, maxRows);
}
/*
* (non-Javadoc)
*
* @see org.sagacity.sqltoy.dialect.Dialect#findBySql(org.sagacity.
* sqltoy.config.model.SqlToyConfig, java.lang.String[], java.lang.Object[],
* java.lang.reflect.Type, org.sagacity.sqltoy.callback.RowCallbackHandler,
* java.sql.Connection)
*/
@Override
public QueryResult findBySql(final SqlToyContext sqlToyContext, final SqlToyConfig sqlToyConfig, final String sql,
final Object[] paramsValue, final 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 = SqlServerDialectUtils.lockSql(sql, null, lockMode);
return DialectUtils.findBySql(sqlToyContext, sqlToyConfig, realSql, paramsValue, queryExecutorExtend,
decryptHandler, conn, dbType, 0, fetchSize, maxRows);
}
/*
* (non-Javadoc)
*
* @see org.sagacity.sqltoy.dialect.Dialect#getCountBySql(java.lang .String,
* java.lang.String[], java.lang.Object[], java.sql.Connection)
*/
@Override
public Long getCountBySql(final SqlToyContext sqlToyContext, final SqlToyConfig sqlToyConfig, final String sql,
final Object[] paramsValue, final boolean isLastSql, final 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#saveOrUpdate(org.sagacity.sqltoy.
* SqlToyContext, java.io.Serializable, java.sql.Connection)
*/
@Override
public Long saveOrUpdate(SqlToyContext sqlToyContext, Serializable entity, final 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, java.sql.Connection)
*/
@Override
public Long saveOrUpdateAll(SqlToyContext sqlToyContext, List entities, final int batchSize,
final ReflectPropsHandler reflectPropsHandler, final String[] forceUpdateFields, Connection conn,
final Integer dbType, final String dialect, final Boolean autoCommit, final String tableName)
throws Exception {
// 为什么不共用oracle等merge方法,因为sqlserver不支持timestamp类型的数据进行插入和修改赋值
return SqlServerDialectUtils.saveOrUpdateAll(sqlToyContext, entities, batchSize, reflectPropsHandler,
forceUpdateFields, conn, dbType, autoCommit, tableName);
}
/*
* (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());
// sqlserver merge into must end with ";" charater
// 返回变更的记录数量
return DialectUtils.saveAllIgnoreExist(sqlToyContext, entities, batchSize, entityMeta,
new GenerateSqlHandler() {
@Override
public String generateSql(EntityMeta entityMeta, String[] forceUpdateFields) {
String sql = SqlServerDialectUtils.getSaveIgnoreExistSql(sqlToyContext.getUnifyFieldsHandler(),
dbType, entityMeta, entityMeta.getIdStrategy(), tableName, "isnull", "@mySeqVariable",
false);
// 2012 版本
if (entityMeta.getIdStrategy() != null
&& entityMeta.getIdStrategy().equals(PKStrategy.SEQUENCE)) {
sql = "DECLARE @mySeqVariable as numeric(20)=NEXT VALUE FOR " + entityMeta.getSequence()
+ " " + sql;
}
return sql.concat(";");
}
}, reflectPropsHandler, conn, dbType, autoCommit);
}
/*
* (non-Javadoc)
*
* @see org.sagacity.sqltoy.dialect.Dialect#load(java.io.Serializable,
* java.util.List, java.sql.Connection)
*/
@Override
public Serializable load(final 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 = SqlServerDialectUtils.lockSql(loadSql, entityMeta.getSchemaTable(tableName, dbType), lockMode);
return (Serializable) DialectUtils.load(sqlToyContext, sqlToyConfig, loadSql, entityMeta, entity, onlySubTables,
cascadeTypes, conn, dbType);
}
/*
* (non-Javadoc)
*
* @see org.sagacity.sqltoy.dialect.Dialect#loadAll(java.util.List,
* java.util.List, java.sql.Connection)
*/
@Override
public List loadAll(final 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, null, fetchSize, maxRows);
}
/*
* (non-Javadoc)
*
* @see org.sagacity.sqltoy.dialect.Dialect#save(org.sagacity.sqltoy.
* SqlToyContext , java.io.Serializable, java.util.List, 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 SqlServerDialectUtils.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 SqlServerDialectUtils.saveAll(sqlToyContext, entities, reflectPropsHandler, conn, dbType, autoCommit,
tableName);
}
/*
* (non-Javadoc)
*
* @see org.sagacity.sqltoy.dialect.Dialect#update(org.sagacity.sqltoy.
* SqlToyContext , java.io.Serializable, java.lang.String[],
* java.sql.Connection)
*/
@Override
public Long update(SqlToyContext sqlToyContext, Serializable entity, String[] forceUpdateFields,
final boolean cascade, final Class[] forceCascadeClasses,
final HashMap subTableForceUpdateProps, Connection conn, final Integer dbType,
final String dialect, final String tableName) throws Exception {
return SqlServerDialectUtils.update(sqlToyContext, entity, forceUpdateFields, cascade, forceCascadeClasses,
subTableForceUpdateProps, conn, dbType, tableName);
}
/*
* (non-Javadoc)
*
* @see org.sagacity.sqltoy.dialect.Dialect#updateAll(org.sagacity.sqltoy.
* SqlToyContext, java.util.List,
* org.sagacity.sqltoy.callback.ReflectPropsHandler, java.sql.Connection)
*/
@Override
public Long updateAll(SqlToyContext sqlToyContext, List entities, final int batchSize,
final String[] uniqueFields, final 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);
}
/*
* (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,
* org.sagacity.sqltoy.model.QueryExecutor,
* org.sagacity.sqltoy.callback.UpdateRowHandler, java.sql.Connection)
*/
@Override
public QueryResult updateFetch(SqlToyContext sqlToyContext, SqlToyConfig sqlToyConfig, String sql,
Object[] paramsValue, UpdateRowHandler updateRowHandler, Connection conn, final Integer dbType,
final String dialect, final LockMode lockMode, final int fetchSize, final int maxRows) throws Exception {
String realSql = SqlServerDialectUtils.lockSql(sql, null, (lockMode == null) ? LockMode.UPGRADE : lockMode);
return DialectUtils.updateFetchBySql(sqlToyContext, sqlToyConfig, realSql, paramsValue, 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 {
// 2012版本存储过程查询需要增加set nocount on 否则不返回结果集
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 {
List tableColumns = SqlServerDialectUtils.getTableColumns(catalog, schema, tableName, conn, dbType,
dialect);
// 获取主键信息
Map pkMap = DefaultDialectUtils.getTablePrimaryKeys(catalog, schema, tableName, conn,
dbType, dialect);
if (pkMap == null || pkMap.isEmpty()) {
return tableColumns;
}
ColumnMeta mapMeta;
for (ColumnMeta colMeta : tableColumns) {
mapMeta = pkMap.get(colMeta.getColName());
if (mapMeta != null) {
colMeta.setPK(true);
}
}
return tableColumns;
}
@Override
public List getTables(String catalog, String schema, String tableName, Connection conn, Integer dbType,
String dialect) throws Exception {
return SqlServerDialectUtils.getTables(catalog, schema,
(tableName != null && "%".equals(tableName)) ? null : tableName, conn, dbType, dialect);
}
}
© 2015 - 2024 Weber Informatics LLC | Privacy Policy