在开发中可能会遇到需要执行动态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
}
可以看到,没有问题
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);
}
}
依赖hutool工具类,可自己实现修改