Mybatis/Mybatis-plus执行动态sql片段工具类

# sql
	select
            rtc.*,
            cu.user_name AS created_by_name,
            uu.user_name AS updated_by_name
        from ram_tmp_conf rtc
         LEFT JOIN ${db_dms}.base_user_info cu ON cu.user_id = rtc.created_by
         LEFT JOIN ${db_dms}.base_user_info uu ON uu.user_id = rtc.updated_by
        <trim prefix="where" prefixOverrides="and | or">
            <if test="tableName != null and tableName != ''">
                AND rtc.table_name like concat('%',#{tableName},'%')
            </if>
        </trim>

{
  "code": 0,
  "data": {
    "iPage": {
      "countId": "",
      "current": 1,
      "hitCount": false,
      "maxLimit": null,
      "optimizeCountSql": true,
      "orders": [],
      "pages": 1,
      "records": [
        {
          "updatedTime": "2022-08-17 17:31:54",
          "createdByName": "张学胜",
          "updatedBy": "3333",
          "agmtTypeId": "1559835398878855168",
          "isValid": "1",
          "updatedByName": "张学胜",
          "tableName": "TEM_001",
          "agmtTmpAddr": "",
          "agmtTypeName": "协议模板001",
          "createdBy": "3333",
          "agmtTypeRemark": "协议模板001",
          "agmtTypeSql": "SELECT * FROM TEM_001;",
          "createdTime": "2022-08-17 17:31:54",
          "importTmpAddr": ""
        }
      ],
      "searchCount": true,
      "size": 1,
      "total": 1
    },
    "list": [
      {
        "updatedTime": "2022-08-17 17:31:54",
        "createdByName": "张学胜",
        "updatedBy": "3333",
        "agmtTypeId": "1559835398878855168",
        "isValid": "1",
        "updatedByName": "张学胜",
        "tableName": "TEM_001",
        "agmtTmpAddr": "",
        "agmtTypeName": "协议模板001",
        "createdBy": "3333",
        "agmtTypeRemark": "协议模板001",
        "agmtTypeSql": "SELECT * FROM TEM_001;",
        "createdTime": "2022-08-17 17:31:54",
        "importTmpAddr": ""
      }
    ]
  },
  "isImport": false,
  "maxWidth": {},
  "msg": "",
  "success": 1
}

import cn.hutool.core.util.IdUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.log.StaticLog;
import com.amazonaws.opendistro.elasticsearch.sql.jdbc.shadow.com.amazonaws.util.StringInputStream;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.eye.channelflow.core.vo.MsgException;
import org.apache.ibatis.builder.MapperBuilderAssistant;
import org.apache.ibatis.builder.xml.XMLMapperEntityResolver;
import org.apache.ibatis.builder.xml.XMLStatementBuilder;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.executor.ExecutorException;
import org.apache.ibatis.mapping.Environment;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.parsing.XNode;
import org.apache.ibatis.parsing.XPathParser;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.RowBounds;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.transaction.Transaction;
import org.apache.ibatis.transaction.TransactionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.util.List;
import java.util.Map;

/**
 * Mybatis构建sql工具类
 * Created by GMQ on 2022/8/20 14:39
 */
@Component
public class MybatisUtil {

    @Autowired
    SqlSession sqlSession;

    /**
     * 执行sql, 支持mybatis mapper.xml语法
     *
     * @param sql       执行的sql表达式
     * @param parameter 参数
     * @return
     */
    public List<Map<String, Object>> query(String sql, Map<String, Object> parameter) {
        List<Map<String, Object>> result = null;
        try {
            Configuration configuration = sqlSession.getConfiguration();
            StaticLog.info("sql:{}", sql);
            String uuid = IdUtil.fastSimpleUUID();
            StringInputStream inputStream = new StringInputStream("<?xml version=\"1.0\" encoding=\"UTF-8\" ?> <!DOCTYPE mapper PUBLIC \"-//mybatis.org//DTD Mapper 3.0//EN\" \"http://mybatis.org/dtd/mybatis-3-mapper.dtd\" > <mapper> <select id=\"" + uuid + "\" resultType=\"java.util.Map\"> " + sql + " </select> </mapper>");
            XPathParser parser = new XPathParser(inputStream, true, configuration.getVariables(), new XMLMapperEntityResolver());
            XNode node = parser.evalNode("/mapper").evalNodes("select").get(0);
            XMLStatementBuilder xmlStatementBuilder = new XMLStatementBuilder(configuration, new MapperBuilderAssistant(configuration, inputStream.toString()), node, null);
            xmlStatementBuilder.parseStatementNode();
            Executor executor = newExecutor(configuration);
            MappedStatement mappedStatement = configuration.getMappedStatement(uuid);

            StaticLog.info("==> Preparing: {}", mappedStatement.getBoundSql(parameter).getSql());
            StaticLog.info("==> Parameters: {}", mappedStatement.getBoundSql(parameter).getParameterObject());
            result = executor.query(mappedStatement, parameter, RowBounds.DEFAULT, Executor.NO_RESULT_HANDLER);
        } catch (Exception e) {
            StaticLog.error("sql执行错误: ", e.getMessage());
            e.printStackTrace();
            throw new MsgException("sql执行错误");
        }
        return result;
    }

    /**
     * 分页查询
     *
     * @param page
     * @param sql
     * @param parameter
     * @return com.baomidou.mybatisplus.core.metadata.IPage<java.util.Map>
     * @throws
     * @author GMQ
     * @date 2022/8/21 11:59
     **/
    public IPage<Map> queryPage(Page page, String sql, Map<String, Object> parameter) {
        IPage<Map> iPage = page;
        try {
            Configuration configuration = sqlSession.getConfiguration();
            String uuid = IdUtil.fastSimpleUUID();
            String countSql = StrUtil.format("select count(*) from ( {} ) count_select", sql);
            StaticLog.info("countSql:{}", countSql);
            StringInputStream inputStream = new StringInputStream("<?xml version=\"1.0\" encoding=\"UTF-8\" ?> <!DOCTYPE mapper PUBLIC \"-//mybatis.org//DTD Mapper 3.0//EN\" \"http://mybatis.org/dtd/mybatis-3-mapper.dtd\" > <mapper> <select id=\"" + uuid + "\" resultType=\"java.lang.Integer\"> " + countSql + " </select> </mapper>");
            XPathParser parser = new XPathParser(inputStream, true, configuration.getVariables(), new XMLMapperEntityResolver());
            XNode node = parser.evalNode("/mapper").evalNodes("select").get(0);
            XMLStatementBuilder xmlStatementBuilder = new XMLStatementBuilder(configuration, new MapperBuilderAssistant(configuration, inputStream.toString()), node, null);
            xmlStatementBuilder.parseStatementNode();
            Executor executor = newExecutor(configuration);
            MappedStatement mappedStatement = configuration.getMappedStatement(uuid);

            StaticLog.info("==> Preparing: {}", mappedStatement.getBoundSql(parameter).getSql());
            StaticLog.info("==> Parameters: {}", mappedStatement.getBoundSql(parameter).getParameterObject());
            Object result = executor.query(mappedStatement, parameter, RowBounds.DEFAULT, Executor.NO_RESULT_HANDLER).get(0);
            page.setTotal(result == null ? 0L : Long.parseLong(result.toString()));
            if (page.getTotal() > 0) {
                page.setRecords(query(StrUtil.format("{} limit {},{}", sql, page.offset(), page.getSize()), parameter));
            }
        } catch (Exception e) {
            StaticLog.error("sql执行错误: ", e.getMessage());
            throw new MsgException("sql执行错误");
        }
        return iPage;
    }


    private Executor newExecutor(Configuration configuration) {
        final Environment environment = configuration.getEnvironment();
        if (environment == null) {
            throw new ExecutorException("ResultLoader could not load lazily.  Environment was not configured.");
        }
        final DataSource ds = environment.getDataSource();
        if (ds == null) {
            throw new ExecutorException("ResultLoader could not load lazily.  DataSource was not configured.");
        }
        final TransactionFactory transactionFactory = environment.getTransactionFactory();
        final Transaction tx = transactionFactory.newTransaction(ds, null, false);
        return configuration.newExecutor(tx, ExecutorType.SIMPLE);
    }

}