package cn.iocoder.yudao.framework.mybatis.core.util;
|
|
import cn.hutool.core.collection.CollUtil;
|
import cn.hutool.core.lang.func.Func1;
|
import cn.hutool.core.lang.func.LambdaUtil;
|
import cn.hutool.core.util.StrUtil;
|
import cn.iocoder.yudao.framework.common.pojo.PageParam;
|
import cn.iocoder.yudao.framework.common.pojo.SortingField;
|
import cn.iocoder.yudao.framework.mybatis.core.enums.DbTypeEnum;
|
import com.baomidou.mybatisplus.annotation.DbType;
|
import com.baomidou.mybatisplus.core.conditions.Wrapper;
|
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
|
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
|
import com.baomidou.mybatisplus.core.metadata.OrderItem;
|
import com.baomidou.mybatisplus.core.toolkit.StringPool;
|
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
|
import com.baomidou.mybatisplus.extension.plugins.inner.InnerInterceptor;
|
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
|
import net.sf.jsqlparser.expression.Alias;
|
import net.sf.jsqlparser.schema.Column;
|
import net.sf.jsqlparser.schema.Table;
|
|
import java.util.ArrayList;
|
import java.util.Collection;
|
import java.util.List;
|
import java.util.regex.Pattern;
|
|
/**
|
* MyBatis 工具类
|
*/
|
public class MyBatisUtils {
|
|
private static final String MYSQL_ESCAPE_CHARACTER = "`";
|
|
private static final Pattern SAFE_COLUMN_NAME_PATTERN = Pattern.compile("^[a-zA-Z0-9_]+(\\.[a-zA-Z0-9_]+)*$");
|
|
private static final String FIND_IN_SET_VALUE_PLACEHOLDER = "#{value}";
|
|
private static final String FIND_IN_SET_COLUMN_PLACEHOLDER = "#{column}";
|
|
public static <T> Page<T> buildPage(PageParam pageParam) {
|
return buildPage(pageParam, null);
|
}
|
|
public static <T> Page<T> buildPage(PageParam pageParam, Collection<SortingField> sortingFields) {
|
// 页码 + 数量
|
Page<T> page = new Page<>(pageParam.getPageNo(), pageParam.getPageSize());
|
page.setOptimizeJoinOfCountSql(false); // 关联 issue：https://gitee.com/zhijiantianya/yudao-cloud/issues/ID2QLL
|
// 排序字段
|
if (CollUtil.isNotEmpty(sortingFields)) {
|
for (SortingField sortingField : sortingFields) {
|
String columnName = buildSafeOrderColumn(sortingField.getField());
|
if (columnName == null) {
|
continue;
|
}
|
page.addOrder(new OrderItem().setAsc(isAscOrder(sortingField.getOrder())).setColumn(columnName));
|
}
|
}
|
return page;
|
}
|
|
@SuppressWarnings("PatternVariableCanBeUsed")
|
public static <T> void addOrder(Wrapper<T> wrapper, Collection<SortingField> sortingFields) {
|
if (CollUtil.isEmpty(sortingFields)) {
|
return;
|
}
|
if (wrapper instanceof QueryWrapper<T>) {
|
QueryWrapper<T> query = (QueryWrapper<T>) wrapper;
|
for (SortingField sortingField : sortingFields) {
|
String columnName = buildSafeOrderColumn(sortingField.getField());
|
if (columnName == null) {
|
continue;
|
}
|
query.orderBy(true, isAscOrder(sortingField.getOrder()), columnName);
|
}
|
} else if (wrapper instanceof LambdaQueryWrapper<T>) {
|
// LambdaQueryWrapper 不直接支持字符串字段排序，使用 last 方法拼接 ORDER BY
|
LambdaQueryWrapper<T> lambdaQuery = (LambdaQueryWrapper<T>) wrapper;
|
StringBuilder orderBy = new StringBuilder();
|
for (SortingField sortingField : sortingFields) {
|
String columnName = buildSafeOrderColumn(sortingField.getField());
|
if (columnName == null) {
|
continue;
|
}
|
if (StrUtil.isNotEmpty(orderBy)) {
|
orderBy.append(", ");
|
}
|
orderBy.append(columnName).append(" ").append(getOrderDirection(sortingField.getOrder()));
|
}
|
if (StrUtil.isNotEmpty(orderBy)) {
|
lambdaQuery.last("ORDER BY " + orderBy);
|
}
|
// 另外个思路：https://blog.csdn.net/m0_59084856/article/details/138450913
|
} else {
|
throw new IllegalArgumentException("Unsupported wrapper type: " + wrapper.getClass().getName());
|
}
|
|
}
|
|
public static boolean isAscOrder(String order) {
|
return SortingField.ORDER_ASC.equals(order);
|
}
|
|
public static String getOrderDirection(String order) {
|
return isAscOrder(order) ? "ASC" : "DESC";
|
}
|
|
private static String buildSafeOrderColumn(String field) {
|
String columnName = StrUtil.toUnderlineCase(field);
|
if (StrUtil.isEmpty(columnName) || !SAFE_COLUMN_NAME_PATTERN.matcher(columnName).matches()) {
|
return null;
|
}
|
return columnName;
|
}
|
|
/**
|
* 将拦截器添加到链中
|
* 由于 MybatisPlusInterceptor 不支持添加拦截器，所以只能全量设置
|
*
|
* @param interceptor 链
|
* @param inner 拦截器
|
* @param index 位置
|
*/
|
public static void addInterceptor(MybatisPlusInterceptor interceptor, InnerInterceptor inner, int index) {
|
List<InnerInterceptor> inners = new ArrayList<>(interceptor.getInterceptors());
|
inners.add(index, inner);
|
interceptor.setInterceptors(inners);
|
}
|
|
/**
|
* 获得 Table 对应的表名
|
* <p>
|
* 兼容 MySQL 转义表名 `t_xxx`
|
*
|
* @param table 表
|
* @return 去除转移字符后的表名
|
*/
|
public static String getTableName(Table table) {
|
String tableName = table.getName();
|
if (tableName.startsWith(MYSQL_ESCAPE_CHARACTER) && tableName.endsWith(MYSQL_ESCAPE_CHARACTER)) {
|
tableName = tableName.substring(1, tableName.length() - 1);
|
}
|
return tableName;
|
}
|
|
/**
|
* 构建 Column 对象
|
*
|
* @param tableName 表名
|
* @param tableAlias 别名
|
* @param column 字段名
|
* @return Column 对象
|
*/
|
public static Column buildColumn(String tableName, Alias tableAlias, String column) {
|
if (tableAlias != null) {
|
tableName = tableAlias.getName();
|
}
|
return new Column(tableName + StringPool.DOT + column);
|
}
|
|
/**
|
* 跨数据库的 find_in_set 实现
|
*
|
* @param columnName 字段名称
|
* @return sql
|
*/
|
public static String findInSet(String columnName) {
|
return findInSet(columnName, 0);
|
}
|
|
/**
|
* 跨数据库的 find_in_set 实现，适用于同一个 apply 语句中有多个参数的场景
|
*
|
* @param columnName 字段名称
|
* @param paramIndex apply 参数序号
|
* @return sql
|
*/
|
public static String findInSetWithParamIndex(String columnName, int paramIndex) {
|
return findInSet(columnName, paramIndex);
|
}
|
|
private static String findInSet(String columnName, int paramIndex) {
|
DbType dbType = JdbcUtils.getDbType();
|
return findInSet(dbType, columnName, paramIndex);
|
}
|
|
static String findInSet(DbType dbType, String columnName, int paramIndex) {
|
if (!isSafeColumnName(columnName)) {
|
throw new IllegalArgumentException("Invalid column name: " + columnName);
|
}
|
if (paramIndex < 0) {
|
throw new IllegalArgumentException("Invalid param index: " + paramIndex);
|
}
|
return DbTypeEnum.getFindInSetTemplate(dbType)
|
.replace(FIND_IN_SET_COLUMN_PLACEHOLDER, columnName)
|
.replace(FIND_IN_SET_VALUE_PLACEHOLDER, "{" + paramIndex + "}");
|
}
|
|
private static boolean isSafeColumnName(String columnName) {
|
return StrUtil.isNotEmpty(columnName) && SAFE_COLUMN_NAME_PATTERN.matcher(columnName).matches();
|
}
|
|
/**
|
* 将驼峰命名转换为下划线命名
|
*
|
* 使用场景：
|
* 1. <a href="https://gitee.com/zhijiantianya/ruoyi-vue-pro/pulls/1357/files">fix:修复"商品统计聚合函数的别名与排序字段不符"导致的 SQL 异常</a>
|
*
|
* @param func 字段名函数(驼峰命名)
|
* @return 字段名(下划线命名)
|
*/
|
public static <T> String toUnderlineCase(Func1<T, ?> func) {
|
String fieldName = LambdaUtil.getFieldName(func);
|
return StrUtil.toUnderlineCase(fieldName);
|
}
|
|
}
|
