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

Polar.

2022-08-21

954 3

温馨提示：本文最后更新于 2023-07-07，若内容或图片失效，请留言反馈。部分素材来自网络，若不小心影响到您的利益，请联系我们删除。

在开发中可能会遇到需要执行动态sql的场景，比如前端传输sql片段或参数，后端拼接sql语句来执行

参考文章：https://www.codeleading.com/article/90405694877/

先看效果

前端传入sql语句和参数，支持全局变量、mybatis的xml语法

# 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
}

可以看到，没有问题

工具类分享

package me.forxx.core.util;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.IdUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.log.StaticLog;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.eye.channelflow.core.vo.MsgException;
import com.google.common.base.CaseFormat;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.ibatis.builder.MapperBuilderAssistant;
import org.apache.ibatis.builder.xml.XMLMapperEntityResolver;
import org.apache.ibatis.builder.xml.XMLStatementBuilder;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.parsing.XNode;
import org.apache.ibatis.parsing.XPathParser;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.type.TypeHandlerRegistry;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.SqlSessionUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.*;
import java.util.regex.Matcher;

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

    @Autowired
    private SqlSessionTemplate sqlSessionTemplate;

    /**
     * 执行sql语句
     *
     * @param sql
     * @return java.util.List<java.util.Map < java.lang.String, java.lang.Object>>
     * @throws
     * @author GMQ
     * @date 2022/9/22 23:06
     **/
    public List<Map<String, Object>> excuteQuerySql(String sql) {
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        PreparedStatement pst = null;
        SqlSession session = getSqlSession();
        ResultSet result = null;
        try {
            pst = session.getConnection().prepareStatement(sql);
            result = pst.executeQuery();
            ResultSetMetaData md = result.getMetaData(); //获得结果集结构信息,元数据
            int columnCount = md.getColumnCount();   //获得列数
            while (result.next()) {
                Map<String, Object> rowData = new HashMap<String, Object>();
                for (int i = 1; i <= columnCount; i++) {
                    rowData.put(CaseFormat.LOWER_UNDERSCORE.to(CaseFormat.LOWER_CAMEL, md.getColumnName(i).replaceAll("[A-Z]", "_$0").toLowerCase()), result.getObject(i));
                }
                list.add(rowData);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            closeSqlSession(session);
        }
        return list;
    }

    /**
     * 获取sqlSession
     *
     * @return
     */
    public SqlSession getSqlSession() {
        return SqlSessionUtils.getSqlSession(sqlSessionTemplate.getSqlSessionFactory(),
                sqlSessionTemplate.getExecutorType(), sqlSessionTemplate.getPersistenceExceptionTranslator());
    }

    /**
     * 关闭sqlSession
     *
     * @param session
     */
    public void closeSqlSession(SqlSession session) {
        SqlSessionUtils.closeSqlSession(session, sqlSessionTemplate.getSqlSessionFactory());
    }

    // 如果参数是String，则添加单引号， 如果是日期，则转换为时间格式器并加单引号； 对参数是null和不是null的情况作了处理
    private static String getParameterValue(Object obj) {
        String value = null;
        if (obj instanceof String) {
            value = "'" + obj.toString() + "'";
        } else if (obj instanceof Date) {
            value = "'" + DateUtil.formatDateTime((Date) obj) + "'";
        } else {
            if (obj != null) {
                value = obj.toString();
            } else {
                value = "";
            }
        }
        return value;
    }

    private static String showSql(Configuration configuration, BoundSql boundSql) {
        // 获取参数
        Object parameterObject = boundSql.getParameterObject();
        List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
        // sql语句中多个空格都用一个空格代替
        String sql = boundSql.getSql().replaceAll("[\\s]+", " ");
        if (CollectionUtils.isNotEmpty(parameterMappings) && parameterObject != null) {
            // 获取类型处理器注册器，类型处理器的功能是进行java类型和数据库类型的转换
            TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
            // 如果根据parameterObject.getClass(）可以找到对应的类型，则替换
            if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {
                sql = sql.replaceFirst("\\?",
                        Matcher.quoteReplacement(getParameterValue(parameterObject)));
            } else {
                // MetaObject主要是封装了originalObject对象，提供了get和set的方法用于获取和设置originalObject的属性值,主要支持对JavaBean、Collection、Map三种类型对象的操作
                MetaObject metaObject = configuration.newMetaObject(parameterObject);
                for (ParameterMapping parameterMapping : parameterMappings) {
                    String propertyName = parameterMapping.getProperty();
                    if (metaObject.hasGetter(propertyName)) {
                        Object obj = metaObject.getValue(propertyName);
                        sql = sql.replaceFirst("\\?",
                                Matcher.quoteReplacement(getParameterValue(obj)));
                    } else if (boundSql.hasAdditionalParameter(propertyName)) {
                        // 该分支是动态sql
                        Object obj = boundSql.getAdditionalParameter(propertyName);
                        sql = sql.replaceFirst("\\?",
                                Matcher.quoteReplacement(getParameterValue(obj)));
                    } else {
                        // 打印出缺失，提醒该参数缺失并防止错位
                        sql = sql.replaceFirst("\\?", "缺失");
                    }
                }
            }
        }
        return sql;
    }

    /**
     * 执行sql, 支持mybatis mapper.xml语法
     *
     * @param sql       执行的sql表达式
     * @param parameter 参数
     * @return
     */
    public List<Map<String, Object>> queryList(String sql, Map<String, Object> parameter) {
        List<Map<String, Object>> result = null;
        try {
            Configuration configuration = sqlSessionTemplate.getConfiguration();
            StaticLog.info("sql:{}", sql);
            String uuid = IdUtil.fastSimpleUUID();
            String xml = StrUtil.format("<?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=\"{}\" resultType=\"java.util.Map\">{}</select></mapper>", uuid, sql);
            XPathParser parser = new XPathParser(xml, true, configuration.getVariables(), new XMLMapperEntityResolver());
            XNode node = parser.evalNode("/mapper").evalNodes("select").get(0);
            XMLStatementBuilder xmlStatementBuilder = new XMLStatementBuilder(configuration, new MapperBuilderAssistant(configuration, xml), node, null);
            xmlStatementBuilder.parseStatementNode();
            MappedStatement mappedStatement = configuration.getMappedStatement(uuid);

            StaticLog.info("==> Preparing: {}", mappedStatement.getBoundSql(parameter).getSql());
            StaticLog.info("==> Parameters: {}", mappedStatement.getBoundSql(parameter).getParameterObject());
            String formatSql = showSql(configuration, mappedStatement.getBoundSql(parameter));
            StaticLog.info("==> FormatSql: {}", formatSql);

            result = excuteQuerySql(formatSql);
        } catch (Exception e) {
            e.printStackTrace();
            throw new MsgException(StrUtil.format("sql执行错误:{}", e.getMessage()));
        }
        return result;
    }

    /**
     * 执行sql,查询map 支持mybatis mapper.xml语法
     *
     * @param sql       执行的sql表达式
     * @param parameter 参数
     * @return
     */
    public Map<String, Object> queryMap(String sql, Map<String, Object> parameter) {
        Map<String, Object> result = null;
        try {
            Configuration configuration = sqlSessionTemplate.getConfiguration();
            StaticLog.info("sql:{}", sql);
            String uuid = IdUtil.fastSimpleUUID();
            String xml = StrUtil.format("<?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=\"{}\" resultType=\"java.util.Map\">{}</select></mapper>", uuid, sql);
            XPathParser parser = new XPathParser(xml, true, configuration.getVariables(), new XMLMapperEntityResolver());
            XNode node = parser.evalNode("/mapper").evalNodes("select").get(0);
            XMLStatementBuilder xmlStatementBuilder = new XMLStatementBuilder(configuration, new MapperBuilderAssistant(configuration, xml), node, null);
            xmlStatementBuilder.parseStatementNode();
            MappedStatement mappedStatement = configuration.getMappedStatement(uuid);

            StaticLog.info("==> Preparing: {}", mappedStatement.getBoundSql(parameter).getSql());
            StaticLog.info("==> Parameters: {}", mappedStatement.getBoundSql(parameter).getParameterObject());
            String formatSql = showSql(configuration, mappedStatement.getBoundSql(parameter));
            StaticLog.info("==> FormatSql: {}", formatSql);

            List<Map<String, Object>> lo = excuteQuerySql(formatSql);
            if (CollUtil.isNotEmpty(lo)) {
                result = lo.get(0);
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new MsgException(StrUtil.format("sql执行错误:{}", e.getMessage()));
        }
        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 = sqlSessionTemplate.getConfiguration();
            String uuid = IdUtil.fastSimpleUUID();
            String countSql = StrUtil.format("select count(*) as count from ( {} ) count_select", sql);
            String xml = StrUtil.format("<?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=\"{}\" resultType=\"java.util.Map\">{}</select></mapper>", uuid, countSql);
            XPathParser parser = new XPathParser(xml, true, configuration.getVariables(), new XMLMapperEntityResolver());
            XNode node = parser.evalNode("/mapper").evalNodes("select").get(0);
            XMLStatementBuilder xmlStatementBuilder = new XMLStatementBuilder(configuration, new MapperBuilderAssistant(configuration, xml), node, null);
            xmlStatementBuilder.parseStatementNode();
            MappedStatement mappedStatement = configuration.getMappedStatement(uuid);

            StaticLog.info("==> Preparing: {}", mappedStatement.getBoundSql(parameter).getSql());
            StaticLog.info("==> Parameters: {}", mappedStatement.getBoundSql(parameter).getParameterObject());
            String formatSql = showSql(configuration, mappedStatement.getBoundSql(parameter));
            StaticLog.info("==> FormatSql: {}", formatSql);

            Map<String, Object> countR = excuteQuerySql(formatSql).get(0);

            page.setTotal(countR.get("count") == null ? 0L : Long.parseLong(countR.get("count") + ""));
            if (page.getTotal() > 0) {
                page.setRecords(queryList(StrUtil.format("{} limit {},{}", sql, page.offset(), page.getSize()), parameter));
            }
        } catch (Exception e) {
            throw new MsgException(StrUtil.format("sql执行错误:{}", e.getMessage()));
        }
        return iPage;
    }

}

依赖hutool工具类，可自己实现修改

Mybatis-Plus代码生成器新版配置

适用版本：mybatis-plus-generator 3.5.1 及其以上版本，对历史版本不兼容。我只需要生成dao、model、mapper，提供定制生成模板代码示例官方教程：https://baomidou.com/pages/779a6e/pom.xml引入依赖<dependency&

Polar.

3182 23

技术分享

Navicat Premium 15不用断网破解教程

Navicat Premium 15.0.17不用断网破解教程环境:Win7 x64版本:在官网下载的最新版navicat150_premium_cs_15.0.17_x641.下载Navicat Premium 15我测试成功的版本navicat150_premium_cs_15.0.17_x64

Polar.

9973 90

技术分享