SpringData JPA的复杂查询

「爱情、让人受尽委屈。」 2023-06-30 15:54 137阅读 0赞

# 查询 #

## 调用接口方法查询 ##

![在这里插入图片描述][20200118155012205.png]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poaXpoZW5nZ3Vhbg_size_16_color_FFFFFF_t_70]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poaXpoZW5nZ3Vhbg_size_16_color_FFFFFF_t_70 1]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poaXpoZW5nZ3Vhbg_size_16_color_FFFFFF_t_70 2]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poaXpoZW5nZ3Vhbg_size_16_color_FFFFFF_t_70 3]

## jpql查询 ##

**使用@Query注解，结合JPQL的语句方式完成查询**

/**
     * JpaRepository<实体类类型，主键类型>：用来完成基本CRUD操作
     * JpaSpecificationExecutor<实体类类型>：用于复杂查询（分页等查询操作）
     */
    public interface CustomerDao extends JpaRepository<Customer, Long>, JpaSpecificationExecutor<Customer> {
        
    
        //@Query 使用jpql的方式查询。
        @Query(value="from Customer")
        public List<Customer> findAllCustomer();
    
        //@Query 使用jpql的方式查询。?1代表参数的占位符，其中1对应方法中的参数索引
        @Query(value="from Customer where custName = ?1")
        public  List<Customer>  findCustomer(String custName);
    
       // @Query结合@Modifying 来将该操作标识为修改查询
        @Query(value="update Customer set custName = ?1 where custId = ?2")
        @Modifying
        public void updateCustomer(String custName,Long custId);
    }

测试方法：

@Test
        public void testfindAllCustomer(){
        
            List<Customer> allCustomer = customerDao.findAllCustomer();
            for (Customer c:
                 allCustomer) {
        
                System.out.println(c);
            }
        }
    
    
        @Test
        public void testfindCustomer(){
        
            List<Customer> allCustomer =customerDao.findCustomer("船只");
            for (Customer c:
                    allCustomer) {
        
                System.out.println(c);
            }
        }
    
        @Test
        @Transactional  // 默认会回滚
        @Rollback(false) // 不自动回滚
        public void testQUpdata(){
        
            customerDao.updateCustomer("aaaa", 8l);
        }

## sql查询【了解】 ##

/**
         * nativeQuery : 使用本地sql的方式查询
         */
        @Query(value="select * from cst_customer",nativeQuery=true)
        public List<Customer> findSql();
    
        @Query(value="select * from cst_customer where cust_name like ?1",nativeQuery=true)
        public List<Customer> findSqlByName(String Nama);

# 方法命名规则查询 #

*  是对jpql查询进行的更加深入的封装
 *  只需要按照SpringDataJPA提供的方法名称规则定义方法，不需要再去配置jpql语句，完成查询
 *  Spring Data JPA在程序执行的时候会根据方法名称进行解析，并自动生成查询语句进行查询

/**
         * 方法名的约定：
         *      findBy : 查询
         *            对象中的属性名（首字母大写） ： 查询的条件
         *            CustName
         *            * 默认情况 ： 使用 等于的方式查询
         *                  特殊的查询方式
         *
         *  findByCustName   --   根据客户名称查询
         *
         *  再springdataJpa的运行阶段
         *          会根据方法名称进行解析  findBy    from  xxx(实体类)
         *                                      属性名称      where  custName =
         *
         *      1.findBy  + 属性名称 （根据属性名称进行完成匹配的查询=）
         *      2.findBy  + 属性名称 + “查询方式（Like | isnull）”
         *          findByCustNameLike
         *      3.多条件查询
         *          findBy + 属性名 + “查询方式”   + “多条件的连接符（and|or）”  + 属性名 + “查询方式”
         */
        //方法命名方式查询（根据客户名称查询客户）
        public List<Customer> findByCustName(String custName);

<table> 
 <thead> 
  <tr> 
   <th>Keyword</th> 
   <th>Sample</th> 
   <th>JPQL</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>And</td> 
   <td>findByLastnameAndFirstname</td> 
   <td>… where x.lastname = ?1 and x.firstname = ?2</td> 
  </tr> 
  <tr> 
   <td>Or</td> 
   <td>findByLastnameOrFirstname</td> 
   <td>… where x.lastname = ?1 or x.firstname = ?2</td> 
  </tr> 
  <tr> 
   <td>Is,Equals</td> 
   <td>findByFirstnameIs,findByFirstnameEquals</td> 
   <td>… where x.firstname = ?1</td> 
  </tr> 
  <tr> 
   <td>Between</td> 
   <td>findByStartDateBetween</td> 
   <td>… where x.startDate 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>findByStartDateAfter</td> 
   <td>… where x.startDate &gt; ?1</td> 
  </tr> 
  <tr> 
   <td>Before</td> 
   <td>findByStartDateBefore</td> 
   <td>… where x.startDate &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>findByFirstnameLike</td> 
   <td>… where x.firstname like ?1</td> 
  </tr> 
  <tr> 
   <td>NotLike</td> 
   <td>findByFirstnameNotLike</td> 
   <td>… where x.firstname not like ?1</td> 
  </tr> 
  <tr> 
   <td>StartingWith</td> 
   <td>findByFirstnameStartingWith</td> 
   <td>… where x.firstname like ?1 (parameter bound with appended %)</td> 
  </tr> 
  <tr> 
   <td>EndingWith</td> 
   <td>findByFirstnameEndingWith</td> 
   <td>… where x.firstname like ?1 (parameter bound with prepended %)</td> 
  </tr> 
  <tr> 
   <td>Containing</td> 
   <td>findByFirstnameContaining</td> 
   <td>… where x.firstname like ?1 (parameter bound wrapped in %)</td> 
  </tr> 
  <tr> 
   <td>OrderBy</td> 
   <td>findByAgeOrderByLastnameDesc</td> 
   <td>… where x.age = ?1 order by x.lastname desc</td> 
  </tr> 
  <tr> 
   <td>Not</td> 
   <td>findByLastnameNot</td> 
   <td>… where x.lastname &lt;&gt; ?1</td> 
  </tr> 
  <tr> 
   <td>In</td> 
   <td>findByAgeIn(Collection ages)</td> 
   <td>… where x.age in ?1</td> 
  </tr> 
  <tr> 
   <td>NotIn</td> 
   <td>findByAgeNotIn(Collection age)</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>FALSE</td> 
   <td>findByActiveFalse()</td> 
   <td>… where x.active = false</td> 
  </tr> 
  <tr> 
   <td>IgnoreCase</td> 
   <td>findByFirstnameIgnoreCase</td> 
   <td>… where UPPER(x.firstame) = UPPER(?1)</td> 
  </tr> 
 </tbody> 
</table>

[20200118155012205.png]: https://img-blog.csdnimg.cn/20200118155012205.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poaXpoZW5nZ3Vhbg_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200118155210502.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poaXpoZW5nZ3Vhbg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poaXpoZW5nZ3Vhbg_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200118155639768.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poaXpoZW5nZ3Vhbg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poaXpoZW5nZ3Vhbg_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200118155846175.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poaXpoZW5nZ3Vhbg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poaXpoZW5nZ3Vhbg_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200118155956971.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poaXpoZW5nZ3Vhbg==,size_16,color_FFFFFF,t_70