mybaties-plus使用@SelectProvider实现动态SQL
新建DynamicSqlProvider.java类
package com.muphy.mapper;
import org.apache.ibatis.jdbc.SQL;
import java.util.*;
public class DynamicSqlProvider {
public String buildSelectSql(Map<String, Object> params) {
// 注意:这里的参数名需要与Mapper接口中的@Param注解匹配
String table = (String) params.get("table");
List<String> columns = (List<String>) params.get("columns");
Map<String, Object> queryParams = (Map<String, Object>) params.get("params");
List<String> orders = (List<String>) params.get("orders");
// 使用StringBuilder或SQL类构建SQL查询
// 这里为了简化,直接使用StringBuilder
SQL sql = new SQL();
if (columns == null || columns.isEmpty()) {
sql.SELECT("*");
} else {
sql.SELECT(String.join(",", columns));
}
sql.FROM(table);
if (queryParams != null && !queryParams.isEmpty()) {
StringBuilder conditionBuilder = new StringBuilder();
for (Map.Entry<String, Object> entry : queryParams.entrySet()) {
String key = entry.getKey();
Object value = entry.getValue();
if (conditionBuilder.length() > 0) {
conditionBuilder.append(" AND ");
}
if (value == null) {
conditionBuilder.append(" is null or ").append(key).append(" = ''");
} else {
// 这里假设我们使用 = 来进行比较,你可以根据实际需要调整
conditionBuilder.append(key).append(" = #{params.").append(key).append("}");
}
}
sql.WHERE(conditionBuilder.toString());
}
if (orders != null && !orders.isEmpty()) {
sql.ORDER_BY(String.join(",", orders));
}
return sql.toString();
}
}
在mapper类中扩展方法
public interface TestMapper extends BaseMapper<TestEntity> {
default List<Map<String, Object>> selectByDynamicSql(String table) {
return selectByDynamicSql(table, null, null, null);
}
default List<Map<String, Object>> selectByDynamicSql(String table, List<String> columns) {
return selectByDynamicSql(table, columns, null, null);
}
default List<Map<String, Object>> selectByDynamicSql(String table, Map<String, Object> queryParams) {
return selectByDynamicSql(table, null, queryParams, null);
}
default List<Map<String, Object>> selectByDynamicSql(String table, Map<String, Object> queryParams, List<String> orders) {
return selectByDynamicSql(table, null, queryParams, orders);
}
default List<Map<String, Object>> selectByDynamicSql(String table, List<String> columns, Map<String, Object> queryParams) {
// return selectBy(table, columns, queryParams, null);
return selectByDynamicSql(table, columns, queryParams, null);
}
default List<Map<String, Object>> selectByDynamicSql(String table, List<String> columns, Map<String, Object> queryParams, List<String> orders) {
Map<String, Object> params = new HashMap<>();
params.put("table", table);
params.put("columns", columns);
params.put("params", queryParams);
params.put("orders", orders);
return selectByDynamicSql(params);
}
@SelectProvider(type = DynamicSqlProvider.class, method = "buildSelectSql")
List<Map<String, Object>> selectByDynamicSql(Map<String, Object> params);
}
使用
Map<String, Object> params = new HashMap<>();
params.put("table", "users");
params.put("columns", Arrays.asList("id", "name", "email"));
Map<String, Object> where = new HashMap<>();
where.put("id", 1);
where.put("name", "John Doe");
params.put("params", where);
params.put("orders", Arrays.asList("id DESC"));
List<Map<String, Object>> maps = baseMapper.selectByDynamicSql(themeTable.getTableName(), columns, queryParams);
标签:mybaties,selectByDynamicSql,SQL,List,params,table,plus,null,columns
From: https://www.cnblogs.com/muphy/p/18405014