JPA-Criteria API进行条件查询，更新和删除

向右看齐 2023-03-13 11:41 13阅读 0赞

# 前言 #

随着JPA（java persistence api）的使用越来越广泛，传统的Java Persistence Query Language (JPQL) 查询暴露出诸多的缺点。最明显的是，Java 编译器不能在编译时发现 JPQL 字符串的语法错误，只能等到运行时执行了JPQL语句才抛出运行时异常。

为了弥补JPQL的缺点，推出了新一代查询API：Criteria API。Criteria API支持在运行时动态构建查询，还支持构建可由编译器验证的类型安全的查询。编译器无法验证JPQL查询的正确性，必须在测试的运行时进行验证。参看[IBM][]。

> 更多详情请参考[logicbig][]。

# 多条件查询 #

JPA的`JpaRepository<T, ID>`接口里面可以方便定义实体对象的字段的筛选条件。比如对象Person有name,age,phone等字段，对于这些对象本身的字段，是很容易创建查询的，但是对于嵌套的字段，却没有办法查询。另外，对于筛选查询，有些条件可能存在，也可能不能存在，这也是接口方法定义无法触及的盲区。

针对以上两个问题，需要引入`JpaSpecificationExecutor<T>`接口，使用`Specification<T>`来定义查询条件。

public interface Specification<T> extends Serializable {
    	@Nullable
    	Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder);
    }

各个参数的说明如下：

*  `Root<T>`：实体节点，可表示实体的属性和嵌套属性，用来指定要查询的字段。
 *  `CriteriaBuilder`：用来构造查询条件，如cb.isNull(root.get(“deleteTime”))
 *  `CriteriaQuery`：最终的查询对象，所有的查询条件汇总到这个对象，由JPA去执行。

# 如果嵌套属性是集合？ #

对于非集合字段，上诉方法没有任何问题。假设嵌套字段是个集合，如下Person所示：

@Entity
    public class Person{
    	
    	@OneToMany(mappedBy="person")
    	Set<Address> addresses;
    	
    }

因为@OneToMany注解默认是懒加载的，所以查询Person时，addresses默认是懒加载的，所以没有数据。如果需要对嵌套的集合属性（这里是addresses）进行条件筛选，最好的办法是以Address为主体（即`Root<Address>`）进行一次查询，然后再赋值给addresses属性。

# 排序 #

List<T> findAll(@Nullable Specification<T> spec, Sort sort);

# 分页（含排序） #

Page<T> findAll(@Nullable Specification<T> spec, Pageable pageable);

# 计数 #

使用`JpaSpecificationExecutor<T>.count(Specification<T> spec)`方法来计数。

也可以使用EntityManager查询，参见`CriteriaBuilder.count()`方法。

# 求和 #

使用EntityManager来建立查询。

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    
    CriteriaQuery<Tuple> criteriaQuery = cb.createQuery(Tuple.class);
    
    Root<QuiltRule> root = criteriaQuery.from(QuiltRule.class);
    
    criteriaQuery.where(cb.greaterThan(root.get("id"), 0))
            .multiselect(cb.sum(root.get("price")));		//这句关键
    
    Tuple tuple = entityManager.createQuery(criteriaQuery).setMaxResults(1).getSingleResult();
    
    Object integer = tuple.get(0);

# 分组查询 #

分组查询要配合聚合函数一起使用，定义聚合函数的统计范围。groupBy跟的条件必须是multiselect之内的一项。

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<Tuple> query = cb.createQuery(Tuple.class);
    Root<QuiltAppointment> root = query.from(QuiltAppointment.class);
    List<Tuple> result = entityManager.createQuery(
            query.multiselect(root.get("order").get("device").get("mbId").alias("mbId"), cb.sum(root.get("order").get("payMoney")).alias("money"))
                    .where(cb.equal(root.get("state"), 3))
                    .groupBy(root.get("order").get("device").get("mbId"))
    ).getResultList();

# 使用函数 #

可以使用sql的内置函数进行统计。

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<Tuple> query = cb.createTupleQuery();
    Root<QuiltOrder> orderRoot = query.from(QuiltOrder.class);
    Expression<String> payTime = cb.function("DATE_FORMAT", String.class, orderRoot.get("payTime"), cb.parameter(String.class, "format"));		//定义函数[函数名，返回类型，参数...]
    List<Tuple> tupleList = entityManager.createQuery(
            query.multiselect(
                    payTime.alias("date"),		//使用函数
                    cb.sum(orderRoot.get("payMoney")).alias("sum")
            ).where(
                    cb.isNull(orderRoot.get("deleteTime")),
                    cb.isNotNull(orderRoot.get("payTime")),
                    cb.greaterThanOrEqualTo(orderRoot.get("payTime"), s),
                    cb.lessThanOrEqualTo(orderRoot.get("payTime"), e),
                    cb.isNull(orderRoot.get("refundTime"))
            ).groupBy(payTime)
    ).setParameter("format", "%Y-%m-%d")		//设置函数参数
            .getResultList();

# 多条件同时查询（case…when…else） #

类似sql的case…when…else，能在一次查询中同时查到多个不同范围的结果。

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    query = cb.createTupleQuery();
    Root<QuiltUser> userRoot = query.from(QuiltUser.class);
    Tuple userResult = entityManager.createQuery(
            query.multiselect(
                    cb.sum(cb.selectCase().when(cb.greaterThanOrEqualTo(userRoot.get("createTime"), DateUtils.truncate(new Date(), Calendar.DATE)), 1).otherwise(0).as(Integer.class)).alias("today"),		//统计今天的用户
                    cb.sum(cb.selectCase().when(cb.greaterThanOrEqualTo(userRoot.get("createTime"), DateUtils.truncate(new Date(), Calendar.MONTH)), 1).otherwise(0).as(Integer.class)).alias("month"),		//统计本月的用户
                    cb.count(userRoot.get("id")).alias("total")		//统计所有的用户
            ).where(
                    cb.isNull(userRoot.get("deleteTime"))
            )
    ).setMaxResults(1).getSingleResult();

# 联结查询（join) #

当需要查询的字段在另外一个表时，或需要指定联结类型时，就需要联结查询了。

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<Tuple> tupleQuery = cb.createTupleQuery();
    Root<QuiltArea> areaRoot = tupleQuery.from(QuiltArea.class);
    Path<Object> schoolPath = areaRoot.join("schools", JoinType.LEFT).get("id");
    Path<Object> devicePath = areaRoot.join("schools", JoinType.LEFT).join("floors", JoinType.LEFT).join("devices", JoinType.LEFT).get("id");
    List<Tuple> resultList = entityManager.createQuery(
            tupleQuery.multiselect(
                    areaRoot.get("name").alias("name"),
                    cb.countDistinct(schoolPath).alias("schools"),
                    cb.countDistinct(devicePath).alias("devices")
            ).where(
                    cb.isNull(areaRoot.get("deleteTime"))
            ).groupBy(areaRoot.get("name"))
    ).getResultList();

上面的代码查询每个区域的所有学校个数和所有设备的个数，按区域名称分组。

# 子查询（subquery) #

子查询实际是一种嵌套查询，类似方法调用，封装复杂的查询。

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    //查询区域
    CriteriaQuery<Tuple> tupleQuery = cb.createTupleQuery();
    Root<QuiltArea> areaRoot = tupleQuery.from(QuiltArea.class);
    //子查询：根据区域ID查询区域学校个数
    Subquery<Long> schoolSQ = tupleQuery.subquery(Long.class);
    Root<QuiltSchool> fromSchool = schoolSQ.from(QuiltSchool.class);
    schoolSQ.select(cb.count(fromSchool.get("id")))
            .where(cb.and(fromSchool.get("deleteTime").isNull(), cb.equal(fromSchool.get("area").get("id"), areaRoot.get("id"))));
    //子查询：根据区域ID查询区域设备个数
    Subquery<Long> deviceSQ = tupleQuery.subquery(Long.class);
    Root<QuiltDevice> fromDevice = deviceSQ.from(QuiltDevice.class);
    deviceSQ.select(cb.count(fromDevice.get("id")))
            .where(cb.and(fromDevice.get("deleteTime").isNull(), cb.equal(fromDevice.get("floor").get("school").get("area").get("id"), areaRoot.get("id"))));
    //参数条件
    List<Predicate> predicateList = new ArrayList<>();
    predicateList.add(areaRoot.get("deleteTime").isNull());
    if (StringUtils.hasLength(q.getCityOrArea())) {
        String ca = "%" + q.getCityOrArea() + "%";
        predicateList.add(cb.or(
                cb.like(areaRoot.get("name"), ca),
                cb.like(areaRoot.get("city").get("name"), ca))
        );
    }
    //最终查询
    List<Tuple> areaList = entityManager.createQuery(
            tupleQuery.multiselect(
                    areaRoot.get("name").alias("area"),
                    areaRoot.get("id").alias("areaId"),
                    areaRoot.join("city", JoinType.LEFT).get("name").alias("city"),
                    areaRoot.join("employee", JoinType.LEFT).get("nickname").alias("nickname"),
                    areaRoot.join("employee", JoinType.LEFT).get("account").alias("account"),
                    schoolSQ.alias("schoolNum"),	//子查询作为表达式
                    deviceSQ.alias("deviceNum")		//子查询作为表达式
            ).where(predicateList.toArray(new Predicate[0]))
    ).setFirstResult(start).setMaxResults(size).getResultList();

# 更新 #

使用`EntityManager`对记录的部分字段更新：

CriteriaDelete<PO> poCriteriaUpdate = criteriaBuilder.createCriteriaUpdate(PO.class);
                Root<PO> mppRoot = poCriteriaUpdate.from(PO.class);
                int mpp = entityManager.createQuery(
                        poCriteriaUpdate
                        		.set(mppRoot.get("name"),"name")
                                .where(mppRoot.get("po").get("id").in(ids))
                ).executeUpdate();

# 删除 #

使用`EntityManager`删除记录：

CriteriaDelete<PO> poCriteriaDelete = criteriaBuilder.createCriteriaDelete(PO.class);
                Root<PO> mppRoot = poCriteriaDelete.from(PO.class);
                int mpp = entityManager.createQuery(
                        poCriteriaDelete
                                .where(mppRoot.get("po").get("id").in(ids))
                ).executeUpdate();

# 使用原生SQL #

## @Query ##

sql查询时使用@Query注解，`nativeQuery`置为`true`（为true不能使用类名，而是数据库的表名）。

## @Modifying和@Transactional ##

增删改除了加`@Query`注解之外，还要加上`@Modifying`，表示这是一个修改方法。另外，任何修改都需要在事务中进行，所以加上`@Transactional`。

## @Query中sql的参数和方法的参数绑定 ##

### @Param(“param”)和:param ###

sql中的参数`:param`，绑定方法上用`@Param("param")`标记的参数。

### ?i表示第i个参数 ###

在sql中使用`?i`绑定方法上的第`i`个参数。

### 其他 ###

in子句只能绑定类型为List的参数。

# 投影（字段映射功能） #

投影其实是字段映射功能，JPA的Criteria API默认将查询结果封装为`javax.persistence.Tuple`类型，然后取出每个查询结果。这里讨论的投影，是指接口上的返回结果映射，具体参考[JPA投影][JPA]。

如果接口的返回结果只能是Entity的所有字段，那么会有些不必要的网络和查询开销。对应特定的场景，可能只需要Entity的部分字段，或一些统计字段，或联结其他表的某些字段。在这种情况下，返回值是Entity就不合适了。

接口返回的字段用接口定义。**尽管官方文档说可以用类定义，但是测试没有成功。**

## 投影为接口 ##

//首先定义一个接受返回结果的接口，遵守POJO命名规范。

interface NamesOnly {
      String getFirstname();//sql中的字段应为firstName
      String getLastname();//sql中的字段应为lastName
    }
    
    interface PersonRepository extends Repository<Person, UUID> {
    	//这里不仅可以根据方法名称来查询；也可以使用@Query注解
      Collection<Person> findByLastname(String lastname);
    }

如果返回字段不遵守POJO的命名，则需要sql中使用`as`定义别名。

interface NamesOnly {
      String getFirstname();
      String getLastname();
    }
    //使用as命名
    @Query(value="select first_name as firstName,last_name as lastName from user where id=?1",nativeQuery=true)

也可以用使用`org.springframework.beans.factory.annotation.@Value`注解来指定字段。

interface NamesOnly {
      @Value("#{tagert.first_name}")	//这里也可以是复杂一点的SpEL表达式
      String getFirstname();
      @Value("#{tagert.last_name}")
      String getLastname();
    }
    //不用as命名
    @Query(value="select first_name,last_name  from user where id=?1",nativeQuery=true)

## 投影为Tuple ##

返回结果用`javax.persistence.Tuple`接收。

## 投影为Map ##

返回结果用`java.util.Map`接收。

## 投影为Object\[\] ##

返回结果用对象数组接收。

# 总结 #

JPA以接口的形式对jdbc进行了良好的封装，比如`JpaRepository<T, ID>`，能够进行非嵌套的条件查询；然后`JpaSpecificationExecutor<T>`对JPA的嵌套条件查询和Maybe查询（可能有也可能没有的条件）进行了补充；最后，对于嵌套的集合属性，最好是切换主体进行一次条件查询后再赋值。

[IBM]: https://developer.ibm.com/articles/j-typesafejpa/
[logicbig]: https://www.logicbig.com/tutorials/java-ee-tutorial/jpa/criteria-api-sub-query.html
[JPA]: https://docs.spring.io/spring-data/jpa/docs/2.4.2/reference/html/#projections