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.

print out the generated queries and that should give you an idea of what is wrong with your code. Btw, that code is not very readable but I guess that's the problem with the criteria api in general. — dsp_user, Feb 05 '23 at 19:45
I added the update statement. I bet Criteria API doesn't deal with above situation. — Paweł Walaszek, Feb 05 '23 at 23:37
does this help https://stackoverflow.com/questions/9025196/how-to-use-jpa-criteria-api-when-joining-many-tables? — dsp_user, Feb 06 '23 at 07:01
No, there was used `CriteriaQuery` instead `CriteriaUpdate`. — Paweł Walaszek, Feb 06 '23 at 15:11

score 1 · Accepted Answer · answered Feb 06 '23 at 13:40

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

answered Feb 06 '23 at 13:40

Christian Beikov

15,141
2
32
58

Thank you for this answer. My update statement is more complex than the mentioned piece of code and I must use Criteria API to implement a dynamic statement. At the moment I implemented two various versions of statements and I call them depending on an input value. I will track the created issue by you. If I use a new solution I will answer to this question in the future. – Paweł Walaszek Feb 06 '23 at 17:52
I didn't want to go through the hassle of crafting the whole subquery stuff with JPA Criteria in this answer, but know that you can express all filtering needs by moving the WHERE part to the exists subquery. In fact, this is going to be the emulation that we will use on DBs that don't support joins in DML. – Christian Beikov Feb 06 '23 at 18:14

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

1 Answers1