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();
}
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();
}
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:
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.
Like this:
Like Loading...
Now that’s what I’m talking about. This looks really neat and it’s a strategic selling point, especially for the JPA centric projects.
Thanks for the feedback. Yes, I think you have to be JPA centric, to appreciate this approach as you don’t add JPA easily to a stack. But if you are, then this approach is gold.
Lukas, do you consider help from IDE as a sort of “type safety”? I find that IntelliJ IDEA’s JQL and SQL inspections as well as code completion are very helpful and work really well. Perhaps, better tooling is the answer to problems that JOOQ is solving?
ALTER TABLE t RENAME COLUMN col1 TO col2;
and BAMMMM!! Where’s your IntelliJ now?
Tooling is good. Merging SQL and Java is much better. Having said so. Tooling may be sufficient in some use-cases of course. Not in our customers’ though.
(… on a more serious note, I’m not sure if you can compare the approaches. jOOQ has a lot of advantages arising from being an API, not just an external DSL. What parts are you interested in, most specifically?)
What happens to JOOQ if you rename a column? In IntelliJ, I re-scan the database and get all problematic places highlighted. In JOOQ, I guess, I have to regenerate table classes and then get a compilation error in all the problematic places. Or do you suggest I fix the generated class to match the renaming?
Let’s compare the features (the serious note). Today, IDE can provide SQL syntax check within Java code, table/column name completions and checking. What does JOOQ provide in addition?
In jOOQ, you would regenerate the table classes as part of your build process and you would get compilation errors inside or outside of your IDE (e.g. in the nightly builds as well). We’ve showed an example of how to do this here, using Flyway for migrations. I don’t think that IntelliJ has such build plugins? I’m mainly criticising relying on the IDE. While auto-completion inside of the IDE is awesome, verification inside of the IDE is not. In fact, I have been talking to IntelliJ people to see if they can take the plain SQL parts of the jOOQ API into their auto-completion scope. You don’t have to execute type safe SQL with jOOQ.
Let’s compare a couple of things you get out of jOOQ that you will not get out of IntelliJ
LIMIT .. OFFSET, theDUALtable, and much much moreSo you see, the list can be grown more and more based on the fact that jOOQ is an AST library, not an external DSL parser. Having said so (as set out in the beginning), you can use both jOOQ and IntelliJ. Chances are, that IntelliJ can still help you auto-complete wherever you use jOOQ to execute “plain SQL”.
I hope this clarified things a bit. If not, just let me know. I’m happy to shed more light on certain parts.
Or, what about some future version of JPA might supporting something like LINQ-to-Entities by leveraging Java 8’s lambdas..?
You may be looking for the black magic as implemented by JINQ.org
From JPA 2.0, we have “public TypedQuery createNamedQuery(String name, Class resultClass)”, and we can use “NamedNativeQuery” to get some type safety, although not as elegant. :-)
This is fantastic… I need to decompose a hideous 100 line, VisualStudio auto generated God Object result set into typesafe JPA entities for Spring Data — many jooq lessons this week…
Hey, excellent use-case! Are you planning to blog about that? I’d love to see the query and the corresponding entities :)