What is the best way to reset the database to a known state while testing database operations?

I'm writing tests with JUnit for some methods operating on a test database.

I need to reset t开发者_如何学Pythonhe database to the original state after each @Test. I'm wondering what's the best way to do that.

Is there some method in the EntityManager? Or should I just delete everything manually or with an SQL statement? Would it be better to just drop and recreate the whole database?

One technique that I have used in the past is to recreate the database from scratch by simply copying the database from a standard 'test database', and using this in the tests.

This technique works if:

1. Your schema doesn't change much (otherwise it's a pain to keep in line)
2. You're using something like hibernate which is reasonably database independent.

This has the following advantages:

1. It works with code that manages its own transactions. My integration tests run under junit. For instance, when I'm testing a batch process I call Batch.main() from junit, and test stuff before and after. I wouldn't want to change the transaction processing in the code under test.
2. It's reasonably fast. If the files are small enough, then speed is not a problem.
3. It makes running integration tests on a ci server easy. The database files are checked in with the code. No need for a real database to be up and running.

And the following disadvantages:

1. The test database files need to be maintained along with the real database. If you're adding columns all of the time, this can be a pain.
2. There is code to manage the jdbc urls, because they change for every test.

I use this with Oracle as the production/integration database and hsqldb as the test database. It works pretty well. hsqldb is a single file, so is easy to copy.

So, in the @Before, using hsqldb, you copy the file to a location such as target/it/database/name_of_test.script. This is picked up in the test.

In the @After, you delete the file (or just leave it, who cares). With hsqldb, you'll need to do a SHUTDOWN as well, so that you can delete the file.

You can also use a @Rule which extends from ExternalResource, which is a better way to manage your resources.

One other tip is that if you're using maven or something like it, you can create the database in target. I use target/it. This way, the copies of databases get removed when I do and mvn clean. For my batches, I actually copy all of my other properties files etc into this directory as well, so I don't get any files appearing in strange places either.

The easiest way is simply rolling back all changes after each test. This requires a transactional RDBMS and a custom test runner or similar that wraps each test into it's own transaction. Spring's AbstractTransactionalJUnit4SpringContextTests does exactly that.

DBUnit can reset your database between tests and even fill it with predefined test data.

I am answering this more for my own reference, but here goes. The answer assumes a per-developer SQL Server DB.

Basic approach

1. Use DBUnit to store an XML file of the known state. You can extract this file once you've set up the DB, or you can create it from scratch. Put this file in your version control along with scripts that call DBUnit to populate the DB with it.

2. In your tests, call the aforementioned scripts using @Before.

Speedup 1

Once this is working, tweak the approach to speed things up. Here's an approach for a SQL Server DB.

Before DBUnit, totally wipe out the DB:

EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL';
EXEC sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL';
EXEC sp_MSForEachTable 'SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON DELETE FROM ?';

After DBUnit, restore the constraints

EXEC sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL';
EXEC sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL';

Speedup 2

Use SQL Server's RESTORE functionality. In my tests, this runs in 25% the time DBUnit takes. If (and only if) this is a major factor in your test duration, it's worth investigating this approach.

The following classes show an implementation using Spring JDBC, JTDS, and CDI injection. This is designed to work for in-container tests, where the container may be making its own connections to the DB, that need to be stopped

import java.io.File;
import java.sql.SQLException;

import javax.inject.Inject;
import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.JdbcTemplate;

/**
 * Allows the DB to be reset quickly using SQL restore, at the price of
 * additional complexity. Recommended to vanilla DBUnit unless speed is
 * necessary.
 * 
 * @author aocathain
 * 
 */
@SuppressWarnings({ "PMD.SignatureDeclareThrowsException" })
public abstract class DbResetterSO {

    protected final Logger logger = LoggerFactory.getLogger(getClass());

    /**
     * Deliberately created in the target dir, so that on mvn clean, it is
     * deleted and will be recreated.
     */
    private final File backupFile = new File(
            "target\\test-classes\\db-backup.bak");

    @Inject
    private OtherDbConnections otherDbConnections;

    /**
     * Backs up the database, if a backup doesn't exist.
     * 
     * @param masterDataSource
     *            a datasource with sufficient rights to do RESTORE DATABASE. It
     *            must not be connected to the database being restored, so
     *            should have db master as its default db.
     */
    public void backup(final DataSource masterDataSource) throws Exception {

        final JdbcTemplate masterJdbcTemplate = new JdbcTemplate(
                masterDataSource);

        if (backupFile.exists()) {
            logger.debug("File {} already exists, not backing up", backupFile);
        } else {
            otherDbConnections.start();

            setupDbWithDbUnit();

            otherDbConnections.stop();
            logger.debug("Backing up");
            masterJdbcTemplate.execute("BACKUP DATABASE [" + getDbName()
                    + "] TO DISK ='" + backupFile.getAbsolutePath() + "'");
            logger.debug("Finished backing up");
            otherDbConnections.start();
        }

    }

    /**
     * Restores the database
     * 
     * @param masterDataSource
     *            a datasource with sufficient rights to do RESTORE DATABASE. It
     *            must not be connected to the database being restored, so
     *            should have db master as its default db.
     */
    public void restore(final DataSource masterDataSource) throws SQLException {
        final JdbcTemplate masterJdbcTemplate = new JdbcTemplate(
                masterDataSource);

        if (!backupFile.exists()) {
            throw new IllegalStateException(backupFile.getAbsolutePath()
                    + " must have been created already");
        }
        otherDbConnections.stop();

        logger.debug("Setting to single user");

        masterJdbcTemplate.execute("ALTER DATABASE [" + getDbName()
                + "] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;");

        logger.info("Restoring");

        masterJdbcTemplate.execute("RESTORE DATABASE [" + getDbName()
                + "] FROM DISK ='" + backupFile.getAbsolutePath()
                + "' WITH REPLACE");

        logger.debug("Setting to multi user");

        masterJdbcTemplate.execute("ALTER DATABASE [" + getDbName()
                + "] SET MULTI_USER;");

        otherDbConnections.start();
    }

    /**
     * @return Name of the DB on the SQL server instance
     */
    protected abstract String getDbName();

    /**
     * Sets up the DB to the required known state. Can be slow, since it's only
     * run once, during the initial backup. Can use the DB connections from otherDbConnections.
     */
    protected abstract void setupDbWithDbUnit() throws Exception;
}


import java.sql.SQLException;

/**
 * To SQL RESTORE the db, all other connections to that DB must be stopped. Implementations of this interface must
 * have control of all other connections.
 * 
 * @author aocathain
 * 
 */
public interface OtherDbConnections
{

    /**
     * Restarts all connections
     */
    void start() throws SQLException;

    /**
     * Stops all connections
     */
    void stop() throws SQLException;

}



import java.sql.Connection;
import java.sql.SQLException;

import javax.annotation.PostConstruct;
import javax.annotation.PreDestroy;
import javax.enterprise.inject.Produces;
import javax.inject.Named;
import javax.inject.Singleton;
import javax.sql.DataSource;

import net.sourceforge.jtds.jdbcx.JtdsDataSource;

import org.springframework.jdbc.datasource.DelegatingDataSource;
import org.springframework.jdbc.datasource.SingleConnectionDataSource;

/**
 * Implements OtherDbConnections for the DbResetter and provides the DataSource during in-container tests.
 * 
 * @author aocathain
 * 
 */
@Singleton
@SuppressWarnings({ "PMD.AvoidUsingVolatile" })
public abstract class ResettableDataSourceProviderSO implements OtherDbConnections
{

    private volatile Connection connection;
    private volatile SingleConnectionDataSource scds;
    private final DelegatingDataSource dgds = new DelegatingDataSource();

    @Produces
    @Named("in-container-ds")
    public DataSource resettableDatasource() throws SQLException
    {
        return dgds;
    }

    @Override
    @PostConstruct
    public void start() throws SQLException
    {
        final JtdsDataSource ds = new JtdsDataSource();

        ds.setServerName("localhost");
        ds.setDatabaseName(dbName());
        connection = ds.getConnection(username(), password());

        scds = new SingleConnectionDataSource(connection, true);
        dgds.setTargetDataSource(scds);

    }

    protected abstract String password();

    protected abstract String username();

    protected abstract String dbName();

    @Override
    @PreDestroy
    public void stop() throws SQLException
    {
        if (null != connection)
        {
            scds.destroy();
            connection.close();
        }

    }
}

It's an old Topic I know but times have changed in the last ten years ;)

A solution I like much is do create a prepatched dockerimage and create a container from it with Testcontainers. It may take a moment to start the container (not that much time) but in this way you are able to run all tests in parallel because it's up to you how many Databse Instances you want to use (I use one per CPU dynamically) and speedup the whole testsoute alot.

The nice thing here is, if your application relies on more other dependencies (e.g. other servers like ssh, ftp, ldap, rest Services, ... whatever) you can deal with it in the same way.

In Addition, you are of course able do combine this solution with any of the other solutions to speed the whole thing up a little more.