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