java Mybatis foreach 集合是一个map-parameter中的列表

Mybatis foreach collection is a list in a map-parameter

提问by Ji Yeon Lee

I'm using mybatis 3.2.8 version.

我使用的是 mybatis 3.2.8 版本。

Mapper.java

映射器

List<BuddyId> findBuddyIds(HashMap<String, Object> map);

xml

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

控制器.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)

查询数据库时出错。原因：java.sql.SQLException：无法设置参数

该错误可能存在于文件 [/Users/jylee/Documents/workspace/.metadata/.plugins/org.eclipse.wst.server.core/tmp0/wtpwebapps/services/WEB-INF/classes/com/ohinc/services/ seerid/mybatis/SeerIdMapper.xml]

错误可能涉及 com.ohinc.services.seerid.mybatis.SeerIdMapper.findBuddyIds-Inline

设置参数时发生错误

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

原因：java.sql.SQLException：无法设置参数；SQL [] 的未分类 SQLException；SQL 状态 [null]; 错误代码

[0]; 无法设置参数；嵌套异常是 java.sql.SQLException: 无法设置参数]，根本原因是 org.mariadb.jdbc.internal.common.query.IllegalParameterException: No '?' 在 org.mariadb.jdbc.internal.common.query.MySQLParameterizedQuery.setParameter(MySQLParameterizedQuery.java:103) 的那个位置

I don't know how to solve this problem.

我不知道如何解决这个问题。

Please help me.

请帮我。

回答by Tianmu

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

我发现了这个问题，也许可以解决这个问题。你的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>

you should be use $ replace # in foreach.

您应该在 foreach 中使用 $ replace #。

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

带有 IN 条件的 MyBatis 问题 <foreach with List inside a Map

回答by Spartz Tao

<select id="findBuddyIds" parameterType="map" resultMap="BuddyIdResultMap">
select * 
from seerid.buddyIds
where id REGEXP
<trim prefix="(" prefixOverrides=" " suffixOverrides=" " suffix=")">
    <foreach collection="idSplits" index="index" item="item" open="'" separator="|" close="'">^${item}</foreach>
</trim>
</select>

I'm using mybatis 3.4.2 version.

我使用的是 mybatis 3.4.2 版本。

回答by Pierre

You're right, this should work. You should be able to pass in a map with multiple key="parameterName" and value="parameterValue". And one of those map entries could be a list, like you're doing (that map entry has key="idSplits" and value=yourArrayList). some ideas:

你是对的，这应该有效。您应该能够传入具有多个 key="parameterName" 和 value="parameterValue" 的映射。这些映射条目之一可能是一个列表，就像您正在做的那样（该映射条目具有 key="idSplits" 和 value=yourArrayList）。一些想法：

1. Could you provide the definition of your BuddyIdResultMap (it has to be defined in your mybatis mappings to define which columns go to which data member and what type of objects get returned).

2. Can you check to be sure that the "idSplits" list is not empty or has funny elements in it?

   map.put("idSplits", new ArrayList(Arrays.asList(idSplits)));

3. To unblock you, you could pass in a POJO (Plain Old Java Object) with one data member for each parameter you need to pass to the mapper. And one data member for the list. Like this:

1. 你能提供你的 BuddyIdResultMap 的定义吗（它必须在你的 mybatis 映射中定义，以定义哪些列去哪个数据成员以及返回什么类型的对象）。

2. 您能否检查以确保“idSplits”列表不为空或其中包含有趣的元素？

   map.put("idSplits", new ArrayList(Arrays.asList(idSplits)));

3. 要解除对您的阻止，您可以传入一个 POJO（Plain Old Java Object），并为需要传递给映射器的每个参数传递一个数据成员。和列表的一个数据成员。像这样：

BuddyDTO.java:

好友DTO.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

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

控制器.java

BuddyDTO dto = new BuddyDTO();
dto.setIdSplits( new ArrayList<String>(Arrays.asList(idSplits))) );
buddyScanResult = seerIdDAO.findBuddyIds(dto);