How to remove multiple objects in batch call using their IDs?

Question

How can I remove multiple objects in batch call using their IDs ?

I tried this

EntityManager em = ...
em.getTransaction().begin();
try
{
    for (Visitor obj : map.keySet())
    {
        Visitor fake = em.getReference(Visitor.class, obj.getId());
        em.remove(fake);
    }

    em.getTransaction().commit();
}
catch (Exception ex)
{
    ex.printStackTrace();
}

I see DELETE statements in log file but them it throws

<openjpa-2.1.1-r422266:1148538 fatal store error> org.apache.openjpa.persistence.RollbackException: Optimistic locking errors were detected when flushing to the data store.  The following objects may have been concurrently modified in another transaction: [com.reporting.data.Visitor-53043]
    at org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:593)
    at com.reporting.ui.DBUtil.saveAnswers(DBUtil.java:311)

I have single thread.

Update:

I also tried

for (Visitor obj : map.keySet())
    em.remove(obj);

But it's slow because on every iteration it sends SELECT to a server. I assume OpenJPA does it to reattach object to context.

Answer 1

After multiple experiments I ended up doing hacky JPQL query. Here is code-snippet:

List<Long> lstExistingVisitors = ...
Query qDeleteVisitors = em.createQuery("delete from Visitor obj where obj.id in (?1)");
qDeleteVisitors.setParameter(1, lstExistingVisitors);
qDeleteVisitors.executeUpdate();

I tried list as big as 5000 IDs. It works fine with mysql 5.1 and H2DB.

Answer 2

Try to use JPQL

em.createQuery("delete from  Visitor v where v.id in (:param)")
.setParameter("param", idsList).executeUpdate();

OpenJPA docs: http://openjpa.apache.org/builds/1.2.0/apache-openjpa-1.2.0/docs/manual/jpa_langref.html#jpa_langref_bulk_ops