Reuse JPA metamodel for native queries

[Alexandre Potvin Latreille]

I'm using QueryDSL expressions to implement domain specifications. This allows me to reuse those specifications to check business rules against in-memory entities, but also in queries (e.g. find all requests that can be closed by a given user).

We write the QueryDSL expressions with the JPA metamodel (Q classes) because it allows to limit the infrastructure pollution in the domain logic (e.g. by using entity shema vs DB schema), but also because those expressions can be evaluated against in-memory entities.

However, this limits the type of queries we can write to JPAQuery, and that often doesn't cut it for complex queries (e.g. CTEs, select from select), but is also slower and more complex than a native JDBC query.

What I'm looking to accomplish is to be able to write a SQLQuery, but using the JPA metamodel. The primary reason that doesn't work is that paths metadata doesn't match the DB. 

One idea I had is to write a query translator, that converts the QueryMetadata of a SQLQuery written from Q classes and transforms all the expressions metadata to match the DB schema by resolving such schema from JPA's metadata API.

For instance, if we have the StringPath entity.someProp, we would lookup the parent path metadata, and lookup the type from JPA's metadata for that class to get the table & column names, then clone the path with the adjusted metadata.

Before digging deeper and attempt an actual implementation, I was wondering if that would be the right direction or if there could be an easier way to achieve what I need. Note that I do not want to use S classes, because then business specifications would have to be written off the DB schema instead of the entity schema, and we would also lose the ability to evaluate expressions against in-memory entities.

Thanks in advance for the help!

[Jan-Willem Gmelig Meyling]

I think the Querydsl integration for Blaze-Persistence is what you're looking for. It allows CTE's and select from select, window functions, etc. inside JPQL (and supports a wide variety of JPA vendors/versions)

Otherwise just wait for Querydsl's Hibernate 6 support, which has most of these features built into HQL.

There is also JPASQLQuery in Querydsl but I don't recommend it personally. It has quite a few quirks.

Kind regards,

Jan-Willem

Op zaterdag 3 juni 2023 om 14:53:29 UTC+2 schreef alexandre.pot...@gmail.com: