0One 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 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 actors = new HashSet<>();
public Film(String title, Year releaseYear) {
this.title = title;
this.releaseYear = releaseYear;
}
}
// Imagine also a Language entity here.
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()
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 |
