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
的@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>