How should I reset a JPA-controlled database before every test?

Question

Is there a best-practice pattern for completely resetting a database to a freshly-paved schema with JPA before a unit test? I have been using a testing persistence unit with hbml2ddl.auto=create-or-drop and recreating EMFs before each test, but I wonder if there's a cleaner way to do it.

Answer 1

Unit tests should not talk to the database.

Assuming you're writing an integration test for your data access layer, you could use a tool like DBUnit, or you could create a static test helper that programmatically resets your database state by doing all of your deletes and inserts using JPA queries inside of a transaction.

Answer 2

Resetting the database is not a big problem if you use a fast Java database such as the H2 database or HSQLDB. Compared to using Oracle / MySQL (or whatever you use for production) this will speed up your tests, and it will ensure all your code is tested as when using the 'real' production database.

For maximum performance, you can use H2 in-memory (that way you may not have to reset the database manually - it's reset automatically if the connection is closed), or you can use a regular persistent database. To reset the database after use in H2, run the (native) statement 'drop all objects delete files'.

Answer 3

Is there a best-practice pattern for completely resetting a database to a freshly-paved schema with JPA before a unit test?

Don't reset the whole DB schema before every unit test, but reset the "DB environment (which is specific to the current unit test)" at END of each unit test.

We have an entity...

@Entity
public class Candidate implements {
    private String id;
    private String userName;
    private EntityLifeCycle lifeCycle;

    protected Candidate() {
    }
    public Candidate(String userName) {
        this.userName = userName;
    }

    @Id @GeneratedValue(generator="uuid", strategy=GenerationType.AUTO)
    @GenericGenerator(name="uuid", strategy="uuid", parameters = {})
    @Column(name="candidate_id", nullable=false, unique=true)
    public String getId() {
        return id;
    }
    protected void setId(String id) {
        this.id = id;
    }

    @Column(name="user_name", nullable=false, unique=true)
    public String getUserName() {
        return userName;
    }
    public void setUserName(String userName) {
        this.userName = userName;
    }

    @Embedded
    public EntityLifeCycle getLifeCycle() {
        if(lifeCycle == null) {
            lifeCycle = new EntityLifeCycleImpl();
        }
        return lifeCycle;
    }
    public void setLifeCycle(EntityLifeCycleImpl lifeCycle) {
        this.lifeCycle = lifeCycle;
    }

    @PrePersist
    public void prePersist() {
         lifeCycle.setCreatedDate(new Date());
    }
}

We are setting the createdDate for each Candidate instance in prePersist() method. Here is a test case that asserts that createdDate is getting set properly....

   public class EntityLifeCycleTest {
    @Test
    public void testLifeCycle() {
        EntityManager manager = entityManagerFactory.createEntityManager();
        Candidate bond = new Candidate("Miss. Bond");
        EntityTransaction tx = manager.getTransaction();
        tx.begin();
        manager.persist(bond);
        tx.commit();

        Assert.assertNotNull(bond.getLifeCycle().getCreatedDate());
        manager.close();
    }
}

This test case will run properly for the first time. But if we run this test case second time it would throw ConstraintViolationException, because the userName is unique key.

Therefore, I think the right approach is to "clean the DB environment (which is specific to the current unit test)" at end of each test case. Like this...

public class EntityLifeCycleTest extends JavaPersistenceTest {
    @Test
    public void testLifeCycle() {
        EntityManager manager = entityManagerFactory.createEntityManager();
        Candidate bond = new Candidate("Miss. Bond");
        EntityTransaction tx = manager.getTransaction();
        tx.begin();
        manager.persist(bond);
        tx.commit();

        Assert.assertNotNull(bond.getLifeCycle().getCreatedDate());

      /* delete Candidate bond, so next time we can run this test case successfully*/
        tx = manager.getTransaction();
        tx.begin();
        manager.remove(bond);
        tx.commit();
        manager.close();
    }
}

I have been using a testing persistence unit with hbml2ddl.auto=create-or-drop and recreating EMFs before each test, but I wonder if there's a cleaner way to do it.

Recreating EMF before each test is time consuming, IMO.

Drop and recreate the DB schema only if you have made some changes to @Entity annotated class that affects the underlying DB (e.g. adding/removing columns and/or constraints). So first validate the schema, if the schema is valid don't recreate it, and if invalid then recreate it. Like this...

public class JavaPersistenceTest {
    protected static EntityManagerFactory entityManagerFactory;
@BeforeClass
public static void setUp() throws Exception {
    if(entityManagerFactory == null) {
         Map<String, String> properties = new HashMap<String, String>(1);
         try {
           properties.put("hibernate.hbm2ddl.auto", "validate");
          entityManagerFactory = Persistence.createEntityManagerFactory("default", properties);
    } catch (PersistenceException e) {
        e.printStackTrace();
        properties.put("hibernate.hbm2ddl.auto", "create");
        entityManagerFactory = Persistence.createEntityManagerFactory("default", properties);
    }
         }
}
}

Now, if you run all the test cases(that extend JavaPersistenceTest) in one go, the EMF will be created only once(or twice if the schema was invalid).

Answer 4

DBUnit has much of what you need, I use Springs Testing framework to rollback, transactions after each test see http://static.springsource.org/spring/docs/3.0.x/spring-framework-reference/html/testing.html