Java – Mybatis foreach collection is a list in a map-parameter

javajdbcmybatis

I'm using mybatis 3.2.8 version.

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);

It will receive the following error.

Error querying database. Cause: java.sql.SQLException: Could not set parameter

The error may exist in 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 involve com.ohinc.services.seerid.mybatis.SeerIdMapper.findBuddyIds-Inline

The error occurred while setting parameters

SQL: select * from seerid.buddyIds where id REGEXP ' ? | ? '

Cause: java.sql.SQLException: Could not set parameter ; uncategorized SQLException for SQL []; SQL state [null]; error code

[0]; Could not set parameter; nested exception is
java.sql.SQLException: Could not set parameter] with root cause
org.mariadb.jdbc.internal.common.query.IllegalParameterException: No
'?' on that position at
org.mariadb.jdbc.internal.common.query.MySQLParameterizedQuery.setParameter(MySQLParameterizedQuery.java:103)

I don't know how to solve this problem.

Please help me.

Best Answer

I found this problem, may be can solve this question. you xml have a mistake:

<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 be use $ replace # in foreach.

MyBatis Issue with IN Condition <foreach with List inside a Map

Related Topic