jOOQ 3.10 Supports JPA AttributeConverter

Posted on by lukaseder
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: 

@Entity
public class Actor {

    @Id
    @GeneratedValue(strategy = IDENTITY)
    public Integer actorId;

    @Column
    public String firstName;

    @Column
    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;
    }
}

@Entity
public class Film {

    @Id
    @GeneratedValue(strategy = IDENTITY)
    public Integer filmId;

    @Column
    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> {

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

    @Override
    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: 

ctx.select(
        ACTOR.FIRSTNAME,
        ACTOR.LASTNAME,
        count().as("Total"),
        count().filterWhere(LANGUAGE.NAME.eq("English"))
          .as("English"),
        count().filterWhere(LANGUAGE.NAME.eq("German"))
          .as("German"),
        min(FILM.RELEASE_YEAR),
        max(FILM.RELEASE_YEAR))
   .from(ACTOR)
   .join(FILM_ACTOR)
     .on(ACTOR.ACTORID.eq(FILM_ACTOR.ACTORS_ACTORID))
   .join(FILM)
     .on(FILM.FILMID.eq(FILM_ACTOR.FILMS_FILMID))
   .join(LANGUAGE)
     .on(FILM.LANGUAGE_LANGUAGEID.eq(LANGUAGE.LANGUAGEID))
   .groupBy(
        ACTOR.ACTORID,
        ACTOR.FIRSTNAME,
        ACTOR.LASTNAME)
   .orderBy(ACTOR.FIRSTNAME, ACTOR.LASTNAME, ACTOR.ACTORID)
   .fetch()
(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: https://vladmihalcea.com/2017/08/24/how-to-get-the-entity-mapping-to-database-table-binding-metadata-from-hibernate/ More similar features are coming up in jOOQ 3.11, e.g. when we look into reverse engineering JPA @Embedded types as well. See https://github.com/jOOQ/jOOQ/issues/6518 If you want to run this example, do check out our jOOQ/JPA example on GitHub:

Published by lukaseder

I made jOOQ

10 thoughts on “jOOQ 3.10 Supports JPA AttributeConverter”

    1. I KNEW you were going to comment on this ManyToMany thingy despite my disclaimer:

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

      ;-)

      Reply

  2. Very good feature!! Allowing developers to use jOOQ on native and complex queries in projects with JPA/Hibernate will increase its adoption a lot.

    Seems like the jOOQ query is not ok because it’s grouping by ACTORID when this field isn’t in SELECT clause.

    Or I really didn’t undestand this query?

    Reply

    1. What’s wrong with the query? I needed to group by ACTORID because there are several actors by the same name, but I don’t have to SELECT that information when I don’t need it…

      Reply

  3. Lukas,

    It seems like jOOQ is ignoring my @Converter when mapping a record to entity. Something like:
    record.into(Product.class)

    Is it correct? Or should jOOQ take into consideration my JPA converter?

    Reply

    1. Sorry about the previous comment, which I deleted. I completely missed the @ before Converter – thought you were talking about a jOOQ Converter, not the JPA @AttributeConverter.

      You brought up an interesting point. The jOOQ runtime doesn’t look at JPA’s @AttributeConverter annotations. Only the code generator does. I’ve created a feature request for this: https://github.com/jOOQ/jOOQ/issues/7550

      Reply

Leave a Reply to lukasederCancel reply