Springboot + MySQL+ JPA Ⅴ find拓展方法

深碍√TFBOYSˉ_ 2023-10-02 15:55 25阅读 0赞

JPA除了自带的几个比较常用的接口外，还提供了许多可以自由组合的拓展接口，这些接口需要再dao层进行自定义，很多接口从名字上就能知道其作用，也是比较方便的。

<table> 
 <thead> 
  <tr> 
   <th>关键字</th> 
   <th>示例</th> 
   <th>JPQL片断</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>And</td> 
   <td>findByNameAndAge</td> 
   <td>... where x.name = ?1 and x.age = ?2</td> 
  </tr> 
  <tr> 
   <td>Or</td> 
   <td>findByNameOrAge</td> 
   <td>... where x.name = ?1 or x.age = ?2</td> 
  </tr> 
  <tr> 
   <td>Is,Equals</td> 
   <td> <p>findByName,findByNameIs,</p> <p>findByNameEquals</p> </td> 
   <td>... where x.name = ?1</td> 
  </tr> 
  <tr> 
   <td>Between</td> 
   <td>findByAgeBetween</td> 
   <td>... where x.age between ?1 and ?2</td> 
  </tr> 
  <tr> 
   <td>LessThan</td> 
   <td>findByAgeLessThan</td> 
   <td>... where x.age &lt; ?1</td> 
  </tr> 
  <tr> 
   <td>LessThanEqual</td> 
   <td>findByAgeLessThanEqual</td> 
   <td>... where x.age &lt;= ?1</td> 
  </tr> 
  <tr> 
   <td>GreaterThan</td> 
   <td>findByAgeGreaterThan</td> 
   <td>... where x.age &gt; ?1</td> 
  </tr> 
  <tr> 
   <td>GreaterThanEqual</td> 
   <td>findByAgeGreaterThanEqual</td> 
   <td>... where x.age &gt;= ?1</td> 
  </tr> 
  <tr> 
   <td>After</td> 
   <td>findByAgeAfter</td> 
   <td>... where x.age &gt; ?1</td> 
  </tr> 
  <tr> 
   <td>Before</td> 
   <td>findByAgeBefore</td> 
   <td>... where x.age&lt; ?1</td> 
  </tr> 
  <tr> 
   <td>IsNull</td> 
   <td>findByAgeIsNull</td> 
   <td>... where x.age is null</td> 
  </tr> 
  <tr> 
   <td>IsNotNull,NotNull</td> 
   <td>findByAge(Is)NotNull</td> 
   <td>... where x.age not null</td> 
  </tr> 
  <tr> 
   <td>Like</td> 
   <td>findByNameLike</td> 
   <td>... where x.name like ?1</td> 
  </tr> 
  <tr> 
   <td>NotLike</td> 
   <td>findByNameNotLike</td> 
   <td>... where x.name not like ?1</td> 
  </tr> 
  <tr> 
   <td>StartingWith</td> 
   <td>findByNameStartingWith</td> 
   <td>... where x.name like ?1 (参数会绑定到%后面)</td> 
  </tr> 
  <tr> 
   <td>EndingWith</td> 
   <td>findByNameEndingWith</td> 
   <td>... where x.name like ?1 (参数会绑定在%前面)</td> 
  </tr> 
  <tr> 
   <td>Containing</td> 
   <td>findByNameContaining</td> 
   <td>... where x.name like ?1 (参数会绑定在两个%中间)</td> 
  </tr> 
  <tr> 
   <td>OrderBy</td> 
   <td>findByAgeOrderByNameDesc</td> 
   <td>... where x.age = ?1 order by name desc</td> 
  </tr> 
  <tr> 
   <td>Not</td> 
   <td>findByNameNot</td> 
   <td>... where x.name &lt;&gt; ?1</td> 
  </tr> 
  <tr> 
   <td>In</td> 
   <td>findByAgeIn(Collection&lt;Age&gt; ages)</td> 
   <td>... where x.age in ?1</td> 
  </tr> 
  <tr> 
   <td>NotIn</td> 
   <td>findByAgeNotIn(Connection&lt;Age&gt; ages)</td> 
   <td>... where x.age not in ?1</td> 
  </tr> 
  <tr> 
   <td>True</td> 
   <td>findByActiveTrue()</td> 
   <td>... where x.active = true</td> 
  </tr> 
  <tr> 
   <td>Flase</td> 
   <td>findByActiveFalse()</td> 
   <td>... where x.active = false</td> 
  </tr> 
  <tr> 
   <td>IgnoreCase</td> 
   <td>findByNameIgnoreCase</td> 
   <td>... where UPPER(x.name) = UPPER(?1)</td> 
  </tr> 
 </tbody> 
</table>

#### **举个简单的例子：** ####

#### **findBy+字段名（select \* from table\_name where XX == ?）** ####

dao层

List<User> findByName(String name);

service层

@Transactional
    public List<User> findByName(String name){
        return userDao.findByName(name);
    }

control层

/**
    * 通过name查询数据
    * @param name
    * @return
    */
    @GetMapping("/findByName")
    public List<User> findByName(String name){
        return userService.findByName(name);
    }

查询数据库中name为ale的数据，测试成功！

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA6Iul5Y2a6LGG_size_19_color_FFFFFF_t_70_g_se_x_16][]

后台执行了两个sql

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA6Iul5Y2a6LGG_size_20_color_FFFFFF_t_70_g_se_x_16][]

### **其他的基本就是按照这样去操作就行了，比较简单就不一一展示了！有问题留言就行！** ###

[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA6Iul5Y2a6LGG_size_19_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/3f7a85dbf4954d8abc753845193d3c92.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA6Iul5Y2a6LGG,size_19,color_FFFFFF,t_70,g_se,x_16
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA6Iul5Y2a6LGG_size_20_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/ef372e22f2e04469ae869d71548765b4.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA6Iul5Y2a6LGG,size_20,color_FFFFFF,t_70,g_se,x_16