java Mybatis foreach 迭代复杂对象参数中的整数列表

Mybatis foreach iteration over list of integers within a complex object parameter

提问by Alex Klibisz

I am using MyBatis 3.2.8 in a Play Framework 2.3.6 Java project. I've been struggling for several days with iterating over a list of integers that is passed to a MyBatis mapper within a complex object parameter. Here is my setup:

我在 Play Framework 2.3.6 Java 项目中使用 MyBatis 3.2.8。几天来，我一直在努力迭代传递给复杂对象参数中的 MyBatis 映射器的整数列表。这是我的设置：

I have a class called EventFilter in EventFilter.java:

我在 EventFilter.java 中有一个名为 EventFilter 的类：

public class EventFilter {
private String beginDate;
private String endDate;
private List<Integer> closestCountry;
private List<Integer> territorialWaterStatus;
private List<Integer> vesselCountry;
private String closestCountryInClause;
private String territorialWaterStatusInClause;
private String vesselCountryInClause;

public EventFilter() { }

public EventFilter(JsonNode jsonNode){
    this.beginDate = jsonNode.get("beginDate").asText();
    this.endDate = jsonNode.get("endDate").asText();
    this.closestCountry = JsonHelper.arrayNodeToIntegerList((ArrayNode) jsonNode.get("closestCountry"));
    this.territorialWaterStatus = JsonHelper.arrayNodeToIntegerList((ArrayNode) jsonNode.get("territorialWaterStatus"));
    this.vesselCountry = JsonHelper.arrayNodeToIntegerList((ArrayNode) jsonNode.get("vesselCountry"));
}

public String getBeginDate() {
    return beginDate;
}

public void setBeginDate(String beginDate) {
    this.beginDate = beginDate;
}

public String getEndDate() {
    return endDate;
}

public void setEndDate(String endDate) {
    this.endDate = endDate;
}

public List<Integer> getTerritorialWaterStatus() {
    if(this.territorialWaterStatus.size() > 0) {
        return territorialWaterStatus;
    } else {
        return null;
    }
}

public void setTerritorialWaterStatus(List<Integer> territorialWaterStatus) {
    this.territorialWaterStatus = territorialWaterStatus;
}

public List<Integer> getClosestCountry() {
    if(this.closestCountry.size() > 0) {
        return closestCountry;
    } else {
        return null;
    }
}

public void setClosestCountry(List<Integer> closestCountry) {
    this.closestCountry = closestCountry;
}

public List<Integer> getVesselCountry() {
    if(this.vesselCountry.size() > 0) {
        return vesselCountry;
    } else {
        return null;
    }
}

public void setVesselCountry(List<Integer> vesselCountry) {
    this.vesselCountry = vesselCountry;
}

}

}

This is referenced as a type alias in my mybatis config file:

这在我的 mybatis 配置文件中被引用为类型别名：

<configuration>

<typeAliases>
    <typeAlias type="models.Event" alias="Event"/>
    <typeAlias type="models.EventFilter" alias="EventFilter"/>
    <typeAlias type="models.Country" alias="Country"/>
</typeAliases>

<environments default="development">
    <environment id="development">
        <transactionManager type="JDBC"/>
        <dataSource type="POOLED">
            <property name="driver" value="org.postgresql.Driver"/>
            <property name="url" value="jdbc:postgresql://localhost:5432/mpmap"/>
            <property name="username" value="postgres"/>
            <property name="password" value="dbpw"/>
        </dataSource>
    </environment>
</environments>

<mappers>
    <mapper resource="EventMapper.xml"/>
</mappers>
</configuration>

I have a mapper that takes an EventFilter object as its parameter. It should then check if the beginDate, endDate, closestCountry, vesselCountry, and territorialWaterStatus are set. If they are it uses them for the WHERE clause:

我有一个将 EventFilter 对象作为参数的映射器。然后它应该检查是否设置了 beginDate、endDate、closestCountry、vesselCountry 和 TerritoryWaterStatus。如果是，则将它们用于 WHERE 子句：

<select id="getEventsWithFilter" resultType="Event" resultMap="EventResult">
    SELECT ev.id, to_char(ev.occurred_on, 'YYYY-MM-DD') AS occurred_on_date,
        to_char(ev.occurred_on, 'HH24:MI:SS') AS occurred_on_time,
        ST_X(ev.location) AS longitude, ST_Y(ev.location) AS latitude,
        COALESCE(co01.name, 'Unspecified') AS closest_country,
        COALESCE(co02.name, 'Unspecified') AS territorial_water_status,
        COALESCE(co03.name, 'Unspecified') AS vessel_flag_country
    FROM event AS ev
    LEFT JOIN country AS co01
        ON co01.cow_id = ev.location_closest_country_id
    LEFT JOIN country AS co02
        ON co02.cow_id = ev.location_water_status_country_id
    LEFT JOIN country AS co03
        ON co03.cow_id = ev.vessel_flag_country_id
    <where>
        <if test="#{eventFilter.beginDate} != null and #{eventFilter.endDate} != null">
            ev.occurred_on &gt;= #{eventFilter.beginDate}::timestamp AND ev.occurred_on &lt;= #{eventFilter.endDate}::timestamp
        </if>
        <if test="#{eventFilter.closestCountry} != null">
            AND ev.location_closest_country_id IN
            <foreach item="id" index="index" collection="#{eventFilter.closestCountry}" open="(" separator="," close=")">
                #{id}
            </foreach>
        </if>
        <if test="#{eventFilter.territorialWaterStatus} != null">
            AND ev.location_water_status_country_id IN
            <foreach item="id" index="index" collection="#{eventFilter.territorialWaterStatus}" open="(" separator="," close=")">
                #{id}
            </foreach>
        </if>
        <if test="#{eventFilter.vesselCountry} != null">
            AND ev.vessel_flag_country_id IN
            <foreach item="id" index="index" collection="#{eventFilter.vesselCountry}" open="(" separator="," close=")">
                #{id}
            </foreach>
        </if>
    </where>
    ORDER BY ev.occurred_on ASC;
</select>

I have the mapper linked into an interface as follows:

我将映射器链接到一个界面，如下所示：

    public List<Event> getEventsWithFilter(@Param("eventFilter") EventFilter eventFilter);

And I am calling it with a MybatisMapper helper class that generates my session as follows:

我用 MybatisMapper 助手类调用它，该类生成我的会话，如下所示：

public static List<Event> getEvents(EventFilter eventFilter) {
    MybatisMapper mapper = new MybatisMapper();
    SqlSession session = mapper.getSession();
    EventMapper eventMapper = session.getMapper(EventMapper.class);

    List<Event> events;

    List<Integer> li = eventFilter.getClosestCountry();

    try {
        events = eventMapper.getEventsWithFilter(eventFilter);
    } finally {
        session.close();
    }

    return events;
}

The Problem(s):

问题：

The beginDate and endDate work completely fine by themselves. But I'm having the following problems with the integer lists:

beginDate 和 endDate 本身完全可以正常工作。但是我在整数列表中遇到了以下问题：

1. The if statement checking if the list is null seems to be getting ignored, or evaluating to true when it should be false.
2. The integer lists are appearing to be passed as null into the IN clauses.
3. If I comment out the integer list IN clauses, and just to beginDate and endDate, it works completely fine. However, if I leave the integer list IN clauses, the query doesn't fail, but it returns an empty set, as if to say "WHERE column IN ()".

1. 检查列表是否为空的 if 语句似乎被忽略了，或者在它应该为假时评估为真。
2. 整数列表似乎作为 null 传递到 IN 子句中。
3. 如果我注释掉整数列表 IN 子句，并且只注释到 beginDate 和 endDate，它完全可以正常工作。但是，如果我保留整数列表 IN 子句，查询不会失败，而是返回一个空集，好像在说“WHERE 列 IN ()”。

Here is the console logging printed by Play and Mybatis when the mapper/query are executed, along with the EventFilter printing its contents. They're a little lengthy so I put them in pastebin:

这是执行映射器/查询时 Play 和 Mybatis 打印的控制台日志记录，以及打印其内容的 EventFilter。它们有点长，所以我把它们放在 pastebin 中：

• When I pass three countries for each of the three integer lists: http://pastebin.com/aWcXyikh
• When I pass only the beginDate and endDate: http://pastebin.com/CeCv256g

• 当我为三个整数列表中的每一个传递三个国家时：http: //pastebin.com/aWcXyikh
• 当我只传递 beginDate 和 endDate 时：http: //pastebin.com/CeCv256g

This became a little longer than I wanted it to, but thanks in advance for any help or suggestions.

这变得比我想要的要长一点，但在此先感谢您的任何帮助或建议。

回答by Alex Klibisz

I finally got it working. The only thing I ended up having to change was addressing the parameter lists in my XML mapper without the surrounding curly braces.

我终于让它工作了。我最终不得不改变的唯一一件事是在我的 XML 映射器中解决参数列表，而没有周围的花括号。

So instead of:

所以而不是：

 <if test="#{eventFilter.closestCountry} != null">
        AND ev.location_closest_country_id IN
        <foreach item="id" index="index" collection="#{eventFilter.closestCountry}" open="(" separator="," close=")">
            #{id}
        </foreach>
    </if>

It should be:

它应该是：

<if test="eventFilter.closestCountry != null">
            AND ev.location_closest_country_id IN
            <foreach item="id" index="index" collection="eventFilter.closestCountry" open="(" separator="," close=")">
                #{id}
            </foreach>
        </if>

Which is odd, because addressing strings in the passed object works with either method - i.e. #{eventFilter.beginDate} is the same as eventFilter.beginDate.

这很奇怪，因为在传递的对象中寻址字符串可以使用任一方法 - 即 #{eventFilter.beginDate} 与 eventFilter.beginDate 相同。

A very subtle difference, but I hope it helps someone save some time in the future.

一个非常微妙的区别，但我希望它可以帮助某人在未来节省一些时间。

回答by Seven4X

try this

试试这个

<if test="eventFilter.closestCountry != null">
        AND ev.location_closest_country_id IN
        <foreach item="id" index="index" collection="eventFilter.closestCountry" open="(" separator="," close=")">
            ${id}
        </foreach>
    </if>

回答by dani_bs

I'm using MyBatis v. 3.4.6.

我正在使用 MyBatis v. 3.4.6。

My case:

我的情况：

@Mapper
public interface CorsoMapper {
    List<CorsoEntity> elenco(Filtro filtro);
}


public class Filtro implements Serializable {
    private static final long serialVersionUID = -8357822764110641974L;

    private String codice;
    private List<String> codici;

    public String getCodice() {
        return codice;
    }

    public void setCodice(String codice) {
        this.codice = codice;
    }

    public List<String> getCodici() {
        return codici;
    }

    public void setCodici(List<String> codici) {
        this.codici = codici;
    }
}

SQL:

查询语句：

<select id="elencoCorsi" parameterType="it.blabla.Filtro" resultMap="corsoMap">
    AND CODICI IN
    <foreach item="c" collection="codici" open="(" separator="," close=")">
       #{c}
    </foreach>
</select>

It works without using "index" attribute.

它在不使用“索引”属性的情况下工作。