解析存储在数据库中的动态SQL并执行

创建table

CREATE TABLE `mp_sql`  (
  `id` int(0) UNSIGNED NOT NULL AUTO_INCREMENT,
  `type` tinyint(1) NOT NULL DEFAULT 4 COMMENT 'sql类型 1-增 2-删 3-改 4-查',
  `ope_table` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '操作的表名',
  `execute_sql` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '执行的sql',
  `created_at` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间',
  `updated_at` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '修改时间',
  `name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT 'sql名称',
  `description` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT 'sql说明',
  `table_operate` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT 'SQL唯一标识，表名+操作名',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `table_operate`(`table_operate`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = 'SQL存储表' ROW_FORMAT = Dynamic;

定义工具类

package cn.xingqutuan.utils;

import cn.xingqutuan.mapper.MpSqlMapper;
import com.baomidou.mybatisplus.core.MybatisConfiguration;
import org.apache.ibatis.mapping.*;
import org.apache.ibatis.scripting.LanguageDriver;
import org.apache.ibatis.scripting.xmltags.XMLLanguageDriver;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.defaults.DefaultSqlSession;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @author : Abo
 * @date : 2021/10/28 13:33
 */
@Component
public class SqlSourceUtil {
    @Autowired
    private SqlSessionFactory sqlSessionFactory;
    @Autowired
    private MpSqlMapper mpSqlMapper;

    /**
     * 拼接sql为<script>标签SQL
     *
     * @param interfaceName 接口方法名
     * @param tableName     操作的表名
     * @return 拼接后的sql
     */
    public String getSql(String interfaceName, String tableName) {
        return String.format("<script>%s</script>", mpSqlMapper.selectByTableAndName(interfaceName, tableName));
    }

    /**
     * 解析sql为最终执行的SQL语句
     *
     * @param sqlString  携带<script>的sql
     * @param parameters 动态参数
     * @return 执行的SQL语句
     */
    public String sqlSource(String sqlString, Map<String, Object> parameters) {
        MybatisConfiguration configuration = (MybatisConfiguration) sqlSessionFactory.getConfiguration();
        LanguageDriver xmlLanguageDriver = new XMLLanguageDriver();
        final StringBuilder sqlXml = new StringBuilder();
        String[] strings = new String[]{sqlString};
        for (String fragment : strings) {
            sqlXml.append(fragment);
            sqlXml.append(" ");
        }
        SqlSource sqlSource = xmlLanguageDriver.createSqlSource(configuration, sqlXml.toString().trim(), Map.class);
        BoundSql boundSql = sqlSource.getBoundSql(parameters);
        final StringBuilder sql = new StringBuilder(boundSql.getSql());
        // FIXME ： 替换参数暂时存在问题
        if (parameters != null && !parameters.isEmpty() && sql.indexOf("?") != -1) {
            for (Object parameter : parameters.values()) {
                if (sql.indexOf("?") != -1) {
                    int start = sql.indexOf("?");
                    sql.replace(start, start + 1, parameter.toString());
                }
            }
        }
        return sql.toString();
    }

    /**
     * 查询sql，直接返回sql查询列表执行结果
     *
     * @param sqlString  携带script标签sql
     * @param apiId     缓存标识
     * @param param     动态参数
     * @return sql执行结果
     */
    public List<Map<String, Object>> query(String sqlString, Long apiId, Map<String, Object> param) {
        String mastpId = "sql_query." + apiId;
        parseAndCacheSql(sqlString, param, mastpId);
        DefaultSqlSession defaultSqlSession = (DefaultSqlSession) sqlSessionFactory.openSession();
        return defaultSqlSession.selectList(mastpId, param);
    }

    /**
     * 查询sql，返回指定对象
     *
     * @param sqlString  携带script标签sql
     * @param apiId      缓存标识
     * @param param      动态参数
     * @param returnType 返回类型
     * @return 返回指定对象
     */
    public <t> List<t> query(String sqlString, Long apiId, Map<String, Object> param, Class<t> returnType) {
        String mastpId = "sql_query." + apiId;
        parseAndCacheSql(sqlString, param, mastpId);
        DefaultSqlSession defaultSqlSession = (DefaultSqlSession) sqlSessionFactory.openSession();
        List<Map<String, Object>> results = defaultSqlSession.selectList(mastpId, param);
        return CommonUtils.listMap2listObj(results, returnType);
    }

    /** 执行新增SQL */
    public void insert(String sqlString, Long apiId, Map<String, Object> param) {
        String mastpId = "sql_insert." + apiId;
        parseAndCacheSql(sqlString, param, mastpId);
        DefaultSqlSession defaultSqlSession = (DefaultSqlSession) sqlSessionFactory.openSession();
        defaultSqlSession.insert(mastpId, param);
    }

    /** 执行删除SQL */
    public void delete(String sqlString, Long apiId, Map<String, Object> param) {
        String mastpId = "sql_delete." + apiId;
        parseAndCacheSql(sqlString, param, mastpId);
        DefaultSqlSession defaultSqlSession = (DefaultSqlSession) sqlSessionFactory.openSession();
        defaultSqlSession.delete(mastpId, param);
    }

    /** 执行更新SQL */
    public void update(String sqlString, Long apiId, Map<String, Object> param) {
        String mastpId = "sql_update." + apiId;
        parseAndCacheSql(sqlString, param, mastpId);
        DefaultSqlSession defaultSqlSession = (DefaultSqlSession) sqlSessionFactory.openSession();
        defaultSqlSession.update(mastpId, param);
    }

    /** 解析SQL并加入缓存 */
    private void parseAndCacheSql(String sqlString, Map<String, Object> param, String mastpId) {
        MybatisConfiguration configuration = (MybatisConfiguration) sqlSessionFactory.getConfiguration();
        if (!configuration.hasStatement(mastpId)) {
            LanguageDriver xmlLanguageDriver = new XMLLanguageDriver();
            final StringBuilder sql = new StringBuilder();
            String[] strings = new String[]{sqlString};
            for (String fragment : strings) {
                sql.append(fragment);
                sql.append(" ");
            }
            SqlSource sqlSource = xmlLanguageDriver.createSqlSource(configuration, sql.toString().trim(), Map.class);

            BoundSql boundSql = sqlSource.getBoundSql(param);
            MappedStatement ms = new MappedStatement.Builder(configuration, mastpId, sqlSource, SqlCommandType.SELECT)
                    .resultMaps(new ArrayList<>() {
                        private static final long serialVersionUID = -4426189564037959447L;
                        {
                            add(new ResultMap.Builder(configuration, "defaultResultMap", Map.class, new ArrayList<>(0))
                                    .build());
                        }
                    }).build();
            // 缓存
            configuration.addMappedStatement(ms);
        }
    }

}

自定义返回值类型转换工具

/**
     * 将map转成指定对象
     * @param results 结果集参数
     * @param clazz 返回值类型
     * @param <T> 返回值类型
     * @return 指定对象
     */
    public static <T> T map2obj(Map<String, Object> results, Class<T> clazz) {
        try {
            T t = clazz.getDeclaredConstructor().newInstance();
            if (results.isEmpty()) return t;
            return JSON.parseObject(JSON.toJSONString(results), clazz);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /***
     * 将集合Map转成集合对象
     * @param results 结果集
     * @param clazz 返回值泛型类型
     * @param <T> 泛型类型
     * @return 指定集合对象
     */
    public static <T> List<T> listMap2listObj(List<Map<String, Object>> results, Class<T> clazz) {
        List<T> targetList = new ArrayList<>();
        if (results.isEmpty()) return targetList;
        for (Map<String, Object> result : results) {
            String jsonString = JSON.toJSONString(result);
            targetList.add(JSON.parseObject(jsonString, clazz));
        }
        return targetList;
    }