Mybatis foreach collection is a list in a map parameter
I am using mybatis version 3.2.8.
Mapper.java
List<BuddyId> findBuddyIds(HashMap<String, Object> map);
XML
<select id="findBuddyIds" parameterType="map" resultMap="BuddyIdResultMap">
select *
from seerid.buddyIds
where id REGEXP
<foreach collection="idSplits" item="item" index="index" open="'" close="'" separator="|">
#{item}
</foreach>
</select>
Controller.java
HashMap<String,Object> map = new HashMap<String,Object>();
map.put("idSplits", new ArrayList<String>(Arrays.asList(idSplits)));
buddyScanResult = seerIdDAO.findBuddyIds(map);
He will get the following error.
Database query error. Reason: java.sql.SQLException: Failed to set parameter
The error may exist in the file [/Users/jylee/Documents/workspace/.metadata/.plugins/org.eclipse.wst.server.core/tmp0/wtpwebapps/services/WEB-INF/classes/com/ohinc/services/seerid /mybatis/SeerIdMapper.xml]
The error may include com.ohinc.services.seerid.mybatis.SeerIdMapper.findBuddyIds-Inline
Parameter setting error
SQL: select * from seerid.buddyIds where id is REGEXP '? |?
Reason: java.sql.SQLException: Failed to set parameter; unclassified SQLException for SQL []; SQL state [null]; error code
[0]; Failed to set parameter; nested exception java.sql.SQLException: Could not set parameter] with root cause org.mariadb.jdbc.internal.common.query.IllegalParameterException: No '?' at this position in org.mariadb.jdbc.internal.common.query.MySQLParameterizedQuery.setParameter (MySQLParameterizedQuery.java:103)
I don't know how to fix this problem.
Please help me.
source to share
You are right, it should work. You should be able to pass a map with multiple keywords = "parameterName" and value = "parameterValue". And one of those entries in the map could be a list like you do (this map entry has a key = "idSplits" and value = yourArrayList). some ideas:
-
Could you please provide a definition for your BuddyIdResultMap (it must be defined in your mybatis mappings to determine which columns go to which data member and which object type is returned).
-
Can you verify that the "idSplits" list is not empty or has fun elements in it?
map.put ("idSplits", new ArrayList (Arrays.asList (idSplits)));
-
To unblock you, you can pass a POJO (Plain Old Java Object) with one data item for each parameter that needs to be passed to the configurator. And one item of data for the list. Like this:
BuddyDTO.java:
public class BuddyDTO implements Serializable {
protected String someDataMember = null;
protected List<String> idSplits= null;
// insert getter and setters here for data members and idSplits
}
Mapper.java
List<BuddyId> findBuddyIds(BuddyDTO dto);
XML
<select id="findBuddyIds" parameterType="BuddyDTO" resultMap="BuddyIdResultMap">
select *
from seerid.buddyIds -- I'm guessing seerid is your schema name?
where id REGEXP
<foreach collection="idSplits" item="item" index="index" open="'" close="'" separator="|">
#{item}
</foreach>
</select>
Controller.java
BuddyDTO dto = new BuddyDTO();
dto.setIdSplits( new ArrayList<String>(Arrays.asList(idSplits))) );
buddyScanResult = seerIdDAO.findBuddyIds(dto);
source to share
I found this problem, maybe can solve this issue. you xml have error:
<select id="findBuddyIds" parameterType="map" resultMap="BuddyIdResultMap">
select *
from seerid.buddyIds
where id REGEXP
<foreach collection="idSplits" item="item" index="index" open="'" close="'" separator="|">
${item}
</foreach>
</select>
you should use $ replace # in foreach.
MyBatis problem with IN <foreach clause with list inside map
source to share