Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10
Using jOOQ with JPA entities
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.
The simplest way to fetch entities via the native query API is by passing the entity class along to the native query method. The following example maps jOOQ query results to JPA entities (from the previous section). Just add the following utility method:
public static <E> List<E> nativeQuery(EntityManager em, org.jooq.Query query, Class<E> type) {
// Extract the SQL statement from the jOOQ query:
Query result = em.createNativeQuery(query.getSQL(), type);
// 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));
}
// There's an unsafe cast here, but we can be sure that we'll get the right type from JPA
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:
static List<Object[]> nativeQuery(EntityManager em, org.jooq.Query query, Class<E> type) {
// Extract the SQL statement from the jOOQ query:
Query result = em.createNativeQuery(query.getSQL(), type);
// 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());
}
With the above simple API, we're ready to write complex jOOQ queries and map their results to JPA entities:
List<JPAAuthor> authors =
nativeQuery(em,
DSL.using(configuration)
.select()
.from(AUTHOR)
.orderBy(AUTHOR.ID)
, JPAAuthor.class);
authors.forEach(author -> {
System.out.println(author.firstName + " " + author.lastName + " wrote");
books.forEach(book -> {
System.out.println(" " + book.title);
});
});
Feedback
Do you have any feedback about this page? We'd love to hear it!