Available in versions: Dev (3.19) | Latest (3.18) | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10 | 3.9
Using jOOQ with JPA EntityResult
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The goal of these sections is to describe how to do this, if you have strong reasons to do so. Mostly, however, you're better off executing your queries directly with jOOQ, especially if you want to use jOOQ's more advanced features. This blog post illustrates various reason why it's better to execute queries directly with jOOQ.
While JPA specifies how the mapping should be implemented (e.g. using jakarta.persistence.SqlResultSetMapping), there are no limitations regarding how you want to generate the SQL statement. The following, simple example shows how you can produce JPABook and JPAAuthor entities (from the previous section) from a jOOQ-generated SQL statement.
In order to do so, we'll need to specify the SqlResultSetMapping. This can be done on any entity, and in this case, we're using jakarta.persistence.EntityResult:
@SqlResultSetMapping(
name = "bookmapping",
entities = {
@EntityResult(
entityClass = JPABook.class,
fields = {
@FieldResult(name = "id", column = "b_id"),
@FieldResult(name = "title", column = "b_title"),
@FieldResult(name = "author", column = "b_author_id")
}
),
@EntityResult(
entityClass = JPAAuthor.class,
fields = {
@FieldResult(name = "id", column = "a_id"),
@FieldResult(name = "firstName", column = "a_first_name"),
@FieldResult(name = "lastName", column = "a_last_name")
}
)
}
)
Note how we need to map between:
- FieldResult.name(), which corresponds to the entity's attribute name
- FieldResult.column(), which corresponds to the SQL result's column name
With the above boilerplate in place, we can now fetch entities using jOOQ and JPA:
public static <E> List<E> nativeQuery(EntityManager em, org.jooq.Query query, String resultSetMapping) {
// Extract the SQL statement from the jOOQ query:
Query result = em.createNativeQuery(query.getSQL(), resultSetMapping);
// Extract the bind values from the jOOQ query:
List<Object> values = query.getBindValues();
for (int i = 0; i < values.size(); i++) {
result.setParameter(i + 1, values.get(i));
}
return result.getResultList();
}
Note, if you're using custom data types or bindings, make sure to take those into account as well. E.g. as follows:
public static <E> List<E> nativeQuery(EntityManager em, org.jooq.Query query, String resultSetMapping) {
// Extract the SQL statement from the jOOQ query:
Query result = em.createNativeQuery(query.getSQL(), resultSetMapping);
// Extract the bind values from the jOOQ query:
int i = 1;
for (Param<?> param : query.getParams().values())
if (!param.isInline())
result.setParameter(i++, convertToDatabaseType(param));
return result.getResultList();
}
static <T> Object convertToDatabaseType(Param<T> param) {
return param.getBinding().converter().to(param.getValue());
}
Using the above API
Now that we have everything setup, we can use the above API to run a jOOQ query to fetch JPA entities like this:
List<Object[]> result =
nativeQuery(em,
DSL.using(configuration
.select(
AUTHOR.ID.as("a_id"),
AUTHOR.FIRST_NAME.as("a_first_name"),
AUTHOR.LAST_NAME.as("a_last_name"),
BOOK.ID.as("b_id"),
BOOK.AUTHOR_ID.as("b_author_id"),
BOOK.TITLE.as("b_title")
)
.from(AUTHOR)
.join(BOOK).on(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
.orderBy(BOOK.ID)),
"bookmapping" // The name of the SqlResultSetMapping
);
result.forEach((Object[] entities) -> {
JPAAuthor author = (JPAAuthor) entities[1];
JPABook book = (JPABook) entities[0];
System.out.println(author.firstName + " " + author.lastName + " wrote " + book.title);
});
The entities are now ready to be modified and persisted again.
Caveats:
- We have to reference the result set mapping by name (a String) - there is no type safety involved here
- We don't know the type contained in the resulting
List- there is a potential forClassCastException - The results are in fact a list of
Object[], with the individual entities listed in the array, which need explicit casting
Feedback
Do you have any feedback about this page? We'd love to hear it!