jOOQ 3.10 Supports JPA AttributeConverter

One of the cooler hidden features in jOOQ is the JPADatabase, which allows for reverse engineering a pre-existing set of JPA-annotated entities to generate jOOQ code.

For instance, you could write these entities here:

public class Actor {

    @GeneratedValue(strategy = IDENTITY)
    public Integer actorId;

    public String firstName;

    public String lastName;

    @ManyToMany(fetch = LAZY, mappedBy = "actors", 
        cascade = CascadeType.ALL)
    public Set<Film> films = new HashSet<>();

    public Actor(String firstName, String lastName) {
        this.firstName = firstName;
        this.lastName = lastName;

public class Film {

    @GeneratedValue(strategy = IDENTITY)
    public Integer filmId;

    public String title;

    @Column(name = "RELEASE_YEAR")
    @Convert(converter = YearConverter.class)
    public Year releaseYear;

    @ManyToMany(fetch = LAZY, cascade = CascadeType.ALL)
    public Set<Actor> actors = new HashSet<>();

    public Film(String title, Year releaseYear) {
        this.title = title;
        this.releaseYear = releaseYear;

// Imagine also a Language entity here...

(Just a simple example. Let’s not discuss the caveats of @ManyToMany mapping).

For more info, the full example can be found on Github:

Now observe the fact that we’ve gone through all the trouble of mapping the database type INT for the RELEASE_YEAR column to the cool JSR-310 java.time.Year type for convenience. This has been done using a JPA 2.1 AttributeConverter, which simply looks like this:

public class YearConverter 
implements AttributeConverter<Year, Integer> {

    public Integer convertToDatabaseColumn(Year attribute) {
        return attribute == null ? null : attribute.getValue();

    public Year convertToEntityAttribute(Integer dbData) {
        return dbData == null ? null : Year.of(dbData);

Using jOOQ’s JPADatabase

Now, the JPADatabase in jOOQ allows you to simply configure the input entities (e.g. their package names) and generate jOOQ code from it. This works behind the scenes with this algorithm:

  • Spring is used to discover all the annotated entities on the classpath
  • Hibernate is used to generate an in-memory H2 database from those entities
  • jOOQ is used to reverse-engineer this H2 database again to generate jOOQ code

This works pretty well for most use-cases as the JPA annotated entities are already very vendor-agnostic and do not provide access to many vendor-specific features. We can thus perfectly easily write the following kind of query with jOOQ:

(more info about the awesome FILTER clause here)

In this example, we’re also using the LANGUAGE table, which we omitted in the article. The output of the above query is something along the lines of:

|FIRSTNAME|LASTNAME |Total|English|German|min |max |
|Daryl    |Hannah   |    1|      1|     0|2015|2015|
|David    |Carradine|    1|      1|     0|2015|2015|
|Michael  |Angarano |    1|      0|     1|2017|2017|
|Reece    |Thompson |    1|      0|     1|2017|2017|
|Uma      |Thurman  |    2|      1|     1|2015|2017|

As we can see, this is a very suitable combination of jOOQ and JPA. JPA was used to insert the data through JPA’s useful object graph persistence capabilities, whereas jOOQ is used for reporting on the same tables.

Now, since we already wrote this nice AttributeConverter, we certainly want to apply it also to the jOOQ query and get the java.time.Year data type also in jOOQ, without any additional effort.

jOOQ 3.10 auto conversion

In jOOQ 3.10, we don’t have to do anything anymore. The existing JPA converter will automatically mapped to a jOOQ converter as the generated jOOQ code reads:

// Don't worry about this generated code
public final TableField<FilmRecord, Year> RELEASE_YEAR = 
    createField("RELEASE_YEAR", org.jooq.impl.SQLDataType.INTEGER, 
        this, "", new JPAConverter(YearConverter.class));

… which leads to the previous jOOQ query now returning a type:

Record7<String, String, Integer, Integer, Integer, Year, Year>

Luckily, this was rather easy to implement as the Hibernate meta model allows for navigating the binding between entities and tables very conveniently as described in this article here:

More similar features are coming up in jOOQ 3.11, e.g. when we look into reverse engineering JPA @Embedded types as well. See

If you want to run this example, do check out our jOOQ/JPA example on GitHub:

“What Java ORM do You Prefer, and Why?” – SQL of Course!

Catchy headline, yes. But check out this Stack Overflow question by user Mike:

(I’m duplicating it here on the blog, as it might be deleted soon)

It’s a pretty open ended question. I’ll be starting out a new project and am looking at different ORMs to integrate with database access.

Do you have any favorites? Are there any you would advise staying clear of?

And the top voted answer (164 points by David Crawshaw is: “Just use SQL”:

I have stopped using ORMs.

The reason is not any great flaw in the concept. Hibernate works well. Instead, I have found that queries have low overhead and I can fit lots of complex logic into large SQL queries, and shift a lot of my processing into the database.

So consider just using the JDBC package.

The second answer (66 points by user simon) is, again: “Just use SQL”:

None, because having an ORM takes too much control away with small benefits. The time savings gained are easily blown away when you have to debug abnormalities resulting from the use of the ORM. Furthermore, ORMs discourage developers from learning SQL and how relational databases work and using this for their benefit.

The third answer (51 points by myself) is saying, once more: “Use SQL” (and use it with jOOQ).

The best way to write SQL in Java

Only the fourth answer (46 points by Abdullah Jibaly) mentiones Hibernate, the most popular ORM in the Java ecosystem.

The truth is, as we’ve shown numerous times on this blog: Hibernate/JPA/ORMs are good tools to get rid of boring (and complex) CRUD. But that’s just boilerplate logic with little value to your business logic. The interesting stuff – the queries, the batch and bulk processing, the analytics, the reporting, they’re all best done with SQL. Here are some additional articles:

Stay tuned as we’re entering an era of programming where object orientation fades, and functional / declarative programming makes data processing extremely easy and lean again.

Turn Around. Don’t Use JPA’s loadgraph and fetchgraph Hints. Use SQL Instead.

Thorben Janssen (also known from our jOOQ Tuesdays series) recently published an interesting wrap-up of what’s possible with Hibernate / JPA query hints. The full article can be seen here:

11 JPA and Hibernate query hints every developer should know

Some JPA hints aren’t really hints, they’re really full-blown query specifications, just like JPQL queries, or SQL queries. They tell JPA how to fetch your entities. Let’s look at javax.persistence.loadgraph and javax.persistence.fetchgraph.

The example given in Oracle’s Java EE 7 tutorial is this:

You have a default entity graph, which is hard-wired to your entity class using annotations (or XML in the old days):

public class EmailMessage implements Serializable {
    String messageId;
    String subject;
    String body;
    String sender;
    @OneToMany(mappedBy="message", fetch=LAZY)
    Set<EmailAttachment> attachments;

Notice how the above entity graph mixes formal graph meta information (@Entity, @Id, @OneToMany, …) with query default information (fetch=EAGER, fetch=LAZY).


Now, the problem with the above is that these defaults are hard-wired and cannot be changed for ad-hoc usage (thank you annotations). Remember, SQL is an ad-hoc query language with all of benefits that derive from this ad-hoc-ness. You can materialise new result sets whose type was not previously known on the fly. Excellent tool for reporting, but also for ordinary data processing, because it is so easy to change a SQL query if you have new requirements, and if you’re using languages like PL/SQL or libraries like jOOQ, you can even do that in a type safe, precompiled way.

Unlike in JPA, whose annotations are not “ad-hoc”, just like SQL’s DDL is not “ad-hoc”. Can you ever switch from EAGER to LAZY? Or from LAZY to EAGER? Without breaking half of your application? Truth is: You don’t know!

The problem is: choosing EAGER will prematurely materialise your entire entity graph (even if you needed only an E-Mail message’s subject and body), resulting in too much database traffic (see also “EAGER fetching is a code smell” by Vlad Mihalcea). Choosing LAZY will result in N+1 problems in case you really do need to materialise the relationship, because for each parent (“1”), you have to individually fetch each child (“N”) lazily, later on.

Do SQL people suffer from N+1?

As a SQL person, this sounds ridiculous to me. Imagine specifying in your foreign key constraint whether you always want to auto-fetch your relationship…

ALTER TABLE email_attachment
ADD CONSTRAINT fk_email_attachment_email
FOREIGN KEY (message_id)
REFERENCES email_message(message_id)

Of course you don’t do that. The point of normalising your schema is to have the data sit there without duplicating it. That’s it. It is the query language’s responsibility to help you decide whether you want to materialise the relationship or not. For instance, trivially:

-- Materialise the relationship
FROM email_message m
JOIN email_attachment a
USING (message_id)

-- Don't materialise the relationship
FROM email_message m

Duh, right?

Are JOINs really that hard to type?

Now, obviously, typing all these joins all the time can be tedious, and that’s where JPA seems to offer help. Unfortunately, it doesn’t help, because otherwise, we wouldn’t have tons of performance problems due to the eternal EAGER vs LAZY discussion. It is a GOOD THING to think about your individual joins every time because if you don’t, you will structurally neglect your performance (as if SQL performance wasn’t hard enough already) and you’ll notice this only in production, because on your developer machine, you don’t have the problem. Why?

Works on my machine ಠ_ಠ

One way of solving this with JPA is to use the JOIN FETCH syntax in JPQL (which is essentially the same thing as what you would be doing in SQL, so you don’t win anything over SQL except for automatic mapping. See also this example where the query is run with jOOQ and the mapping is done with JPA).

Another way of solving this with JPA is to use these javax.persistence.fetchgraph or javax.persistence.loadgraph hints, but that’s even worse. Check out the code that is needed in Oracle’s Java EE 7 tutorial just to indicate that you want this and that column / attribute from a given entity:

EntityGraph<EmailMessage> eg = em.createEntityGraph(EmailMessage.class);
Properties props = new Properties();
props.put("javax.persistence.fetchgraph", eg);
EmailMessage message = em.find(EmailMessage.class, id, props);

With this graph, you can now indicate to your JPA implementation that in fact you don’t really want to get just a single E-Mail message, you also want all the specified JOINs to be materialised (interestingly, the example doesn’t do that, though).

You can pass this graph specification also to a JPA Query that does a bit more complex stuff than just fetching a single tuple by ID – but then, why not just use JPA’s query language to express that explicitly? Why use a hint?

Let me ask you again, why not just specify a sophisticated query language? Let’s call that language… SQL? The above example is solved trivially as such:

FROM email_message
WHERE message_id = :id

That’s not too much typing, is it? You know exactly what’s going on, everyone can read this, it’s simple to debug, and you don’t need to wrestle with first and second level caches because all the caching that is really needed is buffer caching in your database (i.e. caching frequent data in database memory to prevent excessive I/O).

The cognitive overhead of getting everything right and tuning stuff in JPA is so big compared to writing just simple SQL statements (and don’t forget, you may know why you put that hint, but your coworker may so easily overlook it!), let me ask you: Are you very sure you actually profit from JPA (you really need entity graph persistence, including caching)? Or are you wrestling the above just because JPA is your default choice?

When JPA is good

JPA (and its implementations) is excellent when you have the object graph persistence problem. This means: When you do need to load a big graph, modify it in your client application, possibly in a distributed and cached and long-conversational manner, and then store the whole graph back into the database without having to wrestle with locking, caching, lost updates, and all sorts of other problems, then JPA does help you. A lot. You don’t want to do that with SQL.

Do note that the key aspect here is storing the graph back into the database. 80% of JPA’s value is in writing stuff, not reading stuff.

But frankly, you probably don’t have this problem. You’re doing mostly simple CRUD and probably complex querying. SQL is the best language for that. And Java 8 functional programming idioms help you do the mapping, easily.


Don’t use loadgraph and fetchgraph hints. Chances are very low that you’re really on a good track. Chances are very high that migrating off to SQL will greatly simplify your application.

A Beginner’s Guide to Using Java EE with jOOQ

Java EE ships with its own persistence API: JPA. JPA is most powerful when you want to map your RDBMS entities (tables / relations) to Java entities (classes), mostly following a 1:1 mapping strategy. The idea behind this is that often, business logic isn’t really set-oriented as relational algebra or SQL, but record-oriented, meaning that business rules and business logic is applied to individual records.

In other words, when SQL and relational algebra is about values (tuples), JPA is about identity and state (of individual records). And this is where JPA shines, because:

Life is too short to write CRUD with SQL

But as Gavin King always said:


RDBMS are not just about CRUD

Gavin King was well aware of the OLAP hype that was going on at the time he started working on Hibernate, the most popular JPA implementation. Business intelligence, or data science as it is called nowadays, relies on much more advanced functionality than simple CRUD – functionality that has never been targeted by the JPA specification, or by its implementations.

In fact, you don’t necessarily have to do OLAP to benefit from native SQL, simpler use-cases in more ordinary OLTP environments can appear as well, such as

  • Reporting
  • Batch and bulk data processing
  • Query with complex business rules

While JPA offers JPQL and Criteria API, which will help you express some amount of complexity in your queries, you will eventually be limited by the features offered in those languages and APIs, as Michael Simons has recently documented in an interesting Criteria API to jOOQ comparison.

For this reason, all JPA implementations offer a way to query the database using “native SQL”. In a previous blog post, we’ve shown how you can leverage jOOQ’s type safe DSL API to run SQL queries via JPA’s native query API, and then fetch results…

In the above cases, jOOQ is used only as a SQL query builder, while query execution is left to JPA.

Do all database querying with jOOQ, in Java EE

Remember jOOQ’s philosophy:

jOOQ is essentially type safe JDBC. Nothing more.

Even if you can use JPA to execute native SQL, you don’t have to. You can operate directly on a JDBC level, something that is often required with JPA, e.g. when working…

  • … with vendor-specific data types
  • … with non-trivial stored procedures
  • … with statement batches
  • … with updatable cursors

When you run your application on an application server, you can pick the features that you want and need, and use proprietary APIs (such as jOOQ, which runs on top of JDBC) for the rest. For instance, you can use:

  • EJB for session and scope management
  • CDI for dependency injection
  • jOOQ for your database interaction

(you could also add JTA to the stack – for simplicity reasons, we’ll skip that for now)

The procedure is simple: Just inject a javax.sql.DataSource into your session bean using CDI:

public class LibraryEJB {

    private DataSource ds;

… and start working with it using JDBC:

public List<Author> fetchAuthors() 
throws SQLException {
    List<Author> result = new ArrayList<>();

    // Get a Connection from the injected DataSource
    try(Connection con = ds.getConnection();
        PreparedStatement stmt = con.prepareStatement(
        ResultSet rs = stmt.executeQuery()
    ) {
        result.add(new Author(

    return result;

… or using jOOQ:

public Result<AuthorRecord> fetchAuthors() {

    // Pass the injected DataSource to jOOQ
    return DSL.using(ds, H2)

Notice how jOOQ – by default – fetches all results eagerly into memory, closing resources like the JDBC Connection, PreparedStatement, and ResultSet eagerly, such that you’re not required to deal with the hassle of resource management yourself.


jOOQ is essentially type safe JDBC. Nothing more.

JDBC has always been an important part of Java EE applications, for all sorts of reasons, including access to vendor-specific features. jOOQ adds compile-time type safety on top of JDBC. Nothing more. Whatever works with JDBC will work with jOOQ.

In particular, jOOQ will never interfere with your transaction or session model, regardless of the choice you make. All that is needed by jOOQ is a JDBC Connection or DataSource.

Running an example in JBoss WildFly

The above example can be checked out from GitHub and run directly in WildFly, for example – or with only little adaptations in any other Java EE application server:

The example was created for WildFly in the context of a Webinar with Arun Gupta. The webinar answers the following questions:

  • What is jOOQ ?
  • Why JOOQ when there is JDBC and JPA ?
  • How does it fit with Java EE apps ? Does it uses underlying JPA persistence provider or some other connection ?
  • Pros/cons over JPA ? Pure Hibernate ?
  • How well does it scale ?
  • Show code sample in a Java EE application
  • jOOQ for CRUD-based or domain-rich application ?
  • How can eventually all the work in jOOQ be integrated in JPA and be standardized ? Or would it be more of JDBC ?

The full webinar can be seen on YouTube, here:

Type Safe Queries for JPA’s Native Query API

When you’re using JPA – sometimes – JPQL won’t do the trick and you’ll have to resort to native SQL. From the very beginning, ORMs like Hibernate kept an open “backdoor” for these cases and offered a similar API to Spring’s JdbcTemplate, to Apache DbUtils, or to jOOQ for plain SQL. This is useful as you can continue using your ORM as your single point of entry for database interaction.

However, writing complex, dynamic SQL using string concatenation is tedious and error-prone, and an open door for SQL injection vulnerabilities. Using a type safe API like jOOQ would be very useful, but you may find it hard to maintain two different connection, transaction, session models within the same application just for 10-15 native queries.

But the truth is:

You an use jOOQ for your JPA native queries!

That’s true! There are several ways to achieve this.

Fetching tuples (i.e. Object[])

The simplest way will not make use of any of JPA’s advanced features and simply fetch tuples in JPA’s native Object[] form for you. Assuming this simple utility method:

public 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();

Using the API

This is all you need to bridge the two APIs in their simplest form to run “complex” queries via an EntityManager:

List<Object[]> books =
nativeQuery(em, DSL.using(configuration)

books.forEach((Object[] book) -> 
    System.out.println(book[0] + " " + 
                       book[1] + " wrote " + 

Agreed, not a lot of type safety in the results – as we’re only getting an Object[]. We’re looking forward to a future Java that supports tuple (or even record) types like Scala or Ceylon.

So a better solution might be the following:

Fetching entities

Let’s assume you have the following, very simple entities:

@Table(name = "book")
public class Book {

    public int id;

    @Column(name = "title")
    public String title;

    public Author author;

@Table(name = "author")
public class Author {

    public int id;

    @Column(name = "first_name")
    public String firstName;

    @Column(name = "last_name")
    public String lastName;

    @OneToMany(mappedBy = "author")
    public Set<Book> books;

And let’s assume, we’ll add an additional utility method that also passes a Class reference to the EntityManager:

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();

Using the API

This is now rather slick, just put your jOOQ query into that API and get JPA entities back from it – the best of both worlds, as you can easily add/remove nested collections from the fetched entities as if you had fetched them via JPQL:

List<Author> authors =
, Author.class); // This is our entity class here

authors.forEach(author -> {
    System.out.println(author.firstName + " " + 
                       author.lastName + " wrote");
    books.forEach(book -> {
        System.out.println("  " + book.title);

        // Manipulate the entities here. Your
        // changes will be persistent!

Fetching EntityResults

If you’re extra-daring and have a strange affection for annotations, or you just want to crack a joke for your coworkers just before you leave on vacation, you can also resort to using JPA’s javax.persistence.SqlResultSetMapping. Imagine the following mapping declaration:

    name = "bookmapping",
    entities = {
            entityClass = Book.class,
            fields = {
                @FieldResult(name = "id", column = "b_id"),
                @FieldResult(name = "title", column = "b_title"),
                @FieldResult(name = "author", column = "b_author_id")
            entityClass = Author.class,
            fields = {
                @FieldResult(name = "id", column = "a_id"),
                @FieldResult(name = "firstName", column = "a_first_name"),
                @FieldResult(name = "lastName", column = "a_last_name")

Essentially, the above declaration maps database columns (@SqlResultSetMapping -> entities -> @EntityResult -> fields -> @FieldResult -> column) onto entities and their corresponding attributes. With this powerful technique, you can generate entity results from any sort of SQL query result.

Again, we’ll be creating a small little utility method:

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));

    // This implicit cast is a lie, but let's risk it
    return result.getResultList();

Note that the above API makes use of an anti-pattern, which is OK in this case, because JPA is not a type safe API in the first place.

Using the API

Now, again, you can pass your type safe jOOQ query to the EntityManager via the above API, passing the name of the SqlResultSetMapping along like so:

List<Object[]> result =
    "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 " + 

The result in this case is again an Object[], but this time, the Object[] doesn’t represent a tuple with individual columns, but it represents the entities as declared by the SqlResultSetMapping annotation.

This approach is intriguing and probably has its use when you need to map arbitrary results from queries, but still want managed entities. We can only recommend Thorben Janssen‘s interesting blog series about these advanced JPA features, if you want to know more:


Choosing between an ORM and SQL (or between Hibernate and jOOQ, in particular) isn’t always easy.

  • ORMs shine when it comes to applying object graph persistence, i.e. when you have a lot of complex CRUD, involving complex locking and transaction strategies.
  • SQL shines when it comes to running bulk SQL, both for read and write operations, when running analytics, reporting.

When you’re “lucky” (as in – the job is easy), your application is only on one side of the fence, and you can make a choice between ORM and SQL. When you’re “lucky” (as in – ooooh, this is an interesting problem), you will have to use both. (See also Mike Hadlow’s interesting article on the subject)

The message here is: You can! Using JPA’s native query API, you can run complex queries leveraging the full power of your RDBMS, and still map results to JPA entities. You’re not restricted to using JPQL.


While we’ve been critical with some aspects of JPA in the past (read How JPA 2.1 has become the new EJB 2.0 for details), our criticism has been mainly focused on JPA’s (ab-)use of annotations. When you’re using a type safe API like jOOQ, you can provide the compiler with all the required type information easily to construct results. We’re convinced that a future version of JPA will engage more heavily in using Java’s type system, allowing a more fluent integration of SQL, JPQL, and entity persistence.

Is Your Eclipse Running a Bit Slow? Just Use This Simple Trick!

You wouldn’t believe it until you try it yourself. I’ve been using the Eclipse Mars developer milestones lately, and I’ve been having some issues with slow compilation. I always thought it was because of the m2e integration, which has never been famous for working perfectly. But then, it dawned upon me when I added a JPA persistence.xml file to run some jOOQ + Hibernate tests… I ran into this issue, and googled it to learn that many people are complaining about JPA validation running forever in their Eclipses.

So I searched for how to deactivate that, and boom!

All of my Eclipse got much much faster

In fact, I didn’t just deactivate JPA validation, but all validation:

deactivate all validation in your Eclipse to boost performance

I don’t remember the last time I ever needed validation, or thought that it was a useful feature in the first place. If you want to help your whole team, you can also check in the following file in each of your projects’ .settings/org.eclipse.wst.validation.prefs files:


This has the same effect, but can be checked into version control.

Found this tip useful? See also our list of Top 5 Useful Hidden Eclipse Features

A DBA’s point of view of Hibernate

A very nice little rant on Hibernate:

While I don’t agree 100% (e.g. CRUD/OLTP really is different from OLAP, and Hibernate is a strong CRUD tool), I certainly share most of his feelings.