How to test JDBI DAO's with H2-in-memory database?

Question

I'm using Dropwizard framework with JDBI and h2-in-memory for my test purposes. Also I've written my DAOs, and now I want to test them with unit tests. I came along the DBUnit which seem to fit my requirements.

But how to integrate it with JDBI and fill it with test data?

Answer 1

I implemented it like this:

I created a base dao class that sets up my DW environment to build a DBI instance for me. This looks like that:

@BeforeClass
    public static void setup() {
        env = new Environment( "test-env", Jackson.newObjectMapper(), null, new MetricRegistry(), null );
        dbi = new DBIFactory().build( env, getDataSourceFactory(), "test" );
        dbi.registerArgumentFactory(new JodaDateTimeArgumentFactory());
        dbi.registerMapper(new JodaDateTimeMapper(Optional.absent()));
    }

    static DataSourceFactory getDataSourceFactory()
    {
        DataSourceFactory dataSourceFactory = new DataSourceFactory();
        dataSourceFactory.setDriverClass( "org.h2.Driver" );
        dataSourceFactory.setUrl( "jdbc:h2:mem:testDb" );
        dataSourceFactory.setUser( "sa" );
        dataSourceFactory.setPassword( "" );
        return dataSourceFactory;
    }

    public static DBI getDbi() {
        return dbi;
    }

    public static Environment getEnvironment() {
        return env;
    }

Not this will create a Datasource for you pointing to your in-memory database.

No in the actual test you can use the DBI instance to create your DAOs before the test:

DaoA dao;
DaoB otherDao;

@Before
public void setupTests() throws IOException {
    super.setupTests();
    dao = dbi.onDemand(DaoA.class);
    otherDao = dbi.onDemand(DaoB.class);
}

With this your good to go and you can start testing. Hope that helps.

Artur

Edit for init:

My tests initialise themselves as well. For that I use dbi directly to execute sql scripts. For example, a test is associated with a test1.sql script that is a test classpath resource. In that case, all I need to do is read that script and run it before the test. For example like this:

            StringWriter writer = new StringWriter();
            InputStream resourceStream = this.getClass().getResourceAsStream("/sql/schema.sql");
            if(resourceStream == null ) {
                throw new FileNotFoundException("schema not found");
            }
            IOUtils.copy(resourceStream, writer);
            Handle handle = null;
            try {
                handle = dbi.open();
                handle.execute(writer.toString());
                handle.commit();
            } finally {
                handle.close();
                if(resourceStream != null) {
                    resourceStream.close();
                }
                writer.close();
            }