Can you do bulk delete in one transaction using JPA and Hibernate?

Question

So, I've been pounding my head against a wall trying to speed up the deletion process from my database. I'm relatively new to Hibernate, and I was wondering if it's possible to do multiple operations within one transaction.

I'm not too worried about a transaction failing to delete, so the idea of rolling back all of the deletes doesn't bother me.

If it helps to provide the table descriptions I can do that also. The idea though is I have to delete the contents within each for loop before deleting the contents outside of it.

My code

  private void deleteTrackItems() {
        Session session = m_databaseConnection.getSession();
        Transaction tx = null;
        try {

            final List<TrackItem> trackItems = loadAllTrackItemByTrackID(
                    track.getId(), session);

            // Deletes all Track Items for the given track
            List<Long> trackItemIds = new ArrayList();
            
            for (TrackItem trackItem : trackItems) {
                trackItemIds.add(trackItem.getId());

                // this is test code 189 through 214
                Set<TrackPoint> trackPoints = trackItem.getTrackPoints();

                for (TrackPoint trackPoint : trackPoints) {

                    // load track
                    final List<TrackPointDetail> trackPointDetails = loadAllTrackPointDetailByTrackID(
                            trackPoint.getId(), session);

                    for (TrackPointDetail trackPointDetail : trackPointDetails) {
                        // delete track point details
                        deleteObject(trackPointDetail, session);
                    }

                    // delete track point
                    deleteObject(trackPoint, session);
                }

                // get the track informations
                final Set<TrackInformation> trackInformations = trackItem
                        .getTrackInformations();

                // for each track information
                for (TrackInformation trackInformation : trackInformations) {
                    deleteTrackInformation(trackInformation, session);
                }
                deleteObject(trackItem, session);
            }
        }
        catch (Exception ex) {
            System.out.println(ex);
            tx.rollback();
        }
        finally {
            if (session != null) {
                session.close();
            }
        }
    }
    
/**
 * @param p_objectToDelete The object to delete
 * @param p_session the hibernate session
 * @return Returns the {@link Runnable}
 */
protected void deleteObject(Object p_objectToDelete, final Session p_session) {

    // get the transaction
    final Transaction transaction = p_session.getTransaction();
    try {
        LOGGER.info("Deleting object: " + p_objectToDelete.getClass());
        transaction.begin();
        p_session.delete(p_objectToDelete);
        transaction.commit();
        LOGGER.info("Deleted object: " + p_objectToDelete.getClass());
    }
    catch (Exception exception) {
        transaction.rollback();
        LOGGER.error(exception);
    }
}

Because each one of these operations performs a commit after each delete currently I was wondering if it was possible to do this in more of a bulk operation.

What I was thinking about doing was something along the lines of pulling my commit out of my deleteObject function and place it at the end of a for loop that called delete on each object.

so

final Transaction tx = session.getTransaction();
transaction.begin();
try{
   for(Object object : objects){
      session.delete(object);
   }
   tx.commit();
}catch(Exception ex){
   System.out.println(ex);
   tx.rollback();
}

My other solution to bulk delete is to use a hibernate SQLQuery where I just get the id's of each object store them in a list and do a bulk delete that way in one query. However, I feel like when I do that query my where id in (?,?,?,?,?) list takes more time due to sequence scan.

I feel like there should be an easy way to do bulk delete using hibernates session.delete(). Any ideas are greatly appreciated.

Answer 1

The common way to perform bulk deletes with Hibernate and JPA is exactly as you proposed in your last solution - a bulk delete query in HQL/JPQL

DELETE FROM MyEntity e WHERE e.id IN (:ids)

This should be by far the most efficient one as Hibernate does not need to load and instantiate the entities.

Consider putting the bulk delete into it's own transactional method and calling the method as soon as you can in your other method - the persistence context is not updated with the results of the query, so you want to prevent your persistence context from containing stale data.