tk.mybatis.mapper.mapperhelper.SqlHelper Maven / Gradle / Ivy
/*
* The MIT License (MIT)
*
* Copyright (c) 2014-2017 [email protected]
*
* Permission is hereby granted, free of charge, to any person obtaining a copy
* of this software and associated documentation files (the "Software"), to deal
* in the Software without restriction, including without limitation the rights
* to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
* copies of the Software, and to permit persons to whom the Software is
* furnished to do so, subject to the following conditions:
*
* The above copyright notice and this permission notice shall be included in
* all copies or substantial portions of the Software.
*
* THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
* IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
* FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
* AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
* LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
* OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
* THE SOFTWARE.
*/
package tk.mybatis.mapper.mapperhelper;
import tk.mybatis.mapper.annotation.Version;
import tk.mybatis.mapper.entity.EntityColumn;
import tk.mybatis.mapper.entity.IDynamicTableName;
import tk.mybatis.mapper.util.StringUtil;
import tk.mybatis.mapper.version.VersionException;
import java.util.Set;
/**
* 拼常用SQL的工具类
*
* @author liuzh
* @since 2015-11-03 22:40
*/
public class SqlHelper {
/**
* 获取表名 - 支持动态表名
*
* @param entityClass
* @param tableName
* @return
*/
public static String getDynamicTableName(Class entityClass, String tableName) {
if (IDynamicTableName.class.isAssignableFrom(entityClass)) {
StringBuilder sql = new StringBuilder();
sql.append("");
sql.append("");
sql.append("${dynamicTableName}\n");
sql.append(" ");
//不支持指定列的时候查询全部列
sql.append("");
sql.append(tableName);
sql.append(" ");
sql.append(" ");
return sql.toString();
} else {
return tableName;
}
}
/**
* 获取表名 - 支持动态表名，该方法用于多个入参时，通过parameterName指定入参中实体类的@Param的注解值
*
* @param entityClass
* @param tableName
* @param parameterName
* @return
*/
public static String getDynamicTableName(Class entityClass, String tableName, String parameterName) {
if (IDynamicTableName.class.isAssignableFrom(entityClass)) {
if (StringUtil.isNotEmpty(parameterName)) {
StringBuilder sql = new StringBuilder();
sql.append("");
sql.append("");
sql.append("${" + parameterName + ".dynamicTableName}");
sql.append(" ");
//不支持指定列的时候查询全部列
sql.append("");
sql.append(tableName);
sql.append(" ");
sql.append(" ");
return sql.toString();
} else {
return getDynamicTableName(entityClass, tableName);
}
} else {
return tableName;
}
}
/**
* ");
return sql.toString();
}
/**
* ");
sql.append(contents);
sql.append(" ");
return sql.toString();
}
/**
* 如果_cache == null
*
* @param column
* @return
*/
public static String getIfCacheIsNull(EntityColumn column, String contents) {
StringBuilder sql = new StringBuilder();
sql.append("");
sql.append(contents);
sql.append(" ");
return sql.toString();
}
/**
* 判断自动!=null的条件结构
*
* @param column
* @param contents
* @param empty
* @return
*/
public static String getIfNotNull(EntityColumn column, String contents, boolean empty) {
return getIfNotNull(null, column, contents, empty);
}
/**
* 判断自动==null的条件结构
*
* @param column
* @param contents
* @param empty
* @return
*/
public static String getIfIsNull(EntityColumn column, String contents, boolean empty) {
return getIfIsNull(null, column, contents, empty);
}
/**
* 判断自动!=null的条件结构
*
* @param entityName
* @param column
* @param contents
* @param empty
* @return
*/
public static String getIfNotNull(String entityName, EntityColumn column, String contents, boolean empty) {
StringBuilder sql = new StringBuilder();
sql.append("");
sql.append(contents);
sql.append(" ");
return sql.toString();
}
/**
* 判断自动==null的条件结构
*
* @param entityName
* @param column
* @param contents
* @param empty
* @return
*/
public static String getIfIsNull(String entityName, EntityColumn column, String contents, boolean empty) {
StringBuilder sql = new StringBuilder();
sql.append("");
sql.append(contents);
sql.append(" ");
return sql.toString();
}
/**
* 获取所有查询列，如id,name,code...
*
* @param entityClass
* @return
*/
public static String getAllColumns(Class entityClass) {
Set columnSet = EntityHelper.getColumns(entityClass);
StringBuilder sql = new StringBuilder();
for (EntityColumn entityColumn : columnSet) {
sql.append(entityColumn.getColumn()).append(",");
}
return sql.substring(0, sql.length() - 1);
}
/**
* select xxx,xxx...
*
* @param entityClass
* @return
*/
public static String selectAllColumns(Class entityClass) {
StringBuilder sql = new StringBuilder();
sql.append("SELECT ");
sql.append(getAllColumns(entityClass));
sql.append(" ");
return sql.toString();
}
/**
* select count(x)
*
* @param entityClass
* @return
*/
public static String selectCount(Class entityClass) {
StringBuilder sql = new StringBuilder();
sql.append("SELECT ");
Set pkColumns = EntityHelper.getPKColumns(entityClass);
if (pkColumns.size() == 1) {
sql.append("COUNT(").append(pkColumns.iterator().next().getColumn()).append(") ");
} else {
sql.append("COUNT(*) ");
}
return sql.toString();
}
/**
* select case when count(x) > 0 then 1 else 0 end
*
* @param entityClass
* @return
*/
public static String selectCountExists(Class entityClass) {
StringBuilder sql = new StringBuilder();
sql.append("SELECT CASE WHEN ");
Set pkColumns = EntityHelper.getPKColumns(entityClass);
if (pkColumns.size() == 1) {
sql.append("COUNT(").append(pkColumns.iterator().next().getColumn()).append(") ");
} else {
sql.append("COUNT(*) ");
}
sql.append(" > 0 THEN 1 ELSE 0 END AS result ");
return sql.toString();
}
/**
* from tableName - 动态表名
*
* @param entityClass
* @param defaultTableName
* @return
*/
public static String fromTable(Class entityClass, String defaultTableName) {
StringBuilder sql = new StringBuilder();
sql.append(" FROM ");
sql.append(getDynamicTableName(entityClass, defaultTableName));
sql.append(" ");
return sql.toString();
}
/**
* update tableName - 动态表名
*
* @param entityClass
* @param defaultTableName
* @return
*/
public static String updateTable(Class entityClass, String defaultTableName) {
return updateTable(entityClass, defaultTableName, null);
}
/**
* update tableName - 动态表名
*
* @param entityClass
* @param defaultTableName 默认表名
* @param entityName 别名
* @return
*/
public static String updateTable(Class entityClass, String defaultTableName, String entityName) {
StringBuilder sql = new StringBuilder();
sql.append("UPDATE ");
sql.append(getDynamicTableName(entityClass, defaultTableName, entityName));
sql.append(" ");
return sql.toString();
}
/**
* delete tableName - 动态表名
*
* @param entityClass
* @param defaultTableName
* @return
*/
public static String deleteFromTable(Class entityClass, String defaultTableName) {
StringBuilder sql = new StringBuilder();
sql.append("DELETE FROM ");
sql.append(getDynamicTableName(entityClass, defaultTableName));
sql.append(" ");
return sql.toString();
}
/**
* insert into tableName - 动态表名
*
* @param entityClass
* @param defaultTableName
* @return
*/
public static String insertIntoTable(Class entityClass, String defaultTableName) {
StringBuilder sql = new StringBuilder();
sql.append("INSERT INTO ");
sql.append(getDynamicTableName(entityClass, defaultTableName));
sql.append(" ");
return sql.toString();
}
/**
* insert table()列
*
* @param entityClass
* @param skipId 是否从列中忽略id类型
* @param notNull 是否判断!=null
* @param notEmpty 是否判断String类型!=''
* @return
*/
public static String insertColumns(Class entityClass, boolean skipId, boolean notNull, boolean notEmpty) {
StringBuilder sql = new StringBuilder();
sql.append("");
//获取全部列
Set columnSet = EntityHelper.getColumns(entityClass);
//当某个列有主键策略时，不需要考虑他的属性是否为空，因为如果为空，一定会根据主键策略给他生成一个值
for (EntityColumn column : columnSet) {
if (!column.isInsertable()) {
continue;
}
if (skipId && column.isId()) {
continue;
}
if (notNull) {
sql.append(SqlHelper.getIfNotNull(column, column.getColumn() + ",", notEmpty));
} else {
sql.append(column.getColumn() + ",");
}
}
sql.append(" ");
return sql.toString();
}
/**
* insert-values()列
*
* @param entityClass
* @param skipId 是否从列中忽略id类型
* @param notNull 是否判断!=null
* @param notEmpty 是否判断String类型!=''
* @return
*/
public static String insertValuesColumns(Class entityClass, boolean skipId, boolean notNull, boolean notEmpty) {
StringBuilder sql = new StringBuilder();
sql.append("");
//获取全部列
Set columnSet = EntityHelper.getColumns(entityClass);
//当某个列有主键策略时，不需要考虑他的属性是否为空，因为如果为空，一定会根据主键策略给他生成一个值
for (EntityColumn column : columnSet) {
if (!column.isInsertable()) {
continue;
}
if (skipId && column.isId()) {
continue;
}
if (notNull) {
sql.append(SqlHelper.getIfNotNull(column, column.getColumnHolder() + ",", notEmpty));
} else {
sql.append(column.getColumnHolder() + ",");
}
}
sql.append(" ");
return sql.toString();
}
/**
* update set列
*
* @param entityClass
* @param entityName 实体映射名
* @param notNull 是否判断!=null
* @param notEmpty 是否判断String类型!=''
* @return
*/
public static String updateSetColumns(Class entityClass, String entityName, boolean notNull, boolean notEmpty) {
StringBuilder sql = new StringBuilder();
sql.append("");
//获取全部列
Set columnSet = EntityHelper.getColumns(entityClass);
//对乐观锁的支持
EntityColumn versionColumn = null;
//当某个列有主键策略时，不需要考虑他的属性是否为空，因为如果为空，一定会根据主键策略给他生成一个值
for (EntityColumn column : columnSet) {
if (column.getEntityField().isAnnotationPresent(Version.class)) {
if (versionColumn != null) {
throw new VersionException(entityClass.getCanonicalName() + " 中包含多个带有 @Version 注解的字段，一个类中只能存在一个带有 @Version 注解的字段!");
}
versionColumn = column;
}
if (!column.isId() && column.isUpdatable()) {
if (column == versionColumn) {
Version version = versionColumn.getEntityField().getAnnotation(Version.class);
String versionClass = version.nextVersion().getCanonicalName();
//version = ${@tk.mybatis.mapper.version@nextVersionClass("versionClass", version)}
sql.append(column.getColumn())
.append(" = ${@tk.mybatis.mapper.version.VersionUtil@nextVersion(\"")
.append(versionClass).append("\", ")
.append(column.getProperty()).append(")},");
} else if (notNull) {
sql.append(SqlHelper.getIfNotNull(entityName, column, column.getColumnEqualsHolder(entityName) + ",", notEmpty));
} else {
sql.append(column.getColumnEqualsHolder(entityName) + ",");
}
} else if(column.isId()){
//set id = id,
sql.append(column.getColumn()).append(" = ").append(column.getColumn()).append(",");
}
}
sql.append(" ");
return sql.toString();
}
/**
* 不是所有参数都是 null 的检查
*
* @param parameterName 参数名
* @param columnSet 需要检查的列
* @return
*/
public static String notAllNullParameterCheck(String parameterName, Set columnSet) {
StringBuilder sql = new StringBuilder();
sql.append(" 0){
fields.append(",");
}
fields.append(column.getProperty());
}
sql.append(fields);
sql.append("')\"/>");
return sql.toString();
}
/**
* Example 中包含至少 1 个查询条件
*
* @param parameterName 参数名
* @return
*/
public static String exampleHasAtLeastOneCriteriaCheck(String parameterName) {
StringBuilder sql = new StringBuilder();
sql.append("");
//获取全部列
Set columnSet = EntityHelper.getPKColumns(entityClass);
//当某个列有主键策略时，不需要考虑他的属性是否为空，因为如果为空，一定会根据主键策略给他生成一个值
for (EntityColumn column : columnSet) {
sql.append(" AND " + column.getColumnEqualsHolder());
}
if (useVersion) {
sql.append(whereVersion(entityClass));
}
sql.append(" ");
return sql.toString();
}
/**
* where所有列的条件，会判断是否!=null
*
* @param entityClass
* @param empty
* @return
*/
public static String whereAllIfColumns(Class entityClass, boolean empty) {
return whereAllIfColumns(entityClass, empty, false);
}
/**
* where所有列的条件，会判断是否!=null
*
* @param entityClass
* @param empty
* @param useVersion
* @return
*/
public static String whereAllIfColumns(Class entityClass, boolean empty, boolean useVersion) {
StringBuilder sql = new StringBuilder();
sql.append("");
//获取全部列
Set columnSet = EntityHelper.getColumns(entityClass);
//当某个列有主键策略时，不需要考虑他的属性是否为空，因为如果为空，一定会根据主键策略给他生成一个值
for (EntityColumn column : columnSet) {
if (!useVersion || !column.getEntityField().isAnnotationPresent(Version.class)) {
sql.append(getIfNotNull(column, " AND " + column.getColumnEqualsHolder(), empty));
}
}
if (useVersion) {
sql.append(whereVersion(entityClass));
}
sql.append(" ");
return sql.toString();
}
/**
* 乐观锁字段条件
*
* @param entityClass
* @return
*/
public static String whereVersion(Class entityClass) {
Set columnSet = EntityHelper.getColumns(entityClass);
boolean hasVersion = false;
String result = "";
for (EntityColumn column : columnSet) {
if (column.getEntityField().isAnnotationPresent(Version.class)) {
if (hasVersion) {
throw new VersionException(entityClass.getCanonicalName() + " 中包含多个带有 @Version 注解的字段，一个类中只能存在一个带有 @Version 注解的字段!");
}
hasVersion = true;
result = " AND " + column.getColumnEqualsHolder();
}
}
return result;
}
/**
* 获取默认的orderBy，通过注解设置的
*
* @param entityClass
* @return
*/
public static String orderByDefault(Class entityClass) {
StringBuilder sql = new StringBuilder();
String orderByClause = EntityHelper.getOrderByClause(entityClass);
if (orderByClause.length() > 0) {
sql.append(" ORDER BY ");
sql.append(orderByClause);
}
return sql.toString();
}
/**
* example支持查询指定列时
*
* @return
*/
public static String exampleSelectColumns(Class entityClass) {
StringBuilder sql = new StringBuilder();
sql.append("");
sql.append("");
sql.append("");
sql.append("${selectColumn}");
sql.append(" ");
sql.append(" ");
//不支持指定列的时候查询全部列
sql.append("");
sql.append(getAllColumns(entityClass));
sql.append(" ");
sql.append(" ");
return sql.toString();
}
/**
* example支持查询指定列时
*
* @return
*/
public static String exampleCountColumn(Class entityClass) {
StringBuilder sql = new StringBuilder();
sql.append("");
sql.append("");
sql.append("COUNT(${countColumn})");
sql.append(" ");
sql.append("");
sql.append("COUNT(0)");
sql.append(" ");
sql.append(" ");
//不支持指定列的时候查询全部列
sql.append("");
sql.append(getAllColumns(entityClass));
sql.append(" ");
return sql.toString();
}
/**
* example查询中的orderBy条件，会判断默认orderBy
*
* @return
*/
public static String exampleOrderBy(Class entityClass) {
StringBuilder sql = new StringBuilder();
sql.append("");
sql.append("order by ${orderByClause}");
sql.append(" ");
String orderByClause = EntityHelper.getOrderByClause(entityClass);
if (orderByClause.length() > 0) {
sql.append("");
sql.append("ORDER BY " + orderByClause);
sql.append(" ");
}
return sql.toString();
}
/**
* example 支持 for update
*
* @return
*/
public static String exampleForUpdate() {
StringBuilder sql = new StringBuilder();
sql.append("");
sql.append("FOR UPDATE");
sql.append(" ");
return sql.toString();
}
/**
* example 支持 for update
*
* @return
*/
public static String exampleCheck(Class entityClass) {
StringBuilder sql = new StringBuilder();
sql.append("" +
"\n" +
" \n" +
" \n" +
" ${@tk.mybatis.mapper.util.OGNL@andOr(criteria)}" +
" \n" +
" \n" +
" \n" +
" \n" +
" ${@tk.mybatis.mapper.util.OGNL@andOr(criterion)} ${criterion.condition}\n" +
" \n" +
" \n" +
" ${@tk.mybatis.mapper.util.OGNL@andOr(criterion)} ${criterion.condition} #{criterion.value}\n" +
" \n" +
" \n" +
" ${@tk.mybatis.mapper.util.OGNL@andOr(criterion)} ${criterion.condition} #{criterion.value} and #{criterion.secondValue}\n" +
" \n" +
" \n" +
" ${@tk.mybatis.mapper.util.OGNL@andOr(criterion)} ${criterion.condition}\n" +
" \n" +
" #{listItem}\n" +
" \n" +
" \n" +
" \n" +
" \n" +
" \n" +
" \n" +
" \n" +
" " +
" ";
}
/**
* Example-Update中的where结构，用于多个参数时，Example带@Param("example")注解时
*
* @return
*/
public static String updateByExampleWhereClause() {
return "\n" +
" \n" +
" \n" +
" ${@tk.mybatis.mapper.util.OGNL@andOr(criteria)}" +
" \n" +
" \n" +
" \n" +
" \n" +
" ${@tk.mybatis.mapper.util.OGNL@andOr(criterion)} ${criterion.condition}\n" +
" \n" +
" \n" +
" ${@tk.mybatis.mapper.util.OGNL@andOr(criterion)} ${criterion.condition} #{criterion.value}\n" +
" \n" +
" \n" +
" ${@tk.mybatis.mapper.util.OGNL@andOr(criterion)} ${criterion.condition} #{criterion.value} and #{criterion.secondValue}\n" +
" \n" +
" \n" +
" ${@tk.mybatis.mapper.util.OGNL@andOr(criterion)} ${criterion.condition}\n" +
" \n" +
" #{listItem}\n" +
" \n" +
" \n" +
" \n" +
" \n" +
" \n" +
" \n" +
" \n" +
" ";
}
}
© 2015 - 2025 Weber Informatics LLC | Privacy Policy