创作背景
公司采用数据中心模式，导致项目无法使用mybatis、hibernate等数据层框架。sql语句及参数拼接只能在代码里写，导致编码复杂，可读性低，交接困难等诸多问题。而mybatis在xml文件书写sql语句的方式非常便捷且sql格式化方便阅读分析。
于是我便写了一套工具类用以实现解析xml文件中sql语句，特此记录。
一：创建一个maven项目，并引入相关依赖
<dependencies>
<!-- 解析xm文件中sql语句所需依赖包 -->
<!-- https://mvnrepository.com/artifact/org.dom4j/dom4j -->
<dependency>
<groupId>org.dom4j</groupId>
<artifactId>dom4j</artifactId>
<version>2.1.4</version>
</dependency>
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.8.26</version>
</dependency>
<!-- OGNL是Object Graphic Navigation Language(对象图导航语言)的缩写，一个开源项目 本项目用以支持动态sql -->
<dependency>
<groupId>ognl</groupId>
<artifactId>ognl</artifactId>
<version>3.2.1</version>
</dependency>
<!-- 引入测试相关 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>compile</scope>
</dependency>
</dependencies>
二：上代码
2.1 创建 RoleVo.java 用以测试
import java.util.Date;
public class RoleVo {
public RoleVo(String id){
this.id=id;
}
private String id;
private String name;
private Date createTime;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Date getCreateTime() {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
}
2.2 创建函数式接口，以优雅的方式获取执行的文件及方法
import java.io.Serializable;
/**
* 可序列化函数式接口服务
* @param <T>
*/
public interface SerializableConsumer<T> extends Serializable {
void accept();
}
2.3 编写 获取执行文件及方法工具类
import java.lang.invoke.SerializedLambda;
import java.lang.reflect.Method;
import java.util.HashMap;
import java.util.Map;
public class XmlFileNameUtil {
/**
* 获取xml文件名及执行方法名
* @param consumer
* @throws Exception
*/
public static Map<String,String> getFileAndMethodName(SerializableConsumer<Object> consumer) throws Exception {
Map<String ,String> res=new HashMap<>(2);
// 直接调用writeReplace
Method writeReplace = consumer.getClass().getDeclaredMethod("writeReplace");
writeReplace.setAccessible(true);
Object sl = writeReplace.invoke(consumer);
SerializedLambda serializedLambda = (SerializedLambda) sl;
res.put(XmlSqlConfig.XML_FILE_NAME,getFileName(serializedLambda.getImplClass()));
res.put(XmlSqlConfig.XML_METHOD_NAME,serializedLambda.getImplMethodName());
return res;
}
/**
* 获取文件名
* @param className
* @return
*/
private static String getFileName(String className){
String[] split = className.split("/");
if(split.length>0){
String fileName=split[split.length-1];
return fileName;
}
return "";
}
}
2.4 创建配置类，为扩容和优化留下余地
import java.text.SimpleDateFormat;
import java.time.format.DateTimeFormatter;
public interface XmlSqlConfig {
// xml文件名标识
String XML_FILE_NAME="xmlFileName";
// xml 执行sql标识
String XML_METHOD_NAME="xmlMethodName";
// 执行方法属性标识
String XML_METHOD_FLAG="id";
// xml文件路径
String XML_FILE_PATH="src/main/resources/";
// xml文件后缀
String XML_FILE_SUFFIX=".xml";
// 日期类型格式化
DateTimeFormatter ldtFormatter=DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
DateTimeFormatter ltFormatter=DateTimeFormatter.ofPattern("HH:mm:ss");
SimpleDateFormat dateFormat=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
// foreach 标签属性
String FOREACH_COLLECTION="collection";
String FOREACH_ITEM="item";
String FOREACH_OPEN="open";
String FOREACH_SEPARATOR="separator";
String FOREACH_CLOSE="close";
// 参数正则
String REGX_J="#\\{([^}]+)}";
String REGX_D="\\$\\{([^}]+)}";
String REGX_SPECIAL_CHAR="[\\n\\r\\t\\v\\f]";
}
2.5 进入正题，编写sql解析工具类
import cn.hutool.core.util.ReUtil;
import ognl.Ognl;
import ognl.OgnlException;
import org.dom4j.Document;
import org.dom4j.Element;
import org.dom4j.Node;
import org.dom4j.io.SAXReader;
import org.dom4j.tree.DefaultElement;
import org.dom4j.tree.DefaultText;
import java.io.FileInputStream;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.util.*;
public class XmlSqlUtil {
// 节点缓存
private static final Map<String,Element> rootMap=new HashMap<>();
/**
* 获取xml解析后sql
* @param consumer 方法引用
* @param parameter sql参数
* @return
*/
public static String getXmlSql(SerializableConsumer<Object> consumer, Object parameter){
try {
Map<String, String> fileAndMethodName = XmlFileNameUtil.getFileAndMethodName(consumer);
return getSql(parameter,fileAndMethodName.get(XmlSqlConfig.XML_FILE_NAME),fileAndMethodName.get(XmlSqlConfig.XML_METHOD_NAME));
}catch (Exception e){
e.printStackTrace();
}
return "";
}
/**
* 获取xml解析后sql
* @param consumer 方法引用
* @param parameter sql参数
* @param filePath xml文件路径 eg: mapper/user/
* @return
* @throws Exception
*/
public static String getXmlSql(SerializableConsumer<Object> consumer,String filePath, Object parameter){
try {
Map<String, String> fileAndMethodName = XmlFileNameUtil.getFileAndMethodName(consumer);
return getSql(parameter,fileAndMethodName.get(filePath+ XmlSqlConfig.XML_FILE_NAME),fileAndMethodName.get(XmlSqlConfig.XML_METHOD_NAME));
}catch (Exception e){
e.printStackTrace();
}
return "";
}
/**
* 获取xml解析后sql
* @param fileName 文件名 eg: mapper/user/UserMapper.xml
* @param methodName 执行方法名
* @param parameter sql参数
* @return
* @throws Exception
*/
public static String getXmlSql(String fileName,String methodName, Object parameter){
try {
return getSql(parameter,fileName,methodName);
}catch (Exception e){
e.printStackTrace();
}
return "";
}
private static String getSql(Object parameter,String fileName,String methodName) throws Exception {
StringBuilder sql=new StringBuilder();
Element root = rootMap.get(fileName);
if(root==null){
//1. 创建SAXReader对象
SAXReader sr = new SAXReader();
//2. 调用read方法
Document doc = sr.read(new FileInputStream(XmlSqlConfig.XML_FILE_PATH+fileName+ XmlSqlConfig.XML_FILE_SUFFIX));
//3. 获取根节点
root = doc.getRootElement();
rootMap.put(fileName,root);
}
//4. 通过迭代器遍历直接节点
for(Iterator<Element> iter = root.elementIterator(); iter.hasNext();){
Element book = iter.next();
//5. 执行指定方法
if(!methodName.equals(book.attribute(XmlSqlConfig.XML_METHOD_FLAG).getText()))continue;
for (Node node : book.content()) {
if (node instanceof DefaultElement) {
//6. 动态sql 处理
DynamicSql.dynamicSqlDeal((DefaultElement)node,sql,parameter);
} else if (node instanceof DefaultText) {
//7. 如果是文本节点，将文本内容连接起来
sql.append(node.getText());
}
}
}
//8. 参数处理
parameterDeal(sql,parameter);
//9. 去除换行等特殊字符
return sql.toString().replaceAll(XmlSqlConfig.REGX_SPECIAL_CHAR,"");
}
private static void parameterDeal(StringBuilder sql,Object parameter) throws OgnlException {
String s = sql.toString();
if(!(s.length()>0))return;
// 占位参数处理
List<String> parameterJ = ReUtil.findAll(XmlSqlConfig.REGX_J, s, 1);
for (int i = 0; i < parameterJ.size(); i++) {
s=s.replaceFirst(XmlSqlConfig.REGX_J,"'"+parameterTypeDeal(parameterJ.get(i),parameter)+"'");
}
// 参数处理
List<String> parameterD = ReUtil.findAll(XmlSqlConfig.REGX_D, s, 1);
for (int i = 0; i < parameterD.size(); i++) {
s=s.replaceFirst(XmlSqlConfig.REGX_D,parameterTypeDeal(parameterD.get(i),parameter).toString());
}
sql.delete(0,sql.length());
sql.append(s);
}
private static Object parameterTypeDeal(String expression,Object parameter) throws OgnlException {
Object value = Ognl.getValue(expression, parameter);
if(value==null)return "";
if(value instanceof Date){
return XmlSqlConfig.dateFormat.format(value);
}else if(value instanceof LocalDate){
return value;
}else if(value instanceof LocalTime){
return ((LocalTime) value).format(XmlSqlConfig.ltFormatter);
}else if(value instanceof LocalDateTime){
return ((LocalDateTime) value).format(XmlSqlConfig.ldtFormatter);
}else {
return value;
}
}
}
2.6 动态sql处理
import cn.hutool.core.util.ReUtil;
import ognl.Ognl;
import ognl.OgnlException;
import org.dom4j.Node;
import org.dom4j.tree.DefaultElement;
import org.dom4j.tree.DefaultText;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.util.*;
public class DynamicSql {
private static final String dWhere="where";
private static final String dIf="if";
private static final String dForeach="foreach";
private static final String dChoose="choose";
private static final String dWhen="when";
private static final String dOtherwise="otherwise";
public static void dynamicSqlDeal(DefaultElement element, StringBuilder sql, Object parameter){
String name = element.getQName().getName().toLowerCase();
switch (name){
case dWhere:doWhere(element,sql,parameter);break;
case dIf:doIf(element,sql,parameter);break;
case dForeach:doForeach(element,sql,parameter);break;
case dChoose:doChoose(element,sql,parameter);break;
case dWhen:doWhen(element,sql,parameter);break;
case dOtherwise:doOtherwise(element,sql,parameter);break;
default:break;
}
}
private static void doWhere(DefaultElement element,StringBuilder sql,Object parameter){
sql.append(" where ");
StringBuilder whereSql=new StringBuilder();
for (Node node : element.content()) {
if (node instanceof DefaultElement) {
// 子节点处理
dynamicSqlDeal((DefaultElement)node,whereSql,parameter);
} else if (node instanceof DefaultText) {
// 如果是文本节点，将文本内容连接起来
whereSql.append(node.getText());
}
}
String whereStr = whereSql.toString().trim();
if(whereStr.startsWith("and")){
whereStr=whereStr.replaceFirst("and"," ");
}else if(whereStr.startsWith("AND")){
whereStr=whereStr.replaceFirst("AND"," ");
}
sql.append(whereStr);
}
private static void doIf(DefaultElement element,StringBuilder sql,Object parameter){
try {
String test = element.attribute("test").getText();
Object value = Ognl.getValue(test, parameter);
if(value instanceof Boolean&&((Boolean)value)){
for (Node node : element.content()) {
if (node instanceof DefaultElement) {
// 子节点处理
dynamicSqlDeal((DefaultElement)node,sql,parameter);
} else if (node instanceof DefaultText) {
// 如果是文本节点，将文本内容连接起来
sql.append(node.getText());
}
}
}
}catch (Exception e){
// e.printStackTrace();
}
}
private static void doForeach(DefaultElement element,StringBuilder sql,Object parameter){
try {
String collection = element.attribute(XmlSqlConfig.FOREACH_COLLECTION).getText();
String item = element.attribute(XmlSqlConfig.FOREACH_ITEM).getText();
String open = element.attribute(XmlSqlConfig.FOREACH_OPEN).getText();
String separator = element.attribute(XmlSqlConfig.FOREACH_SEPARATOR).getText();
String close = element.attribute(XmlSqlConfig.FOREACH_CLOSE).getText();
String text = element.getText();
if(open.length()>0){
sql.append(open);
}
StringBuilder sb=new StringBuilder();
// 直接传入集合
Object collect = new Object();
if(parameter instanceof List||parameter instanceof Set){
collect=parameter;
}else {
collect = Ognl.getValue(collection, parameter);
}
if(collect==null)return;
if(collect instanceof Set){
Set<?> set=(Set<?>)collect;
// 集合对象类型
if(set.isEmpty())return;
for (Object o : set) {
doForeachParam(o,sb,separator,item,text);
}
}
if(collect instanceof List){
List<?> list=(List<?>)collect;
if(list.isEmpty())return;
for (Object o : list) {
doForeachParam(o,sb,separator,item,text);
}
}
// 去掉末尾分隔符
if(sb.length()>0){
sb.setLength(sb.length() - separator.length());
}
sql.append(sb);
if(close.length()>0){
sql.append(close);
}
}catch (Exception e){
// e.printStackTrace();
}
}
private static void doForeachParam(Object o,StringBuilder sb,String separator,String item,String text){
if(o instanceof String){
sb.append("'"+o.toString()+"'"+separator);
}else if(o instanceof Integer){
sb.append(o.toString()+separator);
}else if(o instanceof Long){
sb.append(o.toString()+separator);
}else if(o instanceof Float){
sb.append(o.toString()+separator);
}else if(o instanceof Double){
sb.append(o.toString()+separator);
}else{
Map<String,Object> map=new HashMap<>(1);
map.put(item,o);
String s = parameterDeal(text, map);
sb.append(s+separator);
}
}
private static void doChoose(DefaultElement element,StringBuilder sql,Object parameter){
for (Node node : element.content()) {
if (node instanceof DefaultElement) {
// 子节点处理
dynamicSqlDeal((DefaultElement)node,sql,parameter);
} else if (node instanceof DefaultText) {
// 如果是文本节点，将文本内容连接起来
sql.append(node.getText());
}
}
}
private static void doWhen(DefaultElement element,StringBuilder sql,Object parameter){
try {
String test = element.attribute("test").getText();
Object value = Ognl.getValue(test, parameter);
if(value instanceof Boolean&&((Boolean)value)){
for (Node node : element.content()) {
if (node instanceof DefaultElement) {
// 子节点处理
dynamicSqlDeal((DefaultElement)node,sql,parameter);
} else if (node instanceof DefaultText) {
// 如果是文本节点，将文本内容连接起来
sql.append(node.getText());
}
}
}
}catch (Exception e){
// e.printStackTrace();
}
}
private static void doOtherwise(DefaultElement element,StringBuilder sql,Object parameter){
for (Node node : element.content()) {
if (node instanceof DefaultElement) {
// 子节点处理
dynamicSqlDeal((DefaultElement)node,sql,parameter);
} else if (node instanceof DefaultText) {
// 如果是文本节点，将文本内容连接起来
sql.append(node.getText());
}
}
}
private static String parameterDeal(String s,Object parameter){
try {
if(!(s.length()>0))return "";
// 占位参数处理
List<String> parameterJ = ReUtil.findAll(XmlSqlConfig.REGX_J, s, 1);
for (int i = 0; i < parameterJ.size(); i++) {
s=s.replaceFirst(XmlSqlConfig.REGX_J,"'"+parameterTypeDeal(parameterJ.get(i),parameter)+"'");
}
// 参数处理
List<String> parameterD = ReUtil.findAll(XmlSqlConfig.REGX_D, s, 1);
for (int i = 0; i < parameterD.size(); i++) {
s=s.replaceFirst(XmlSqlConfig.REGX_D,parameterTypeDeal(parameterD.get(i),parameter).toString());
}
return s;
}catch (Exception e){
// e.printStackTrace();
}
return "";
}
private static Object parameterTypeDeal(String expression,Object parameter) throws OgnlException {
Object value = Ognl.getValue(expression, parameter);
if(value==null)return "";
if(value instanceof Date){
return XmlSqlConfig.dateFormat.format(value);
}else if(value instanceof LocalDate){
return value;
}else if(value instanceof LocalTime){
return ((LocalTime) value).format(XmlSqlConfig.ltFormatter);
}else if(value instanceof LocalDateTime){
return ((LocalDateTime) value).format(XmlSqlConfig.ldtFormatter);
}else {
return value;
}
}
}
三：进行测试
3.1 编写 UserMapper
public class UserMapper {
public static void getUlserListByMulTable(){}
public static void insertRole(){}
public static void insertRole2(){}
}
3.2 编写UserMapper.xml
这里dtd约束仍使用mybatis的，这个没必要自己写
<?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.hf.study.dao.UserMapper">
<!-- 联表查询实现分页, 如这里是根据用户ID查询用户具有的角色列表
就把联表查询得到的数据当成一张单表来看就行了，具体看UserMapper.java -->
<select id="getUlserListByMulTable" resultType="cn.hf.study.entity.RoleVo">
select sr.role_name
from sys_user su
left join sys_user_role sur on su.id = sur.user_id
left join sys_role sr on sur.role_id = sr.id
<where>
and su.id = #{userId} and date_time <= #{t1} and l_d > #{t2} and l_t >= #{t3} and ld_t < #{t4}
<if test="roleName != null and roleName != ''">
AND sr.role_name like concat('%', #{roleName}, '%')
<if test="roleTest != null and roleTest != ''">
AND sr.role_test like concat('%', ${roleTest}, '%')
</if>
</if>
<if test="role.name != null and role.name != ''">
AND sr.role_name_test like concat('%', #{role.name}, '%')
</if>
<if test="idList.size>0">
and id in
<foreach collection="idList" item="role" open="(" separator="," close=")">
#{role.id}
</foreach>
</if>
<choose>
<when test="name != null">
and name = #{name}
</when>
<when test="age != null">
and age = #{age}
</when>
<otherwise>
and otherwise=otherwise
</otherwise>
</choose>
</where>
${groupBy}
order by id desc
</select>
<select id="insertRole">
insert into t_supplier(supCode,supName) values
<foreach collection="Supplier" item="supplier" separator="," open="(" close=")">
(#{supplier.id},#{supplier.name},#{supplier.createTime})
</foreach>
</select>
<select id="insertRole2">
insert into t_supplier(supCode,supName) values
<foreach collection="Supplier" item="supplier" separator="," open="(" close=")">
#{id}
</foreach>
</select>
<select id="getUlserListByMulTable222" resultType="cn.hf.study.entity.RoleVo">
select sr.role_name
from sys_user su
left join sys_user_role sur on su.id = sur.user_id
left join sys_role sr on sur.role_id = sr.id
<where>
and su.id = #{userId}
<if test="roleName != null and roleName != ''">
AND sr.role_name like concat('%', #{roleName}, '%')
<if test="roleTest != null and roleTest != ''">
AND sr.role_test like concat('%', ${roleTest}, '%')
</if>
</if>
<if test="noStr != null and noStr != ''">
AND sr.no_str like concat('%', ${noStr}, '%')
</if>
<if test="idList.size>0">
and id in
<foreach collection="idList" item="id" open="(" separator="," close=")">
#{id}
</foreach>
</if>
<choose>
<when test="name != null">
and name = #{name}
</when>
<when test="age != null">
and age = #{age}
</when>
<otherwise>
and otherwise=otherwise
</otherwise>
</choose>
</where>
order by id desc
</select>
</mapper>
3.3 编写测试类
package cn.hf.study.test;
import cn.hf.study.dao.UserMapper;
import cn.hf.study.dbutil.XmlParseTest;
import cn.hf.study.entity.RoleVo;
import cn.hf.study.xmlSqlUtil.XmlSqlUtil;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Node;
import org.dom4j.io.SAXReader;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.format.annotation.DateTimeFormat;
import org.springframework.test.context.junit4.SpringRunner;
import java.io.File;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
@RunWith(SpringRunner.class)
@SpringBootTest
public class Test {
@org.junit.Test
public void test2(){
for (int i = 0; i < 10; i++) {
Map<String,Object> parm=new HashMap<>();
List<Object> list=new ArrayList<>();
RoleVo r1=new RoleVo("a1");
r1.setName("张三");
RoleVo r2=new RoleVo("b2");
RoleVo r3=new RoleVo("c3");
list.add("r1");
list.add("r2");
list.add("r3");
parm.put("userId",12345);
parm.put("roleName","12345");
parm.put("roleTest","角色测试");
parm.put("idList",list);
parm.put("role",r1);
parm.put("name","光头强");
parm.put("t1",new Date());
parm.put("t2",LocalDate.now());
parm.put("t3",LocalTime.now());
parm.put("t4",LocalDateTime.now());
parm.put("groupBy","group by my_age");
long start = System.currentTimeMillis();
String xmlSql = XmlSqlUtil.getXmlSql(UserMapper::getUlserListByMulTable, parm);
long end = System.currentTimeMillis();
System.out.println("----->sql结果："+xmlSql);
System.out.println("----->执行耗时："+(end-start));
}
}
@org.junit.Test
public void test3(){
Map<String,Object> parm=new HashMap<>();
List<Object> list=new ArrayList<>();
RoleVo r1=new RoleVo("a1");
r1.setName("张三");
r1.setCreateTime(new Date());
list.add(r1);
RoleVo r2=new RoleVo("a2");
r2.setName("李四");
r2.setCreateTime(new Date());
list.add(r2);
parm.put("Supplier",list);
parm.put("role",r1);
long start = System.currentTimeMillis();
String xmlSql = XmlSqlUtil.getXmlSql(UserMapper::insertRole, parm);
long end = System.currentTimeMillis();
System.out.println("----->sql结果："+xmlSql);
System.out.println("----->执行耗时："+(end-start));
}
@org.junit.Test
public void test4(){
List<Object> list=new ArrayList<>();
RoleVo r1=new RoleVo("a1");
r1.setName("张三");
list.add(r1);
RoleVo r2=new RoleVo("a2");
r2.setName("李四");
list.add(r2);
long start = System.currentTimeMillis();
String xmlSql = XmlSqlUtil.getXmlSql(UserMapper::insertRole, list);
long end = System.currentTimeMillis();
System.out.println("----->sql结果："+xmlSql);
System.out.println("----->执行耗时："+(end-start));
}
}
3.4 执行结果
3.4.1 insert操作
8393
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
