在开发中可能会遇到需要执行动态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工具类，可自己实现修改
