@Usage of Query annotation (Spring Data JPA)

Reference article: http://www.tuicool.com/articles/jQJBNv

 

1. A simple example of @ Query annotation

@Query(value = "select name,author,price from Book b where b.price>?1 and b.price<?2")
List<Book> findByPriceRange(long price1, long price2);

 

2. Like expression

@Query(value = "select name,author,price from Book b where b.name like %:name%")
List<Book> findByNameMatch(@Param("name") String name);

 

3. Use Native SQL Query

The so-called local query refers to the operation of querying the database using native sql statements (the syntax or structure of sql may be different according to different databases).

@Query(value = "select * from book b where b.name=?1", nativeQuery = true)
List<Book> findByName(String name);

 

4. Inject parameters with @ Param annotation

@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);

 

5. SPEL expression (please refer to the final supplementary instructions when using)

The '#{#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> findByName(String name);// The sql of this method will report an error (java.lang.IllegalArgumentException). Do you see the reason? If not, please 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, 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);

}

 

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

Because nativeQuery = true is specified, that is, query with native sql statements. It is naturally wrong to use the java object 'Book' as the table name. Simply replace Book with the table name Book.

@Query(value = "select * from book b where b.name=?1", nativeQuery = true)
List<Book> findByName(String name);

 

 

Supplementary notes (January 12, 2017):

A classmate put it forward. Why can't you get a value by using '#{#entityName}' in example 5?

Let's talk about what '#{#entityName}' is. Literally, '#{#entityName}' is the name of the entity class. Yes, it is.

Entity class Book. After using @ entity annotation, spring will bring entity class Book into management. The default value of '#{#entityName}' is' Book '.

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

At this point, it will be clear that you only need to specify the name of the table corresponding to this Entity class when you annotate the Entity class with @ Entity. In the native sql statement, '#{#entityName}' can be used as the data table name.

Tags: Java

Posted by sandthipe on Wed, 25 May 2022 08:55:33 +0300