Query annotation and param annotation in spring

@Query Annotation

1.@Query could be used to write the more flexible query to fetch data When we can not use the query methods to perform database operations.

2. We can use both JPQL and native SQL queries through @Query annotation. 

3. We would get the query syntax error exceptions if we write normal SQL queries.We can only write JPQL to execute the queries.

4. Set the nativeQuery flag to true , If we want to write native SQL queries. Spring data JPA not supported pagination and dynamic sorting for native queries.

5. If we are using the query methods with @Query annotation and also using @NamedQuery than @Query will take the precedence, named queries in orm.xml and method names.

For example, if we create findById query method and applying @Query annotation then spring data jpa will not search the entity with Id property.It will call the query configured with @Query annotation.

A simple example of using the query annotation.

public interface BookRepository extends JpaRepository<Book , Long> {

  @Query(value = "select name,author,price from Book b where b.price>?1 and b.price<?2")

   List<Book> findByPriceRange(long price1, long price2):

}

In the above query example, we are having to parameters to compare the prices and filter the results. In above query, parameter is preceded by? character to indicate that this is the parameter to be bind with method arguments.

Named Parameters using @Param

1.To bind the query parameter names in method parameter name, we use @Param annotation in method parameter. 

2.we have to use: paramName to indicate that the same paramNames to be bind with method parameter, inside the query.

Here is the example query:

@Query (value = "select name,author,price from Book b where b.name = :name AND b.auhtor = :author AND b.price = :price")

List<Book> findByNamedParam(@Param("name") String name , @Param("author") String author, @Param("price") long price);

In the above query, we used the named parameters (name, author, price) to bind the query parameter and method arguments. This is the best way to binding the parameters.