DBUnit & Postgres UUID Primary Key

Question

I am trying to use DBUnit to run integration tests, however I am finding myself unable to insert the primary key columns, which obviously will not work with foreign keys referencing the primary key later on in the file.

For example, I have the following DDL:

CREATE TABLE attributes(
    attribute_id UUID NOT NULL DEFAULT uuid_generate_v4(),
    attribute VARCHAR(64) NOT NULL,
    description TEXT NOT NULL,
    PRIMARY KEY(attribute_id)
);

And the DBUnit setup XML looks like:

<?xml version="1.0" encoding="UTF-8" ?>
<dataset>
    <attributes attribute_id="233bc966-4fcd-4b46-88e6-3e07090f322d" attribute="Empathy" description="Empathy Description" />
</dataset>

When I attempt to run the test, I get the failure:

    org.dbunit.dataset.NoSuchColumnException: attributes.ATTRIBUTE_ID -  (Non-uppercase input column: attribute_id) in ColumnNameToIndexes cache map. Note that the
ap's column names are NOT case sensitive.

Here is the test being run:

@RunWith(SpringJUnit4ClassRunner.class)
@WebAppConfiguration
@DbUnitConfiguration(dataSetLoader = TestConfiguration.FlatXmlDataLoaderProxy.class)
@ContextConfiguration(classes = {ApplicationConfiguration.class, TestConfiguration.class})
@TestExecutionListeners({
        DependencyInjectionTestExecutionListener.class,
        DirtiesContextTestExecutionListener.class,
        TransactionalTestExecutionListener.class,
        DbUnitTestExecutionListener.class
})
public class ApplicationAssessmentJobTest {
    @Autowired
    private ApplicationAssessmentJob applicationAssessmentJob;

    @Test
    @DatabaseSetup("/dbunit/ApplicationAssessmentJobTestSetup.xml")
    @DatabaseTearDown("dbunit/ApplicationAssessmentJobTestTearDown.xml")
    public void testJob() {
        ApplicationAssessmentJobModel model = new ApplicationAssessmentJobModel();
        model.setApplicationId(UUID.fromString("41fa1d51-c1ee-482b-80a7-a6eefda64436"));

        applicationAssessmentJob.receiveMessage(model);
    }
}

This error shown does not appear to be directly related to the underlying issue. If I remove the attribute_id column from the XML, the record is inserted.

Maybe I'm too late, but DbUnit won't recognize any vendor-specific data-types (such as `uuid`), unless you explicitly [use the vendor-specific data-type factory](http://dbunit.sourceforge.net/faq.html#typefactory). In your case, you should use an instance of `org.dbunit.ext.postgresql.PostgresqlDataTypeFactory`. — pozs, Sep 21 '15 at 10:24

score 4 · Answer 1 · answered Oct 28 '16 at 13:29

I got the same problem and i finally found a solution. As mentionned in the documentation, you need to override the default dbunit DatabaseConfig to set the IDataTypeFactory specific for PostgreSQL.

Here's my test configuration :

@Autowired
private DataSource dataSource;

@Bean
public DatabaseConfigBean databaseConfigBean() {
    DatabaseConfigBean databaseConfigBean = new DatabaseConfigBean();
    databaseConfigBean.setDatatypeFactory(new PostgresqlDataTypeFactory());
    return databaseConfigBean;
}

@Bean(name = "dbUnitDatabaseConnection")
public DatabaseDataSourceConnectionFactoryBean dbUnitDatabaseConnection() throws SQLException, DatabaseUnitException, IOException {
    DatabaseDataSourceConnectionFactoryBean databaseDataSourceConnectionFactoryBean = new DatabaseDataSourceConnectionFactoryBean();
    databaseDataSourceConnectionFactoryBean.setDatabaseConfig(databaseConfigBean());
    databaseDataSourceConnectionFactoryBean.setDataSource(dataSource);
    databaseDataSourceConnectionFactoryBean.setSchema("public");
    return databaseDataSourceConnectionFactoryBean;
}

Then, you need to specifiy that you will use custom IDatabaseConnection in your tests. Those connections will be create by the bean 'dbUnitDatabaseConnection'. For example, here's how I declare my spring test configuration :

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(classes = {SpringRepositoryConfigurationTest.class})
@TestExecutionListeners({ DependencyInjectionTestExecutionListener.class, DbUnitTestExecutionListener.class })
@DbUnitConfiguration(databaseConnection = {"dbUnitDatabaseConnection"})

score 2 · Accepted Answer · 2015-03-05T23:51:17.507

After fighting with DBUnit/Spring-Test-DBUnit for most of my day, I decided to scrap that library and roll my own for the mere fact that DBUnit seems to be nothing but pain.

I was able to write a setup/teardown in about 40 SLoC in less than 30 minutes. It uses plain SQL, which is philosophically more in line with my choice of jOOQ over Hibernate. Certainly less than ideal, but 15 minutes of searching didn't turn up anything to handle a simple use case of running SQL on setup and on teardown. Worth noting is a requirement on a java.sql.DataSource Bean available from the ApplicationContext.

DbInitTestExecutionListener.java

import org.springframework.core.io.ClassPathResource;
import org.springframework.jdbc.datasource.init.ResourceDatabasePopulator;
import org.springframework.test.context.TestContext;
import org.springframework.test.context.TestExecutionListener;

import javax.sql.DataSource;
import java.util.Arrays;

public class DbInitTestExecutionListener implements TestExecutionListener {
    @Override
    public void beforeTestClass(TestContext testContext) throws Exception {}

    @Override
    public void prepareTestInstance(TestContext testContext) throws Exception {}

    @Override
    public void beforeTestMethod(TestContext testContext) throws Exception {
        DatabaseSetup setup = testContext.getTestMethod().getAnnotation(DatabaseSetup.class);

        if (setup != null) {
            if (setup.clearInsert()) {
                afterTestMethod(testContext);
            }

            ResourceDatabasePopulator populator = new ResourceDatabasePopulator();

            Arrays.asList(setup.value()).stream()
                    .map(ClassPathResource::new)
                    .forEach(populator::addScript);

            populator.execute(testContext.getApplicationContext().getBean(DataSource.class));
        }
    }

    @Override
    public void afterTestMethod(TestContext testContext) throws Exception {
        DatabaseTearDown tearDown = testContext.getTestMethod().getAnnotation(DatabaseTearDown.class);

        if (tearDown != null) {
            ResourceDatabasePopulator populator = new ResourceDatabasePopulator();

            Arrays.asList(tearDown.value()).stream()
                    .map(ClassPathResource::new)
                    .forEach(populator::addScript);

            populator.execute(testContext.getApplicationContext().getBean(DataSource.class));
        }
    }

    @Override
    public void afterTestClass(TestContext testContext) throws Exception {}
}

DatabaseTearDown.java

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

@Retention(RetentionPolicy.RUNTIME)
@Target({ ElementType.METHOD })
public @interface DatabaseTearDown {
    String[] value();
}

DatabaseSetup.java

import java.lang.annotation.*;

@Retention(RetentionPolicy.RUNTIME)
@Target({ ElementType.METHOD })
public @interface DatabaseSetup {
    boolean clearInsert() default true;

    String[] value();
}

With a minimal test configuration:

@RunWith(SpringJUnit4ClassRunner.class)
@WebAppConfiguration
@ContextConfiguration(classes = {ApplicationConfiguration.class})
@TestExecutionListeners({
        DependencyInjectionTestExecutionListener.class,
        DirtiesContextTestExecutionListener.class,
        TransactionalTestExecutionListener.class,
        DbInitTestExecutionListener.class
})
public class TestClass {
    @Test
    @DatabaseSetup("/dbinit/TestSetup.sql")
    @DatabaseTearDown("/dbinit/TestTearDown.sql")
    public void testJob() {
      // TODO: Add test code here
    }
}

score 0 · Answer 3 · answered Mar 05 '15 at 14:33

This error message

org.dbunit.dataset.NoSuchColumnException: attributes.ATTRIBUTE_ID ...

suggests that dbunit is looking for a column named ATTRIBUTE_ID. Your CREATE TABLE statement creates a column named attribute_id.

If dbunit uses delimited identifiers, PostgreSQL will consider "ATTRIBUTE_ID" and "attribute_id" as two different identifiers. (In your case, two different columns.) If dbunit doesn't use delimited identifiers, PostgreSQL will fold to lower case, and it will consider ATTRIBUTE_ID and attribute_id to be the same identifier--attribute_id.

SQL standards require folding ordinary identifiers to upper case. PostgreSQL's behavior here is not standard, but it's not likely to change.

It looks like dbunit might be folding to upper case (following SQL standards) on its own. If that's the case, you can probably configure dbunit to use case-sensitive identifiers. (I don't know how to do that off the top of my head.)

That was my thought as well, but configuring the `FlatXmlDataSetLoader` to be case-sensitive did nothing to solve this problem. Of note, is that when one removes the `attribute_id` column, all other columns are inserted without problem. Further testing seems to indicate this issue has to do with the type of column and the default. Unfortunately I simply lack the time to discover the root cause. — , Mar 05 '15 at 23:47

DBUnit & Postgres UUID Primary Key

3 Answers3