Column not found exception, with @ExpectedDatabase

[vict0r81]

Hey. I'm very keen on your helpful framework, thanks!

But some days ago I get the issue with @ExpectedDatabase annotation and table, names users. When I was running my tests, unexpectedly I've found message, that column admin can't be found for table users. That was weird, 'cause I have not had that column neither under my entity mappings nor under expected datasets. So I've started a little investigation, and found, that H2 has its own table, named users as well. Here is the link with H2 information schema description: http://www.h2database.com/html/grammar.html#information_schema But, when I was doing my checks w.o. using ExpectedDatabase annotations everything went fine. I had not had any similar exceptions. So, when I was in debug, I've found, that table's metadata, that populated during test indeed contained admin column from H2 own users table, and merged my table structure. So I decided to wrote a little test project to illustrate this issue, you could simple try by cloning my repository: https://github.com/vict0r81/spring-dbunit-test

Hope my description will help you to bother that kind of problem.

Thanks, Victor.

[bcisneros]

Yes, I have the same issue. My workaround was just simply to name my table to USER, but I don't like to have the table names in singular.

Thanks @vict0r81 for the explanation.

Regards,

Benja

[FingolfinTEK]

You need to use the public schema in combination with an escape pattern and everything should work correctly:

@Configuration
public static class DbUnitConfiguration {

    @Bean
    public DatabaseConfigBean dbUnitDatabaseConfig() {
        final DatabaseConfigBean bean = new DatabaseConfigBean();
        bean.setDatatypeFactory(new H2DataTypeFactory());
        bean.setEscapePattern("`");
        return bean;
    }

    @Bean
    public DatabaseDataSourceConnectionFactoryBean dbUnitDatabaseConnection(
        DataSource dataSource) {
        final DatabaseDataSourceConnectionFactoryBean bean =
            new DatabaseDataSourceConnectionFactoryBean(dataSource);
        bean.setDatabaseConfig(dbUnitDatabaseConfig());
        bean.setSchema("public");
        return bean;
    }
}

[vict0r81]

Thanks @FingolfinTEK for good suggestion.

But, unfortunately that doesn't work for me. Checked with springtestdbunit ver 1.3.0 & 1.3.1 I guess, the reason is hidden here:

databaseConnection = DatabaseDataSourceConnectionFactoryBean
                        .newConnection((DataSource) databaseConnection);

Source: https://github.com/springtestdbunit/spring-test-dbunit/blob/master/spring-test-dbunit/src/main/java/com/github/springtestdbunit/DbUnitTestExecutionListener.java#L149-L150

Marked lines run before tests, and then this method will be called

public static IDatabaseConnection newConnection(DataSource dataSource) {
        try {
            return (new DatabaseDataSourceConnectionFactoryBean(dataSource)).getObject();
        } catch (Exception ex) {
            throw new IllegalStateException(ex);
        }
}

https://github.com/springtestdbunit/spring-test-dbunit/blob/master/spring-test-dbunit/src/main/java/com/github/springtestdbunit/bean/DatabaseDataSourceConnectionFactoryBean.java#L142-L148

But, instead of taking bean from Spring context it just creating new instance manually. So, all configs you've provided under @Configuration class won't make any sense for that method and essentially they would be skipped.

But few lines before creating a new connection, testContext variable utilised and has a type of DbUnitTestContextAdapter and as we can see application context can be easily extracted from there:

Object databaseConnection = testContext.getApplicationContext().getBean(connectionBeanNames[i]);

The code is from here: https://github.com/springtestdbunit/spring-test-dbunit/blob/master/spring-test-dbunit/src/main/java/com/github/springtestdbunit/DbUnitTestExecutionListener.java#L147

So, if the DatabaseDataSourceConnectionFactoryBean could potentially utilise the same thing for creating new connection under the hood and just in case of been absents create a new one manually, then your solution probably will works in a great way.

Does it make any sense?

Thanks, Victor.

[FingolfinTEK]

Hey @vict0r81,

I'm not exactly sure I follow, especially since this works for me in the exact same setup on both 1.2.1 and 1.3.0. I also suppose you meant 1.2.1 instead of 1.3.1 since 1.3.1 isn't out yet.

But what you're saying isn't exactly making sense since those code snippets have been taken out of context - they will only be executed if the application context does not contain beans of type IDatabaseConnection, which it will in case you have configured DatabaseDataSourceConnectionFactoryBean, with the name of dbUnitDatabaseConnection

So, the reasons why that's not working for you may be multiple - for instance, if you haven't used the exact same code that I've supplied, you may have renamed the method instantiating the DatabaseDataSourceConnectionFactoryBean to a value other than dbUnitDatabaseConnection, which is a default value. Otherwise, you may be using @DbUnitConfiguration(databaseConnection = "dataSource") instead of @DbUnitConfiguration(databaseConnection = "dbUnitDatabaseConnection") or omitting the @DbUnitConfiguration annotation completely.

The only thing I can suggest is you supplying a small sample that reproduces your issues.

[sylvietseng]

setSchema in uppercase https://github.com/vict0r81/spring-dbunit-test/blob/master/src/test/java/com/example/config/SpringH2Config.java

@Bean
    public DatabaseConfigBean dbUnitDatabaseConfig() {
        final DatabaseConfigBean bean = new DatabaseConfigBean();
        bean.setDatatypeFactory(new H2DataTypeFactory());
        bean.setEscapePattern("`");
        return bean;
    }

    @Bean
    public DatabaseDataSourceConnectionFactoryBean dbUnitDatabaseConnection(DataSource dataSource) {
        final DatabaseDataSourceConnectionFactoryBean bean = new DatabaseDataSourceConnectionFactoryBean(dataSource);
        bean.setDatabaseConfig(dbUnitDatabaseConfig());
        bean.setSchema("PUBLIC");
        return bean;
    }

[vict0r81]

Hey!

Thanks, @sylvietseng for your solution. I've combined it with @FingolfinTEK latest comment and now exception I got before has gone.

So, the final solution here based on such points: 1) Updated my SpringH2Config.java with 2 beans you have provided; 2) Changed my AbstractTestBase.java in this way: from

@DbUnitConfiguration(databaseConnection = "dataSource")

to

@DbUnitConfiguration

And bingo!

Thanks, again for your help.

P.S. all updated code in my repo if anyone need it ;) P.S.S. I believe issue can be closed.