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

前言

随着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投影。

如果接口的返回结果只能是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查询（可能有也可能没有的条件）进行了补充；最后，对于嵌套的集合属性，最好是切换主体进行一次条件查询后再赋值。