SpringDataJPA如何优雅的定制高效率SQL

其实SpringDataJPA很方便，虽然他是重量级ORM框架，但是在SQL定制上一点都不输于Mybatis。
用@Query注解能使用QueryDsl语法。将nativeQuery属性设置为true就能使用原生SQL手写,也就是Mybatis一样的效果。
实现方式也挺简单的,DAO层接口继承一下CrudRepository就可以进行开发。看国内用的少，应该是因为比较新吧。
这是目前最新的官方文档,里边很多操作可以学习https://docs.spring.io/spring-data/jpa/docs/2.1.3.RELEASE/reference/html/
 
那么SpringDataJPA在代码中如何进行优雅的动态查询呢 ?
如何通过各种条件组装不同的SQL呢 ?
在复杂SQL中为了避免全表扫描要用函数代替某关键字要如何使用呢?
 
使用SpringDataJPA后,DAO层接口一般会继承JpaRepository接口,它可以提供大量供开发者使用的重载查询方法。
比如带Sort的findAll()方法可以按照指定列排序,带Pageable 的findAll()方法可以轻松地实现分页+排序的功能。
但是对于动态查询、复杂SQL来说还是少了。普通的复杂SQL,子查询左右连接啥的,大不了用@Query注解手写。
但是要再加上动态就不一样了。三个参数就会有七种查询方式(1/2/3/12/13/23/123)。总不能写七个方法定制吧。
莫慌，这个时候, JpaSpecificationExecutor 该接口应该就是所需要的了。
 
来看看 JpaSpecificationExecutor  接口自带的方法:
T findOne(Specification<T> spec);
List<T> findAll(Specification<T> spec);
Page<T> findAll(Specification<T> spec, Pageable pageable);
List<T> findAll(Specification<T> spec, Sort sort);
long count(Specification<T> spec);
 
 
最核心的就是 Specification 这个入参。
若想使用 JpaSpecificationExecutor  该接口的方法,则需要创建出实现了 Specification 的对象。然后将其作为查询条件才行。
这是Specification接口：

/*
 * Copyright 2008-2017 the original author or authors.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package org.springframework.data.jpa.domain;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
/**
 * 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);
}

 
关于 toPredicate() 的几个核心参数 :

• CriteriaQuery
  接口：该接口定义了顶级查询功能，它包含着查询到各个部分，如：select、from、where、group by、order by 等，CriteriaQuery 对象必须在实体类或嵌入式类型上才起作用
• CriteriaBuilder
  类：它是 CriteriaQuery 的工厂类，用于构建 JPA 安全查询，可通过 EntityManager.getCriteriaBuilder 而得
• Root
  接口：该接口继承了 From接口，From 接口继承了 Path 接口，该代表 Criteria 查询的根对象，定义了查询的实体类型，类似于 SQL 中的 FROM 子句，可有多个查询根
• Predicate
  接口：代表了简单或复杂的查询断言（语句），一个 Predicate 可认为由多个连接词构成

 
这是我在公司写的一个model,可以接收前台用户输入的参数，然后通过一个getSpecification() 方法获取一个Specification实现。
之后该Specification即可作为入参进行 findAll() 的查询。
用来举例应是足够了。
我是用了一个lambda来实现 , 没用过JDK1.8以上版本的可以使用 new Specification 也是一样的。

package com.rocket.model;
import com.rocket.entity.TaoBaoItem;
import org.springframework.data.jpa.domain.Specification;
import javax.persistence.criteria.Predicate;
import java.util.Arrays;
import java.util.LinkedList;
import java.util.List;
/**
 * 获取商品的 model
 * 里面保存着用户在视图层输入的参数
 */
public class ItemGetModel {
    private Double leftPrice = 0.0;
    private Double rightPrice = Double.MAX_VALUE;
    private Integer leftSoldQuantity = 0;
    private Integer rightSoldQuantity = Integer.MAX_VALUE;
    private String titleContains;
    private Long[] cids;
    /**
     * 得到 Specification,这是 JPA 进行高度定制的动态查询所需要的
     *
     * @return
     */
    public Specification<TaoBaoItem> getSpecification() {
        return (root, query, cb) -> {
            List<Predicate> list = new LinkedList<>();
            //标题匹配
            if (titleContains != null && !titleContains.trim().equals(""))
                list.add(cb.gt(cb.locate(root.get("title"), this.getTitleContains()), 0));
            //销量区间
            if ((leftSoldQuantity != 0 || rightSoldQuantity != Integer.MAX_VALUE) &&
                    rightSoldQuantity > leftSoldQuantity)
                list.add(cb.between(root.get("soldQuantity"),
                        this.getLeftSoldQuantity(), this.getRightSoldQuantity()));
            //价格区间
            if ((leftPrice != 0.0 || rightPrice != Double.MAX_VALUE) && rightPrice > leftPrice)
                list.add(cb.between(root.get("price"),
                        this.getLeftPrice(), this.getRightPrice()));
            //类目选择
            if (cids != null && cids.length != 0)
                list.add(root.get("cid").in(cids));
            return cb.and(list.toArray(new Predicate[list.size()]));
        };
    }
    public Double getLeftPrice() {
        return leftPrice;
    }
    public void setLeftPrice(Double leftPrice) {
        this.leftPrice = leftPrice;
    }
    public Double getRightPrice() {
        return rightPrice;
    }
    public void setRightPrice(Double rightPrice) {
        this.rightPrice = rightPrice;
    }
    public Integer getLeftSoldQuantity() {
        return leftSoldQuantity;
    }
    public void setLeftSoldQuantity(Integer leftSoldQuantity) {
        this.leftSoldQuantity = leftSoldQuantity;
    }
    public Integer getRightSoldQuantity() {
        return rightSoldQuantity;
    }
    public void setRightSoldQuantity(Integer rightSoldQuantity) {
        this.rightSoldQuantity = rightSoldQuantity;
    }
    public String getTitleContains() {
        return titleContains;
    }
    public void setTitleContains(String titleContains) {
        this.titleContains = titleContains;
    }
    public Long[] getCids() {
        return cids;
    }
    public void setCids(Long[] cids) {
        this.cids = cids;
    }
    @Override
    public String toString() {
        return "ItemGetModel{" +
                "leftPrice=" + leftPrice +
                ", rightPrice=" + rightPrice +
                ", leftSoldQuantity=" + leftSoldQuantity +
                ", rightSoldQuantity=" + rightSoldQuantity +
                ", titleContains='" + titleContains + '\'' +
                ", cids=" + Arrays.toString(cids) +
                '}';
    }
}

 
可以看到我的标题匹配使用了mysql的 locate 函数,为的就是避免like关键字的效率低下。
再加上between and获取两个参数的区间，最后还带了一个in,  关键是这几个值都是动态的!若不满足条件则不会进行拼接!
若是该model的值全为默认, 通过这个get方法获取的 Specification 默认就变成一个 WHERE 1=1 的子句。
否则则会根据传入的值返回相应的表达。可以算是比较优雅了。
 
最后，以上边代码的

cb.gt(cb.locate(root.get("title"), this.getTitleContains()), 0)

举个栗子,具体说明一下,这个Predicate是怎么得来的,代表了什么逻辑。
root.get(“title”) 表示为我查询的表(TaoBaoItem)中一个名为 title的列。
cb.locate() 代表使用mysql的 locate函数,它可以找出字符串中一个子串的位置/下标。
 
LOCATE(substr,str) :返回子串 substr 在字符串 str 中第一次出现的位置。如果子串 substr 在 str 中不存在，返回值为 0： 
 
 
cb.gt() 表示 > 符号，gt应该清楚,到处都能用到。第一个参数我填入LOCATE函数,第二个写了个0。
这个Predicate若是以sql的形式表示出来,则是这样子的:

--假设title字段在mysql中的列名中不变,寻找的子串是'xxx'
LOCATE(title,'xxx') > 0

 
这么一举例大概就明白了⑧ , Specification 来定制 sql 确实是好用啊。