本文介绍了一种利用Java和MyBatis框架解析带有MyBatis标签的SQL语句的方法，通过实例展示了如何将带有占位符的SQL转换为实际执行的SQL语句，包括动态SQL的处理。
工具类一：
package com.cwp.data.service.utils;
import org.apache.commons.io.input.ReaderInputStream;
import org.apache.ibatis.builder.xml.XMLMapperBuilder;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.mapping.ParameterMode;
import org.apache.ibatis.parsing.XNode;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.type.TypeHandlerRegistry;
import java.io.StringReader;
import java.text.SimpleDateFormat;
import java.util.*;
/**
* @ClassName CustomMapperUtils
* @Description TODO
* @Author cwp
* @Date 2021/6/9 17:05
*/
public class CustomMapperUtils {
public static SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyyMMdd");
// mybatis xml 文件起始字符串
public static final String xmlStartSql="<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n" +
"<!DOCTYPE mapper PUBLIC \"-//mybatis.org//DTD Mapper 3.0//EN\" \"http://mybatis.org/dtd/mybatis-3-mapper.dtd\">\n" +
"<mapper namespace=\"customMapperUtils\">\n <select id=\"selectData\" parameterType=\"java.util.Map\">\n";
// mybatis xml 文件结束字符串
public static final String xmlEndSql="\n</select>\n </mapper>";
// 查询语句ID
public static final String statementSelectId="selectData";
public static void main(String[] args) {
String sourceSql="<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n" +
"<!DOCTYPE mapper PUBLIC \"-//mybatis.org//DTD Mapper 3.0//EN\" \"http://mybatis.org/dtd/mybatis-3-mapper.dtd\">\n" +
"<mapper namespace=\"customMapperUtils\">\n" +
" <select id=\"demoPage\" parameterType=\"java.util.Map\">\n" +
" SELECT a.*, cu.username as createPerName\n" +
" FROM bdp_alarm a\n" +
" LEFT JOIN bdp_alarm_received ar on ar.alarm_id = a.id\n" +
" LEFT JOIN sys_user cu on cu.user_id = a.create_per\n" +
" <where>\n" +
" <if test=\"id != null\">\n" +
" and a.id = #{id}\n" +
" </if>\n" +
" <if test=\"alarmIds != null\">\n" +
" and a.id in\n" +
" <foreach item=\"item\" index=\"index\" collection=\"alarmIds\" open=\"(\" separator=\",\" close=\")\">\n" +
" #{item}\n" +
" </foreach>\n" +
" </if>\n" +
" <if test=\"name != null and name != ''\">\n" +
" and a.name like concat(\"%\",#{name},\"%\")\n" +
" </if>\n" +
" <if test=\"createPer != null and createPer != ''\">\n" +
" and cu.username like concat(\"%\",#{createPer},\"%\")\n" +
" </if>\n" +
" <if test=\"receivedPer != null\">\n" +
" and ar.received_name like concat(\"%\", #{receivedPer} ,\"%\")\n" +
" </if>\n" +
" <if test=\"triggerCondition != null\">\n" +
" and a.trigger_condition = #{triggerCondition}\n" +
" </if>\n" +
" <if test=\"userIdList!=null and userIdList.size()>0\">\n" +
" and a.create_per in\n" +
" <foreach collection=\"userIdList\" open=\"(\" close=\")\" separator=\",\" item=\"item\">\n" +
" #{item}\n" +
" </foreach>\n" +
" </if>\n" +
" </where>\n" +
" GROUP BY a.id\n" +
" ORDER BY a.create_time desc\n" +
" </select>\n" +
"</mapper>";
String selectSql="SELECT a.*, cu.username as createPerName\n" +
" FROM bdp_alarm a\n" +
" LEFT JOIN bdp_alarm_received ar on ar.alarm_id = a.id\n" +
" LEFT JOIN sys_user cu on cu.user_id = a.create_per\n" +
" <where>\n" +
" <if test=\"id != null\">\n" +
" and a.id = #{id}\n" +
" </if>\n" +
" <if test=\"alarmIds != null\">\n" +
" and a.id in\n" +
" <foreach item=\"item\" index=\"index\" collection=\"alarmIds\" open=\"(\" separator=\",\" close=\")\">\n" +
" #{item}\n" +
" </foreach>\n" +
" </if>\n" +
" <if test=\"name != null and name != ''\">\n" +
" and a.name like concat(\"%\",#{name},\"%\")\n" +
" </if>\n" +
" <if test=\"createPer != null and createPer != ''\">\n" +
" and cu.username like concat(\"%\",#{createPer},\"%\")\n" +
" </if>\n" +
" <if test=\"receivedPer != null\">\n" +
" and ar.received_name like concat(\"%\", #{receivedPer} ,\"%\")\n" +
" </if>\n" +
" <if test=\"triggerCondition != null\">\n" +
" and a.trigger_condition = #{triggerCondition}\n" +
" </if>\n" +
" <if test=\"userIdList!=null and userIdList.size()>0\">\n" +
" and a.create_per in\n" +
" <foreach collection=\"userIdList\" open=\"(\" close=\")\" separator=\",\" item=\"item\">\n" +
" #{item}\n" +
" </foreach>\n" +
" </if>\n" +
" </where>\n" +
" GROUP BY a.id\n" +
" ORDER BY a.create_time desc";
HashMap paramMap = new HashMap();
paramMap.put("id","ad");
paramMap.put("name","name1");
paramMap.put("createPer","createPer1");
paramMap.put("receivedPer","receivedPer1");
paramMap.put("triggerCondition","triggerCondition1");
ArrayList userIdList=new ArrayList();
userIdList.add(1);
userIdList.add(2);
userIdList.add(3);
paramMap.put("userIdList",userIdList);
String statementId="selectData";
String sql= parseMybatisSelectSql(selectSql,paramMap);
System.out.println("获取到的:statementId="+statementId+"语句的最终执行sql:\n"+sql);
}
/**
* @Description 解析含有mybatis 标签的查询sql,只需要传sql过来就行,方法自动拼接前后mybatis xml文件前后字符
* @Author chengweiping
* @Date 2021/6/10 9:37
*/
public static String parseMybatisSelectSql(String selectSql,Map paramMap){
System.out.println("原始查询sql:\n"+selectSql);
StringBuilder sqlBuilder=new StringBuilder();
sqlBuilder.append(xmlStartSql).append(selectSql).append(xmlEndSql);
String sourceSql=sqlBuilder.toString();
System.out.println("原始mybatis xml sql:\n"+sourceSql);
//解析
Configuration configuration=new Configuration();
XMLMapperBuilder xmlMapperBuilder = new XMLMapperBuilder(new ReaderInputStream(new StringReader(sourceSql),"UTF-8"), configuration, sourceSql, new HashMap<String, XNode>());
xmlMapperBuilder.parse();
MappedStatement mappedStatement = xmlMapperBuilder.getConfiguration().getMappedStatement(statementSelectId);
BoundSql boundSql = mappedStatement.getBoundSql(paramMap);
String compileSql=boundSql.getSql();
System.out.println("去掉mybatis标签后compileSql:\n"+compileSql);
//替换参数值获取最终的sql
String finalSql=replaceSqlParamValue(configuration,boundSql);
System.out.println("替换参数值后finalSql:\n"+finalSql);
return finalSql;
}
/**
* @Description 解析含有mybatis 标签的sql (包含完整的xml文件字符串）
* @Author chengweiping
* @Date 2021/6/10 9:37
*/
public static String parseMybatisSql(String statementId,String sourceSql,Map paramMap){
//解析
Configuration configuration=new Configuration();
XMLMapperBuilder xmlMapperBuilder = new XMLMapperBuilder(new ReaderInputStream(new StringReader(sourceSql),"UTF-8"), configuration, sourceSql, new HashMap<String, XNode>());
xmlMapperBuilder.parse();
MappedStatement mappedStatement = xmlMapperBuilder.getConfiguration().getMappedStatement(statementId);
BoundSql boundSql = mappedStatement.getBoundSql(paramMap);
String compileSql=boundSql.getSql();
System.out.println("去掉mybatis标签后compileSql:\n"+compileSql);
//替换参数值获取最终的sql
String finalSql=replaceSqlParamValue(configuration,boundSql);
System.out.println("替换参数值后finalSql:\n"+finalSql);
return finalSql;
}
/**
* @Description 替换sql参数值
* @Author chengweiping
* @Date 2021/6/10 9:37
*/
private static String replaceSqlParamValue(Configuration configuration,BoundSql boundSql) {
String sql=boundSql.getSql();
//美化Sql
sql.replaceAll("[\\s\n]+", " ");
// 填充占位符， 把传参填进去，使用#｛｝、${} 一样的方式
Object parameterObject = boundSql.getParameterObject();
//参数映射列表，有入的（in） 有出的（O），后面只遍历传入的参数，并且把传入的参数赋值成我们代码里的值
List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
ArrayList<String> parmeters = new ArrayList<String>();
if (parameterMappings != null) {
MetaObject metaObject = parameterObject == null ? null : configuration.newMetaObject(parameterObject);
for (int i = 0; i < parameterMappings.size(); i++) {
ParameterMapping parameterMapping = parameterMappings.get(i);
if (parameterMapping.getMode() != ParameterMode.OUT) {
//参数值
Object value;
String propertyName = parameterMapping.getProperty();
//获取参数名称
if (boundSql.hasAdditionalParameter(propertyName)) {
//获取参数值
value = boundSql.getAdditionalParameter(propertyName);
} else if (parameterObject == null) {
value = null;
} else if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {
//如果是单个值则直接赋值
value = parameterObject;
} else {
value = metaObject == null ? null : metaObject.getValue(propertyName);
}
//由于传参类型多种多样，数值型的直接把value的值传参进去即可，如果是字符串的、日期的，要在前后加上单引号‘’，才能到sql里判读语句里使用
if (value instanceof Number) {
parmeters.add(String.valueOf(value));
} else {
StringBuilder builder = new StringBuilder();
builder.append("'");
if (value instanceof Date) {
builder.append(simpleDateFormat.format((Date) value));
} else if (value instanceof String) {
builder.append(value);
}
builder.append("'");
parmeters.add(builder.toString());
}
}
}
}
//sql里的东西处理完了，都返回给我们声明list容器里了，接下来要用我们一开始获取到绑定的sql 来替换赋值，把真正 传参赋值的sql返回给计算引擎，spark或者flink
for (String value : parmeters) {
/*
把占位符问号 逐个 从第一位去替换我们list里获取到的值。如果对ArrayList数组的顺序不放心，可以换成LinkList去实现，不需要考虑性能问题
毕竟最多10来个参数，如果你写sql还要传入上千个的参数，那么就得好好反思，是不是要改成代码去实现了。
*/
sql = sql.replaceFirst("\\?", value);
}
return sql;
}
}
工具类二：
package cn.getech.data.service.utils;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.datasource.pooled.PooledDataSource;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.Environment;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.mapping.ParameterMode;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.transaction.jdbc.JdbcTransactionFactory;
import org.apache.ibatis.type.TypeHandlerRegistry;
import java.text.SimpleDateFormat;
import java.util.*;
public class MybatisXmlUtils {
public static SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyyMMdd");
public static void main(String[] args) {
HashMap paramMap = new HashMap();
paramMap.put("id","ad");
paramMap.put("name","name1");
paramMap.put("createPer","createPer1");
paramMap.put("receivedPer","receivedPer1");
paramMap.put("triggerCondition","triggerCondition1");
ArrayList userIdList=new ArrayList();
userIdList.add(1);
userIdList.add(2);
userIdList.add(3);
paramMap.put("userIdList",userIdList);;
String sql = MybatisXmlUtils.getRealSql("demoPage",paramMap);
System.out.println("最终的sql:"+sql);
/*String alarmPage2 = MybatisXmlUtils.getRealSql("demoPage2",paramMap);
System.out.println(alarmPage2+":"+alarmPage2);*/
}
public static String getRealSql(String sqlId, Map map) {
Environment environment = new Environment("development", new JdbcTransactionFactory(), new PooledDataSource());
//通过底层开发者模式的environment创建configuration对象
Configuration configuration = new Configuration(environment);
//加入mapper接口类映射器，并注册别名
//TODO 可以加入多个dao类别。如果业务量大起来，可以使用数组去遍历路径下的接口类
/*configuration.getTypeAliasRegistry().registerAlias("DemoMapper", DemoMapper.class);
configuration.addMapper(DemoMapper.class);*/
configuration.addMappers("cn.getech.data.service.utils");
//绑定里面的sql, 并且把写的map参数也传给sql
BoundSql boundSql = configuration.getMappedStatement(sqlId).getBoundSql(map);
String sql = boundSql.getSql();
System.out.println("去掉mybatis标签的sql:"+sql);
if (StringUtils.isEmpty(sql)) {
return "";
}
//美化Sql
sql.replaceAll("[\\s\n]+", " ");
// 填充占位符， 把传参填进去，使用#｛｝、${} 一样的方式
Object parameterObject = boundSql.getParameterObject();
//参数映射列表，有入的（in） 有出的（O），后面只遍历传入的参数，并且把传入的参数赋值成我们代码里的值
List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
ArrayList<String> parmeters = new ArrayList<String>();
if (parameterMappings != null) {
MetaObject metaObject = parameterObject == null ? null : configuration.newMetaObject(parameterObject);
for (int i = 0; i < parameterMappings.size(); i++) {
ParameterMapping parameterMapping = parameterMappings.get(i);
if (parameterMapping.getMode() != ParameterMode.OUT) {
//参数值
Object value;
String propertyName = parameterMapping.getProperty();
//获取参数名称
if (boundSql.hasAdditionalParameter(propertyName)) {
//获取参数值
value = boundSql.getAdditionalParameter(propertyName);
} else if (parameterObject == null) {
value = null;
} else if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {
//如果是单个值则直接赋值
value = parameterObject;
} else {
value = metaObject == null ? null : metaObject.getValue(propertyName);
}
//由于传参类型多种多样，数值型的直接把value的值传参进去即可，如果是字符串的、日期的，要在前后加上单引号‘’，才能到sql里判读语句里使用
if (value instanceof Number) {
parmeters.add(String.valueOf(value));
} else {
StringBuilder builder = new StringBuilder();
builder.append("'");
if (value instanceof Date) {
builder.append(simpleDateFormat.format((Date) value));
} else if (value instanceof String) {
builder.append(value);
}
builder.append("'");
parmeters.add(builder.toString());
}
}
}
}
//sql里的东西处理完了，都返回给我们声明list容器里了，接下来要用我们一开始获取到绑定的sql 来替换赋值，把真正 传参赋值的sql返回给计算引擎，spark或者flink
for (String value : parmeters) {
/*
把占位符问号 逐个 从第一位去替换我们list里获取到的值。如果对ArrayList数组的顺序不放心，可以换成LinkList去实现，不需要考虑性能问题
毕竟最多10来个参数，如果你写sql还要传入上千个的参数，那么就得好好反思，是不是要改成代码去实现了。
*/
sql = sql.replaceFirst("\\?", value);
}
return sql;
}
}
相关类：
package cn.getech.data.service.utils;
import org.apache.ibatis.annotations.Mapper;
import java.util.Map;
@Mapper
public interface DemoMapper {
}
相关xml
<?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 namespace="cn.getech.data.service.utils.DemoMapper">
<select id="demoPage" parameterType="java.util.Map">
SELECT a.*, cu.username as createPerName
FROM bdp_alarm a
LEFT JOIN bdp_alarm_received ar on ar.alarm_id = a.id
LEFT JOIN sys_user cu on cu.user_id = a.create_per
<where>
<if test="id != null">
and a.id = #{id}
</if>
<if test="alarmIds != null">
and a.id in
<foreach item="item" index="index" collection="alarmIds" open="(" separator="," close=")">
#{item}
</foreach>
</if>
<if test="name != null and name != ''">
and a.name like concat("%",#{name},"%")
</if>
<if test="createPer != null and createPer != ''">
and cu.username like concat("%",#{createPer},"%")
</if>
<if test="receivedPer != null">
and ar.received_name like concat("%", #{receivedPer} ,"%")
</if>
<if test="triggerCondition != null">
and a.trigger_condition = #{triggerCondition}
</if>
<if test="userIdList!=null and userIdList.size()>0">
and a.create_per in
<foreach collection="userIdList" open="(" close=")" separator="," item="item">
#{item}
</foreach>
</if>
</where>
GROUP BY a.id
ORDER BY a.create_time desc
</select>
</mapper>
扫一扫
485
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
