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)
.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]));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:
@Entity
@Table(name = "book")
public class Book {
@Id
public int id;
@Column(name = "title")
public String title;
@ManyToOne
public Author author;
}
@Entity
@Table(name = "author")
public class Author {
@Id
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 =
nativeQuery(em,
DSL.using(configuration)
.select()
.from(AUTHOR)
.orderBy(AUTHOR.ID)
, 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:
@SqlResultSetMapping(
name = "bookmapping",
entities = {
@EntityResult(
entityClass = Book.class,
fields = {
@FieldResult(name = "id", column = "b_id"),
@FieldResult(name = "title", column = "b_title"),
@FieldResult(name = "author", column = "b_author_id")
}
),
@EntityResult(
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 =
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 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:
- Result Set Mapping: The Basics
- Result Set Mapping: Complex Mappings
- Result Set Mapping: Constructor Result Mappings
- Result Set Mapping: Hibernate specific features
Conclusion
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.
Side-note
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.
| Reference: | Type Safe Queries for JPA’s Native Query API from our JCG partner Lukas Eder at the JAVA, SQL, AND JOOQ blog. |
