Creating pojos from sql join query

[dcl...@gmail.com]

Hi all,

I am using Jooq to generate POJOs from my database schema. I'm using SQLite with a lot of forced type conversions (since SQLite by design takes a minimalist view of supported data types). I have converters for things like Duration, Instant, BigDecimal, etc.

I'm running a join query across several tables and I'd like to create the appropriate POJOs for the returned records. I initially investigated using SimpleFlatMapper as it supports creating POJOs from the ResultSet. It doesn't seem to be a good fit due to my use of forced types. I emailed Arnaud and it sounded like my use case would end up being quite ugly, as I'd essentially have to manually re-implement all the type mapping that I have wired up to my Jooq POJO generation.

For reference, my join query looks like:

select * from project_type
join project_metadata pm on project_type.id = pm.type_id
join project_permission pp on project_type.id = pp.project_type_id
cross join project_requirement pr on project_type.id = pr.project_type_id

where project_type.id in ('ABC', 'XYZ');

In the SQL query I have an association of project_type to zero or more project_requirements. Each of the tables has a POJO associated with it.

So I basically need to build a DTO that looks like:

class Project {

ProjectType projectType;

ProjectMetadata projectMetadata;

ProjectPermission projectPermission;

List<ProjectRequirement> projectRequirements;

}

One way to achieve the desired result is to break the join query up into individual select statements and stitch together the DTO by hand in my code from the result set. It doesn't look like I can use the RecordMapper as the cross join results in multiple records being required to build the Project DTO. I could use a custom RecordMapper to get most of the way there and then just patch in the project requirements with setters and a secondary query at the end.

Any suggestions on the best way to achieve this in one shot?

Thanks,

Dan

[Lukas Eder]

Hi Dan,

Thanks a lot for your message.

jOOQ historically left the exercise of mapping flat SQL results to nested tree representations to third parties, offering mostly the APIs and SPIs to get this working. By APIs, I mean e.g. ResultQuery.fetchGroups(), and by SPIs, I mean e.g. RecordMapperProvider.

In the future, complex nested mappings will be possible via SQL/XML and SQL/JSON APIs, which are going to be supported starting from jOOQ 3.14. Standard APIs in all editions, and the SQL Server APIs in the commercial distributions. See e.g.:

https://blog.jooq.org/2020/05/05/using-sql-server-for-xml-and-for-json-syntax-on-other-rdbms-with-jooq/ 

This will be the recommended way to nest any data structure (and have DefaultRecordMapper map from JSON to Java using Gson or Jackson, if it's on the classpath).

Today, you can already nest to-one relationships (to-many relationships are harder) by using the DefaultRecordMapper's "dot notation". E.g. to project into your Project class, you'd have to do something like:

ctx.select(

  PROJECT_TYPE.COL1.as("projectType.col1"),

  PROJECT_TYPE.COL2.as("projectType.col2"),

  PROJECT_METADATA.COL1.as("projectMetadata.col1"),

  PROJECT_METADATA.COL2.as("projectMetadata.col2"),

  ...

).from(...)

.fetchInto(Project.class);

This is documented in https://www.jooq.org/javadoc/latest/org.jooq/org/jooq/impl/DefaultRecordMapper.html, and works just like the above linked SQL Server FOR JSON syntax. You can probably generate the various column names automatically using some utility.

I hope this helps,

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.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/34c82ec5-3080-469f-8d8b-0bcc1dace0c7n%40googlegroups.com.