TDD with HSQLDB -- removing foreign keys

I'm using HSQLDB for data layer integration testing, which is great. However, I'm finding that my foreign key constraints are getting in the way of my tests. For example, to test a simple select on one table, I have to insert dummy data into five additional tables. This makes me want to throw things.

I have JPA annotations throughout our model code, and have configured Hibernate to开发者_StackOverflow中文版 recreate the schema (hbm2ddl.create-drop) in configuration. The joins are being interpreted correctly as foreign key constraints when the tables are generated.

What I'd like is to either:

1. Not create the foreign keys initially (ideal, cleanest), or
2. Find a way to programmatically drop all the foreign keys in the database (kinda hacky but will get the job done)

If it's helpful, I'm using Spring to autowire these tests. The tests in question inherit from AbstractTransactionalJUnit4SpringContextTests.

What do you think? Can this be done?

You can deactivate FK constraints with the following instruction:

SET REFERENTIAL_INTEGRITY FALSE;

You could execute it via a JDBC Statement before your test methods (and set it back to TRUE after).

I encountered the exact same trouble while trying to test my DAO with flat xml Dataset. Config is DBunit + HSQLDB 2.2.8 + JUnit4 + Spring + JPA-> all together leading to

java.sql.SQLIntegrityConstraintViolationException: integrity constraint violation: foreign key no parent; FK13EE6CE6F09A6AAC table: ****

I found a nice workaround by implementing a listener extending AbstractTestExecutionListener. You would specify the type of action to be taken before each test, in our case, disabling foreign key constraints. NOTE: the syntax may differ depending of the version of HSQLDB used.

public class ForeignKeyDisabling extends AbstractTestExecutionListener {    
    @Override
    public void beforeTestClass(TestContext testContext) throws Exception {
        IDatabaseConnection dbConn = new DatabaseDataSourceConnection(
                testContext.getApplicationContext().getBean(DataSource.class)
                );
        dbConn.getConnection().prepareStatement("SET DATABASE REFERENTIAL INTEGRITY FALSE").execute();

    }
}

You then only need to add this listener in the collection already in place in your tests:

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration({"applicationContext-test.xml"})
@TestExecutionListeners({DependencyInjectionTestExecutionListener.class, DataSetTestExecutionListener.class, ForeignKeyDisabling.class})

Do it fast:

SET REFERENTIAL_INTEGRITY FALSE; into import.sql file in your test resources directory.

Problem resolved fast and nicely :)

Here are some information about import.sql http://christopherlakey.com/articles/import-sql.html

Base on the inspiration in this thread, I've created somewhat more robust solution for this problem. The point was, I really like constraints while running test and all the other solutions just kept it disabled. This code will disable them only for duration of dataset import and then re-enable them. And can be easily extended to support another DB engine:

import com.github.springtestdbunit.DbUnitTestExecutionListener;
import org.apache.log4j.Logger;
import org.dbunit.database.DatabaseDataSourceConnection;
import org.dbunit.database.IDatabaseConnection;
import org.springframework.test.context.TestContext;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;

/**
 * Class DisableForeignKeysDbUnitTestExecutionListener
 * Simple wrapper class around DbUnitTestExecutionListener, which - for the time of importing the database -
 * disables Foreign Key Constraints checks.
 * This class can be extended by simply overriding toggleForeignKeysConstraintsForDbEngine(Connection, String, boolean);
 * subclasses should always call super-implementation for default case.
 */
public class DisableForeignKeysDbUnitTestExecutionListener
    extends DbUnitTestExecutionListener
{
    private static final Logger logger = Logger.getLogger(DisableForeignKeysDbUnitTestExecutionListener.class);
    private Connection cachedDbConnection;

    @Override
    public void beforeTestMethod(TestContext testContext)
        throws Exception
    {
        this.toggleForeignKeysConstraints(testContext, false);
        super.beforeTestMethod(testContext);
        this.toggleForeignKeysConstraints(testContext, true);
    }

    /**
     * Method should perform query to disable foreign keys constraints or return false,
     * if it is not able to perform such query (e.g. unknown database engine)
     *
     * @param connection    Database connection
     * @param dbProductName Name of the database product (as reported by connection metadata)
     * @param enabled       Expected state of foreign keys after the call
     *
     * @return True, if there was suitable statement for specified engine, otherwise false
     *
     * @throws SQLException
     */
    protected boolean toggleForeignKeysConstraintsForDbEngine(Connection connection, String dbProductName, boolean enabled)
        throws SQLException
    {
        switch (dbProductName)
        {
            case "HSQL Database Engine":
                connection.prepareStatement("SET DATABASE REFERENTIAL INTEGRITY " + (enabled ? "TRUE" : "FALSE"))
                          .execute();
                return (true);
        }
        return (false);
    }

    private void toggleForeignKeysConstraints(TestContext testContext, boolean enabled)
    {
        try
        {
            Connection connection = this.getDatabaseConnection(testContext);
            String databaseProductName = connection.getMetaData().getDatabaseProductName();
            if (!this.toggleForeignKeysConstraintsForDbEngine(connection, databaseProductName, enabled))
            {
                throw new IllegalStateException("Unknown database engine '" + databaseProductName +
                                                    "'. Unable to toggle foreign keys constraints.");
            }
        }
        catch (Throwable throwable)
        {
            logger.error("Unable to toggle Foreign keys constraints: " + throwable.getLocalizedMessage());
        }
    }

    synchronized private Connection getDatabaseConnection(TestContext testContext)
        throws SQLException
    {
        if (this.cachedDbConnection == null)
        {
            DataSource dataSource = testContext.getApplicationContext().getBean(DataSource.class);
            if (dataSource == null)
            {
                throw new IllegalStateException("Unable to obtain DataSource from ApplicationContext. " +
                                                    "Foreign constraints will not be disabled.");
            }

            IDatabaseConnection dsConnection = new DatabaseDataSourceConnection(dataSource);
            this.cachedDbConnection = dsConnection.getConnection();
        }

        return (this.cachedDbConnection);
    }
}

I would consider spending some time on creating a couple of fixtures, possibly with DBUnit, which you insert @Before.

BTW, AbstractTransactionalJUnit4Test is deprecated in Spring 3.0