SpringDataJPA笔记(4)-命名查询与基础规则
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();
}