In my previous post on Spring Data, I have explained the basic concepts and configurations for Spring Data JPA. In this tutorial, I am going to explain @Query annotation and how to create custom query using the @Query annotation.
The greatest advantage of using Spring Data is that it implements the repositories at run time for creating the queries. Developer need not worry about the SQL knowledge and writing error prone SQL queries. Query methods implemented in spring data repositories will be used for creating the dynamic queries.
But, if you want to write very complex queries with multiple conditions to filter the data, then the query method name would become awful. To avoid this situation, one can comfortably use the JPQL / SQL queries inside the @Query annotation. This annotation supports both, JPA Query Language (JPQL) and SQL. I am going to explain how to write query using query annotation in Spring Data JPA.
If you encounter any problems, please write it in the comments section or directly contact me for the help.
Here is the list of topics covered under this tutorial.
- @Query Annotation
- Like Expressions
- How to use Native SQL Query
- Named Parameters using @Param
- SpEL Expressions
- Example Application
- Source Code
- Conclusion
@Query Annotation
- When you can not use the query methods to perform database operations,
@Querycould be used to write the more flexible query to fetch data. @Queryannotation supports both JPQL and native SQL queries.- By default,
@Queryannotation will be using JPQL to execute the queries. If you try to use the normal SQL queries, you would get the query syntax error exceptions. - If you want to write native SQL queries, set the
nativeQueryflag totrue. Also pagination and dynamic sorting for native queries are not supported in spring data jpa. - If you are using the query methods with
@Queryannotation, this will take the precedence over@NamedQuery, named queries in orm.xml and method names. - For example, if we create a query method called findByName() and annotate it with the
@Queryannotation, Spring Data JPA will not find the entity with name property is equal then the given method parameter. It would invoke the query that is configured by using the@Queryannotation.
A simple example snippet for using the query annotation.
[code lang=”java”]
@Query(value = "select name,author,price from Book b where b.price>?1 and b.price<?2")
List<Book> findByPriceRange(long price1, long price2);
[/code]
It is a simple example for query annotation. In the above snippet, we are having to parameters to compare the prices and filter the results. The parameter in the query is preceded by ? character to indicate that this is the parameter to be bind with method arguments.
Like Expressions
One can use the advanced like mechanism inside the @Query definition. The example for using the like expressions inside query as below:
[code lang=”java”]
@Query(value = "select name,author,price from Book b where b.name like %:name%")
List<Book> findByNameMatch(@Param("name") String name);
[/code]
In the above query, we are using the like expression to filter the results.
How to use Native SQL Query
As I have pointed out in the previous section, query annotation supports the definition of native SQL query by enabling the nativeQuery flag in the query annotation. This feature is very handy when you want to try just the normal database query syntax instead of JPQL syntax.
Here is the example code for how to use the native SQL query in @Query annotation:
[code lang=”java”]
@Query(value = "select * from #{#entityName} b where b.name=?1", nativeQuery = true)
List<Book> findByName(String name);
[/code]
In the above example code snippet, we are using the nativeQuery=true for telling spring data jpa to use the native query execution. By default the value for this flag is false.
Named Parameters using @Param
- By default, Spring Data query parameters are substituted based on its position. This is error prone when you are refactoring the code and updating the parameter positions.
@Paramannotation can be used in the method parameter to bind the query parameter names.- Inside query, you have to use
:paramNameto indicate that the same paramName has to be bind with the method parameter.
Here is the example code:
[code lang=”java”]
@Query(value = "select name,author,price from Book b where b.name = :name AND b.author=:author AND b.price=:price")
List<Book> findByNamedParam(@Param("name") String name, @Param("author") String author,
@Param("price") long price);
[/code]
In the above query, we are using the named parameters to bind the query parameter and method arguments. This is the most recommended way for binding the parameters. When we have multiple parameters, this option is good instead of position based binding.
SpEL Expressions
Support for SpEL expression in the query is limited and it is introduced from the release Spring Data JPA 1.4. When query execution happens, the query is evaluated with the pre-determined value using the SpEL expressions.
Here is the example code for SpEL expressions.
[code lang=”java”]
@Query(value = "select * from #{#entityName} b where b.name=?1", nativeQuery = true)
List<Book> findByName(String name);
[/code]
#{#entityName} is the SpEL expression used to get the entity name. At this time SpEL expressions are used only for the entity name, in the future version there could be more expressions.
@Query Annotation Example Application
Note that here I am not listing the entire code used for this tutorial. If you want to browser the whole project, please download the source code in the download section.
The only extra class I have added for this example is a new repository interface to define the query methods with @Query annotation.
BookQueryRepositoryExample.java
Here is the new repository class for demonstrating the @Query annotation.
[code lang=”java”]
public interface BookQueryRepositoryExample extends Repository<Book, Long> {
@Query(value = "select * from Book b where b.name=?1", nativeQuery = true)
List<Book> findByName(String name);
@Query(value = "select name,author,price from Book b where b.price>?1 and b.price<?2")
List<Book> findByPriceRange(long price1, long price2);
@Query(value = "select name,author,price from Book b where b.name like %:name%")
List<Book> findByNameMatch(@Param("name") String name);
@Query(value = "select name,author,price from Book b where b.name = :name AND b.author=:author AND b.price=:price")
List<Book> findByNamedParam(@Param("name") String name, @Param("author") String author,
@Param("price") long price);
}
[/code]
Apart from the above class, I have modified the controller class, service implementation access this repository from the REST API.
Also I have newly added data.sql and schema.sql to initialize the data at the time of spring boot application startup.
data.sql
[code]
INSERT INTO book(id,name,author,price) VALUES (1,’Spring In Action:Covers Spring 3.0′,’Craig Walls’, 400);
INSERT INTO book(id,name,author,price) VALUES (2,’Spring Batch In Action’,’Arnaud Cogoluegnes’, 500);
INSERT INTO book(id,name,author,price) VALUES (3,’Spring Book’,’Rod’, 300);
INSERT INTO book(id,name,author,price) VALUES (4,’Java Book’,’test’, 100);
INSERT INTO book(id,name,author,price) VALUES (5,’Spring Boot Application’,’test’, 200);
INSERT INTO book(id,name,author,price) VALUES (6,’Learn Spring Data JPA’,’test’, 300);
INSERT INTO book(id,name,author,price) VALUES (7,’Hibernat in Action’,’Gavin King’, 300);
INSERT INTO book(id,name,author,price) VALUES (8,’JSF in Action’,’Kit’, 400);
INSERT INTO book(id,name,author,price) VALUES (9,’Java Interview Questions’,’Subramanian’, 450);
INSERT INTO book(id,name,author,price) VALUES (10,’Java Complete Reference’,’Herbert’, 450);
[/code]
schema.sql
[code]
create table book(
id int not null primary key,
name varchar_ignorecase(50) not null,
author varchar_ignorecase(50) not null,
price int);
[/code]
Download Source Code
Here is the complete source code used for this tutorial available for the download. This source code is not only for the query annotation example, but it contains the all other Spring Data JPA examples.
[wpdm_package id=’22582′]
Conclusion
I hope this tutorial have provided more insight on how to use the query annotation. If you are working on Spring Data, understanding the features of query annotation is important since most of the times using query methods won’t be sufficient. In this tutorial I have explained about how to use @Query annotation in the query methods, using like expressions, using named parameters for the binding and SpEL expression inside query. If you are interested in reading the basics of Spring Data JPA, please read our more detailed tutorial about Spring Data JPA.
Exceptions
QuerySyntaxException : unexpected token / Validation failed for query for method
When you are trying to execute the native SQL queries, you may encounter the below exception. If you are happen to see the below exception, there is most likely chance is that you are using native SQL query without the attribute nativeQuery=true. If you are using the SQL query without the attribute nativeQuery=true, Spring Data JPA will try to validate the query against the JPQL format. You will get the below exception.
Note that, this same exception would be thrown even if there is a syntax error in the query.
[code]
Caused by: java.lang.IllegalArgumentException: Validation failed for query for method public abstract java.util.List net.javabeat.spring.data.service.BookQueryRepositoryExample.findByName(java.lang.String)!
at org.springframework.data.jpa.repository.query.SimpleJpaQuery.validateQuery(SimpleJpaQuery.java:92)
at org.springframework.data.jpa.repository.query.SimpleJpaQuery.<init>(SimpleJpaQuery.java:62)
Caused by: java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: * near line 1, column 8 [select * from net.javabeat.spring.data.domain.Book b where b.name=?1]
at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1750)
at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1677)
[/code]
To avoid the above exception, add the nativeQuery attribute as below:
[code lang=”java”]
@Query(value = "select * from #{#entityName} b where b.name=?1", nativeQuery = true)
List<Book> findByName(String name);
[/code]
If this tutorial is useful, we are happy. If you could find the information what you are looking for, please request us to update the tutorial in the comments section.
Endpoint – Metrics
Let us run the application by using the command: 
Let us run the application again using 
