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)
    .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:

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.

11 thoughts on “Type Safe Queries for JPA’s Native Query API

  1. 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.

    1. 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.

  2. 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?

    1. 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?)

      1. 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?

        1. 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

          • Composable SQL. In jOOQ, you’re building an AST (Abstract Syntax Tree), not a SQL string. This means that your SQL statement is very easy to compose, even across several methods, e.g. when doing dynamic SQL. This is much harder with string-based SQL, as it will be hard to say whether a given table / column name will eventually make it into the SQL statement or not, and thus how to verify it in IntelliJ.
          • Syntax. Again, because you’re creating an AST rather than a string, SQL syntax will always be generated correctly for you by jOOQ – you cannot do anything wrong, even with dynamic SQL. This is specifically true for vendor-specific syntax, which is much harder to parse than to generate from a pre-existing AST. I’m talking about 21 supported popular SQL dialects in the upcoming jOOQ 3.7
          • Vendor-specific SQL. We can take this argument a step further and look into supporting vendor-specific things such as Oracle / PostgreSQL / SQL Server table-valued functions, which are like parameterised views. These are also part of the AST and are properly type checked – something that is much harder to do in a parser
          • SQL transformation: Let’s go to the runtime advantages. Once you build that AST in your code, you will be building it also at run time, not only at compile time. This means that you can transform the AST. We support features like schema / table mapping (e.g. to implement multi-tenancy), generic SQL transformation (e.g. to implement row-level security), custom data type binding (e.g. to support vendor-specific data types and bind them to your favourite library, e.g. JSON to Jackson), and much more. jOOQ is part of your application, not just part of your development lifecycle
          • SQL standardisation: Some of our most demanding customers use jOOQ because jOOQ is the only API that can generate SQL reliably across e.g. 6 supported RDBMS in parallel. I’m talking about an example that supports DB2, H2, MySQL, Oracle, SQL Server, and Sybase. They’re doing rather complex querying and reporting, and jOOQ can handle it for them as jOOQ still abstracts the SQL language a little bit (e.g. LIMIT .. OFFSET, the DUAL table, and much much more
          • Active Records. Heck, it’s a library, not just a language. So we’ve added “ActiveRecords”. If you’re not really in need of JPA yet, ActiveRecords may be good enough and will save you from writing tons of CRUD queries. They’re a pain, even if IntelliJ helps you auto-complete and to some extent, verify the SQL.
          • Stored Procedures. This is not a favourite topic for Java developers, but our main target audience includes PL/SQL developers that are “forced” to write Java as well. They would like to access their sophisticated PL/SQL APIs from their Java clients. Neither Oracle, nor anyone else offers them a useful tool (including IntelliJ). Binding PL/SQL objects via JDBC is a pain in the ass as we’ve shown in this article. It is not with jOOQ, it’s a no-brainer, even for very complex nested data structures. This doesn’t resonate with everyone, but it is certainly a very good reason for using jOOQ (and an interesting niche market).

          So 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.

  3. 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. :-)

  4. 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…

    1. Hey, excellent use-case! Are you planning to blog about that? I’d love to see the query and the corresponding entities :)

Leave a Reply