Native Queries – How to call native SQL queries with JPA & Hibernate

Take your skills to the next level!

The Persistence Hub is the place to be for every Java developer. It gives you access to all my premium video courses, monthly Java Persistence News, monthly coding problems, and regular expert sessions.

Join the Persistence Hub!

The Java Persistence Query Language (JPQL) is the most common way to query data from a database with JPA. It enables you to reuse your mapping definitions and is easier to use than SQL. But it supports only a small subset of the SQL standard, and it also provides no support for database-specific features.

So what shall you do if you need to use a database-specific query feature or your DBA gives you a highly optimized query that you can’t transform into JPQL? Just ignore it and do all the work in the Java code?

Of course not! JPA has its own query language, but it’s designed as a leaky abstraction and supports native SQL queries. You can create these queries in a similar way as JPQL queries, and they can even return managed entities if you want.

In this article, I will show you how to use native SQL queries, different options to map the query result to DTOs and entity objects, and avoid a common performance pitfall.

Defining and executing a native query

Like JPQL queries, you can define your native SQL query ad-hoc or use an annotation to define a named native query.

Create ad-hoc native queries

Creating an ad-hoc native query is quite simple. The EntityManager interface provides the createNativeQuery method for it. It returns an implementation of the Query interface, which is the same that you get when you call the createQuery method to create a JPQL query.

The following code snippet shows a simple example of using a native query to select the first and last names from the author table. I know there is no need to do this with a native SQL query. I could use a standard JPQL query for this, but I want to focus on the JPA part and not bother you with some crazy SQL stuff 😉

The persistence provider does not parse the SQL statement so that you can use any SQL statement your database supports. For example, in one of my recent projects, I used it to query PostgreSQL-specific jsonb columns with Hibernate and mapped the query results to POJOs and entities.

Query q = em.createNativeQuery("SELECT a.firstname, a.lastname FROM Author a");
List<Object[]> authors = q.getResultList();

for (Object[] a : authors) {
    System.out.println("Author "
            + a[0]
            + " "
            + a[1]);
}

As you can see, you can use the created Query in the same way as any JPQL query. I didn’t provide any mapping information for the result. Because of that, the EntityManager returns a List of Object[] which you need to handle afterward. Instead of mapping the result yourself, you can also provide additional mapping information and let the EntityManager do the mapping for you. I get into more details about that in the result handling section at the end of this post.

Create named native queries

You will not be surprised if I tell you that the definition and usage of a named native query are again very similar to a named JPQL query.

In the previous code snippets, I created a dynamic native query to select the names of all authors. I use the same statement in the following code snippet to define a @NamedNativeQuery. Since Hibernate 5 and JPA 2.2, this annotation is repeatable, and you can add multiple of it to your entity class. If you’re using an older JPA or Hibernate version, you need to wrap it in a @NamedNativeQueries annotation.

@NamedNativeQuery(name = "selectAuthorNames", 
                  query = "SELECT a.firstname, a.lastname FROM Author a")
@Entity
public class Author { ... }

As you can see, the definition looks very similar to the one of a named JPQL query. As I will show you in the following section, you can even include the result mapping. But more about that later.

You can use the @NamedNativeQuery in exactly the same way as a named JPQL query. You only need to provide the name of the named native query as a parameter to the createNamedQuery method of the EntityManager.

Query q = em.createNamedQuery("selectAuthorNames");
List<Object[]> authors = q.getResultList();

for (Object[] a : authors) {
    System.out.println("Author "
            + a[0]
            + " "
            + a[1]);
}

Parameter binding

Similar to JPQL queries, you can and should use parameter bindings for your query parameters instead of putting the values directly into the query String. This provides several advantages:

• you do not need to worry about SQL injection,
• the persistence provider maps your query parameters to the correct types and
• the persistence provider can do internal optimizations to improve the performance.

JPQL and native SQL queries use the same Query interface, which provides a setParameter method for positional and named parameter bindings. But the support of named parameter bindings for native queries is a Hibernate-specific feature. Positional parameters are referenced as “?” in your native query and their numbering starts at 1.

The following code snippet shows an example of an ad-hoc native SQL query with a positional bind parameter. You can use the bind parameter in the same way in a @NamedNativeQuery.

Query q = em.createNativeQuery("SELECT a.firstname, a.lastname FROM Author a WHERE a.id = ?");
q.setParameter(1, 1);
Object[] author = (Object[]) q.getSingleResult();

System.out.println("Author "
        + author[0]
        + " "
        + author[1]);

Hibernate also supports named parameter bindings for native queries, but as I already said, this is not defined by the specification and might not be portable to other JPA implementations.

By using named parameter bindings, you define a name for each parameter and provide it to the setParameter method to bind a value to it. The name is case-sensitive, and you need to add the “:” symbol as a prefix.

Query q = em.createNativeQuery("SELECT a.firstname, a.lastname FROM Author a WHERE a.id = :id");
q.setParameter("id", 1);
Object[] author = (Object[]) q.getSingleResult();

System.out.println("Author "
        + author[0]
        + " "
        + author[1]);

Result handling

As you have seen in the previous code snippets, your native query returns an Object[] or a List of Object[]. If you want to retrieve your query result as a different data structure, you need to provide additional mapping information to your persistence provider. There are 3 commonly used options:

• You can map each record of your query result to a managed entity using the entity’s mapping definition.
• You can use JPA’s @SqlResultSetMapping annotation to map each result record to a combination of DTOs, managed entities, or scalar values.
• And you can use Hibernate’s ResultTransformer to map each record or the entire result set to DTOs, managed entities, or scalar values.

Apply the entity mapping

Reusing the mapping definition of your entity class is the simplest way to map each record of the query result to a managed entity object. When doing that, you need to select all columns mapped by the entity class using the alias used in your entity’s mapping definition.

Next, you need to tell your persistence provider to which entity class it shall map the query result. For an ad-hoc native SQL query, you do that by providing a class reference as a parameter to the createNativeQuery method.

Query q = em.createNativeQuery("SELECT a.id, a.version, a.firstname, a.lastname FROM Author a", Author.class);
List<Author> authors = (List<Author>) q.getResultList();

for (Author a : authors) {
    System.out.println("Author "
            + a.getFirstName()
            + " "
            + a.getLastName());
}

You can do the same using a @NamedNativeQuery by referencing the entity class as the @NamedNativeQuery‘s resultClass attribute.

@NamedNativeQuery(name = "selectAuthorEntities", 
                  query = "SELECT a.id, a.version, a.firstname, a.lastname FROM Author a", 
                  resultClass = Author.class)
@Entity
public class Author { ... }

Hibernate then automatically applies that mapping when you execute that query.

Use JPA’s @SqlResultSetMapping

JPA’s @SqlResultSetMapping is much more flexible than the previous one. You can not only use it to map your query result to managed entity objects but also to DTOs, scalar values, and any combination of these. The only limitation is that Hibernate applies the defined mapping to each record of the result set. Due to that, you can’t easily group multiple records of your result set.

These mappings are quite powerful, but their definition can get complex. That’s why I only provide a quick introduction in this article. If you want to dive deeper into @SqlResultMappings, please read the following articles:

• Result Set Mapping: The Basics
• Result Set Mapping: Complex Mappings
• Result Set Mapping: Constructor Result Mappings
• Result Set Mapping: Hibernate specific features

Here you can see a basic example of a DTO mapping.

@SqlResultSetMapping(
        name = "BookAuthorMapping",
        classes = @ConstructorResult(
                targetClass = BookAuthor.class,
                columns = {
                    @ColumnResult(name = "id", type = Long.class),
                    @ColumnResult(name = "firstname"),
                    @ColumnResult(name = "lastname"),
                    @ColumnResult(name = "numBooks", type = Long.class)}))

Every @SqlResultSetMapping has to have a unique name within the persistence unit. You will use it in your code to reference this mapping definition.

The @ConstructorResult annotation tells Hibernate to call the constructor of the BookAuthor class and provide the resultset’s id, firstName, lastName, and numBooks fields as parameters. This enables you to instantiate unmanaged DTO objects, which are a great fit for all read-only operations.

After defining the mapping, you can provide its name as the 2nd parameter to the createNativeQuery method. Hibernate will then look up the mapping definition within the current persistence unit and apply it to every record of the result set.

Query q = em.createNativeQuery("SELECT a.id, a.firstname, a.lastname, count(b.id) as numBooks FROM Author a JOIN BookAuthor ba on a.id = ba.authorid JOIN Book b ON b.id = ba.bookid GROUP BY a.id", 
                               "BookAuthorMapping");
List<BookAuthor> authors = (List<BookAuthor>) q.getResultList();

for (BookAuthor a : authors) {
    System.out.println("Author "
            + a.getFirstName()
            + " "
            + a.getLastName()
            + " wrote "
            + a.getNumBooks()
            + " books.");
}

And similar to the previous examples, you can apply the same mapping to a @NamedNativeQuery by providing the name of the mapping as the resultSetMapping attribute.

@NamedNativeQuery(name = "selectAuthorValue", 
                  query = "SELECT a.id, a.firstname, a.lastname, count(b.id) as numBooks FROM Author a JOIN BookAuthor ba on a.id = ba.authorid JOIN Book b ON b.id = ba.bookid GROUP BY a.id", 
                  resultSetMapping = "BookAuthorMapping")
@Entity
public class Author { ... }

After you did that, you can execute your @NamedNativeQuery and Hibernate applies the @SqlResultSetMapping automatically.

Query q = em.createNamedQuery("selectAuthorValue");
List<BookAuthor> authors = (List<BookAuthor>) q.getResultList();

for (BookAuthor a : authors) {
    System.out.println("Author "
            + a.getFirstName()
            + " "
            + a.getLastName()
            + " wrote "
            + a.getNumBooks()
            + " books.");
}

Use Hibernate-specific ResultTransformer

ResultTransformers are a Hibernate-specific feature with the same goal as JPA’s @SqlResultSetMapping. They allow you to define a custom mapping of the resultset of your native query. But in contrast to the @SqlResultSetMapping, you implement that mapping as Java code, and you can map each record or the entire result set.

Hibernate provides a set of standard transformers, and the implementation of the custom transformer got much easier in Hibernate 6. I explained all of that in great detail and the difference between the Hibernate versions in my guide to ResultTransformer.

The following code snippet shows the implementation of a TupleTransformer for Hibernate 6. It applies the same mapping as the previously used @SqlResultSetMapping.

List<BookAuthor> authors = (List<BookAuthor>) session
		.createQuery("SELECT a.id, a.firstname, a.lastname, count(b.id) as numBooks FROM Author a JOIN BookAuthor ba on a.id = ba.authorid JOIN Book b ON b.id = ba.bookid GROUP BY a.id")
		.setTupleTransformer((tuple, aliases) -> {
				log.info("Transform tuple");
				BookAuthor a = new BookAuthor();
				a.setId((Long) tuple[0]);
				a.setFirstName((String) tuple[1]);
				a.setLastName((String) tuple[2]);
				a.setNumBooks((Integer) tuple[3]);
				return a;
		}).getResultList();

for (BookAuthor a : authors) {
    System.out.println("Author "
            + a.getFirstName()
            + " "
            + a.getLastName()
            + " wrote "
            + a.getNumBooks()
            + " books.");
}

As you can see in the code snippet, I called the setTupleTransformer method to add the transformer to the query. That makes the transformer independent of the query, and you can apply it to a @NamedNativeQuery in the same way.

Define the query space to avoid performance problems

At the beginning of the article, I mentioned that Hibernate doesn’t parse your native SQL statement. That provides the benefit that you’re not limited to the features Hibernate supports but that you can use all features supported by your database.

But it also makes it impossible to determine the query space. The query space describes which entity classes your query references. Hibernate uses it to optimize the dirty check and flush operation that it has to perform before executing the query. I explain this in more detail in Hibernate Query Spaces – Optimizing Flush and Cache Operations.

The important thing you need to know when using native SQL queries is to specify the query space. You can do that by unwrapping Hibernate’s SynchronizeableQuery from JPA’s Query interface and calling the addSynchronizedEntityClass method with a reference to your entity class.

Query q = em.createNamedQuery("selectAuthorEntities");
SynchronizeableQuery hq = q.unwrap(SynchronizeableQuery.class);
hq.addSynchronizedEntityClass(Author.class);
List<Author> authors = (List<Author>) q.getResultList();

for (Author a : authors) {
    System.out.println("Author "
            + a.getFirstName()
            + " "
            + a.getLastName());
}

This tells Hibernate which entity classes your query references. It can then limit the dirty check to objects of these entity classes and flush them to the database. While doing that, Hibernate ignores all changes on entity objects of other entity classes. This avoids unnecessary database operations and allows Hibernate to apply further performance optimizations.

Conclusion

JPQL is the most commonly used query language with JPA and Hibernate. It provides an easy way to query data from the database. But it supports only a small subset of the SQL standard, and it also does not support database-specific features. If you want to use any of these features, you need to use a native SQL query.

You can define a native ad-hoc query by calling the EntityManager‘s createNativeQuery method and providing the SQL statement as a parameter. Or you can use the @NamedNativeQuery annotation to define a named query that you can execute in the same way as JPQL’s @NamedQuery.

Native queries return their result as an Object[] or a List<Object[]>. You can convert this in multiple ways. If you select all columns mapped by an entity class, you can provide a class reference as the 2nd parameter to the createNativeQuery method. Hibernate then applies that class’ mapping to each record in the result set and returns managed entity objects. If you want to map the result to DTOs, you need to define an @SqlResultSetMapping or implement a Hibernate-specific ResultTransformer.

And you should always define the query space of your native queries. It enables Hibernate to optimize the dirty check and flush operation it needs to perform before executing the query.