One to many relationship mapper?

[Thierry Lévèque]

Hi,

Is it possible to create a one to many relationship mapper?

I am doing an inner join request and I want to map the result to an object like

class a {

int id;

String name;

List<Value> values;

}

But if I am using a mapper class (a ResultSetMapper implementation), the method "map" is called for each row. So I cannot build my Value list.

Is there a way to do that using a mapper? Or do I have to forget about the mapper and use the result set to build the object by myself?

[Brian McCallister]

On Fri, Sep 7, 2012 at 1:15 PM, Thierry Lévèque <tlev...@gmail.com> wrote:
> Hi,
>
> Is it possible to create a one to many relationship mapper?

It is possible to create one, for one offs the easiest way is to fold
across the result set, building up the result tree. Map the result to
something convenient for the line, and then fold(...) instead of
list() or iterate() to retreive the result, processing the elements
into the form you want them.

>
> I am doing an inner join request and I want to map the result to an object
> like
> class a {
> int id;
> String name;
> List<Value> values;
> }
>
> But if I am using a mapper class (a ResultSetMapper implementation), the
> method "map" is called for each row. So I cannot build my Value list.
> Is there a way to do that using a mapper? Or do I have to forget about the
> mapper and use the result set to build the object by myself?
>

> --
>
>

[Derek Stainer]

Does anyone have some example code to demonstrate this? For some reason this is just not clicking for me.

Thanks

Derek

[Brian McCallister]

On Tue, Sep 25, 2012 at 10:16 AM, Derek Stainer <derek....@gmail.com> wrote:
> Does anyone have some example code to demonstrate this? For some reason this
> is just not clicking for me.

Here is an example of folding across an inner join, both with the
fluent api and the sql object api.

https://github.com/brianm/jdbi/blob/master/src/test/java/org/skife/jdbi/v2/docs/TestFoldToObjectGraph.java

This is example *very* explicit for each step, and not particularly
optimized. If you were going to do a lot of this kind of thing, you
might be better off using Hibernate's stateless session as it is
designed for this kind of object graph creation.

-Brian

> --
>
>

[Derek Stainer]

Thank you for getting that example up so quickly. Definitely makes way more sense now than before both conceptually and from an implementation perspective.

Derek

[Danilo Reinert]

I've just developped an alternative solution for this problem using only a Mapper.

 

Updating the example from Thierry:

 

class A {

  int id; 

  String name;

  List<B> bList;

}

 

class B {

  int id;

  String description;

}

 

Create a Mapper for A following this pattern:

 

public class AMapper implements ResultSetMapper<Empresa> {
 
 private A mA;

 

 @Override
 public A map(int index, ResultSet r, StatementContext ctx) throws SQLException {
  
  short id = r.getShort("id");

  // TODO: resolve boilerplate
  if (index == 0) {
   mA= new A();
   a.setId(id);
   a.setName(r.getString("name"));
  } else if (empresa.getId() != id) {
   mA= new A();
   a.setId(id);
   a.setName(r.getString("name"));
  }
  
  B b = new B();
  b.setId(r.getShort("b_id"));
  b.setDescription(r.getString("description"));
  
  // Avoid inserting empty bean
  if (b.getId() > 0) mA.getBList().add(b);
  
  return mA;
 }

}

 

And your DAO must return a Set (never a List!)

 

Example:

 @SqlQuery("SELECT id, name, b.id as b_id, description, FROM a left join b on b.a_id = a.id")
 @Mapper(AMapper.class)
 Set<A> findAll();

 

Note that the fact that you get a Set from the query it guarantees that you won't receive repeated objects.

I took advantage of the fact that a Set keeps the ultimate inserted instance of equal objects.

 

Of course, you must implement Equals and Hashcode in your model object.

 

Another advantage - besides the ease and clarity of implementation - from this solution is that it's not necessary to iterate twice over the result (as in the above example), but only once, representing a great processing gain.

 

I would appreciate enhancements.

 

Thanks.

[Ayush Vora]

"A Set keeps the ultimate inserted instance of equal objects" is wrong information. http://docs.oracle.com/javase/6/docs/api/java/util/Set.html#add%28E%29 clearly says, "If this set already contains the element, the call leaves the set unchanged and returns false." However, since you're simply adding adding a new 'B' element to the 'listB' in 'A' and not creating a new instance of 'A' for each new 'B' that comes for the same 'A'.

[Danilo Reinert]

Sure. I expressed myself badly. It keeps the same instance updated. Thanks for clarification.

[Manikandan K]

Hi All,

I have written small library which will be useful for mapping one to many relationship objects. It also provides more feature for default mapper.

https://github.com/rkmk/jdbi-folder

[Onezino Gabriel Moreira]

Good to see a work around it. +1