Create and clean DB setup only once for testing all DAOs [closed]

Closed. This question is seeking recommendations for books, tools, software libraries, and more. It does not meet Stack Overflow guidelines. It is not currently accepting answers.

---

We don’t allow questions seeking recommendations for books, tools, software libraries, and more. You can edit the question so it can be answered with facts and citations.

Closed 4 years ago.

Improve this question

Does anybody knows a way to achieve the following flow for testing DAOs:

• Run SQL script to create common DB setup (insert data into all tables)
• Test DAO1
• Test DAO2
• …
• Clean-up DB Data created in Step 1

Using Spring, Hibernate, JUnit, Maven stack.

I understand that best practice would be that we create data for each test DAO (@BeforeClass) and clean-up the same after all tests are done (@AfterClass).

But in our case there are just too many dependencies between different database tables (client’s legacy DB :-( can’t do anything about that at the moment). Populating each table with test data requires data in many other tables as well. So, creating data individually for each DAO would be very tough and time consuming. So, we really need to create DB test data only once.

I’ve created test data using static block in BaseDAO (extended by each DAO Test Class) – which obviously runs only once. But problem how to clean-开发者_如何学运维up the same when all tests (of all DAO Test subclasses) get completed. An @AfterClass teardown method in base class will run each time after a DAO Test class completes.

Please advise.

---

If you're using Maven, a good option is to use DBUnit. It lets you export test data from the database (or just write it in XML), to be imported for your tests. It has a Maven plugin which will do what you require.

---

In Spring 3:

<jdbc:embedded-database id="dataSource">
    <jdbc:script location="classpath:schema.sql"/>
    <jdbc:script location="classpath:test-data.sql"/>
</jdbc:embedded-database>

Documentation. To clean up the database after tests, create a Spring bean that will be picked up only during tests:

@Service
public class DbCleanup {
    @Resource
    private DataSource ds;

    @PreDestroy
    public cleanUpDb() {
        //do your cleanup with DB
    }
}

---

I use the following solution.

(1) I have a simple interface called Fixture

package com.obecto.fixtures;

public interface Fixture {

    void prepare();

}

(2) I prepare an SQL fixture to populate an empty database - this can be done either through entities or by executing SQL like this:

package com.avaco2.fixtures;

import java.util.logging.Logger;

import org.apache.commons.dbcp.BasicDataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.stereotype.Component;
import org.springframework.test.jdbc.SimpleJdbcTestUtils;

import com.obecto.fixtures.Fixture;

@Component(value="logEventsSQLFixture")
public class LogEventsSQLFixture implements Fixture {
    private static String IMPORT_SQL = "import-LogEvents.sql";
    private static Logger LOG = Logger.getLogger(LogEventsSQLFixture.class.getName());

    @Autowired
    private BasicDataSource dataSource;

    @Override
    public void prepare() {
        SimpleJdbcTemplate jdbcTemplate = new SimpleJdbcTemplate(dataSource);
        Resource sqlScript = new ClassPathResource(IMPORT_SQL);
        try {
            SimpleJdbcTestUtils.executeSqlScript(jdbcTemplate, sqlScript, true);
        } catch (Exception e) {
            LOG.severe("Cannot import " + IMPORT_SQL);
        }

    }

}

(3) Inject your fixtures into the Test class and prepare-them in the @Before-method.

Always use another database for the tests, so you can safely use the create-drop setting of hibernate. To reload the context before each test method you can use the following annotation - @DirtiesContext(classMode=ClassMode.AFTER_EACH_TEST_METHOD)

---

Nobody suggested annotating with @Transactional attribute for each test. Provided the db engine supports it, that should take care of rolling back the state. See here or stackoverflow link for more on this.

---

Usually I back up entire db instance (actually I'm working with Oracle and imp/exp are great tools). SQL Server and the alikes have replication method. Once you've got your data ready, just export the entire instance and load it before testing.

Once the test is done, drop the DB and recreate. (dumping and loading an entire DB with native programs can be faster than expected )

Regards

PS: if you can, just build up a clone db on a virtual machine then save a snapshot ov the virtual machine (so you can restore it later on).

---

You can also use a DB mockup tool such as Acolyte ( https://github.com/cchantep/acolyte ) in which case you don't need to clean data, being sure which data is available in which JDBC for which test, without altering the JDBC based code you want to test.

Note: I am the author of Acolyte.