4

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.

Stefan Haberl
  • 9,812
  • 7
  • 72
  • 81

2 Answers2

6

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>
Roman-Stop RU aggression in UA
  • 14,905
  • 3
  • 48
  • 53
  • So if you had global paraneters, it is not avaiable in nested select statetments. – vsingh Dec 04 '20 at 18:58
  • 1
    @vsingh this old answer was outdated and actually it is possible using the trick `h3adache` mentioned and also described in https://stackoverflow.com/questions/54026175/how-to-transfer-parameter-to-sub-query – Roman-Stop RU aggression in UA Dec 04 '20 at 19:58
  • i have my table names defined as global variable example Seelct * from ${table_1} where x =1. This variable has been populated in paramterMap but not available ib subSelect – vsingh Dec 09 '20 at 18:04
  • @vsingh Check the answer above. The parameter will not be automatically available in nested select. You need to modify the query to pass the parameter through to nested select. – Roman-Stop RU aggression in UA Dec 09 '20 at 18:26
0

If I'm reading this correctly then the same solution that I discuss here:

how to pass a constant value to nested column with mybaits association

Should work. You pass it in as a column attribute by selecting it from the selectFoosWithBars select.

h3adache
  • 1,296
  • 11
  • 18