Native Queries with Spring Data JPA
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.
Spring Data JPA supports various ways to retrieve data from your database. Some are very easy to use, like derived queries. Others provide great flexibility and enable you to read and transform the data in various ways, e.g., custom queries. Native queries are the extreme of the 2nd category. They are your most flexible and most powerful option to read data.
As I explained in a previous post about native queries in JPA, they provide you access to all features supported by your database. That makes them an excellent fit for all use cases that require a complex query to extract the required information. The same is true, of course, if you’re using native queries with Spring Data JPA because you’re using the same mechanism internally. Spring Data JPA just makes the definition and execution of a native query a little easier.
Defining a Native Query
When using plain JPA or Hibernate, defining and executing a native query requires multiple steps. Spring Data’s @Query annotation removes all the boilerplate code. We already used that annotation in a previous post to define a custom JPQL query.
When defining a native query, you annotate your repository method with @Query, set its nativeQuery attribute to true, and provide an SQL statement as the value. As shown in the following code snippet, you can use bind parameters in the same way as in a custom JPQL query.
@Repository
public interface AuthorRepository extends CrudRepository<Author, Long>, PagingAndSortingRepository<Author, Long> {
@Query(value="select * from author a where a.first_name= :firstName", nativeQuery=true)
List<Author> getAuthorsByFirstName(String firstName);
}
After you’ve done that, you only need to inject your repository into one of your services and call the getAuthorsByFirstName method with the firstName for which you want to search.
List<Author> authors = authorRepository.getAuthorsByFirstName("Janssen");
Spring Data’s repository implementation then provides the required code to instantiate a query for the statement provided in your @Query annotation. It then sets the provided value as a bind parameter on that query and executes it.
Write Operations as Native Queries
As I explain in great detail in my Hibernate Performance Tuning Online Training, bulk operations are often the better option to change or remove a huge number of database records. You can implement these operations using JPQL, Criteria, or native queries.
You can use the @Query annotation to define such a JPQL or native SQL statement. Because write operations need to be executed differently than read operations, you also need to annotate the repository method with a @Modifying annotation. That’s the only difference to the previously discussed native SQL SELECT statements.
@Repository
public interface AuthorRepository extends CrudRepository<Author, Long>, PagingAndSortingRepository<Author, Long> {
@Modifying
@Query(value="delete from author a where a.last_name= :lastName", nativeQuery = true)
void deleteAuthorByLastName(@Param("lastName") String lastName);
@Modifying
@Query(value="update author set last_name= :lastName where first_name = :firstName", nativeQuery=true)
void updateAuthorByFirstName(String firstName, String lastName);
...
}
Limitations of Native Queries With Spring Data JPA
When using native queries, you need to be aware of 2 limitations:
- Spring Data JPA and your persistence provider don’t adjust the query to your database’s specific SQL dialect. Because of that, you need to ensure that all RDBMS supported by your application can handle the provided statement.
- Pagination of native query results requires an extra step.
- Spring Data JPA doesn’t support dynamic sorting for native SQL statements.
Let’s take a closer look at the 2nd and 3rd limitation.
Add a Count Query to Enable Pagination
When working with a custom JPQL query, you can add a parameter of type Pageable to your repository method. This enables pagination for your query result. Spring Data JPA then adds all the required boilerplate code to retrieve the query result one page at a time.
Doing the same with a native query requires an extra step. You need to provide a count query that returns the total number of records included in the non-paged result. One way to do that is to provide the query String as the value of the countQuery attribute of the @Query annotation.
@Repository
public interface AuthorRepository extends CrudRepository<Author, Long>, PagingAndSortingRepository<Author, Long> {
@Query(value="select * from author a where a.last_name= ?1",
countQuery = "select count(id) from author a where a.last_name= ?1",
nativeQuery = true)
Page<Author> getAuthorsByLastName(String lastname, Pageable page);
...
}
If your repository method references a named native query, you need to provide the count query as a 2nd named query and add the suffix .count to its name.
@NamedNativeQuery(name = "Author.getAuthorsByLastName",
query = "select * from author a where a.last_name= ?1",
resultClass = Author.class)
@NamedNativeQuery(name = "Author.getAuthorsByLastName.count",
query = "select count(id) from author a where a.last_name= ?1")
@Entity
public class Author { ... }
No Dynamic Sorting
When working with a JPQL query, you can add a parameter of type Sort to your repository method. This enables you to define the sorting criteria at runtime. Spring Data JPA then generates the required ORDER BY clause based on the provided parameter value.
Unfortunately, Spring Data JPA doesn’t support this feature for native queries. Doing that would require Spring Data to analyze the provided statement and generate the ORDER BY clause in the database-specific dialect. This would be a very complex operation and is currently not supported by Spring Data JPA.
You can, of course, add your own ORDER BY clause to your query. But that limits you to one specific ordering per query. If you need to support multiple ones, using a composite repository is often the better approach. You can then implement your own query method using JPA’s Criteria API and specify the ORDER BY clause based on the provided input parameters.
Conclusion
Native queries are the most powerful and flexible way to implement your read operations. They enable you to use all features supported by your database, and Spring Data JPA handles almost all of the required boilerplate code.
But using them takes more effort than a derived query, and they provide a few limitations compared to a custom JPQL query. The most notable ones are:
- To use pagination for your query result, you need to provide a count query. You can do that by setting the countQuery attribute of the @Query annotation.
- Spring Data JPA doesn’t support dynamic sorting for native queries. If you want to retrieve your query result in a specific order, you need to include the ORDER BY clause in your query.
- Spring Data JPA and your persistence provider don’t adjust your native query statement to your database’s SQL dialect. Because of that, you need to ensure that all of your supported DBMS support your SQL statement.
