How to update records that are dependent on a different table using JPA Criteria API?

Question

I have the following entities with the one-to-one relationship:

@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;

    // Omitted columns

    @ToString.Exclude
    @OneToOne(mappedBy = "product", cascade = CascadeType.ALL)
    private 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;

    // Omitted columns

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

I would like to update all records in PushPermission where feed (column from Product) is not equal to PROMO using JPA Criteria API.

I have used the following CriteriaUpdate:

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

but I got the following error message:

ERROR: missing FROM-clause entry for table "p2_0"

Generated update statement by Hibernate:

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

Besides I tried to use joining:

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

but I got the following message:

The root node [me.foo.app.PushPermission] does not allow join/fetch

Hibernate didn't generate any update statement.

I use Postgres SQL 14.5 and I know I can do the native query which works:

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

but I wonder I can do it with the use of JPA Criteria API.

I use Spring Boot 3.0.2 that implies Hibernate 6.

Answer 1

That's not yet possible, but support for that is on the roadmap. For now, you'd have to use an exists subquery to model this i.e.

update PushPermission p 
set p.exampleField=true 
where exists (
    select 1
    from product pr
    where p.id=pr.id 
    and pr.feed<>'PROMO';
)