Java MyBatis 'IN' 子句中的列表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/37885076/
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
Lists in MyBatis 'IN' clause
提问by bub
How can I pass an Integer List to MyBatis XML, to be used in an in clause in my MySQL query?
如何将整数列表传递给 MyBatis XML，以便在 MySQL 查询的 in 子句中使用？
I am using Java 7, MySQL 5.6 DB and MyBatis 3.0.4 with queries in a mapper-xmlfile.
我在文件中使用 Java 7、MySQL 5.6 DB 和 MyBatis 3.0.4 进行查询mapper-xml。
Presently, I am converting this list of integers to a string, and using string substitution (${}operator) to put the values in the 'IN' clause - while it works as expected, this approach leaves the parameter vulnerable to Injection.
目前，我正在将此整数列表转换为字符串，并使用字符串替换（${}运算符）将值放入“IN”子句中 - 虽然它按预期工作，但这种方法使参数容易受到注入攻击。
I have tried using a <foreach>element, but I am not able to figure out what attributes to specify.
我曾尝试使用一个<foreach>元素，但我无法弄清楚要指定哪些属性。
Below is a sample Java code :
下面是一个示例 Java 代码：
public List<Stripper> getStripperDetails(String club, List<Integer> stripperIds) {
Map<String, Object> input = new HashMap<>();
input.put("club", club);
input.put("stripperIds", stripperIds);
return stripClubMapper.getStripperDetails(input);
}
Mapper xml :
映射器 xml ：
<select id="getStripperDetails" parameterType="java.util.HashMap" resultMap="StripperMap">
SELECT STRIPPER_ID, STAGE_NAME, REAL_NAME, CLUB FROM EXOTIC_DANCERS WHERE CLUB = #{club} AND STRIPPER_ID IN
<foreach item="item" index="index" collection="stripperIds" open="(" separator="," close=")">
#{index}
</foreach>
</select>
I am not able to figure out what attributes to specify for the <foreach>element - I keep running into a NullPointerException for the value at #{index}.
我无法弄清楚要为<foreach>元素指定哪些属性- 我一直遇到 #{index} 处的值的 NullPointerException。
Can you please help me understand the correct usage of the <foreach>element?
你能帮我理解<foreach>元素的正确用法吗？
Edit :
编辑 ：
@10086 ,
@10086，
Below is the stack trace :
以下是堆栈跟踪：
org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException:
### Error querying database. Cause: java.lang.NullPointerException
### The error may involve com.stripclub.mapper.stripClubMapper.getStripperDetails-Inline
### The error occurred while setting parameters
### Cause: java.lang.NullPointerException
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:67) ~[mybatis-spring-1.0.0-RC3.jar:1.0.0-RC3]
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:345) ~[mybatis-spring-1.0.0-RC3.jar:1.0.0-RC3]
at com.sun.proxy.$Proxy208.selectList(Unknown Source) ~[na:na]
at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:193) ~[mybatis-spring-1.0.0-RC3.jar:1.0.0-RC3]
at org.apache.ibatis.binding.MapperMethod.executeForList(MapperMethod.java:85) ~[mybatis-3.0.4.jar:3.0.4]
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:65) ~[mybatis-3.0.4.jar:3.0.4]
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:38) ~[mybatis-3.0.4.jar:3.0.4]
at com.sun.proxy.$Proxy209.getTransactionIds(Unknown Source) ~[na:na]
采纳答案by N.M
The value specified by the item attribute should be used inside the foreach tag, when used with Lists. Use as below :
与列表一起使用时，应在 foreach 标记内使用 item 属性指定的值。使用如下：
<foreach item="sId" collection="stripperIds" separator="," open="(" close=")">
#{sId}
</foreach>
The index attibute is not mandatory, when using a List. Refer the MyBatis docs section for more info, or check out the DTD - http://mybatis.org/dtd/mybatis-3-mapper.dtdfor more info about the parameters :
使用 List 时，索引属性不是强制性的。有关更多信息，请参阅 MyBatis 文档部分，或查看 DTD - http://mybatis.org/dtd/mybatis-3-mapper.dtd有关参数的更多信息：
<!ELEMENT foreach (#PCDATA | include | trim | where | set | foreach | choose | if | bind)*>
<!ATTLIST foreach
collection CDATA #REQUIRED
item CDATA #IMPLIED
index CDATA #IMPLIED
open CDATA #IMPLIED
close CDATA #IMPLIED
separator CDATA #IMPLIED
>
Also, lists of objects can be accessed in foreach as below. You would typically use this for INSERT/UPDATE statements :
此外，可以在 foreach 中访问对象列表，如下所示。您通常会将其用于 INSERT/UPDATE 语句：
Sample bean :
样品豆：
public class StripperBean {
public StripperBean(int stripperID, String stripperName, String realName) {
this.stripperID = stripperID;
this.stripperName = stripperName;
this.realName = realName;
}
private int stripperID;
private String stripperName;
private String realName;
public int getStripperID() {
return stripperID;
}
public void setStripperID(int stripperID) {
this.stripperID = stripperID;
}
public String getStripperName() {
return stripperName;
}
public void setStripperName(String stripperName) {
this.stripperName = stripperName;
}
public String getRealName() {
return realName;
}
public void setRealName(String realName) {
this.realName = realName;
}
}
In your implementation :
在您的实施中：
Map<String, Object> input = new HashMap<>();
input.put("club", club);
List<StripperBean> strippers = new ArrayList<>();
strippers.add(new StripperBean(1,"Ashley", "Jean Grey"));
strippers.add(new StripperBean(2,"Candice","Diana Prince"));
strippers.add(new StripperBean(3,"Cristal","Lara Croft"));
input.put("strippers", strippers);
return stripClubMapper.saveStripperDetails(input);
In the mapper xml :
在映射器 xml 中：
<insert id="saveStripperDetails">
INSERT INTO EXOTIC_DANCERS (STRIPPER_ID, STAGE_NAME, REAL_NAME)
VALUES
<foreach item="stripper" collection="input" separator=",">
(#{stripper.stripperID},
#{stripper.stripperName},
#{stripper.realName})
</foreach>
</select>
Nice question BTW :)
好问题顺便说一句:)
回答by Sky
Your xml should be like this:
你的 xml 应该是这样的：
<foreach item="item" index="index" collection="stripperIds" open="(" separator="," close=")">
#{item}
</foreach>
When using a Map (or Collection of Map.Entry objects), index will be the key object and item will be the value object.
当使用 Map（或 Map.Entry 对象的集合）时，索引将是键对象，项目将是值对象。
You can reference herefor the details. You will have a solid understanding about the attributes.
您可以参考此处了解详细信息。您将对这些属性有一个深入的了解。
回答by starzmasta
your input is a map. so you need to resolve stripperIds from input before directly calling stripperIds.
你的输入是一张地图。所以你需要在直接调用stripperIds之前从输入中解析stripperIds。
回答by Tiago Medici
Using annotation should be easier
使用注释应该更容易
@Select({
"<script>", "select", " * ", "FROM TABLE",
"WHERE CONDITION IN " +
"<foreach item='item' index='index' collection='list' open='(' separator=',' close=')'> #{item} </foreach>" +
"</script>" })
@Results({ })
List<POJO> selectByKeys(@Param("list") List<String> ids);
