CODEWITHSUNDEEP
javajpaormpersistencecrud
ioreskovic
ioreskovic

Reputation: 5699

What is correct way to do CRUD operation in JPQL when working with multiple IDs

I was wondering what is the pereferred/correct way to do operations that involve fetching and removing multiple entity objects?

Here's an example:

Here's the way that uses IN operator: 

@Override
public void delete(Iterable<Long> ids) {
    startTransaction();

    Query query = getEntityManager().createNamedQuery(Entity.DELETE_ALL_BY_IDS_NQ);
    query.setParameter(Entity.DELETE_ALL_BY_IDS_PARAMS[0], ids);
    query.executeUpdate();

    endTransaction();
    closeTransaction();
}

where the named query looks like this: 

@NamedQuery(
        name = "Entity.deleteAllByIds",
        query = "DELETE from Entity e WHERE e.id IN :ids"
)

And here is the way that just does it via EntityManager#remove

public void delete(Iterable<Long> ids) {
    int count = 0;

    startTransaction();

    for (Long id : ids) {
        count++;

        Query query = getEntityManager().createNamedQuery(ImeiTacEntity.IMEITAC_READ_BY_ID_NQ);
        query.setParameter(ImeiTacEntity.IMEITAC_READ_BY_ID_PARAMS[0], id);

        ImeiTacEntity entity = (ImeiTacEntity) query.getSingleResult();
        getEntityManager().remove(entity);

        if ((count % BATCH_THRESHOLD) == 0) {
            endTransaction();
            clearTransaction();
            startTransaction();
        }
    }

    endTransaction();
    closeTransaction();
}

and the read query is this: 

@NamedQuery(
    name = "Entity.readById", 
    query = "SELECT e from Entity e WHERE e.id = :id"
)

Upvotes: 0

Views: 173

Answers (2)

Alan Hay
Alan Hay

Reputation: 23226

The first operation is an example of a JPQL 'bulk' operation and while more efficient in terms of number of SQL statements executed, you should be aware of the following important points:

https://docs.oracle.com/html/E24396_01/ejb3_langref.html#ejb3_langref_bulk_ops

10.2.9. JPQL Bulk Update and Delete

Operations Bulk update and delete operations apply to entities of a single entity class (together with its subclasses, if any).

A delete operation only applies to entities of the specified class and its subclasses. It does not cascade to related entities. The new_value specified for an update operation must be compatible in type with the state-field to which it is assigned. Bulk update maps directly to a database update operation, bypassing optimistic locking checks. Portable applications must manually update the value of the version column, if desired, and/or manually validate the value of the version column. The persistence context is not synchronized with the result of the bulk update or delete. Caution should be used when executing bulk update or delete operations because they may result in inconsistencies between the database and the entities in the active persistence context. In general, bulk update and delete operations should only be performed within a separate transaction or at the beginning of a transaction (before entities have been accessed whose state might be affected by such operations).

Therefore, when deciding on one or other approach you will need to take the above into account.

Upvotes: 1

Predrag Maric
Predrag Maric

Reputation: 24403

First option definitely has its advantages in this case. The biggest one being that it translates to single delete SQL query, while in the second option you load each entity before passing it to em.remove().

Upvotes: 1

Related Questions