JPA - How to truncate tables between unit tests

Question

I want to cleanup the database after every test case without rolling back the transaction. I have tried DBUnit's DatabaseOperation.DELETE_ALL, but it does not work if a deletion violates a foreign key constraint. I know that I can disable foreign key checks, but that would also disable the checks for the tests (which I want to prevent).

I'm using JUnit 4, JPA 2.0 (Eclipselink), and Derby's in-memory database. Any ideas?

Thanks, Theo

Cahra

Why do you need to clean up the database after each test? – Autostrada

To avoid side effects. I want every test to run isolated from each other. – Cahra

Answer 1

Simple: Before each test, start a new transaction and after the test, roll it back. That will give you the same database that you had before.

Make sure the tests don't create new transactions; instead reuse the existing one.

Cajuput

Yeah, but that way, you're not able to capture any bugs that occur during transaction commit (e.g. if you test relationships, etc.). – Cahra

Commit just closes the transaction and makes the data in the DB permanent. If you have FK errors, you get those when you flush the session. No commit necessary. – Cajuput

OK, that means a flush has the same effect as commit, except that the transactions is closed and the data is made permanent at the DB. Will try it out. Thanks – Cahra

To make it more clear: flush() just creates the SQL statements which are pending in memory. This allows you to make many changes to your model in memory, and then persist all changes at once. Imagine you change the name and the phone number of a user and then delete the record. Without such a cache, that could be three SQLs. With the cache, it's just one. – Cajuput

Answer 2

The simplest way to do this is probably using the nativeQuery jpa method.

@After
public void cleanup() {
    EntityManager em = entityManagerFactory.createEntityManager();
    em.getTransaction().begin();
    em.createNativeQuery("truncate table person").executeUpdate();
    em.createNativeQuery("truncate table preferences").executeUpdate();
    em.getTransaction().commit();
}

Robby

'truncate' is DDL statement and there is no need to begin transaction, all DDLs are autocommitted. – Salol

I would of agreed but the createNativeQuery required a transaction. Otherwise it was throwing an exception. At least that was the case in hibernate. – Robby

@gadon: That depends on the database. At least PostgreSQL, DB2 and Informix have (some) support for transactional DDL. Oracle, however, does not. – Wyckoff

Answer 3

I am a bit confused as DBUnit will reinitialize the database to a known state before every test.

They also recommend as a best practice not to cleanup or otherwise change the data after the test.

So if it is cleanup you're after to prepare the db for the next test, I would not bother.

Housebreaker

Actually, I'm not using the DBUnit. I just tried the mentioned DELETE_ALL operation. I'm filling my database in a JUnit @Before method. Is DBUnit a perfect fit with JPA? – Cahra

I use Unitils which is a rapper around dbunit. You create a dataset with the stuff you care about (in xml : nobody is perfect), add a couple of annotations to your testclass and you're done. See : unitils.org/tutorial.html#Testing_with_JPA – Housebreaker

Answer 4

Yes, in-transaction test would make your life much easier, but if transaction is your thing then you need to implement compensating transaction(s) during cleanup (in @After). It sounds laborious and it might be but if properly approached you may end up with a set of helper methods (in tests) that compensate (cleanup) data accumulated during @Before and tests (using JPA or straight JDBC - whatever makes sense).

For example, if you use JPA and call create methods on entities during tests you may utilize (using AOP if you fancy or just helper test methods like us) a pattern across all tests to:

1. track ids of all entities that have been created during test
2. accumulate them in order created
3. replay entity deletes for these entities in reverse order in @After

Selfstarter

This approach sounds good, but is still too much work for my taste. Even if you keep track of the entities created, things can go wrong if you delete them in the wrong order (due to referential integrity issues). – Cahra

Yes, it is too elaborate for smaller test suites. To prevent RI problems entities get deleted in order reversed to being created. The main benefit is that after this gets done, test cleanup becomes transparent and places zero overhead on developers. The more tests the more it makes sense. Another benefit: all developers utilize the same framework to create test data - otherwise their tests either won't work or will break something. – Selfstarter

Answer 5

Not the correct answer to this question. But was the first result from google page so here is the answer for Hibernate users:

import jakarta.persistence.EntityManager;
import org.hibernate.Session;

Session session = entityManager.unwrap(Session.class);
session.getSessionFactory().getSchemaManager().truncateMappedObjects();

Just call it in beforeEach.

Maxia

Answer 6

My setup is quite similar: it's Derby (embedded) + OpenJPA 1.2.2 + DBUnit. Here's how I handle integration tests for my current task: in every @Before method I run 3 scripts:

1. Drop DB — an SQL script that drops all tables.
2. Create DB — an SQL script that recreates them.
3. A test-specific DB unit XML script to populate the data.

My database has only 12 tables and the test data set is not very big, either — about 50 records. Each script takes about 500 ms to run and I maintain them manually when tables are added or modified.

This approach is probably not recommended for testing big databases, and perhaps it cannot even be considered good practice for small ones; however, it has one important advantage over rolling back the transaction in the @After method: you can actually detect what happens at commit (like persisting detached entities or optimistic lock exceptions).

Currycomb

Answer 7

Better late then never ... I just had the same problem and came around a pretty simple solution:

1. set the property "...database.action" to the value "drop-and-create" in your persistence-unit config
2. close the entity-manager and the entity-manager factory after each test

persistence.xml

    <persistence-unit name="Mapping4" transaction-type="RESOURCE_LOCAL" >
    <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
    <class>...</class>
    <class>...</class>

    <properties>
        ...
        <property name="javax.persistence.schema-generation.database.action" value="drop-and-create" />
        ...
    </properties>
</persistence-unit>

unit-test:

...
@Before
public void setup() {
    factory = Persistence.createEntityManagerFactory(PERSISTENCE_UNIT_NAME);
    entityManager = factory.createEntityManager();
}


@After
public void tearDown() {
    entityManager.clear();
    entityManager.close();
    factory.close();
}

...

Neper

Answer 8

I delete the DB file after each run:

boolean deleted = Files.deleteIfExists(Paths.get("pathToDbFile"));

A little dirty but works for me. Regards

Presnell

Answer 9

Option 1: You can disable foreign key checks before truncating tables, and enable them again after truncation. You will still have checks in tests in this way.

Option 2: H2 database destroys the in-memory database when the last connection closed. I guess Derby DB supports something similar, or you can switch to H2.

See also: I wrote a code to truncate tables before each test using Hibernate in a related question: https://mcmap.net/q/748898/-best-way-to-truncate-all-tables-with-hibernate

Dutch