Best practice for mapping a straight-forward one-to-many

[col...@plevium.ca]

I am trying to figure out the easiest way to use Jooq alongside some kind of mapping tool like ModelMapper, JPA, or some such thing. I haven't been able to find a full and proper example with a simple one-to-many.

For example, with this simple result set from a joing between users and orders. Result set:

┌─────────┬──────┬──────────┬────────────┐

│ user_id │ name │ order_id │ order_desc │

├─────────┼──────┼──────────┼────────────┤

│       1 │ john │       10 │ order1     │

│       1 │ john │       20 │ order2     │

│       2 │ mary │       30 │ order3     │

│       2 │ mary │       40 │ order4     │

└─────────┴──────┴──────────┴────────────┘

Model

User {

  Integer userId;

  String name;

  List<Order> orders;

}

Order {

  Integer orderId;

  String description;

}

I know that Jooq doesn't do this nor is/should be responsible for mapping. I just can't find an example using either ModelMapper or JPA or anything that actually does it. Any thoughts or places you can point me to with a more full example?

[Lukas Eder]

The "jOOQ" way to implement this kind of nested collection from a denormalised, joined result set would be to

- Either, use the org.jooq.Result.intoGroups() methods

- Or, use Java 8's Stream.collect() to produce the result

Here's an example of Result.intoGroups() that takes RecordMappers for extracting keys and values out of a result set:

http://www.jooq.org/javadoc/latest/org/jooq/Result.html#intoGroups-org.jooq.RecordMapper-org.jooq.RecordMapper-

I suspect, you will need this generic approach, because there is no automatic mapping between column names and model attribute names (order_desc, vs. description).

Thanks for asking, by the way. We should definitely publish a blog post about different ways to achieve this kind of mapping with jOOQ / Java 8

Hope this helps. Let me know if you have any additional questions.

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[sfish...@gmail.com]

Hi Lukas - 

I have a similar situation -- one-to-many mapping -- but I'm using raw (plain) SQL for the query, so I'd like to not have to hard-code the intoGroups() parameters, e.g. "id", "name".

Is there any way you can provide a one-to-many mapping example with Java 8's Stream.collect() function?

My classes below:

public class User {

    public int id;

    public String name;

    public List<Role> roles;

}

public class Role {

    public int id;

    public String name;

    public String function;

}

Thanks,

Sam

[Lukas Eder]

Hi Sam,

Thanks for following up. Sure there are many ways, as always. What's your SQL query so far?

Cheers,
Lukas

[Samantha Fisher]

public class User {

    public int id;

    public String name;

    public List<Role> roles;

}

public class Role {

    public int roleId;

    public String roleName;

    public String function;

}

SELECT

u.user_id id,

u.user_name name,

r.Role_Lookup_Id roleId,

r.name roleName,

r.function

FROM role_lookup r

INNER JOIN user_x_role uxr ON uxr.Role_ID = r.Role_Lookup_Id

INNER JOIN user_lookup u ON u.user_id = uxr.user_ID;

I'm just struggling to nest the Role objects in their corresponding User object.  Like I mentioned, I'd like to avoid using the intoGroups() method, because I'd have to hardcode "id" and "name" as parameters somewhere in my code.  I'd appreciate it if you could show me how to achieve the same result with Java 8's stream() and collect() methods.

Thanks!

-Sam

--
You received this message because you are subscribed to a topic in the Google Groups "jOOQ User Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/jooq-user/IiOkMBC5QH4/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jooq-user+...@googlegroups.com.

[Lukas Eder]

Hi Sam,

I see, so you have a mismatch between column names in SQL and attribute names in Java. Well, at some point, you will have to hardcode "id" and "name" values, e.g. to generate your SQL statement. So I'm not quite sure what the big impediment for using fetchGroups() or intoGroups() really is...

But anyway, here's a Java 8 stream() and collect() version of the query:

    List<User> result =

    create().fetch("... your SQL statement ...")

            .stream()

            // Produces Map<User, List<Role>> with User.roles being empty

            .collect(Collectors.groupingBy(

                r -> new User(r.getValue("ID"), r.getValue("NAME")),

                LinkedHashMap::new,

                Collectors.mapping(

                    r -> new Role(r.getValue("ROLEID"), r.getValue("ROLENAME"), r.getValue("FUNCTION")),

                    Collectors.toList()

                )

            ))

            // Moves the List<Role> Entry.value into each User.roles key

            .entrySet()

            .stream()

            .map(e -> { 

                 e.getKey().roles.addAll(e.getValue()); 

                 return e.getKey(); 

            })

            .collect(Collectors.toList());

This depends on proper equals / hashcode implementations in User and Role

Hope this helps. Let me know if you have any additional questions.

Lukas

[Jason Herr]

Lukas,

This is along the lines of something we discussed a while back.  If you had users using conventions for the naming, then you could code classes to automatically generate these relationships in Java...

for table 'one' with columns id, example and table many with columns id, one_id, other_example you'd get Java getter methods like:

int One.getId()

String One.getExample()

Array<Many> One.getMany() 

int Many.getId()

String Many.getOtherExample()

One getOne()

Setter methods could include things like: addMany(Many) or setOne(One) for One and/or Many respectively.  Does Java8 help make that a possibility?  I don't have a need for it, but using Rails on a constant basis, their ActiveRecord implementation does this very well and tersely...

Thanks,

Jason

[Lukas Eder]

Hi Jason,

I'm sorry I didn't quite get this. What are One, Many, etc.? Just example names? Or the actual convention? I'm really lost here, I'm afraid.

Lukas

[Jason Herr]

Ah, the table names are examples.  The columns are named in convention using the table name of the related table.  So, the convention these tables all have id columns and the relations are done with <table name>_id for 1..1 and 1..many.  A m..n relation tables are also doable this way:

Table: relation between table example_one and table example_two would have these columns:

id, example_one_id, example_two_id

If jOOQ could take that and build the relationships in the ExampleOne (get/setExampleTwos) and ExampleTwo(get/setExampleOnes) classes based on the convention, that would be super cool.

And if go back we simplify my one to many example using example_three and example_four as examples:

example_three would have:

id, data (where data refers to actual columns relevant to the table)

and 

example_four would have:

id, example_three_id, other_data (where other_data refers to actual columns relevant to the table)

This would see:

ExampleThree have a .Collection ExampleThree.getExampleFours() and ExampleThree.addExampleFour(ExampleFour) and a ExampleThree.setExampleFours(Collection<ExampleFour>) while Example four would have a get/setExampleThree (note the singular).

I realize now that my previous example was difficult to parse due to naming.  This MIGHT be better.  If not, I'll step back and make a real-world example.

Thanks,

J