Build pojo from multiple sql queries results using jooq

user_1058106 0
Viewed 36

Usual way is to map a single query result to a class e.g.

List<MyPojo> p = ctx.fetch("select * from table...").into(MyPojo.class);

But what if I want to build a pojo based on several queries results? Note that some of pojo fields are objects.

class MyPojo{
  String field1 - query1
  int field2 - query1
  List<SomeClass> field3 - query2
  SomeClass2 - query3
  ...
}

Will really appreciate any thoughts.

jooq postgresql java
1 Answers

You could obviously map your code manually by running several queries and assembling the results into your result type individually (there's no automatic way to do this in jOOQ). But much better than that, just use

An example would be:

List<MyPojo> result =
ctx.select(
      T.FIELD1,
      T.FIELD2,
      multiset(
        select(U.COL1, U.COL2)
        .from(U)
        .where(U.T_ID.eq(T.ID))
      ).convertFrom(r -> r.map(Records.mapping(SomeClass::new))),
      field(
        select(row(X.COL1, X.COL2).mapping(SomeClass2::new))
        .from(X)
        .where(X.T_ID.eq(T.ID))
      ))
   .from(T)
   .fetch(Records.mapping(MyPojo::new));

All of this is completely type safe and not using any reflection. All you need for those constructor references to work is add the constructor to your MyPojo, or make it a record, instead:

record MyPojo(
  String field1,
  int field2,
  List<SomeClass> field3,
  SomeClass2 field4
) {}
user_5217990
Related