SpringDataJPA筆記(4)-命名查詢與基礎規則

命名查詢與基本規則

1. JPA可以根據函數名生成基本的查詢語句，下表是支援的關鍵字

Keyword	Sample	JPQL snippet
And	findByLastnameAndFirstname	… where x.lastname = ?1 and x.firstname = ?2
Or	findByLastnameOrFirstname	… where x.lastname = ?1 or x.firstname = ?2
Is,Equals	findByFirstname,findByFirstnameIs,findByFirstnameEquals	… where x.firstname = ?1
Between	findByStartDateBetween	… where x.startDate between ?1 and ?2
LessThan	findByAgeLessThan	… where x.age < ?1
LessThanEqual	findByAgeLessThanEqual	… where x.age <= ?1
GreaterThan	findByAgeGreaterThan	… where x.age > ?1
GreaterThanEqual	findByAgeGreaterThanEqual	… where x.age >= ?1
After	findByStartDateAfter	… where x.startDate > ?1
Before	findByStartDateBefore	… where x.startDate < ?1
IsNull	findByAgeIsNull	… where x.age is null
IsNotNull,NotNull	findByAge(Is)NotNull	… where x.age not null
Like	findByFirstnameLike	… where x.firstname like ?1
NotLike	findByFirstnameNotLike	… where x.firstname not like ?1
StartingWith	findByFirstnameStartingWith	… where x.firstname like ?1(parameter bound with appended %)
EndingWith	findByFirstnameEndingWith	… where x.firstname like ?1(parameter bound with prepended %)
Containing	findByFirstnameContaining	… where x.firstname like ?1(parameter bound wrapped in %)
OrderBy	findByAgeOrderByLastnameDesc	… where x.age = ?1 order by x.lastname desc
Not	findByLastnameNot	… where x.lastname <> ?1
In	findByAgeIn(Collection<Age> ages)	… where x.age in ?1
NotIn	findByAgeNotIn(Collection<Age> ages)	… where x.age not in ?1
True	findByActiveTrue()	… where x.active = true
False	findByActiveFalse()	… where x.active = false
IgnoreCase	findByFirstnameIgnoreCase	… where UPPER(x.firstame) = UPPER(?1)

寫幾個方法來測試一下

CatRepository.java

List<CatEntity> findByAgeAndWeight(int age, int weight);

    List<CatEntity> findByAgeLessThan(int age);

    List<CatEntity> findByNameEndingWith(String name);           

複制

ChapterFourController.java

@ApiOperation(value = "findByAgeAndWeight", httpMethod = "GET")
    @GetMapping("/find/age/weight")
    public List<CatEntity> findCat(@RequestParam int age, @RequestParam int weight) {
        return catRepository.findByAgeAndWeight(age, weight);
    }

    @ApiOperation(value = "findByAgeLessThan", httpMethod = "GET")
    @GetMapping("/find/age/less")
    public List<CatEntity> findCat(@RequestParam int age) {
        return catRepository.findByAgeLessThan(age);
    }

    @ApiOperation(value = "findByNameEndingWith", httpMethod = "GET")
    @GetMapping("/find/name/ending")
    public List<CatEntity> findCat(@RequestParam String name) {
        return catRepository.findByNameEndingWith(name);
    }           

複制

2. JPA還有一種命名查詢是在實體類上加上NamedQuery的注解

在CatEntity上加注解

@Data
@Entity
@Table(name = "cat_tb")
@EqualsAndHashCode(callSuper = false)
@NamedQueries(value = {
        @NamedQuery(name = "CatEntity.findCatBySex", query = "select n from CatEntity n where n.sex = ?1"),
        @NamedQuery(name = "CatEntity.findView", query = "select new com.mt.demo.jpa.entity.view.CatView(n.id, n.name) from CatEntity n "),
        @NamedQuery(name = "CatEntity.findView2", query = "select n.id as id, n.name as name from CatEntity n")
})
public class CatEntity extends AnimalEntity<Long> {


    private static final long serialVersionUID = 7456065103323391049L;

    private String miao;
}           

複制

在相應的Repository接口裡面定義一個同名的方法，Spring會先找是否有同名的NamedQuery，如果有，那麼就不會按照接口定義的方法來解析

這裡還寫了兩個視圖查詢的方法

List<CatEntity> findCatBySex(String sex);

List<CatView> findView();

List<CatView2> findView2();           

複制

同樣寫controller進行測試

@ApiOperation(value = "findCatBySex", httpMethod = "GET")
@GetMapping("/find/sex")
public List<CatEntity> findCatBySex(@RequestParam String sex) {
	return catRepository.findCatBySex(sex);
}

@ApiOperation(value = "findCatView", httpMethod = "GET")
@GetMapping("/find/cat/view")
public List<CatView> findCatView() {
	return catRepository.findView();
}

@ApiOperation(value = "findCatView2", httpMethod = "GET")
@GetMapping("/find/cat/view2")
public List<CatView2> findCatView2() {
	return catRepository.findView2();
}           

複制

3. Query注解的使用

下表是JPA的Query支援的關鍵字

Logical keyword	Keyword expressions
AND	And
OR	Or
AFTER	After, IsAfter
BEFORE	Before, IsBefore
CONTAINING	Containing, IsContaining, Contains
BETWEEN	Between, IsBetween
ENDING_WITH	EndingWith, IsEndingWith, EndsWith
EXISTS	Exists
FALSE	False, IsFalse
GREATER_THAN	GreaterThan, IsGreaterThan
GREATER_THAN_EQUALS	GreaterThanEqual, IsGreaterThanEqual
IN	In, IsIn
IS	Is, Equals, (or no keyword)
IS_EMPTY	IsEmpty, Empty
IS_NOT_EMPTY	IsNotEmpty, NotEmpty
IS_NOT_NULL	NotNull, IsNotNull
IS_NULL	Null, IsNull
LESS_THAN	LessThan, IsLessThan
LESS_THAN_EQUAL	LessThanEqual, IsLessThanEqual
LIKE	Like, IsLike
NEAR	Near, IsNear
NOT	Not, IsNot
NOT_IN	NotIn, IsNotIn
NOT_LIKE	NotLike, IsNotLike
REGEX	Regex, MatchesRegex, Matches
STARTING_WITH	StartingWith, IsStartingWith, StartsWith
TRUE	True, IsTrue
WITHIN	Within, IsWithin

在DogRepository裡面寫幾個方法

@Query("select new com.mt.demo.jpa.entity.view.DogView(n .id, n .name) from DogEntity n")
List<DogView> findDogView();

@Query("select n .id, n .name from DogEntity n")
List<DogView2> findDogView2();           

複制

再寫controller進行測試

@ApiOperation(value = "findDogView", httpMethod = "GET")
@GetMapping("/find/dog/view")
public List<DogView> findDogView() {

	return dogRepository.findDogView();
}

@ApiOperation(value = "findDogView2", httpMethod = "GET")
@GetMapping("/find/dog/view2")
public List<DogView2> findDogView2() {
	return dogRepository.findDogView2();
}           

複制