oracle 使用 mybatis 注释在 IN 子句中传递多个值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/38226742/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Pass multiple values in IN clause using mybatis annotations
提问by gla315
I am trying to pass multiple values in a SELECT QUERY consisting of an IN clause. I would like to execute the following query using mybatis:
我试图在由 IN 子句组成的 SELECT QUERY 中传递多个值。我想使用 mybatis 执行以下查询：
SELECT * FROM DBA_COMPARISON_ROW_DIF WHERE SCAN_ID in (#{childScanIDs})
where the childScanIDsvariable consists of multiple comma separated values. I am passing these values as a String consisting of all the values separated with a comma. Below is the method being used in the mapper interface.
其中childScanIDs变量由多个逗号分隔值组成。我将这些值作为由逗号分隔的所有值组成的字符串传递。下面是映射器接口中使用的方法。
@Select(getDifferencesByScanIDs)
@Results({
@Result(property="owner", column="OWNER"),
@Result(property="comparisonName", column="COMPARISON_NAME"),
@Result(property="scanID", column="SCAN_ID"),
@Result(property="localRowID", column="LOCAL_ROWID"),
@Result(property="remoteRowID", column="REMOTE_ROWID"),
@Result(property="indexValue", column="INDEX_VALUE"),
@Result(property="status", column="STATUS"),
@Result(property="lastUpdateTime", column="LAST_UPDATE_TIME")
})
List<Difference> getDifferencesByScanIDs(@Param("childScanIDs") String childScanIDs);
I am constructing the string of values this way:
我正在以这种方式构造值字符串：
String scanIDs = StringUtils.join(cmp.getChildScanIDs(), ",");
When executing the function, it is raising this exception.
执行该函数时，它会引发此异常。
### Error querying database. Cause: java.sql.SQLSyntaxErrorException: ORA-01722: invalid number
### The error may involve com.rs2.automation.soacomparison.dao.differences.DifferencesMapper.getDifferencesByScanIDs-Inline
### The error occurred while setting parameters
### Cause: java.sql.SQLSyntaxErrorException: ORA-01722: invalid number
Any help would be appreciated. I think the problem is that mybatis is not recognizing the values as separate numbers. Another solution would be to execute the query on each number but I wanted to pass all the values at once. Thanks in advance.
任何帮助，将不胜感激。我认为问题在于 mybatis 没有将这些值识别为单独的数字。另一种解决方案是对每个数字执行查询，但我想一次传递所有值。提前致谢。
回答by BackSlash
SELECT * FROM DBA_COMPARISON_ROW_DIF WHERE SCAN_ID in (#{childScanIDs})
If we imagine a list like 2, 13, 15, 16, this query will result in:
如果我们想象一个像 的列表2, 13, 15, 16，这个查询将导致：
SELECT * FROM DBA_COMPARISON_ROW_DIF WHERE SCAN_ID in ('2,13,15,16')
As you can see, the value is treated as Stringand not as list, which is not what you need.
如您所见，该值被视为String而不是列表，这不是您所需要的。
I would pass cmp.getChildScanIDs()to mybatis, without transforming it to string, and use mybatis' dynamic sql.
我将传递cmp.getChildScanIDs()给 mybatis，而不将其转换为字符串，并使用 mybatis 的动态 sql。
Edit the method as:
将方法编辑为：
List<Difference> getDifferencesByScanIDs(@Param("childScanIDs") List<Integer> childScanIDs); // or int[]
Edit the query as:
将查询编辑为：
SELECT * FROM DBA_COMPARISON_ROW_DIF WHERE SCAN_ID in
<foreach item="item" index="index" collection="childScanIDs" open="(" separator="," close=")">
#{item}
</foreach>
What it does:
它能做什么：
- <foreach>: starts a foreach loop
- item: the name of the current object inside the loop
- index: the index variable (not really needed in this case)
- collection: the name of the collection (as per your definition in the mapper interface)
- open: string to insert before the first iteration
- close: string to insert after the last iteration
- separator: string to insert between an element and another
- #{item}: prints the current item
- <foreach>：开始一个foreach循环
- item：循环内当前对象的名称
- index：索引变量（在这种情况下不是真的需要）
- 集合：集合的名称（根据您在映射器界面中的定义）
- open: 在第一次迭代之前插入的字符串
- close: 在最后一次迭代后插入的字符串
- 分隔符：在一个元素和另一个元素之间插入的字符串
- #{item}：打印当前项目
Link for reference: MyBatis Dynamic SQL
参考链接：MyBatis Dynamic SQL
回答by h3adache
This isn't very clear on the documentation. There are several ways to do this but a straight forward way is to just use OGNL. We evaluate any parameters #{xx}or ${xx}(difference explained in the docs) via OGNL.
这在文档上不是很清楚。有几种方法可以做到这一点，但一个直接的方法是只使用 OGNL。我们通过 OGNL评估任何参数#{xx}或${xx}（文档中解释的差异）。
So you can for example create
所以你可以例如创建
public class InList {
public static String build(final List<?> params) {
return Joiner.on(",").join(params);
}
}
And use it in your query as
并在您的查询中使用它作为
WHERE SCAN_ID IN (${@InList@build(childScanIds)})
Note: Using ${}opens up accidental or intentional SQL injection problems but, obviously, that is also dependent on your use case so use it carefully.
注意：使用会${}导致意外或有意的 SQL 注入问题，但显然，这也取决于您的用例，因此请谨慎使用。
