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

By Thorben Janssen

Query, Sql

The Java Persistence Query Language (JPQL) is the most common way to query data from a database with JPA. 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 cannot be transformed 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 also supports native SQL. You can create these queries in a very similar way as JPQL queries and they can even return managed entities if you want.

Create dynamic native queries

Creating a dynamic native query is quite simple. The EntityManager interface provides a method called createNativeQuery for it. This method returns an implementation of the Query interface which is the same as if you call the createQuery method to create a JPQL query.

The following code snippet shows a simple example in which I used 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 you can use any SQL statement that is supported by your database. In one of my recent projects for example, 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, the created Query can be used in the same way as any JPQL query. I didn’t provide any mapping information for the result and so the EntityManager returns a List of Object[] which need to be handled afterwards. 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.

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 provide a better performance.

JPQL and native SQL queries use the same Query interface which provides a setParameter method for positional and named parameter bindings. But the use of named parameter bindings for native queries is not defined by the JPA specification. Positional parameters are referenced as “?” in your native Query and their numbering starts at 1.

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 needs to be prefixed with a “:” symbol.

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[]. You can change that, if you provide additional mapping information to the EntityManager. By doing this you can tell the EntityManager to map the result into managed entities, scalar values of specific types or POJOs. The simplest way to map the result of a native query into a managed entity is to select all properties of the entity and provide its 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 = q.getResultList();

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

All other mappings, like the following one which maps the query result into a POJO, need to be defined as SQLResultSetMappings.

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

To use this mapping, you need to provide the name of the mapping as a parameter to the createNativeQuery method.

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", "AuthorValueMapping");
List<AuthorValue> authors = q.getResultList();

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

These mappings are quite powerful and you can even combine them to map a query result into multiple entities, POJOs and scalar values. Have a look at the following posts to dive deeper into SQLResultMappings:

Create named native queries

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

In the previous code snippets, I created 3 dynamic native queries to select the names of all authors, to select all authors as entities and to select all authors and map them into AuthorValue POJOs. I used the same queries in the following code snippet and created named queries for them.

@NamedNativeQueries({
    @NamedNativeQuery(name = "selectAuthorNames", query = "SELECT a.firstname, a.lastname FROM Author a"),
    @NamedNativeQuery(name = "selectAuthorEntities", query = "SELECT a.id, a.version, a.firstname, a.lastname FROM Author a", resultClass = Author.class),
    @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 = "AuthorValueMapping")
})

As you can see, the definition looks very similar to the definition of named JPQL queries and you can even provide the result class or the name of an SQLResultSetMapping. The named native queries are used in exactly the same way as named JPQL queries. 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("selectAuthorValue");
List<AuthorValue> authors = q.getResultList();

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


Native Queries with Hibernate - 3d cover

Free ebook: Native Queries with Hibernate


Join the free Thoughts on Java Library to get access to lots of member-only content, like the "Native Queries with Hibernate" ebook.

Conclusion

JPQL 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. But this is not a real issue. You can use all these features by creating native SQL queries via EntityManager.createNativeQuery(String sql) which are send directly to the database.

Do you have any questions or comments? Do you use native SQL queries often in your projects or do you try to avoid them?
Tell me about it in the comments below or on twitter.


Tags

Query, Sql


About the author

Thorben is an independent consultant, international speaker, and trainer specialized in solving Java persistence problems with JPA and Hibernate.
He is also the author of Amazon’s bestselling book Hibernate Tips - More than 70 solutions to common Hibernate problems.

Books and Courses

Coaching and Consulting

Leave a Repl​​​​​y

Your email address will not be published. Required fields are marked

This site uses Akismet to reduce spam. Learn how your comment data is processed.

  1. I used a Native Query on tables that had no table association, as you can use the Native Query to do so. Yet, when I used a certain ServiceBean to do so, I was could not remove the rows? When I did a a different ServiceBean with the same Native Query statement, the rows were removed? I am trying to figure out as to why I was not able to use the Native Query in the first ServiceBean?

    public void ***deleteConvQueue(String fcn) {
    Query query1 = em
    .createNativeQuery(“delete from person_convictions where id = ‘” + id + “‘ and Current_Status is null”);
    query1.executeUpdate();
    }

    Reply

    1. Hi Jan,

      That query should work. Are you sure that there are records that match the WHERE conditions?

      Regards,
      Thorben

      Reply

    1. Hibernate’s result transformer are great but they are deprecated in Hibernate 5.
      So, you use them at your own risk and I don’t know what will happen to them in Hibernate 6. The Hibernate team said that there would be a new version of the result transformers.
      But I don’t expect them to be compatible with the old ones…

      Reply

  2. what about different databases and using native query with them ?

    A Postgresql database table , B another oracle database table defined
    Select fields from A a ,B b where a.field = b.field
    can we use native also ?
    Thanks

    Reply

    1. Your persistence unit connects to 1 database. As long as this database contains all tables (some database can link external tables), you can use them in your query.

      Reply

  3. Thank you for this thorough article. I lucked out and found it right as I was using some database-specific features. It saved me hours of work, and I find I understand the underlying concepts, too. Thanks, man.

    Reply

  4. Whew, Thanks! Finally an example that understands that I need to crawl before I can walk! And you’re right (at least in my case). The SQL I can handle, it’s getting to the results without pages of Java that I want. The Spring tutorials seem to assume that every database is really just a dumb datastore and I want to write a full Java object for every data interaction. No, that’s why I have a real SQL database…

    Reply

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}