SpringDataJPA笔记(4)-命名查询与基础规则

柔情只为你懂 2022-02-05 04:53 494阅读 0赞

命名查询与基本规则

#### 1. JPA可以根据函数名生成基本的查询语句，下表是支持的关键字 ####

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

写几个方法来测试一下

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支持的关键字

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

在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();
    }

源码参考 [GITHUB][]  
欢迎关注微信交流  
![在这里插入图片描述][70]

[GITHUB]: https://github.com/yingziisme/spring-boot-jpa-demo
[70]: /images/20220205/64e27d130d5947d48ec303523787f1d6.png