Native Queries – How to call native SQL queries with JPA

Native Queries – How to call native SQL queries with JPA

 

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 can not 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]);

EclipseLink 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 Listof 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:

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

 

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 AuthorValuePOJOs. 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.);
}

 

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 about it in the comments below.