Usage of @Query annotations (Spring Data JPA)

1. A simple example of using @query annotations

@Query (value = "Select Name,author,price from book B where B.price>?1 and b.price<?2") list<book> Findbypricer Ange (Long Price1, long price2);

2. Like expressions

@Query (value = "Select Name,author,price from Book B, where b.name like%:name%") list<book> Findbynamematch (@Param (" Name ") String name);

3. Using native SQL Query

The so-called local query, is the use of native SQL statements (depending on the database, the syntax or structure of SQL may be different) to query the operation of the database.

@Query (value = "SELECT * from book B where b.name=?1", Nativequery = True) list<book> findbyname (String name);

4. Injecting parameters with @param annotations

@Query (value = "Select Name,author,price from book B where b.name =: Name and B.author=:author and b.price=:p") list< ; Book> Findbynamedparam (@Param ("name") string name, @Param ("author") string author,        @Param ("price") of long Price);

5. Spel expression (refer to the last supplemental note when using)

' #{#entityName} ' value is the data table name (book) corresponding to the ' book ' object.

Public interface Bookqueryrepositoryexample extends Repository<book, long>{

@Query (value = "SELECT * from #{#entityName} b where b.name=?1", Nativequery = True)
List<book> findbyname (String name);

}

6. A more complete example

 public interface Bookqueryrepositoryexample extends Repository <book, long> {@Query (value = "SELECT * from book B where b.name=?1", Nativequery = True) list<book> fi Ndbyname (String name);//This method SQL will error (java.lang.IllegalArgumentException), see the reason, if not see, see the next example @Query (value = "Select Name,author,price from book B where B.price>?1 and b.price<?2 ") list<book> Findbypricerange (Long Price1, lo    ng Price2); @Query (value = "Select Name,author,price from Book B, where b.name like%:name%") list<book> Findbynamematch (@Para    M ("name") String name); @Query (value = "Select Name,author,price from book B where b.name =: Name and B.author=:author and b.price=:p") Lis  T<book> Findbynamedparam (@Param ("name") string name, @Param ("author") string author, @Param ("price") long Price);}  

7. Explain the cause of the error in example 6:

Because Nativequery = True is specified, a native SQL statement is used to query. It is not right to use the Java object ' book ' as the table name to check nature. Simply replace the book with the table name book.

@Query (value = "SELECT * from book B where b.name=?1", Nativequery = True) list<book> findbyname (String name);

Supplementary note (2017-01-12):

Some classmates proposed, example 5 with ' #{#entityName} ' Why not get the value ah?

First of all, say ' #{#entityName} ' What the hell is. Literally, ' #{#entityName} ' is not the name of the entity class, yes, he is.

Entity class book, with @entity annotations, spring will incorporate entity class book into management. The value of the default ' #{#entityName} ' is ' book '.

But if @entity (name = "book") is used to annotate the entity class book, then the value of ' #{#entityName} ' becomes ' book '.

Here, it is clear that you only need to specify the name of the table for this entity class when you annotate the entity class with @entity. In the native SQL statement, you can use the ' #{#entityName} ' as the data table name.

Usage of @Query annotations (Spring Data JPA)