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 Native Query
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.
If your query doesn't really map to JPA entities, you can fetch ordinary, untyped Object[] representations for your database records by using the following utility method:
static List<Object[]> nativeQuery(EntityManager em, org.jooq.Query query) {
// Extract the SQL statement from the jOOQ query:
Query result = em.createNativeQuery(query.getSQL());
// 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:
static List<Object[]> nativeQuery(EntityManager em, org.jooq.Query query) {
// Extract the SQL statement from the jOOQ query:
Query result = em.createNativeQuery(query.getSQL());
// 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());
}
This way, you can construct complex, type safe queries using the jOOQ API and have your jakarta.persistence.EntityManager execute it with all the transaction semantics attached:
List<Object[]> books =
nativeQuery(em, DSL.using(configuration)
.select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, BOOK.TITLE)
.from(AUTHOR)
.join(BOOK).on(AUTHOR.ID.eq(BOOK.AUTHOR_ID))
.orderBy(BOOK.ID));
books.forEach((Object[] book) -> System.out.println(book[0] + " " + book[1] + " wrote " + book[2]));
Feedback
Do you have any feedback about this page? We'd love to hear it!