Proper testing of JPA DAO with Spring

Question

I've created simple JPA entities: Topic and Word. Topic contains a list of words.

@ManyToMany(fetch = FetchType.EAGER, cascade = {CascadeType.MERGE, CascadeType.REMOVE})
@JoinTable
private List<Word> words;

Now I want to test my DAO using an in-memory H2 database. In the test class, I write different tests for manipulating the data. But I want to get a clean database for each test, so I've added tearDown method to delete all words and topics.

Snippet of my test class:

@RunWith(SpringRunner.class)
@SpringBootTest
public class TopicServiceJpaDaoTest {
    @Autowired
    private TopicDao topicDao;

    @Autowired
    private WordDao wordDao;

    // different test methods

    @After
    public void tearDown() throws Exception {
        topicDao.deleteAll();
        wordDao.deleteAll();
    }
}

1. But when I try to run all tests in the class I receive an error, that Entry is locked.
2. Also, when I run all my integrational tests, I receive following error: Unable to acquire JDBC Connection.

Stack trace for the Entry is locked error:

javax.persistence.PessimisticLockException: could not execute statement

    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.wrapLockException(AbstractEntityManagerImpl.java:1760)
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1649)
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1602)
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:1700)
    at org.hibernate.jpa.spi.AbstractQueryImpl.executeUpdate(AbstractQueryImpl.java:70)
    at io.github.solomkinmv.glossary.persistence.dao.impl.WordServiceJpaDao.deleteAll(WordServiceJpaDao.java:66)
    at io.github.solomkinmv.glossary.persistence.dao.impl.TopicServiceJpaDaoTest.tearDown(TopicServiceJpaDaoTest.java:287)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
    at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:33)
    at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)
    at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)
    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:252)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:94)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
    at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
    at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:191)
    at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
    at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
    at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:51)
    at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:237)
    at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at com.intellij.rt.execution.application.AppMain.main(AppMain.java:147)
Caused by: org.hibernate.PessimisticLockException: could not execute statement
    at org.hibernate.dialect.H2Dialect$3.convert(H2Dialect.java:370)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:95)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:207)
    at org.hibernate.hql.internal.ast.exec.BasicExecutor.doExecute(BasicExecutor.java:91)
    at org.hibernate.hql.internal.ast.exec.BasicExecutor.execute(BasicExecutor.java:60)
    at org.hibernate.hql.internal.ast.exec.DeleteExecutor.execute(DeleteExecutor.java:111)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:429)
    at org.hibernate.engine.query.spi.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:374)
    at org.hibernate.internal.SessionImpl.executeUpdate(SessionImpl.java:1348)
    at org.hibernate.internal.QueryImpl.executeUpdate(QueryImpl.java:102)
    at org.hibernate.jpa.internal.QueryImpl.internalExecuteUpdate(QueryImpl.java:405)
    at org.hibernate.jpa.spi.AbstractQueryImpl.executeUpdate(AbstractQueryImpl.java:61)
    ... 34 more
Caused by: org.h2.jdbc.JdbcSQLException: Timeout trying to lock table ; SQL statement:
delete from word [50200-193]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
    at org.h2.message.DbException.get(DbException.java:168)
    at org.h2.command.Command.filterConcurrentUpdate(Command.java:307)
    at org.h2.command.Command.executeUpdate(Command.java:260)
    at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:160)
    at org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:146)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:204)
    ... 43 more
Caused by: org.h2.jdbc.JdbcSQLException: Concurrent update in table "WORD": another transaction has updated or deleted the same row [90131-193]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
    at org.h2.message.DbException.get(DbException.java:168)
    at org.h2.mvstore.db.MVTable.convertException(MVTable.java:898)
    at org.h2.mvstore.db.MVPrimaryIndex.remove(MVPrimaryIndex.java:168)
    at org.h2.mvstore.db.MVTable.removeRow(MVTable.java:677)
    at org.h2.command.dml.Delete.update(Delete.java:95)
    at org.h2.command.CommandContainer.update(CommandContainer.java:98)
    at org.h2.command.Command.executeUpdate(Command.java:258)
    ... 46 more
Caused by: java.lang.IllegalStateException: Entry is locked [1.4.193/101]
    at org.h2.mvstore.DataUtils.newIllegalStateException(DataUtils.java:765)
    at org.h2.mvstore.db.TransactionStore$TransactionMap.set(TransactionStore.java:1031)
    at org.h2.mvstore.db.TransactionStore$TransactionMap.remove(TransactionStore.java:989)
    at org.h2.mvstore.db.MVPrimaryIndex.remove(MVPrimaryIndex.java:162)
    ... 50 more

I've tried different manipulations with database URL, like adding MVCC=TRUE, but nothing changes.

Looks like my approach for unit-testing is wrong. Hot to do this the right way?

I don't use Spring Data, just simple JPA implementation. My DAO implementation:

@Repository
public class TopicServiceJpaDao extends AbstractJpaDaoService implements TopicDao {
    private static final Logger LOGGER = LoggerFactory.getLogger(TopicServiceJpaDao.class);

    @Override
    public List<Topic> listAll() {
        EntityManager entityManager = emf.createEntityManager();

        return entityManager.createQuery("FROM Topic", Topic.class)
                            .getResultList();
    }

    @Override
    public Optional<Topic> getById(Long id) {
        EntityManager entityManager = emf.createEntityManager();

        return Optional.ofNullable(entityManager.find(Topic.class, id));
    }

    @Override
    public Topic saveOrUpdate(Topic unsavedTopic) {
        EntityManager entityManager = emf.createEntityManager();

        entityManager.getTransaction().begin();
        Topic savedTopic = entityManager.merge(unsavedTopic);
        entityManager.getTransaction().commit();
        return savedTopic;
    }

    @Override
    public void delete(Long id) {
        EntityManager entityManager = emf.createEntityManager();

        entityManager.getTransaction().begin();
        Topic entity = entityManager.find(Topic.class, id);
        if (entity == null) {
            LOGGER.error("No such entity in the DB");
            throw new EntityNotFoundException("Topic id: " + id);
        }
        entityManager.remove(entityManager.find(Topic.class, id));
        entityManager.getTransaction().commit();
    }

    @Override
    public void deleteAll() {
        EntityManager entityManager = emf.createEntityManager();

        entityManager.getTransaction().begin();
        entityManager.createQuery("DELETE FROM Topic").executeUpdate();
        entityManager.getTransaction().commit();
    }
}

Answer 1

The other answer I provided is for Spring Data JPA Repository, which you are not using.

---

Looking at your error and your code again, here's my thoughts:

JdbcSQLException: Concurrent update in table "WORD": another transaction has updated or deleted the same row [90131-193]

suggests that a transaction has not been closed properly.

---

I believe your Dao contain a bug: you are not rollbacking in case of errors.

For example, in your saveOrUpdate method:

public Topic saveOrUpdate(Topic unsavedTopic) {
    EntityManager entityManager = emf.createEntityManager();

    entityManager.getTransaction().begin();
    Topic savedTopic = entityManager.merge(unsavedTopic);
    entityManager.getTransaction().commit();
    return savedTopic;
}

if entityManager.merge(unsavedTopic); fails, a transaction will be started but never be commited or rollbacked.

You could change it to:

public Topic saveOrUpdate(Topic unsavedTopic) {
    EntityManager entityManager = emf.createEntityManager();

    try{
        entityManager.getTransaction().begin();
        Topic savedTopic = entityManager.merge(unsavedTopic);
        entityManager.getTransaction().commit();
    } catch(RuntimeException e){
        entityManager.getTransaction().rollback();
        throw e; //you choose how to handle the exceptions.
    }
    return savedTopic;
}

The same should be applied to delete and deleteAll.

Answer 2

This answer would only be adequate if Spring Data JPA Repository was used

With Spring Data JPA repository, the Dao could be replaced by this:

public interface TopicRepository extends JpaRepository<Topic, Long> {
    // no need for code here   
}

From what I see in the Dao, it almost a perfect match:

• listAll -> findAll
• getById -> findOne
• saveOrUpdate -> save
• delete
• deleteAll.

It's not your question, but you could take a look at Spring Data JPA Repository

---

You could use @DataJpaTest instead, which would take care of loading an in-memory h2 database and rollbacks.

source: https://docs.spring.io/spring-boot/docs/current/reference/html/boot-features-testing.html

By default it will configure an in-memory embedded database, scan for @Entity classes and configure Spring Data JPA repositories. Regular @Component beans will not be loaded into the ApplicationContext.

Data JPA tests are transactional and rollback at the end of each test by default [...]

Data JPA tests may also inject a TestEntityManager bean which provides an alternative to the standard JPA EntityManager specifically designed for tests. If you want to use TestEntityManager outside of @DataJpaTests you can also use the @AutoConfigureTestEntityManager annotation. [...]

@RunWith(SpringRunner.class)
@DataJpaTest
public class ExampleRepositoryTests {

    @Autowired
    private TestEntityManager entityManager;

    @Autowired
    private UserRepository repository;

    @Test
    public void testExample() throws Exception {
        this.entityManager.persist(new User("sboot", "1234"));
        User user = this.repository.findByUsername("sboot");
        assertThat(user.getUsername()).isEqualTo("sboot");
        assertThat(user.getVin()).isEqualTo("1234");
    }
}