Is there a way to nest result maps or turn off the roll-up behavior of associations and collections?

[dq]

I spent some more time thinking about what was going on with my problem yesterday and now I understand the cause, but not a (good) solution.  See bottom for example code.

First, some background.  I'm converting ibatis code to mybatis 3.1.1.  In ibatis I used to use nested resultmaps to construct and set sub-objects on objects.  In other words, if a Car object contained an Engine, I'd have a select query that would retrieve rows for all the elements of the car and then the Car resultmap had in it <result property="engine" resultMap="engineMap"> and that way the Engine object would be set on my Car object by ibatis.

In Mybatis it seems you can no longer reference resultmaps from results.  It appeared as though either associations or collections are the new way of handling this, but I've found they contain a rollup functionality that is very unwanted by me.  For example, let's say I had 2 trim levels of the same Car model that just have different Engines.  if I use a collection or association to set the Engine on the Car, I only get 1 Car object back (it is the last row returned).  

Here's some example code.  What I want to happen is to have 2 Abcd objects returned, one with an 'a' value of 1 and a Bcd containing 2,3, and 4 and the other also with an 'a' value of 1 but a Bcd containing 3, 5, and 7.  Instead all that's returned is a single Abcd with an a of 1 and a Bcd of 3,5,7.

I'm using mysql 5.5.

First the table definition and rows inserted:

CREATE TABLE `abcd` (

  `a` int NOT NULL,

  `b` int NOT NULL,

  `c` int NOT NULL,

  `d` int NOT NULL,

  PRIMARY KEY (`a`,`b`,`c`,`d`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into abcd(a,b,c,d) values (1,2,3,4);

insert into abcd(a,b,c,d) values (1,3,5,7);

Now the mapping xml:

<select fetchSize="100" resultSetType="FORWARD_ONLY" id="getAbcds" resultMap="abcd">

    select a,b,c,d from abcd

  <where>

    <if test="a != null" >

      AND a = #{a}

    </if>

   </where> 

</select>

<resultMap id="abcd" type="com.Abcd">

  <result property="a" column="a"/>

  <association property="bcd" resultMap="bcdMap"/>

</resultMap>

<resultMap id="bcdMap" type="com.Bcd">

 <result property="b" column="b"/>

 <result property="c" column="c"/>

 <result property="d" column="d"/>

</resultMap>

And here are the Java classes:

package com;

public class Abcd {

    

    private int a;

    

    private Bcd bcd;

    public int getA() {return a;}

    public void setA(int a) {this.a = a;}

    public Bcd getBcd() {return bcd;}

    public void setBcd(Bcd bcd) {this.bcd = bcd;}

    

    

}

package com;

public class Bcd {

    

    private int b,c,d;

    public int getB() {return b;}

    public void setB(int b) {this.b = b; }

    public int getC() {return c;}

    public void setC(int c) {this.c = c;}

    public int getD() {return d;}

    public void setD(int d) {this.d = d;}

    

}

And here's the unit test:

    @Test

    public void testAbcd() {

        List<Abcd> abcds= reader.getAbcds(1);

        assertEquals(2, abcds.size());

    }

Now, I can use result handlers to work around this but:

A) that seems to diminish the power and value of Mybatis

B) I have to copy all of the columns of the nested objects everywhere they're used, which creates lots of duplicate definition xml

I'm hoping there's an easy solution that I'm missing here.

Thanks!

[dq]

Oh, and here's the select java code:

   public List<Abcd> getAbcds(int a) {

       Map<String,Object> params = new HashMap<String,Object>();

       params.put("a", a);

       List<Abcd> abcds = mapper.selectList(Abcd.class, "getAbcds", params);

       return abcds;

   }

[dq]

The solution I (don't like) found is to use resultMaps with the <constructor> tag, which thanks to http://code.google.com/p/mybatis/issues/detail?id=15 can call a constructor on the encompassing object passing in the objects constructed in its constituent resultMaps.  I'd still prefer being able to do it the iBatis way so I don't have to create constructors to satisfy the DAO.

[Jeff Butler]

This probably isn't exactly what you're looking for either, but it should work:

<resultMap id="abcd" type="com.Abcd">

  <result property="a" column="a"/>

  <result property="bcd.b" column="b"/>

  <result property="bcd.c" column="c"/>

  <result property="bcd.d" column="d"/>

</resultMap>

Jeff Butler

[dq]

Interesting.  I think I prefer constructors over this...  Using <constructor>s each resultMap is responsible for constructing the object whereas this way in order for bcd.b setter to work, the default constructor of Abcd would need to create an empty Bcd, and I don't get the benefit of re-using resultMaps so every object that has a Bcd would need to include those 3 <result> lines in its resultMap.

But good to know that's possible, thanks!  It may come in very handy in a different situation.

[Jeff Butler]

I *think* MyBatis will make the Bcd object for you - so you don't need to create it in the Abcd constructor.

You are right that this is a copy/paste job if Bcd is in a lot of objects.

Jeff Butler

[Jeff Butler]

I got kind of interested in this and coded a test.  The problem with your example is that there is nothing to distinguish a unique Abcd (or car).  If you can add an attribute to your car (carId???) that makes it unique, then it will work as you expect.

Your example is so contrived that it is hard to see how this translates to a real life problem.  In coding my test I had to write some bad SQL to make it fail in the way you are reporting.  I understand you're moving from 2.x to 3.x.  This may be a good chance to revisit some of these designs and improve them.

If you're interested in looking at the tests, they are here:

http://mybatis.googlecode.com/svn/trunk/src/test/java/org/apache/ibatis/submitted/associationtest/

One test works the way you are hoping it would (with unique cars).  The other test work the way you are reporting (with non-unique cars).

[dq]

The car example was contrived, but I used it to explain the problem because the abcd example seemed to abstract to describe without confusing people.

The abcd example is very close to the situation I've got.  We have a situation where we have rows in a table with all columns comprising the primary key.  The first column is an ID that's a reference to another table and the data from all the other columns is put into a different object type.

So an Abcd has an ID and a Bcd, and another object contains a List of Abcds. 

I don't see anything wrong with that design.  I guess you could argue that we should add a primary key column and then have a unique key on the combination of columns a,b,c,d.  That's a valid critique, but I'm not sure it's valid in relation to this problem I'm having because there could be a situation where you want to perform a select on a table (or multiple tables) and only select certain columns that result in duplicate results being returned.  

[Jeff Butler]

You could file an enhancement request for adding resultMap as an option to the result mapping. That would restore the 2.x function. 

Jeff Butler

[Ikchan Sim]

Hi.

Sorry. I can not write english.

Understand me...

This solution.

- first is 1:1 relation two case

  : more good after case...(^^;;;)

- second is 1:N relation two case

  : more good after case...(^^;;;)

----------------------------------------------------------------------------------------------------------------------------------------------------------------

1. First Case 1: association (1:1 Relation) - Nested Select

                 ex) User (1) - Information(1)

                 first query execute and association query execute.

                 check under log console image file.

----------------------------------------------------------------------------------------------------------------------------------------------------------------

1. User.java

public class User {

    // memberes...

    Information information;

    // setter, getter....

}

----------------------------------------------------------------------------------------------------------------------------------------------------------------

2. Mapper.xml description relation.

----------------------------------------------------------------------------------------------------------------------------------------------------------------

3. Print Consol log.

----------------------------------------------------------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------------------------------------------------------------------

2. First Case 2: association (1:1 Relation) - Nested Select

                 ex) User (1) - Information(1)

                 join query execute and onestop set resultmap.

                 check under log console image file.

----------------------------------------------------------------------------------------------------------------------------------------------------------------

1. User.java

public class User {

    // memberes...

    Information information;

    // setter, getter....

}

----------------------------------------------------------------------------------------------------------------------------------------------------------------

2. Mapper.xml description relation.

----------------------------------------------------------------------------------------------------------------------------------------------------------------

3. Print Consol log.

----------------------------------------------------------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------------------------------------------------------------------

3. Second Case 1: collection (1:N Relation) - Nested Select

                  ex) User (1) - Information(N)

                  first query execute and collection query execute.

                  check under log console image file.

----------------------------------------------------------------------------------------------------------------------------------------------------------------

1. User.java

public class User {

    // memberes...

    List information;

    // setter, getter....

}

----------------------------------------------------------------------------------------------------------------------------------------------------------------

2. Mapper.xml description relation.

----------------------------------------------------------------------------------------------------------------------------------------------------------------

3. Print Consol log.

----------------------------------------------------------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------------------------------------------------------------------

4. Second Case 2: collection (1:N Relation) - Nested Select

                  ex) User (1) - Information(N)

                  join query execute and onestop set resultmap.

                  check under log console image file.

----------------------------------------------------------------------------------------------------------------------------------------------------------------

1. User.java

public class User {

    // memberes...

    List information;

    // setter, getter....

}

----------------------------------------------------------------------------------------------------------------------------------------------------------------

2. Mapper.xml description relation.

----------------------------------------------------------------------------------------------------------------------------------------------------------------

3. Print Consol log.

----------------------------------------------------------------------------------------------------------------------------------------------------------------

I hope you help.

 

 

==================================

IkChan SIM

Software Architeture.
E-mail:plusp...@gmail.com
H.P : 010 - 8242 - 2727
Nothing in the world can take the place of persistence.
==================================

[dq]

Unfortunately because NO is unique and part of the SelectUserAndSelectListInformationResultMap, that's why you're not seeing the roll-up behavior that I'm talking about.