用Spring Data Jpa的SpecificationExecutor接口做复杂查询

男娘i 2022-04-12 03:55 218阅读 0赞

JpaSpecificationExecutor接口

Dao接口继承(官网代码示例)

public interface CustomerRepository extends CrudRepository<Customer, Long>, JpaSpecificationExecutor {
 …
 }

JpaSpecificationExecutor提供很多条件查询方法

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

比如方法:

<table> 
 <tbody> 
 <tr> 
 <td> 1 </td> 
 <td> <code>List&lt;T&gt; findAll(Specification&lt;T&gt; var1);</code> </td> 
 </tr> 
 </tbody> 
</table>

就可以查找出符合条件的所有数据，如果你的框架使用的是前段分页的技术，那么这个方法就挺简便的。

那么这个方法该如何使用呢？我们看到它需要的参数是一个

<table> 
 <tbody> 
 <tr> 
 <td> 1 </td> 
 <td> <code>org.springframework.data.jpa.domain.Specification</code> </td> 
 </tr> 
 </tbody> 
</table>

对象。那我们就创建这个对象先看看。

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 </td> 
 <td> <code>Specification specification = </code><code>new</code> <code>Specification() { </code> <code>&nbsp;&nbsp;&nbsp;</code><code>@Override</code> <code>&nbsp;&nbsp;&nbsp;</code><code>public</code> <code>Predicate toPredicate(Root root, CriteriaQuery criteriaQuery, CriteriaBuilder criteriaBuilder) { </code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>return</code> <code>null</code><code>;</code> <code>&nbsp;&nbsp;&nbsp;</code><code>}</code> <code>&nbsp;&nbsp;</code><code>}</code> </td> 
 </tr> 
 </tbody> 
</table>

IDE自动生成了要重写的方法toPredicate。

root参数是我们用来对应实体的信息的。criteriaBuilder可以帮助我们制作查询信息。

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 </td> 
 <td> <code>/**</code> <code>&nbsp;</code><code>* A root type in the from clause.</code> <code>&nbsp;</code><code>* Query roots always reference entities.</code> <code>&nbsp;</code><code>*</code> <code>&nbsp;</code><code>* @param &lt;X&gt; the entity type referenced by the root</code> <code>&nbsp;</code><code>* @since Java Persistence 2.0</code> <code>&nbsp;</code><code>*/</code> <code>public</code> <code>interface</code> <code>Root&lt;X&gt; </code><code>extends</code> <code>From&lt;X, X&gt; {...}</code> </td> 
 </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 10 11 </td> 
 <td> <code>/**</code> <code>&nbsp;</code><code>* Used to construct criteria queries, compound selections,</code> <code>&nbsp;</code><code>* expressions, predicates, orderings.</code> <code>&nbsp;</code><code>*</code> <code>&nbsp;</code><code>* &lt;p&gt; Note that &lt;code&gt;Predicate&lt;/code&gt; is used instead of &lt;code&gt;Expression&lt;Boolean&gt;&lt;/code&gt;</code> <code>&nbsp;</code><code>* in this API in order to work around the fact that Java</code> <code>&nbsp;</code><code>* generics are not compatible with varags.</code> <code>&nbsp;</code><code>*</code> <code>&nbsp;</code><code>* @since Java Persistence 2.0</code> <code>&nbsp;</code><code>*/</code> <code>public</code> <code>interface</code> <code>CriteriaBuilder {...}</code> </td> 
 </tr> 
 </tbody> 
</table>

CriteriaBuilder对象里有很多条件方法，比如制定条件:某条数据的创建日期小于今天。

<table> 
 <tbody> 
 <tr> 
 <td> 1 </td> 
 <td> <code>criteriaBuilder.lessThan(root.get(</code><code>"createDate"</code><code>), today)</code> </td> 
 </tr> 
 </tbody> 
</table>

该方法返回的对象类型是Predicate。正是toPredicate需要返回的值。

如果有多个条件，我们就可以创建一个Predicate集合，最后用CriteriaBuilder的and和or方法进行组合，得到最后的Predicate对象

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 </td> 
 <td> <code>/**</code> <code>&nbsp;&nbsp;</code><code>* Create a conjunction of the given restriction predicates.</code> <code>&nbsp;&nbsp;</code><code>* A conjunction of zero predicates is true.</code> <code>&nbsp;&nbsp;</code><code>*</code> <code>&nbsp;&nbsp;</code><code>* @param restrictions zero or more restriction predicates</code> <code>&nbsp;&nbsp;</code><code>*</code> <code>&nbsp;&nbsp;</code><code>* @return and predicate</code> <code>&nbsp;&nbsp;</code><code>*/</code> <code>&nbsp;</code><code>Predicate and(Predicate... restrictions);</code> </td> 
 </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 </td> 
 <td> <code>/**</code> <code>&nbsp;&nbsp;</code><code>* Create a disjunction of the given restriction predicates.</code> <code>&nbsp;&nbsp;</code><code>* A disjunction of zero predicates is false.</code> <code>&nbsp;&nbsp;</code><code>*</code> <code>&nbsp;&nbsp;</code><code>* @param restrictions zero or more restriction predicates</code> <code>&nbsp;&nbsp;</code><code>*</code> <code>&nbsp;&nbsp;</code><code>* @return or predicate</code> <code>&nbsp;&nbsp;</code><code>*/</code> <code>&nbsp;</code><code>Predicate or(Predicate... restrictions);</code> </td> 
 </tr> 
 </tbody> 
</table>

示例

public List<WeChatGzUserInfoEntity> findByCondition(Date minDate, Date maxDate, String nickname){
 List<WeChatGzUserInfoEntity> resultList = null;
 Specification querySpecifi = new Specification<WeChatGzUserInfoEntity>() {
 @Override
 public Predicate toPredicate(Root<WeChatGzUserInfoEntity> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
 
 List<Predicate> predicates = new ArrayList<>();
 if(null != minDate){
 predicates.add(criteriaBuilder.greaterThan(root.get("subscribeTime"), minDate));
 }
 if(null != maxDate){
 predicates.add(criteriaBuilder.lessThan(root.get("subscribeTime"), maxDate));
 }
 if(null != nickname){
 predicates.add(criteriaBuilder.like(root.get("nickname"), "%"+nickname+"%"));
 }
 return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()]));
 }
 };
 resultList = this.weChatGzUserInfoRepository.findAll(querySpecifi);
 return resultList;
 }