​使用MyBatis解析出XML结构中的sql语句

1. 功能背景

项目上有一个需求，需要根据用户手动编写的带xml的<if>、<choose>等标签的sql脚本，解析（转换）出可执行的sql脚本进行查询。

PS：以下代码实现了自定义表达式替换参数值的功能，如不需要删除代码即可。

MyBatis版本为3.5.3

2. 具体实现

2.1. 原始脚本

PS：SQL比较复杂，请复制到本地文本编辑器中查看

<choose>
<when test='@cmbStatClass@=="1"'>
 SELECT A.TAKE_DEPT_ID, B.DEPT_NAME 科室,A.SEL_MONEY 零售金额,A.BILL_COUNT 单据张数
FROM 
(
        SELECT A.TAKE_DEPT_ID,SUM(A.SEL_MONEY) SEL_MONEY,SUM(A.BILL_COUNT) BILL_COUNT FROM 
        (
                SELECT A.PATIENT_DEPT_ID TAKE_DEPT_ID, COUNT(1) BILL_COUNT ,SUM(A.SUM_MONEY) SEL_MONEY
                FROM DRG.DRG_ROOM_DISPENSING A 
                WHERE 1=1 AND A.org_code='NHFY' 
                AND (A.DEPT_ID='@DeptId@' or '@DeptId@'='-1'  )
                     AND A.DISPENSING_DATE<![CDATA[>=]]>to_date('@dtpStartDate@','YYYY-MM-DD HH24:MI:SS') 
                     AND A.DISPENSING_DATE<![CDATA[<=]]>to_date('@dtpEndDate@','YYYY-MM-DD HH24:MI:SS')
                                AND ( '@txtTakeDept@' IS NULL OR A.PATIENT_DEPT_ID='@txtTakeDept@' )
                                AND 
                                (
                                        '@cmbTakeType@'='0'   
                                        OR (  '@cmbTakeType@'='1' AND A.BIZ_TYPE_ID='017'  )
                                        OR (  '@cmbTakeType@'='3' AND A.BIZ_TYPE_ID='020'  )
                                )
                GROUP BY A.PATIENT_DEPT_ID
                UNION ALL
                SELECT A.TAKE_DEPT_ID, COUNT(1) BILL_COUNT,SUM(A.SUM_SEL_MONEY) SEL_MONEY
                FROM DRG.DRG_ROOM_GROUP_DISPENSING A                 
                WHERE 1=1 AND A.org_code='NHFY' 
                 AND (A.DEPT_ID='@DeptId@' or '@DeptId@'='-1'  )
                      AND A.DISPENSING_DATE<![CDATA[>=]]>to_date('@dtpStartDate@','YYYY-MM-DD HH24:MI:SS') 
                      AND A.DISPENSING_DATE<![CDATA[<=]]>to_date('@dtpEndDate@','YYYY-MM-DD HH24:MI:SS')
                               AND ( '@txtTakeDept@' IS NULL OR A.TAKE_DEPT_ID='@txtTakeDept@' or '@txtTakeDept@'='')
                                  AND 
                                (
                                        '@cmbTakeType@'='0'   OR '@cmbTakeType@'='2'
                                )
                GROUP BY A.TAKE_DEPT_ID 
        ) A GROUP BY A.TAKE_DEPT_ID
) A 
LEFT JOIN BASEDATA.BAS_ORG_DEPARTMENT B ON A.TAKE_DEPT_ID=B.ID
ORDER BY A.TAKE_DEPT_ID

 
</when>
<when  test='@cmbStatClass@=="2"'>
 SELECT A.TAKE_DEPT_ID, B.DEPT_NAME 科室,A.SEL_MONEY 零售金额,A.BILL_COUNT 单据张数
FROM 
(
        SELECT A.TAKE_DEPT_ID,SUM(A.SEL_MONEY) SEL_MONEY,SUM(A.BILL_COUNT) BILL_COUNT FROM 
        (
                SELECT A.PATIENT_DEPT_ID TAKE_DEPT_ID, COUNT(1) BILL_COUNT ,SUM(A.SUM_MONEY) SEL_MONEY
                FROM DRG.DRG_ROOM_DISPENSING A 
                WHERE 1=1 AND A.org_code='NHFY' 
                AND (A.DEPT_ID='@DeptId@' or '@DeptId@'='-1') and a.id in(select a1.dispensing_id from DRG.DRG_ROOM_DISPENSING_LIST a1 where a1.Order_Type<![CDATA[<>]]>'101')
                     AND A.DISPENSING_DATE<![CDATA[>=]]>to_date('@dtpStartDate@','YYYY-MM-DD HH24:MI:SS') 
                     AND A.DISPENSING_DATE<![CDATA[<=]]>to_date('@dtpEndDate@','YYYY-MM-DD HH24:MI:SS')
                                AND ( '@txtTakeDept@' IS NULL OR A.PATIENT_DEPT_ID='@txtTakeDept@' or '@txtTakeDept@'='')
                                AND 
                                (
                                        '@cmbTakeType@'='0'   
                                        OR (  '@cmbTakeType@'='1' AND A.BIZ_TYPE_ID='017'  )
                                        OR (  '@cmbTakeType@'='3' AND A.BIZ_TYPE_ID='020'  )
                                )
                GROUP BY A.PATIENT_DEPT_ID
                UNION ALL
                SELECT A.TAKE_DEPT_ID, COUNT(1) BILL_COUNT,SUM(A.SUM_SEL_MONEY) SEL_MONEY
                FROM DRG.DRG_ROOM_GROUP_DISPENSING A                 
                WHERE 1=1 AND A.org_code='NHFY' 
                 AND A.DEPT_ID='@DeptId@' 
                      AND A.DISPENSING_DATE<![CDATA[>=]]>to_date('@dtpStartDate@','YYYY-MM-DD HH24:MI:SS') 
                      AND A.DISPENSING_DATE<![CDATA[<=]]>to_date('@dtpEndDate@','YYYY-MM-DD HH24:MI:SS')
                               AND ( '@txtTakeDept@' IS NULL OR A.TAKE_DEPT_ID='@txtTakeDept@' or '@txtTakeDept@'='')
                                  AND 
                                (
                                        '@cmbTakeType@'='0'   OR '@cmbTakeType@'='2'
                                )
                GROUP BY A.TAKE_DEPT_ID 
        ) A GROUP BY A.TAKE_DEPT_ID
) A 
LEFT JOIN BASEDATA.BAS_ORG_DEPARTMENT B ON A.TAKE_DEPT_ID=B.ID
ORDER BY A.TAKE_DEPT_ID

 
</when>

<when test='@cmbStatClass@=="3"'>
SELECT A.TAKE_DEPT_ID, B.DEPT_NAME 科室,A.SEL_MONEY 零售金额,A.BILL_COUNT 单据张数
FROM 
(
        SELECT A.TAKE_DEPT_ID,SUM(A.SEL_MONEY) SEL_MONEY,SUM(A.BILL_COUNT) BILL_COUNT FROM 
        (
                SELECT A.PATIENT_DEPT_ID TAKE_DEPT_ID, COUNT(1) BILL_COUNT ,SUM(A.SUM_MONEY) SEL_MONEY
                FROM DRG.DRG_ROOM_DISPENSING A 
                WHERE 1=1 AND A.org_code='NHFY' 
                AND (A.DEPT_ID='@DeptId@' or '@DeptId@'='-1')  and a.id in(select a1.dispensing_id from DRG.DRG_ROOM_DISPENSING_LIST a1 where a1.Order_Type='101')
                     AND A.DISPENSING_DATE<![CDATA[>=]]>to_date('@dtpStartDate@','YYYY-MM-DD HH24:MI:SS') 
                     AND A.DISPENSING_DATE<![CDATA[<=]]>to_date('@dtpEndDate@','YYYY-MM-DD HH24:MI:SS')
                                AND ( '@txtTakeDept@' IS NULL OR A.PATIENT_DEPT_ID='@txtTakeDept@' or '@txtTakeDept@'='')
                                AND 
                                (
                                        '@cmbTakeType@'='0'   
                                        OR (  '@cmbTakeType@'='1' AND A.BIZ_TYPE_ID='017'  )
                                        OR (  '@cmbTakeType@'='3' AND A.BIZ_TYPE_ID='020'  )
                                )
                GROUP BY A.PATIENT_DEPT_ID
                UNION ALL
                SELECT A.TAKE_DEPT_ID, COUNT(1) BILL_COUNT,SUM(A.SUM_SEL_MONEY) SEL_MONEY
                FROM DRG.DRG_ROOM_GROUP_DISPENSING A                 
                WHERE 1=1 AND A.org_code='NHFY' 
                 AND (A.DEPT_ID='@DeptId@' or '@DeptId@'='-1')
                      AND A.DISPENSING_DATE<![CDATA[>=]]>to_date('@dtpStartDate@','YYYY-MM-DD HH24:MI:SS') 
                      AND A.DISPENSING_DATE<![CDATA[<=]]>to_date('@dtpEndDate@','YYYY-MM-DD HH24:MI:SS')
                               AND ( '@txtTakeDept@' IS NULL OR A.TAKE_DEPT_ID='@txtTakeDept@' or '@txtTakeDept@'='')
                                  AND 
                                (
                                        '@cmbTakeType@'='0'   OR '@cmbTakeType@'='2'
                                )
                GROUP BY A.TAKE_DEPT_ID 
        ) A GROUP BY A.TAKE_DEPT_ID
) A 
LEFT JOIN BASEDATA.BAS_ORG_DEPARTMENT B ON A.TAKE_DEPT_ID=B.ID
ORDER BY A.TAKE_DEPT_ID
</when>
</choose>

2.2. 功能代码

2.2.1. 分步思路

1. 利用正则表达式，找到并替换<if>、<when>标签中的test属性内的@符为空字符串。兼容了用户自定义输入时，可能随机会有多余的空字符的问题

标准的书写格式
<if test='@aaa@'......
非标准的书写格式
<if test = '@aaa@'......

// 使用正则替换if或when标签test中带@的内容
String pattern = "<(if|when) +test *= *' *@" + key + "@[^>]+/?>";
// 创建 Pattern 对象
Pattern r = Pattern.compile(pattern);
// 创建 matcher 对象
Matcher m = r.matcher(sql);
// 有则替换为参数key
while (m.find()) {
	sql = sql.replaceFirst(pattern, m.group(0).replaceAll("@", ""));
	m = r.matcher(sql);
}

2.通过参数key，找到并替换非标签自定义参数为参数值。如where条件内的参数

String permission = "@" + key + "@";
// 替换非if标签@@内容
value = null == value ? "" : value;
if (sql.contains(permission)) {
	sql = sql.replaceAll(permission, value);
}

3.通过MyBatis擦除<if>、<choose>标签，转换为jdbc可执行的完全SQL

// 追加select的xml标签
sql = "<select>" + sql + "</select>";
// 实例化解析XML对象
XPathParser parser = new XPathParser(sql, false, null, new XMLMapperEntityResolver());
// 定义从select节点开始
XNode context = parser.evalNode("/select");
Configuration configuration = new Configuration();
configuration.setDatabaseId("");
XMLScriptBuilder xmlScriptBuilder = new XMLScriptBuilder(configuration, context);
SqlSource sqlSource = xmlScriptBuilder.parseScriptNode();
// 获取转换xml标签后的sql对象
BoundSql bs = sqlSource.getBoundSql(paramsJson);
// 获取查询sql
sql = bs.getSql();

2.2.2. 整体代码

// 引入的包
import java.util.*;

import com.alibaba.fastjson.JSONObject;

import org.apache.ibatis.builder.xml.XMLMapperEntityResolver;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.SqlSource;
import org.apache.ibatis.parsing.XNode;
import org.apache.ibatis.parsing.XPathParser;
import org.apache.ibatis.scripting.xmltags.XMLScriptBuilder;
import org.apache.ibatis.session.Configuration;


......


    /**
     * 根据sql和参数，替换sql内带@符号的表达式和xml标签
     *
     * @author: xxx
     * @date: 2022-05-10 11:09:21
     * @param params 参数
     * @return
     */
    public static SqlCondition replaceSqlPermission(SqlParameter params) {
        String sql = params.getSql();
        List<ReportDbParam> paramList = params.getParamList();
        // 转换paramList为JSONObject
        JSONObject paramsJson = new JSONObject();
        if (null != paramList && !paramList.isEmpty()) {
            // 循环替换@表达式为参数值
            for (ReportDbParam param : paramList) {
                String key = param.getParamName();
                String value = param.getParamValue();
                if ("system".equals(param.getWidgetType())) {
                    ThpsUser thpsUser = LoginUserUtil.getThpsUser();
                    Map map = JSON.parseObject(JSON.toJSONString(thpsUser), Map.class);
                    value = String.valueOf(map.get(value));
                } else {
                    // 使用正则替换if或when标签test中带@的内容
                    String pattern = "<(if|when) +test *= *' *@" + key + "@[^>]+/?>";
                    // 创建 Pattern 对象
                    Pattern r = Pattern.compile(pattern);
                    // 创建 matcher 对象
                    Matcher m = r.matcher(sql);
                    // 有则替换为参数key
                    while (m.find()) {
                        sql = sql.replaceFirst(pattern, m.group(0).replaceAll("@", ""));
                        m = r.matcher(sql);
                    }

                    String permission = "@" + key + "@";
                    // 替换非if标签@@内容
                    value = null == value ? "" : value;
                    if (sql.contains(permission)) {
                        sql = sql.replaceAll(permission, value);
                    }
                }
                paramsJson.put(key, value);
            }
            // 替换XML标签
            if (sql.contains("<if") || sql.contains("<choose") || sql.contains("<![CDATA")) {
                // 追加select的xml标签
                sql = "<select>" + sql + "</select>";
                // 实例化解析XML对象
                XPathParser parser = new XPathParser(sql, false, null, new XMLMapperEntityResolver());
                XNode context = parser.evalNode("/select");
                Configuration configuration = new Configuration();
                configuration.setDatabaseId("");
                XMLScriptBuilder xmlScriptBuilder = new XMLScriptBuilder(configuration, context);
                SqlSource sqlSource = xmlScriptBuilder.parseScriptNode();
                // 获取转换xml标签后的sql对象
                BoundSql bs = sqlSource.getBoundSql(paramsJson);
                // 获取查询sql
                sql = bs.getSql();
            }
        }

        // 最后处理双@替换为空字符串
        sql = sql.replaceAll("@[^@]+/?@", "");
        // 封装并返回
        SqlCondition sqlCondition = new SqlCondition();
        sqlCondition.setSql(sql);

        return sqlCondition;
    }

2.3. 传入的参数

2.3.1. 参数值

params.getParamList();// 这是一个对象集合

[ReportDbParam(id=29adfb4d7230b0e1379113a677cef79c, reportDbId=50629c373c087bde0909211333d93a7d, paramName=cmbStatClass, paramTxt=cmbStatClass, paramValue=1, widgetType=String, orderNum=1, createBy=A33056EAE9704EBD92B91DC834EBEA55, createTime=Wed May 25 09:24:24 CST 2022, updateBy=null, updateTime=null), ReportDbParam(id=47e97e72d4206a8b0ebbcda606dc6b46, reportDbId=50629c373c087bde0909211333d93a7d, paramName=txtTakeDept, paramTxt=领药科室, paramValue=, widgetType=String, orderNum=5, createBy=A33056EAE9704EBD92B91DC834EBEA55, createTime=Wed May 25 09:24:24 CST 2022, updateBy=null, updateTime=null), ReportDbParam(id=60b902d5e4a7c202ae651eca15e44a0f, reportDbId=50629c373c087bde0909211333d93a7d, paramName=DeptId, paramTxt=DeptId, paramValue=190, widgetType=String, orderNum=2, createBy=A33056EAE9704EBD92B91DC834EBEA55, createTime=Wed May 25 09:24:24 CST 2022, updateBy=null, updateTime=null), ReportDbParam(id=7807055da87ad0ef8d9a93f753cea6f0, reportDbId=50629c373c087bde0909211333d93a7d, paramName=cmbTakeType, paramTxt=类别, paramValue=0, widgetType=String, orderNum=6, createBy=A33056EAE9704EBD92B91DC834EBEA55, createTime=Wed May 25 09:24:24 CST 2022, updateBy=null, updateTime=null), ReportDbParam(id=79e402c93a19a2c24c7750774ff805ca, reportDbId=50629c373c087bde0909211333d93a7d, paramName=dtpStartDate, paramTxt=开始时间, paramValue=2020-05-01 00:00:00, widgetType=Date, orderNum=3, createBy=A33056EAE9704EBD92B91DC834EBEA55, createTime=Wed May 25 09:24:24 CST 2022, updateBy=null, updateTime=null), ReportDbParam(id=abf8a205c652012ea953076e64271f68, reportDbId=50629c373c087bde0909211333d93a7d, paramName=dtpEndDate, paramTxt=结束时间, paramValue=2022-05-25 09:12:55, widgetType=Date, orderNum=4, createBy=A33056EAE9704EBD92B91DC834EBEA55, createTime=Wed May 25 09:24:24 CST 2022, updateBy=null, updateTime=null)]

2.3.2. 参数对象结构

package com.xxx.modules.chunjun.desreport.entity;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.fasterxml.jackson.annotation.JsonFormat;
import java.io.Serializable;
import java.util.Date;

import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import org.springframework.format.annotation.DateTimeFormat;

import javax.validation.constraints.Max;
import javax.validation.constraints.Min;

@Data
public class ReportDbParam implements Serializable {
    private static final long serialVersionUID = 1L;
    @SuppressWarnings("deprecation")
    @ApiModelProperty(value = "主键id")
    private String id;

    @ApiModelProperty(value = "数据集id")
    private String reportDbId;

    @ApiModelProperty(value = "参数名", required = true)
    private String paramName;

    @ApiModelProperty(value = "参数文本")
    private String paramTxt;

    @ApiModelProperty(value = "参数值")
    private String paramValue;

    @ApiModelProperty(value = "字段类型")
    private String widgetType;

    @ApiModelProperty(value = "排序", required = true)
    @Min(message = "最小值不能小于1", value = 1)
    @Max(message = "最大值不能超过999", value = 999)
    private Integer orderNum;

    @ApiModelProperty(value = "创建人")
    private String createBy;

    @JsonFormat(timezone = "GMT+8", pattern = "yyyy-MM-dd HH:mm:ss")
    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    @ApiModelProperty(value = "创建时间")
    private Date createTime;

    @ApiModelProperty(value = "更新人")
    private String updateBy;

    @JsonFormat(timezone = "GMT+8", pattern = "yyyy-MM-dd HH:mm:ss")
    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    @ApiModelProperty(value = "更新时间")
    private Date updateTime;

}

2.4. 转换后的sql

SELECT A.TAKE_DEPT_ID, B.DEPT_NAME 科室,A.SEL_MONEY 零售金额,A.BILL_COUNT 单据张数
FROM 
(
        SELECT A.TAKE_DEPT_ID,SUM(A.SEL_MONEY) SEL_MONEY,SUM(A.BILL_COUNT) BILL_COUNT FROM 
        (
                SELECT A.PATIENT_DEPT_ID TAKE_DEPT_ID, COUNT(1) BILL_COUNT ,SUM(A.SUM_MONEY) SEL_MONEY
                FROM DRG.DRG_ROOM_DISPENSING A 
                WHERE 1=1 AND A.org_code='NHFY' 
                AND (A.DEPT_ID='190' or '190'='-1'  )
                     AND A.DISPENSING_DATE >= to_date('2020-05-01 00:00:00','YYYY-MM-DD HH24:MI:SS') 
                     AND A.DISPENSING_DATE <= to_date('2022-05-25 09:12:55','YYYY-MM-DD HH24:MI:SS')
                                AND ( '' IS NULL OR A.PATIENT_DEPT_ID='' )
                                AND 
                                (
                                        '0'='0'   
                                        OR (  '0'='1' AND A.BIZ_TYPE_ID='017'  )
                                        OR (  '0'='3' AND A.BIZ_TYPE_ID='020'  )
                                )
                GROUP BY A.PATIENT_DEPT_ID
                UNION ALL
                SELECT A.TAKE_DEPT_ID, COUNT(1) BILL_COUNT,SUM(A.SUM_SEL_MONEY) SEL_MONEY
                FROM DRG.DRG_ROOM_GROUP_DISPENSING A                 
                WHERE 1=1 AND A.org_code='NHFY' 
                 AND (A.DEPT_ID='190' or '190'='-1'  )
                      AND A.DISPENSING_DATE >= to_date('2020-05-01 00:00:00','YYYY-MM-DD HH24:MI:SS') 
                      AND A.DISPENSING_DATE <= to_date('2022-05-25 09:12:55','YYYY-MM-DD HH24:MI:SS')
                               AND ( '' IS NULL OR A.TAKE_DEPT_ID='' or ''='')
                                  AND 
                                (
                                        '0'='0'   OR '0'='2'
                                )
                GROUP BY A.TAKE_DEPT_ID 
        ) A GROUP BY A.TAKE_DEPT_ID
) A 
LEFT JOIN BASEDATA.BAS_ORG_DEPARTMENT B ON A.TAKE_DEPT_ID=B.ID
ORDER BY A.TAKE_DEPT_ID