ROSE框架中SQL批量操作使用

错误示例

假设第一个参数的元素个数为 3

// 实际执行的时候 会变成 3 条语句
// delete from sheet where id IN (1)
// delete from sheet where id IN (2)
// delete from sheet where id IN (3)
@SQL("delete from sheet where id IN (:list)")
Integer delete(@SQLParam("list") List<Integer> list);
  
// 错误同上
@SQL("update sheet set id=id where id IN (:list)")
Integer update(@SQLParam("list") List<Integer> list);
  
// 错误同上
@SQL("insert into sheet (id) values (:vo.id) ")
Integer insert(@SQLParam("vo") List<Sheet> list);
  
// 同样错误，会被解析成 3条语句，#for 指令名没有正常执行（被 Rose 解析后 vos 的元素个数已经变成了 1）
// insert into sheet (id) values (10)
// insert into sheet (id) values (20)
// insert into sheet (id) values (30)
@SQL("insert into sheet (id) values #for(vo in :vos){ (:vo.id) } ")
Integer insertBatch(@SQLParam("vos") List<Sheet> list);

正确示例

使用批量操作的时候，只要方法第一个参数不是 List，Collection、Set、数组、Bean 对象中的集合都是可以正常使用 IN 的

?
1
2
3
4
5
@SQL("delete from sheet where id IN (:list)")
Integer delete(@SQLParam("list") Collection<Integer> list);

@SQL("update sheet set id=id where id IN (:list)")
Integer update(@SQLParam("list") Collection<Integer> list);

注意: insert 批量操作的方式，会比较特殊

首先第一个参数不能 List

使用逗号拼接SQL的时候，最后一个逗号要手动清空（是空字符串不是 null）

for 循环的元素 vo 和 左括号之间 不能有空格，否如会报错，比如：

正确 #for(vo in :vos){ }

正确 #for(vo in :vos) { }

正确 #for (vo in :vos){ }

错误 #for( vo in :vos){ }

@SQL("insert into sheet (id) values #for(vo in :vos){ (:vo.id) ##(:vo.comma) } ")
Integer insertBatchNew(@SQLParam("vos") Collection<Sheet> list);
  
List<MyTestDAO.Sheet> sheets = Arrays.asList(
      new MyTestDAO.Sheet(10L), 
      new MyTestDAO.Sheet(20L), 
      new MyTestDAO.Sheet(30L)
);
// 最有一个 逗号修改成空字符串
sheets.get(sheets.size() - 1).setComma("");
myTestDAO.insertBatchNew(sheets);

总结注意事项

Write 类操作，如果想使用 IN 进行批量操作，DAO 方法的第一个参数类型一定不要使用 List
- Collection、Set、数组等都可以
Read 类操作，不受第一个参数是否是 List 的限制
insert 批量操作 需要对分隔符逗号 , 进行特殊处理，最后一个逗号要设置成空字符串，避免最后拼成的 SQL 多一个逗号，会导致语法错误

为什么会这样

所有以 SELECT、SHOW、 DESC、 DESCRIBE 开头的被认为是 SQLType.READ，否则是 SQLType.WRITE，也可以手动指定 @SQL 注解中的 type。

public JdbcStatement(StatementMetaData statementMetaData, SQLType sqlType, Interpreter[] interpreters, Querier querier) {
    ...
    // 如果是 WRITE 类型，判断是否是批量操作
    if (sqlType == SQLType.WRITE) {
        Class<?>[] types = method.getParameterTypes();
        Class<?> returnType = method.getReturnType();
        ...
        // 有参数并且是 List 类型
        if (types.length > 0 && List.class.isAssignableFrom(types[0])) {
            // 标记为批量操作
            this.batchUpdate = true;
            // 返回值必须是以下类型
            if (returnType != void.class
                    && returnType != int[].class
                    && returnType != Integer[].class
                    && returnType != Integer.class) {
  
                throw new IllegalArgumentException(...);
            }
        } else {
            this.batchUpdate = false;
            // 返回值必须是以下类型
            if (returnType != void.class
                    && returnType != Boolean.class
                    && !Number.class.isAssignableFrom(returnType)) {
                throw new IllegalArgumentException(...);
            }
        }
    } else {
        this.batchUpdate = false;
    }
}

如果是批量操作：

public Object execute(Map<String, Object> parameters) {
    // 如果是批量操作（即第一个参数是 List）※ 会根据第一个参数构造成多条执行语句，循环执行 ※
    if (batchUpdate) {
        // 获取第一个参数
        List<?> list = (List<?>) parameters.get(":1");
        // 构造执行语句
        StatementRuntime[] runtimes = new StatementRuntime[list.size()];
        // 循环根据第一个 List 参数循环构建 执行语句
        for (int i = 0; i < list.size(); i++) {
            Object arg = list.get(i);
            HashMap<String, Object> clone = new HashMap<String, Object>(parameters);
            // 更新执行参数
            clone.put(":1", arg);
            if (metaData.getSQLParamAt(0) != null) {
                clone.put(metaData.getSQLParamAt(0).value(), arg);
            }
            StatementRuntime runtime = new StatementRuntimeImpl(metaData, clone);
            // 对每个 SQL 进行解释处理
            for (Interpreter interpreter : interpreters) {
                interpreter.interpret(runtime);
            }
            runtimes[i] = runtime;
        }
        return querier.execute(sqlType, runtimes);
    } else {
        StatementRuntime runtime = new StatementRuntimeImpl(metaData, parameters);
        // 对每个 SQL 进行解释处理
        for (Interpreter interpreter : interpreters) {
            interpreter.interpret(runtime);
        }
        return querier.execute(sqlType, runtime);
    }
}

拦截器使用的是 net.paoding.rose.jade.statement.SystemInterpreter ，基于 apache.commons.jexl，会对 SQL 表达式进行处理

String sql = "xxxx in (:ids)";
final Map<String, List> params = Collections.singletonMap("ids", Arrays.asList(1, 2, 3));
  
ExqlPattern pattern = ExqlPatternImpl.compile(sql);
ExqlContextImpl context = new ExqlContextImpl(sql.length() + 32);
final String execute = pattern.execute(context, params);
System.out.println(execute);
System.out.println(Arrays.toString(context.getParams()));

输出：

xxxx in (?,?,?)
[1, 2, 3]

Druid 打印可执行的 SQL

druid.sql.Statement loggerName 设置为 Debug 级别
设置 Slf4jLogFilter 属性 statementExecutableSqlLogEnable 为 true，并添加到过滤器中

<bean id="druidLogFilter" class="com.alibaba.druid.filter.logging.Slf4jLogFilter">
    <!-- 开发打印可执行的 SQL 语句 -->
    <property name="statementExecutableSqlLogEnable" value="true"/>
</bean>
  
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
    ...
    <property name="proxyFilters">
        <list>
            <ref bean="druidLogFilterr"/>
        </list>
    </property>
</bean>