使用mybatis的动态sql解析能力生成sql

背景： 当前项目要引入doris，因为原来的同事采用jdbcTemplate的方式，执行sql查询，所以，也就不修改采用双数据源的方式了，仍旧采用jdbcTemplate的方式执行sql语句。但是在代码中拼接sql语句实在是有点头疼，看代码看的也是云里雾里的。于是想能否通过mybatis的动态sql解析来拼接sql语句，这样jdbcTemplate只需要执行sql语句即可。
搜索资料，发现该想法可行，具体的代码如下：

package com.david.common.mybaits;

import com.google.common.collect.Lists;
import org.apache.ibatis.builder.xml.XMLMapperBuilder;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.session.Configuration;

import java.io.IOException;
import java.io.InputStream;

/**
 * @author david_van
 * @version v1.0.0
 * @date 2023/10/26 22:35
 */
public class SqlGenerator {
    public static void main(String[] args) throws IOException {
        Configuration configuration = new Configuration();
        String resource = "mapper/test.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        XMLMapperBuilder builder = new XMLMapperBuilder(inputStream, configuration, resource, configuration.getSqlFragments());
        builder.parse();
        MappedStatement mappedStatement = configuration.getMappedStatement("test2");
        SceneAndAccessReq req = new SceneAndAccessReq();
        req.setSceneId(1L);
        req.setUserIdList(Lists.newArrayList(1L, 5L));
        BoundSql boundSql = mappedStatement.getBoundSql(req);
        String sql = boundSql.getSql();
        System.out.println("sql = " + sql);
    }
}

package com.david.common.mybaits;

import lombok.Data;

import java.util.List;

/**
 * @author david_van
 * @date 2023/10/25 13:54
 */
@Data
public class SceneAndAccessReq {

    private Long sceneId;
    private List<Long> userIdList;
    private List<String> connectionIds;
    private List<Long> dbIds;
    private List<Long> schemaIdList;

}

<?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="com.david.common.mybaits.SqlGenerator">

    <select id="test2">
        SELECT t1.user_id, t1.server_id, t1.db_id, t1.schema_id, 1 AS scene_id, collect_set(t3.label_id) lableIdList
        FROM (SELECT access_date, user_id, server_id, db_id, schema_id, bitmap_union(to_bitmap(field_id)) AS field_ids
        FROM db_access_relation
        GROUP BY access_date, user_id, server_id, db_id, schema_id) t1
        LEFT JOIN dtl_access_scene t2
        ON bitmap_contains(t2.user_ids, t1.user_id) AND bitmap_contains(t2.schema_ids, t1.schema_id)
        LEFT JOIN dtl_access_label t3 ON BITMAP_HAS_ANY(t1.field_ids, t3.field_ids) = 1
        WHERE
        <if test="sceneId = 1">
            t2.scene_id IS NULL
        </if>
        <if test="sceneId != 1">
            t2.scene_id  = #{req.sceneId}
        </if>
        <if test="userIdList != null and userIdList.size() >1  ">
            AND t1.user_id IN
            <foreach collection="userIdList" open="(" close=")" separator="," index="idex">
                #{index}
            </foreach>
        </if>
        AND t1.user_id IN (1, 2)
        AND t1.server_id IN (1, 2)
        AND t1.db_id IN (1, 2)
        AND t1.schema_id IN (1471, 1472)
        GROUP BY t1.user_id, t1.server_id, t1.db_id, t1.schema_id;
    </select>
</mapper>

MyBatis的动态SQL是通过OGNL表达式来实现的。OGNL（Object-Graph Navigation Language）是一种基于Java对象图遍历的表达式语言，它可以方便地访问Java对象的属性和方法。
在MyBatis中，通过OGNL表达式可以动态地计算条件是否成立，从而确定是否将SQL片段添加到最终的SQL语句中。OGNL表达式通常嵌入在MyBatis中的动态SQL标签中，例如if、choose、when、otherwise等。

参考资料：

1. 使用mybatis的动态sql解析能力生成sql https://www.cnblogs.com/yszzu/p/11005769.html
2. MyBatis详解 - 动态SQL使用与原理 https://pdai.tech/md/framework/orm-mybatis/mybatis-y-dynamic-sql.html#关于动态sql的接口和类
3. 简单学习一下 MyBatis 动态SQL使用及原理 https://juejin.cn/post/7231921877466677285