Spring Data JPA复杂查询

在使用Spring Data JPA框架时，可以按照符合框架规则的自定义方法或@Query进行固定条件查询。如果是动态条件查询框架也提供了接口。

public interface JpaSpecificationExecutor<T>

1.符合框架规则的自定义方法：

 Supported keywords inside method names

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> 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)

2.@Query查询：

  @Modifying
  @Transactional
  @Query("DELETE FROM UnitRevocationRecord WHERE rid in (:ids)")
  void deleteUnitRevocationRecordByIds(@Param("ids") List<String> ids);

3.JpaSpecificationExecutor复杂查询
这个接口的使用和其它接口一样，在你的Dao接口继承即可。

public interface UnitRevocationDao extends PagingAndSortingRepository <UnitRevocationRecord, String>, JpaSpecificationExecutor<UnitRevocationRecord> {}

JpaSpecificationExecutor接口提供了很多的条件查询的接口：

/**
 * Interface to allow execution of {@link Specification}s based on the JPA criteria API.
 * 
 * @author Oliver Gierke
 */
public interface JpaSpecificationExecutor<T> {

	/**
	 * Returns a single entity matching the given {@link Specification}.
	 * 
	 * @param spec
	 * @return
	 */
	T findOne(Specification<T> spec);

	/**
	 * Returns all entities matching the given {@link Specification}.
	 * 
	 * @param spec
	 * @return
	 */
	List<T> findAll(Specification<T> spec);

	/**
	 * Returns a {@link Page} of entities matching the given {@link Specification}.
	 * 
	 * @param spec
	 * @param pageable
	 * @return
	 */
	Page<T> findAll(Specification<T> spec, Pageable pageable);

	/**
	 * Returns all entities matching the given {@link Specification} and {@link Sort}.
	 * 
	 * @param spec
	 * @param sort
	 * @return
	 */
	List<T> findAll(Specification<T> spec, Sort sort);

	/**
	 * Returns the number of instances that the given {@link Specification} will return.
	 * 
	 * @param spec the {@link Specification} to count instances for
	 * @return the number of instances
	 */
	long count(Specification<T> spec);
}

在这些方法中，可以看到查询需要Specification类型的参数

/**
 * Specification in the sense of Domain Driven Design.
 * 
 * @author Oliver Gierke
 * @author Thomas Darimont
 * @author Krzysztof Rzymkowski
 */
public interface Specification<T> {

	/**
	 * Creates a WHERE clause for a query of the referenced entity in form of a {@link Predicate} for the given
	 * {@link Root} and {@link CriteriaQuery}.
	 * 
	 * @param root
	 * @param query
	 * @return a {@link Predicate}, may be {@literal null}.
	 */
	Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder cb);
}

root参数用来对应实体的信息的。criteriaBuilder可以帮助我们制作查询信息,它提供了很多的方法:大于，小于，等于等。如果有多个条件，我们就可以创建一个Predicate集合，最后用CriteriaBuilder的and和or方法进行组合，得到最后的Predicate对象。

示例代码：

public PageQueryData<UnitRevocationListDto> queryUnitRevocationRecord(PageQueryData <UnitRevocationListDto> pageQueryData, Integer queryType) {
    String userId = WebServiceUtils.getCurrentUserId();
    Pageable pageable = new PageRequest(pageQueryData.getPage() - 1,pageQueryData.getRows());
    // 查询条件
    Map <String, String> searchTexts = pageQueryData.getSearchTexts();
    // 条件分页功能
    Page <UnitRevocationRecord> pages = unitRevocationDao.findAll((root, query, cb) -> {
      List<Predicate> lstPredicates = new ArrayList <>();
      if(searchTexts != null){
        if(!CheckUtil.isNullorEmpty(searchTexts.get("unitId"))){
          lstPredicates.add(cb.equal(root.get("unitId"),searchTexts.get("unitId")));
        }
        if(!CheckUtil.isNullorEmpty(searchTexts.get("applicantId"))){
          lstPredicates.add(cb.equal(root.get("applicantId"),searchTexts.get("applyUnitId")));
        }
        if(!CheckUtil.isNullorEmpty(searchTexts.get("state"))){
          lstPredicates.add(cb.equal(root.get("state"),searchTexts.get("state")));
        }
      }
      if(queryType == 0){
        lstPredicates.add(cb.equal(root.get("applicantId"),userId));
      }else if(queryType == 1){
        lstPredicates.add(cb.notEqual(root.get("state"),EnumUnitRevocationState.NREPORT.getState()));
        lstPredicates.add(cb.notEqual(root.get("state"),EnumUnitRevocationState.CALLBACK.getState()));
      }
      Predicate[] arrayPredicates = new Predicate[lstPredicates.size()];
      return cb.and(lstPredicates.toArray(arrayPredicates));
    }, pageable);
    
    List <UnitRevocationRecord> content = pages.getContent();
    List <UnitRevocationListDto> records = new ArrayList <>();
    content.forEach(item -> {
      UnitRevocationListDto unitRevocationListDto = this.convertRevocation(queryType, item);
      records.add(unitRevocationListDto);
    });
    pageQueryData.setTotal((int) pages.getTotalElements());
    pageQueryData.setQueryList(records);
    return pageQueryData;
  }