如何使用JPA Criteria API更新关联其他表的记录？

实体类定义 #

Product类 #

@NoArgsConstructor
@Data
@DynamicUpdate
@Table(name = "product")
@Entity
public class Product implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.UUID)
    @Column(name = "id", updatable = false, nullable = false)
    private UUID id;

    @Column(name = "feed", length = 100, nullable = false)
    private String feed;

    // 省略其他字段

    @ToString.Exclude
    @OneToOne(mappedBy = "product", cascade = CascadeType.ALL)
    private PushPermission pushPermission;
}

PushPermission类 #

@Data
@NoArgsConstructor
@Table(name = "push_permission")
@Entity
public class PushPermission implements Serializable {

    @Id
    @Column(name = "id", updatable = false, nullable = false)
    private UUID id;

    // 省略其他字段

    @ToString.Exclude
    @OneToOne
    @JoinColumn(name = "id")
    @MapsId
    private Product product;
}

需求 #

更新所有关联Product表中feed字段不等于"PROMO"，且自身ID在指定ids集合内的PushPermission记录。

尝试的方法及错误 #

方法1：直接关联属性查询 #

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaUpdate<PushPermission> criteriaUpdate = cb.createCriteriaUpdate(PushPermission.class);
Root<PushPermission> root = criteriaUpdate.from(PushPermission.class);
criteriaUpdate.set("exampleField", true);
Predicate selectedProductsPredicate = root.get("id").in(ids);
Predicate skipFeedPredicate = cb.notEqual(root.get("product").get("feed"), "PROMO");
criteriaUpdate.where(cb.and(selectedProductsPredicate, skipFeedPredicate));
Query query = entityManager.createQuery(criteriaUpdate);
query.executeUpdate();

错误信息：ERROR: missing FROM-clause entry for table "p2_0"
Hibernate生成的SQL缺少Product表的关联语句：

update
    push_permission 
set
    exampleField=?,
where
    id in(?,?) 
    and p2_0.feed!=?

方法2：尝试join关联 #

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaUpdate<PushPermission> criteriaUpdate = cb.createCriteriaUpdate(PushPermission.class);
Root<PushPermission> root = criteriaUpdate.from(PushPermission.class);
Join<PushPermission, Product>  productJoin = root.join("product");
criteriaUpdate.set("exampleField", true);
Predicate selectedProductsPredicate = root.get("id").in(ids);
Predicate skipFeedPredicate = cb.notEqual(productJoin.get("feed"), "PROMO");
criteriaUpdate.where(cb.and(selectedProductsPredicate, skipFeedPredicate));
Query query = entityManager.createQuery(criteriaUpdate);
query.executeUpdate();

错误信息：The root node [me.foo.app.PushPermission] does not allow join/fetch
JPA Criteria Update规范不允许在更新操作的root节点上执行join操作。

已知原生SQL可实现需求：

update push_permission set exampleField=true from product where push_permission.id=product.id and product.feed<>'PROMO';

解决方案：使用子查询实现关联条件筛选 #

JPA Criteria Update虽不支持直接join，但可通过子查询筛选符合条件的PushPermission ID，实现逻辑如下：

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaUpdate<PushPermission> criteriaUpdate = cb.createCriteriaUpdate(PushPermission.class);
Root<PushPermission> root = criteriaUpdate.from(PushPermission.class);

// 设置要更新的字段
criteriaUpdate.set("exampleField", true);

// 子查询：获取所有feed不等于"PROMO"的Product的ID
Subquery<UUID> productSubquery = criteriaUpdate.subquery(UUID.class);
Root<Product> productRoot = productSubquery.from(Product.class);
productSubquery.select(productRoot.get("id"));
productSubquery.where(cb.notEqual(productRoot.get("feed"), "PROMO"));

// 组合条件：id在指定集合中，且id存在于子查询的结果中
Predicate selectedPredicate = root.get("id").in(ids);
Predicate productFeedPredicate = root.get("id").in(productSubquery);
criteriaUpdate.where(cb.and(selectedPredicate, productFeedPredicate));

Query query = entityManager.createQuery(criteriaUpdate);
int updatedCount = query.executeUpdate();

原理说明 #

1. 子查询从Product表筛选出feed<>'PROMO'的记录ID；
2. 主更新语句的where条件同时校验：PushPermission的ID在指定集合内、且ID存在于子查询返回的Product ID集合中（因两者是一对一关联，ID完全一致）；
3. 该实现符合JPA规范，Hibernate会生成等价于原生SQL逻辑的正确语句。

内容的提问来源于stack exchange，提问作者Paweł Walaszek