JQPL Update Query to update entity without using the primary key

Question

This may be a simple question, but I'm trying to find out if there is a way that I can create a JPQL update query that would allow me to update a single Persisted Entity using a unique column identifier that is not the primary key.

Say I have and entity like the following:

@Entity
public class Customer {
    @ID
    private Long id;
    @Column
    private String uniqueExternalID;
    @Column
    private String firstname;
    ....
}

Updating this entity with a Customer that has the id value set is easy, however, id like to update this customer entity using the uniqueExternalId without having to pre-query for the local entity and merge the changes in or manually construct a jpql query with all the fields in it manually.

Something like

UPDATE Customer c SET c = :customer WHERE c.uniqueExternalId = :externalId

Is something like this possible in JQPL?

kostja kostja · Accepted Answer · 2014-05-01T05:13:59

You cannot do it in the exact way you describe - by passing an entity reference, but you can use bulk queries to achieve the same effect.

UPDATE Customer c SET c.name = :name WHERE c.uniqueExternalId = :externalId

Please note that you will have to explicitly define each updated attribute.

It is important to note that bulk queries bypass the persistence context. Entity instances that are managed within the persistence context will not reflect the changes to the records that are changed by the bulk update. Further, if you use optimistic locking, consider incrementing the @Version field of your entities with the bulk update:

 UPDATE Customer c SET c.name = :name, c.version = c.version + 1  WHERE c.uniqueExternalId = :externalId

EDIT: The JPA 2.0 spec advises in § 4.10:

In general, bulk update and delete operations should only be performed within a transaction in a new persistence context or before fetching or accessing entities whose state might be affected by such operations.

JQPL Update Query to update entity without using the primary key

1 Answers