[FIXED] Deleting entity in in-memory H2 database

Issue

In my test I have an in-memory H2 DB and I want to test deleting an entity.

My entity:

@Entity
@Table(name = "REPORT_BLOB")
public class BlobInfoDBVO {

  @Id
  @Column(name = "REPORT_ID")
  private long reportId;

}

Test code

 @Test
  void deleteByReportId() {
    var reportId = 1L;

    EntityTransaction tx = em.getTransaction();

    tx.begin();

    var blobInfo = new BlobInfoDBVO();
    blobInfo.setReportId(reportId);
    em.persist(blobInfo);

    tx.commit();
    tx.begin();

    BlobInfoDBVO findByReportId1 = em.find(BlobInfoDBVO.class, reportId);
    assertNotNull(findByReportId1); // ---- OK

    var getByReportId1 = blobInfoDao.getByReportId(reportId);
    assertTrue(getByReportId1.isPresent()); // ---- OK

    tx.commit();
    tx.begin();

    blobInfoDao.deleteByReportId(reportId);

    tx.commit();
    tx.begin();

    var getByReportId2 = blobInfoDao.getByReportId(reportId);
    assertFalse(getByReportId2.isPresent()); // ---- OK

    BlobInfoDBVO findByReportId2 = em.find(BlobInfoDBVO.class, reportId);
    assertNull(findByReportId2); // ---- FAILS

    tx.commit();
  }

And here is my DAO code to find and delete an entity by reportId:

public class BlobInfoDaoImpl {

  EntityManager em;

  public BlobInfoDaoImpl(EntityManager em) {
    super();
    this.em = em;
  }

  public Optional<BlobInfoDBVO> getByReportId(long reportId) {
    var query =
        em.createQuery(
            """
            select blobInfo
              from BlobInfoDBVO blobInfo
             where blobInfo.reportId = :reportId
            """,
            BlobInfoDBVO.class);
    query.setParameter("reportId", reportId);
    return query
        .getResultStream()
        .findFirst();
  }

  public void deleteByReportId(long reportId) {
    var query =
        em.createQuery(
            """
            delete from BlobInfoDBVO
             where reportId = :reportId
            """);
    query.setParameter("reportId", reportId);

    query.executeUpdate();
  }
}

After deleting an entity, I cannot find it anymore using manually executing a query by calling my DAO (getByReportId) what is correct and expected.

But when I use a find method from an EntityManager using a reportId field as a primary key (it's marked as @Id) - it still returns not deleted entity, even if transaction was commited before.

Any idea?

hobernate-core 5.6.14

quarkus-test-h2 2.14.0.Final

Solution

The JPA EntityManager may be caching the result from your query, even across transactions (not sure whether the spec says this should or should not happen). Try doing an EntityManager.clear() to clear its cache after the delete's commit so that the subsequent queries go to the real database.

Answered By - prunge
Answer Checked By - Senaida (JavaFixing Volunteer)