Lists in MyBatis 'IN' clause
Solution 1
The value specified by the item attribute should be used inside the foreach tag, when used with Lists. Use as below :
<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.dtd for more info about the parameters :
<!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 :
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 :
<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 :)
Solution 2
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);
Solution 3
Your xml should be like this:
<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.
You can reference here for the details. You will have a solid understanding about the attributes.
bub
Updated on July 09, 2022Comments
-
bub almost 2 years
How can I pass an Integer List to MyBatis XML, to be used in an in clause in my MySQL query?
I am using Java 7, MySQL 5.6 DB and MyBatis 3.0.4 with queries in a
mapper-xml
file.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.I have tried using a
<foreach>
element, but I am not able to figure out what attributes to specify.Below is a sample Java code :
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 :
<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}.Can you please help me understand the correct usage of the
<foreach>
element?Edit :
@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]