mybaties-plus使用@SelectProvider实现动态SQL

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);