All versions: 3.11 | 3.10 | 3.9 | 3.8 | 3.7 | Development versions: 3.12 | Unsupported versions: 3.6

While JPA specifies how the mapping should be implemented (e.g. using javax.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 javax.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:

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 = 0;
    for (Param<?> param : query.getParams().values()) {
        result.setParameter(i + 1, convertToDatabaseType(param));
        i++;
    }

    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 for ClassCastException
  • The results are in fact a list of Object[], with the individual entities listed in the array, which need explicit casting
The jOOQ Logo