Vert.X 和 动态SQL

Vert.X SQL Client Templates 提供了一个类似 Spring JdbcTemplate 的 SQL 查询 API，方便开发者查询数据库时进行 sql参数映射 和 row结果映射。当然，它的返回结果时响应式的。

背景

在 java dao 对象中，如果每个查询都是这样：

• queryById(id)

• queryAllByNameLike(name)

• queryAllByNameAndDate(name, date)

即每个方法内，sql 语句都是确定的，如 SELECT * FROM users WHERE id = ?，这时候时很简单，写起来也很丝滑。

但是复杂查询的情况也是经常遇到的，

前端可能会请求 GET /api/videos?keyword=宅舞

也可能会请求 GET /api/videos?keyword=宅舞&author=狐狸&type=2&dateType=1&sorted=3

最后执行到 java dao 对象的方法时可能是 searchVideos(SearchRequest request)， SearchRequest 类的属性包括:

private String keyword;
private String author;
private int type;
private int dateType;
private int sorted;
// and more ...

对应与前端的请求，当 author is null 时，应该查询所有的作者，type = 0 时应该，就不应该限定 type 筛选。最终，查询的 SQL 分别是：

API	SQL
/api/videos?keyword=宅舞	SELECT * FROM videos WHERE keyword like '宅舞'
/api/videos?keyword=宅舞&author=狐狸&type=2&dateType=1&sorted=3	SELECT * FROM videos WHERE keyword like '%宅舞%' AND author like '%狐狸%' AND type =2 AND dateType = 1 ORDER BY ${3}

方法的参数不同，最终生成、执行的sql查询也不同，这在大大小小的后端管理平台上应该常见。

诚然，对于都熟悉 MyBatis 的大家来说，这并不是什么难得，MyBatis 支持动态 SQL，非常简洁易用。

遗憾的是，MyBatis 的动态SQL xml 写法只能用于 JDBC，对于 vert.x 的异步 sql client 来说，并不适用。

不过，可以借助 MyBatis 的 SQL 语句构建器，编程式的实现动态 SQL。

Vertx + Mybatis SqlBuilder

代码示例

上面 searchVideos 方法的 Vertx 动态sql实现可以是：

    public Future<List<Object>> searchVideos(SearchRequest request){

        // build dynamic sql
        SQL sql = new SQL()
            .SELECT("*")
            .FROM("videos")
            .WHERE("keyword like #{keyword}");
        
        if(request.author != null){
            sql.WHERE("author like #{author}");
        }
        if (request.type != 0) {
            sql.WHERE("type = #{type}");
        }
        if(request.dateType != 0){
            sql.WHERE("dateType = #{dateType}");
        }
        if(request.sorted != 0){
            sql.ORDER_BY(sortedColumn(request.sorted));
        }
        
        // build sql params
        Map<String, Object> params = Map.of(
            "keyword", "%" + request.keyword + "%",
            "author",  "%" + request.author + "%",
            "type", request.type,
            "dateType", request.dateType);

        // vert.x sql template query
        return SqlTemplate.forQuery(client, sql.toString())
            .mapTo(row -> new Object()) // row to pojo
            .execute(params)
            .map( rows -> StreamSupport.stream(rows.spliterator(), false).collect(Collectors.toList())); // rows to list
    }

生成SQL

举例，对于参数 SearchRequest(keyword=宅舞, author=狐狸, type=2, dateType=1, sorted=3)，生成的 sql 语句是:

SELECT *
FROM videos
WHERE (keyword like #{keyword} AND author like #{author} AND type = #{type} AND dateType = #{dateType})
ORDER BY col_3

org.apache.ibatis.jdbc.SQL 的问题

使用 Mybatis 的 org.apache.ibatis.jdbc.SQL 可能没法构造出下面的 sql 语句（我尝试了，没成功）：

SELECT *
FROM table_1
WHERE col_1 = 'value' AND ( col_2 = 'value2' OR col_3 = 'value3' )

💡 org.apache.ibatis.jdbc.SQL 继承自 AbstractSQL<T>, AbstractSQL 中包含了所有的实现

我想到的一种方式是增强 AbstractSQL 的实现，以支持在 where 语句中添加额外的括号。 不过 AbstractSQL 类中的属性和方法访问权限都是私有和缺省，所以不能写一个子类继承重写的方式了，只能把 mybatis 的代码拷贝过来，自己改一下。

AbstractSQL.java

添加下面的代码

private static final String LEFT_BRACKET = " (";
private static final String RIGHT_BRACKET = ") ";

// ...

public T LEFT_BRACKET(){
    sql().lastList.add(LEFT_BRACKET);
    return getSelf();
}

public T RIGHT_BRACKET(){
    sql().lastList.add(RIGHT_BRACKET);
    return getSelf();
}

sqlClause 方法中，if 判断中添加条件

if (i > 0 && !part.equals(AND) && !part.equals(OR) && !last.equals(AND) && !last.equals(OR)
    && !part.equals(LEFT_BRACKET) && !part.equals(RIGHT_BRACKET) 
    && !last.equals(LEFT_BRACKET) && !last.equals(RIGHT_BRACKET)
) {
    builder.append(conjunction);
}

SQL.java

继承自己的AbstractSQL，其他和mybatis的一样。

代码使用

SQL sql = new SQL()
        .SELECT("*")
        .FROM("table_1")
        .WHERE("col_1 = 'value'")
        .AND()
        .LEFT_BRACKET()
        .WHERE("col_2 = 'value2'")
        .OR()
        .WHERE("col_3 = 'value3'")
        .RIGHT_BRACKET();

System.out.println(sql.toString());

目标SQL

SELECT *
FROM table_1
WHERE col_1 = 'value' AND ( col_2 = 'value2' OR col_3 = 'value3' )

生成SQL

SELECT *
FROM table_1
WHERE (col_1 = 'value')
AND ( (col_2 = 'value2')
OR (col_3 = 'value3') )

It's work! ✌️✌️✌️