转载

Spring Data JPA 持久层接口进阶：自定义查询和自定义SQL查询

jpa中查询分为两类：一类是继承了父类的方法的基本查询（前文已大概讲述了），另一类是自定义查询。

一、基础自定义查询

Spring Data JPA 可以根据接口方法名来实现数据库操作，主要的语法是 findXXBy、readAXXBy、queryXXBy、countXXBy、getXXBy 后面跟属性名称，利用这个功能仅需要在定义的 Repository 中添加对应的方法名即可，使用时 Spring Boot 会自动帮我们实现.

1、根据用户名查询用户：

User findByUserName(String userName);

2、也可以加一些关键字 And、or：

User findByUserNameOrEmail(String username,String email);

3、修改、删除、统计也是类似语法：

Long deleteById(Long id);

Long countByUserName(String userName)

4、基本上 SQL 体系中的关键词都可以使用，如 LIKE 、IgnoreCase、OrderBy：

List findByEmailLike(String email);

User findByUserNameIgnoreCase(String userName);

List findByUserNameOrderByEmailDesc(String email);

二、关键字的使用和生成的SQL语句

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	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 ages)	… where x.age in ?1
NotIn	findByAgeNotIn(Collection age)	… 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)

三、自定义SQL查询

在UserRepository中增加方法:

/**
     * @Author Smith
     * @Description 自定义Sql查询.(这个本来是HQL的写法,我的运行不了,改成了本地的SQL)
     * @Date 10:18 2019/1/24
     * @Param 
     * @return org.springframework.data.domain.Page<com.jpa.springdatajpa.model.User>
     **/
    @Query(value = "select * from user",nativeQuery = true)
    Page<User> findALL(Pageable pageable);

    /**
     * @Author Smith
     * @Description 原生SQL的写法,?1表示方法参数中的顺序
     * @Date 10:20 2019/1/24
     * @Param 
     * @return org.springframework.data.domain.Page<com.jpa.springdatajpa.model.User>
     **/
    @Query(value = "select * from user where nick_name = ?1",nativeQuery = true)
    Page<User> findByNickName(String nickName, Pageable pageable);

    /**
     * @Author Smith
     * @Description 修改,添加事务的支持
     * @Date 10:21 2019/1/24
     * @Param 
     * @return int
     **/
    @Transactional(timeout = 10)
    @Modifying
    @Query("update User set userName = ?1 where id = ?2")
    int modifyById(String  userName, Long id);

    /**
     * @Author Smith
     * @Description 删除
     * @Date 10:22 2019/1/24
     * @Param 
     * @return void
     **/
    @Transactional
    @Modifying
    @Query("delete from User where id = ?1")
    @Override
    void deleteById(Long id);

3.1、测试

@Test
public void testFindALL(){
    int page = 1;
    int size = 1;
    Sort sort = new Sort(Sort.Direction.DESC,"id");
    Pageable pageable = PageRequest.of(page,size,sort);
    Page<User> all = userRepository.findALL(pageable);
    Assert.assertEquals(1,all.getContent().size());
    Assert.assertEquals(2,all.getTotalPages());
}

@Test
public void testFindByNickName(){
    int page = 0;
    int size = 1;
    Sort sort = new Sort(Sort.Direction.DESC,"id");
    Pageable pageable = PageRequest.of(page,size,sort);
    Page<User> all = userRepository.findByNickName("bb",pageable);
    Assert.assertEquals(1,all.getContent().size());
    Assert.assertEquals(1,all.getTotalPages());
}

四、限制查询

只需要查询前 N 个元素，或者只取前一个实体。

User findFirstByOrderByNickNameAsc();
User findTopByOrderByIdDesc();
Page<User> queryFirst10ByNickName(String nickName, Pageable pageable);
List<User> findFirst10ByNickName(String nickName, Sort sort);
List<User> findTop10ByNickName(String nickName, Pageable pageable);

五、复杂查询

在某些情况下查询条件很多,需要不断拼接属性,方法名会显得很长,这个时候就要使用JpaSpecificationExecutor 接口了.

概念:

Root root，代表了可以查询和操作的实体对象的根，开一个通过 get(“属性名”) 来获取对应的值。
CriteriaQuery query，代表一个 specific 的顶层查询对象，它包含着查询的各个部分，比如 select 、from、where、group by、order by 等。
CriteriaBuilder cb，来构建 CritiaQuery 的构建器对象，其实就相当于条件或者是条件组合，并以 Predicate 的形式返回。