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:
I have a class called EventFilter 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;
}
}
This is referenced as a type alias in my mybatis config file:
<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:
<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 >= #{eventFilter.beginDate}::timestamp AND ev.occurred_on <= #{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:
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:
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:
This became a little longer than I wanted it to, but thanks in advance for any help or suggestions.
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.
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.
A very subtle difference, but I hope it helps someone save some time in the future.
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>
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With