As in the last post on query DSL, I have discussed about creating various custom finder methods to query in database. You also know that spring data JPA internally form JPQL to query in database. In this module I am going to cover query annotations that spring data JPA provides with some advanced features.
@Query annotation
In order to define SQL to execute for a Spring Data repository method, we can annotate the method with the @Query annotation — its value attribute contains the JPQL or SQL to execute.
In the custom JPA repository we can define multiple query DSL methods to query data from database for different condition. Consider a case where condition being passed in the query are too many. Though you can form custom finder method but the method is going to be very large and cumbersome. In such type of scenario the best way is to write simple method and use @Query annotation on top of method. When the @query annotation is being used spring data JPA ignores the structure of finder methods. Let me demonstrate this with an example-
Consider that in the Promotion table ,you have to fetch all promotion having startdate less than a particular date and enddate is greater than a particular date with given description. The JPQL query would look like -
'select p from Promotion p where p.startdate <= :date and p.enddate >= :date and description= :desc' .Now if you happen to write a finder method it will be 'findByStartdateAndEnddateAndDescription'. Still it looks good but what if there are more conditions to add then the method will be very cumbersome.
You can see above that the method is annotated with @Query and the JPQL query has been passed as part of parameter. The query condition is being passed by using @Param annotation inside the method. When the application starts up spring data JPA first looks at @Query and run the query. @Query annotation takes privilege over DSL method.
@Query syntax options
Spring Data provides many ways to define a query that we can execute. One of these is the @Query annotation. Spring data JPA provides many ways to pass parameters inside @Query annotation. Let us discuss each with an example to understand more clearly.
- Named Parameters : In this case parameters are passed as ':paramName'.
Let’s look at a simple repository method that returns Promotion entities from the database as below-
- JPQL syntax : In this case parameters are passed as '?1'.Let’s look at a simple repository method that returns Promotion entities from the database name has 'like' as below-
- Native Queries : We can use also native SQL to define our query. All we have to do is to set the value of the nativeQuery attribute to true and define the native SQL query in the value attribute of the annotation as below-
- Modifiable queries : You can also pass update SQL statement in the parameters of @Query annotation by using @Modifying annotation.
JPA Named Query
When we annotate methods in custom JPA repository interface with @Query ,spring data JPA validates the query at the application starts up. Sometimes it is better to have this validation during runtime and that can be achieved by using @NamedQuery annotation. @NamedQuery annotation is used at entity level.
Let understand this by using an example, consider the PromotionType entity that we have created earlier and the corresponding PromotionTypeJpaRepository interface to write query method -
If we want to create a JPQL query, we must follow these steps:
1. Annotate the entity with the @NamedQuery annotation.
2. Set the name of the named query (PromotionType.findPromotionTypeByname) as the value of the @NamedQuery annotation’s name attribute.
3. Set the JPQL query (select p from PromotionType p where p.name = :name) as the value of the @NamedQuery annotation’s query attribute.
The relevant part of our entity looks as follows:
Now the second things to do is to modify PromotionTypeJpaRepository to add a query method as following-
Here if our named query uses the default naming strategy of Spring Data JPA, we must ensure that the name of the query method identifies the invoked named query and the parameters are used by @Param.
If our named query doesn’t use the default naming strategy, we have to annotate the query method with the @Query annotation and configure the name of invoked named query by using the name attribute of the @Query annotation as below.
Native SQL Support
Spring data JPA also provides support for SQL as well, if you remember we were annotating the query method with the @Query annotation and setting the value of its nativeQuery attribute to true for SQL support. We can do the same task by using @NamedNativeQuery annotation.This can be very useful if you have very complex query with many joins.
Then write method in Jpa repository interface-
Query Location and Precedence
Now that we have learned how to query in database using spring data JPA, you must be wondering as where should I put my query and which will take the highest precedence. There are many ways that you can put query if you are using spring data JPA as the data access layer for your application. They are as follow -
- By defining entity in orm.xml
- By using @NamedQuery and @NamedNativequery annotation
- By using custom finder method in query DSL
- By using @Query annotation
When talking about the precedence methods with @Query annotation take the highest precedence then the methods that match named or native named query "name". At last the methods that follow the query DSL keywords naming structure.
As spring data JPA is a very vast topic hence I have covered it by multiple post. In order to learn and understand spring data JPA completely you need go through each post. Below mentioned order you must follow so that you won't miss anything-
Comments
Post a Comment