Passing a non-column parameter in a MyBatis ResultMap to a nested select

I’ve a One-to-Many relationship in my domain model where I basically want to read Foos and a filtered set of Bars with one MyBatis select statement using a nested select for the Bars.

To explain: My domain model classes look more or less like this (the real domain model is more complex of course, but my problem boils down to this):

public class Foo {
   private String name;
   private Set<Bar> bars;
   // getters and setters omitted
}

public class Bar {
   private String color;
   // getters and setters omitted
}

Now I want to read Foos with a certain name with Bars of a certain color:

public interface FooRepository {
  public List<Foo> selectFoosWithBars(String name, String color);
}

The relevant parts of my MyBatis XML Mapper files look like:

<select id="selectFoosWithBars" resultMap="fooResult">
   SELECT f.id f_id, f.name f_name FROM foos f WHERE f.name = #{name}
</select>

<select id="selectBars" resultMap="barResult">
   SELECT b.color b_color FROM bars b
   JOIN foos f ON (b.f_id = #{id})
   WHERE b.color = #{color}
</select>

<resultMap id="fooResult" type="Foo">
   <result property="name" column="f_name">
   <collection property="bars" select="selectBars" column="f_id" />
</resultMap>

<resultMap id="barResult" type="Bar">
   <result property="color" column="b_color" />
</resultMap>

All fine, except the #{color} parameter within the selectBars SELECT. I can use the color parameter within the first selectFoosWithBars without any problem, but how can I pass the parameter to the nested selectBars?

Note, that I’m currently trying to performance tune the SQL and simply joining the bars and foos tables in the first SELECT is unfortunately not an option.

Answer

This may be achieved by using a trick with an artificial column in the main query and configure column parameter appropriately.

Here is relevant part of the column attribute documentation:

The column name from the database, or the aliased column label that holds the value that will be passed to the nested statement as an input parameter.

Add artificial column with the color value to the main query:

<select id="selectFoosWithBars" resultMap="fooResult">
   SELECT f.id f_id, f.name f_name, #{color} f_color
   FROM foos f WHERE f.name = #{name}
</select>

And then use f_color column to pass parameter to selectBars:

<select id="selectBars" resultMap="barResult">
   SELECT b.color b_color FROM bars b
   JOIN foos f ON (b.f_id = #{id})
   WHERE b.color = #{color}
</select>

<resultMap id="fooResult" type="Foo">
   <result property="name" column="f_name">
   <collection property="bars" select="selectBars" column="{id=f_id,color=f_color}" />
</resultMap>

Advertisement

Answer