Delete via HQL bulk operation with rollback

Question

I'm looking for an eloquent way to delete multiple entities inside a transaction.

Given a list of ids, I would like to throw an exception if the count of affected rows differs from the list count. Currently I use the below snippet, but it involves a lot of boilerplate:

  private int deleteMyEntities(final List<Integer> ids) {
    final Session session = SomeHelper.getOpenSession();
    Transaction tx = null;
    try {
      tx = session.beginTransaction();
      final int affectedCount = session.createQuery("delete MyEntity where id in (:ids)")
          .setParameterList("ids", ids)
          .executeUpdate();
      if (affectedCount == ids.size()) {
        tx.commit();
        tx = null;
        return affectedCount;
      } else {
        throw new HibernateException("Delete count does not match target count.");
      }
    } finally {
      if (tx != null) {
        tx.rollback();
      }
    }
  }

Some gotchas:

This is a legacy app lacking dependency injection, annotation driven transactions and other niceties. Answers akin to "Use spring" aren't exceptionally helpful.
We compile to java 1.6.

what is the issue with this code ? – Vasu Nov 01 '16 at 21:43 — Vasu, Nov 01 '16 at 21:43
There is a ton of boilerplate. It works, it is just ugly – Andreas Nov 01 '16 at 21:53 — Andreas, Nov 01 '16 at 21:53
Instead of MyEntity object create a DTO with only id field – Joe Taras Nov 02 '16 at 14:20 — Joe Taras, Nov 02 '16 at 14:20

Alex Beardsley · Accepted Answer · 2016-11-01T22:18:50.437

I took a stab at it. In this specific case you don't need to start your transaction in the try statement, because if you're unable to start it then you are likely unable to roll it back, but even if you could there would be no point because you haven't done anything with it yet. If the transaction couldn't open, there would be nothing to close. In other words, there would not be an orphaned thread from the connection pool.

private int deleteMyEntities(final List<Integer> ids) {
  final Session session = SomeHelper.getOpenSession();
  Transaction tx = session.beginTransaction();
  try {
    final int affectedCount = session.createQuery("delete MyEntity where id in (:ids)")
        .setParameterList("ids", ids)
        .executeUpdate();
    if (affectedCount == ids.size()) {
      tx.commit();
      return affectedCount;
    } else {
      throw new HibernateException("Delete count does not match target count.");
    }
  } catch (Exception e) {
     tx.rollback();
     throw e;
  } 
}

Unfortunately, it will be difficult to make it "nice" without writing your own custom framework to do something like annotation based transactions. If you have access to an AOP library you could use that to hide a lot of this though based on your description that seems doubtful.

With this solution my method signiture would have to be `public int deleteMyEntities(final List ids) throws Exception`. `throws Exception` is unpleasant, to say the least. — Andreas, Nov 02 '16 at 14:39
You could wrap 'e' in a RuntimeException like throw new RuntimeException(e) which eliminates the method signature change, but is not the most elegant solution. — Alex Beardsley, Nov 02 '16 at 19:25

score 0 · Answer 2 · answered Nov 21 '16 at 17:50

The solution I ended up going with included Alex's suggestions. I also pulled out a lot of logic to keep the code a little DRYer. Note: the hibernate session is opened in a filter and held for the duration of the request (open session in view), thus the session reset in recoverFromFailedTransaction

  public int deleteMyEntity(final List<Integer> ids) {
    return deleteEntities("MyEntity", ids);
  }

  private int deleteEntities(final String entityName, final List<Integer> ids) {
    final Session session = SomeHelper.getOpenSession();
    final Query query = session.createQuery("delete " + entityName + " where id in (:ids)")
        .setParameterList("ids", ids);
    return performBatchOperation(query, ids.size());
  }

  private int performBatchOperation(final Query query, final int expectedAffectedCount) {
    final Session session = SomeHelper.getOpenSession();
    final Transaction tx = session.beginTransaction();
    try {
      final int affectedCount = query.executeUpdate();
      if (affectedCount == expectedAffectedCount) {
        tx.commit();
        return affectedCount;
      } else {
        throw new HibernateException(String.format(
            "Affected count [%d] does not match expected count [%d].",
            affectedCount,
            expectedAffectedCount));
      }
    } catch (RuntimeException e) {
      logger.error(e);
      recoverFromFailedTransaction(tx);
      throw e;
    }
  }

private void recoverFromFailedTransaction(final Transaction tx) {
    try {
      if (tx != null) {
        tx.rollback();
      }
    } catch (HibernateException e) {
      logger.error("Exception when rolling back failed transaction. ", e);
    }
    try {
      SomeHelper.getOpenSession().close();
    } catch (HibernateException e) {
      logger.error("Exception when closing session . ", e);
    }
    SomeHelper.resetOpenSession();
    logger.warn("Session discarded.");
  }

Delete via HQL bulk operation with rollback

2 Answers2