All geek questions in one place

Mybatis foreach iterate over a list of integers as part of a complex parameter of an object

I am using MyBatis 3.2.8 in a Play Framework 2.3.6 Java project. For several days I struggled with repeating the list of integers that is passed to MapBaris Mapper inside a complex parameter of an object. Here is my setup:

I have an EventFilter class in EventFilter.java:

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

}

My configuration file mybatis has an alias of type:

<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 a parameter. Then he must check whether the initial, final, nearest countries, ship and territorial waters are established. If they use them for a WHERE clause:

<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 an interface binding as follows:

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

And I call it with the helper class MybatisMapper, which generates my session as follows:

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

():

beginDate endDate . :

  • if, , , , true, .
  • , , null IN.
  • IN beginDate endDate, . , list IN, , set, "WHERE column IN()".

, Play Mybatis, /, EventFilter, . , pastebin:

, , .

+4
3

- . , , - XML- .

, :

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

, - # {eventFilter.beginDate} eventFilter.beginDate.

, , - .

+5

<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>
+2

MyBatis v. 3.4.6.

:

@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>

Works without using the index attribute.

0
source

Source: https://habr.com/ru/post/1568919/

More articles:


All Articles