CODEWITHSUNDEEP
springh2dbunit
Bobby Quninne
Bobby Quninne

Reputation: 21

Recreate h2 db with setup data via dbunit

Some context:

I am doing a POC with spring batch to replace out data extracts and reporting framework. We read data from an Oracle db and generate CSV reports from there. Now I have this working and unit tests connecting to an oracle db. However I would prefer to remove the external dependency of Oracle db to test with.

Problem:

When it comes time to adding data to the db dbunit is complaining that the table 'Account' does not exist.

Logging:

I can see the sql to drop and create the tables being fired:

06:54:58,940 DEBUG org.springframework.jdbc.core.JdbcTemplate:417 - Executing SQL statement [DROP TABLE ACCOUNT CASCADE CONSTRAINTS]
06:54:58,988 DEBUG org.springframework.jdbc.core.JdbcTemplate:417 - Executing SQL statement [CREATE TABLE ACCOUNT ( ID NUMBER(22) NOT NULL, CURRENT_BALANCE NUMBER(22) NOT NULL, RESERVED_AMOUNT NUMBER(22), ACCOUNT_TYPE VARCHAR2(50) NOT NULL, CURRENCY_CODE CHAR(3) NOT NULL, ACCOUNT_STATUS VARCHAR2(50) NOT NULL, PRODUCT_ID NUMBER(22) DEFAULT 0, CLIENT_CODE VARCHAR2(50) NOT NULL, LEDGER_ACCOUNT_TYPE VARCHAR2(50), CHANGED_BY VARCHAR2(50) NOT NULL, CHANGED_ON TIMESTAMP(6) NOT NULL, VERSION INTEGER DEFAULT 0, ACTIVATION_DATE TIMESTAMP(6), NEXT_BILLING_DATE TIMESTAMP(6), MSISDN VARCHAR2(50), IS_DEFAULT CHAR(1) DEFAULT '0', CONSTRAINT PK_ACCOUNT PRIMARY KEY (ID) )]

I have a SQL script to create the db schema and flat xml data for dbunit to populate. From the logging I can see the following happening:

Dbunit loads xml data:

06:54:59,886 DEBUG org.dbunit.dataset.xml.FlatXmlProducer:414 - startElement(uri=, localName=, qName=ACCOUNT, attributes=com.sun.org.apache.xerces.internal.parsers.AbstractSAXParser$AttributesProxy@1e2b807) - start
06:54:59,886 DEBUG org.dbunit.dataset.OrderedTableNameMap:149 - getLastTableName() - start
06:54:59,886 DEBUG org.dbunit.dataset.OrderedTableNameMap:127 - isLastTable(tableName=ACCOUNT) - start
06:54:59,886 DEBUG org.dbunit.dataset.OrderedTableNameMap:253 - getTableName(tableName=ACCOUNT) - start
06:54:59,886 DEBUG org.dbunit.dataset.OrderedTableNameMap:264 - getTableName(tableName=ACCOUNT) - end - result=ACCOUNT
06:54:59,887 DEBUG org.dbunit.dataset.xml.FlatXmlProducer:184 - createTableMetaData(tableName=ACCOUNT, attributes=com.sun.org.apache.xerces.internal.parsers.AbstractSAXParser$AttributesProxy@1e2b807) - start
06:54:59,912 DEBUG org.dbunit.dataset.Columns:71 - getColumns(columnNames=[], columns=[(ID, UNKNOWN, nullableUnknown), (CURRENT_BALANCE, UNKNOWN, nullableUnknown), (RESERVED_AMOUNT, UNKNOWN, nullableUnknown), (ACCOUNT_TYPE, UNKNOWN, nullableUnknown), (CURRENCY_CODE, UNKNOWN, nullableUnknown), (ACCOUNT_STATUS, UNKNOWN, nullableUnknown), (PRODUCT_ID, UNKNOWN, nullableUnknown), (CLIENT_CODE, UNKNOWN, nullableUnknown), (LEDGER_ACCOUNT_TYPE, UNKNOWN, nullableUnknown), (CHANGED_BY, UNKNOWN, nullableUnknown), (CHANGED_ON, UNKNOWN, nullableUnknown), (VERSION, UNKNOWN, nullableUnknown), (IS_DEFAULT, UNKNOWN, nullableUnknown)]) - start
06:54:59,912 DEBUG org.dbunit.dataset.OrderedTableNameMap:191 - add(tableName=ACCOUNT, object=tableName=ACCOUNT, columns=[(ID, UNKNOWN, nullableUnknown), (CURRENT_BALANCE, UNKNOWN, nullableUnknown), (RESERVED_AMOUNT, UNKNOWN, nullableUnknown), (ACCOUNT_TYPE, UNKNOWN, nullableUnknown), (CURRENCY_CODE, UNKNOWN, nullableUnknown), (ACCOUNT_STATUS, UNKNOWN, nullableUnknown), (PRODUCT_ID, UNKNOWN, nullableUnknown), (CLIENT_CODE, UNKNOWN, nullableUnknown), (LEDGER_ACCOUNT_TYPE, UNKNOWN, nullableUnknown), (CHANGED_BY, UNKNOWN, nullableUnknown), (CHANGED_ON, UNKNOWN, nullableUnknown), (VERSION, UNKNOWN, nullableUnknown), (IS_DEFAULT, UNKNOWN, nullableUnknown)], keys=[]) - start
06:54:59,913 DEBUG org.dbunit.dataset.OrderedTableNameMap:253 -getTableName(tableName=ACCOUNT) - start
06:54:59,913 DEBUG org.dbunit.dataset.OrderedTableNameMap:264 - getTableName(tableName=ACCOUNT) - end - result=ACCOUNT
06:54:59,913 DEBUG org.dbunit.dataset.OrderedTableNameMap:253 - getTableName(tableName=ACCOUNT) - start
06:54:59,913 DEBUG org.dbunit.dataset.OrderedTableNameMap:264 - getTableName(tableName=ACCOUNT) - end - result=ACCOUNT
06:54:59,913 DEBUG org.dbunit.dataset.CachedDataSet:135 - startTable(metaData=tableName=ACCOUNT, columns=[(ID, UNKNOWN, nullableUnknown), (CURRENT_BALANCE, UNKNOWN, nullableUnknown), (RESERVED_AMOUNT, UNKNOWN, nullableUnknown), (ACCOUNT_TYPE, UNKNOWN, nullableUnknown), (CURRENCY_CODE, UNKNOWN, nullableUnknown), (ACCOUNT_STATUS, UNKNOWN, nullableUnknown), (PRODUCT_ID, UNKNOWN, nullableUnknown), (CLIENT_CODE, UNKNOWN, nullableUnknown), (LEDGER_ACCOUNT_TYPE, UNKNOWN, nullableUnknown), (CHANGED_BY, UNKNOWN, nullableUnknown), (CHANGED_ON, UNKNOWN, nullableUnknown), (VERSION, UNKNOWN, nullableUnknown), (IS_DEFAULT, UNKNOWN, nullableUnknown)], keys=[]) - start
06:54:59,916 DEBUG org.dbunit.dataset.OrderedTableNameMap:149 - getLastTableName() - start
06:54:59,916 DEBUG org.dbunit.dataset.OrderedTableNameMap:253 - getTableName(tableName=ACCOUNT) - start
06:54:59,916 DEBUG org.dbunit.dataset.OrderedTableNameMap:264 - getTableName(tableName=ACCOUNT) - end - result=ACCOUNT
06:54:59,916 DEBUG org.dbunit.dataset.AbstractTableMetaData:101 - getColumnIndex(columnName=ID) - start
06:54:59,917 DEBUG org.dbunit.dataset.AbstractTableMetaData:101 - getColumnIndex(columnName=CURRENT_BALANCE) - start
06:54:59,917 DEBUG org.dbunit.dataset.AbstractTableMetaData:101 - getColumnIndex(columnName=RESERVED_AMOUNT) - start
06:54:59,933 DEBUG org.dbunit.dataset.AbstractTableMetaData:101 - getColumnIndex(columnName=ACCOUNT_TYPE) - start
06:54:59,933 DEBUG org.dbunit.dataset.AbstractTableMetaData:101 - getColumnIndex(columnName=CURRENCY_CODE) - start
06:54:59,933 DEBUG org.dbunit.dataset.AbstractTableMetaData:101 - getColumnIndex(columnName=ACCOUNT_STATUS) - start
06:54:59,934 DEBUG org.dbunit.dataset.AbstractTableMetaData:101 - getColumnIndex(columnName=PRODUCT_ID) - start
06:54:59,934 DEBUG org.dbunit.dataset.AbstractTableMetaData:101 - getColumnIndex(columnName=CLIENT_CODE) - start
06:54:59,934 DEBUG org.dbunit.dataset.AbstractTableMetaData:101 - getColumnIndex(columnName=LEDGER_ACCOUNT_TYPE) - start
06:54:59,934 DEBUG org.dbunit.dataset.AbstractTableMetaData:101 - getColumnIndex(columnName=CHANGED_BY) - start
06:54:59,934 DEBUG org.dbunit.dataset.AbstractTableMetaData:101 - getColumnIndex(columnName=CHANGED_ON) - start
06:54:59,935 DEBUG org.dbunit.dataset.AbstractTableMetaData:101 - getColumnIndex(columnName=VERSION) - start
06:54:59,935 DEBUG org.dbunit.dataset.AbstractTableMetaData:101 - getColumnIndex(columnName=IS_DEFAULT) - start
06:54:59,935 DEBUG org.dbunit.dataset.OrderedTableNameMap:149 - getLastTableName() - start
06:54:59,935 DEBUG org.dbunit.dataset.OrderedTableNameMap:253 - getTableName(tableName=ACCOUNT) - start
06:54:59,935 DEBUG org.dbunit.dataset.OrderedTableNameMap:264 - getTableName(tableName=ACCOUNT) - end - result=ACCOUNT

And finally:

06:55:00,037 DEBUG org.dbunit.database.DatabaseDataSet:279 - getTableMetaData(tableName=ACCOUNT) - start
06:55:00,037 DEBUG org.dbunit.database.DatabaseDataSet:173 - initialize() - start
06:55:00,037 DEBUG org.dbunit.database.DatabaseDataSet:182 - Initializing the data set from the database...
06:55:00,037 DEBUG org.dbunit.database.DatabaseDataSourceConnection:113 - getConnection() - start
06:55:00,090 DEBUG org.dbunit.database.DatabaseDataSet:203 - 
    database product name=H2
    database version=1.3.163 (2011-12-30)
    database major version=1
    database minor version=3
    jdbc driver name=H2 JDBC Driver
    jdbc driver version=1.3.163 (2011-12-30)
    jdbc driver major version=1
    jdbc driver minor version=3
06:55:00,090 DEBUG org.dbunit.database.DatabaseDataSet:204 - metadata resultset=org.apache.commons.dbcp.DelegatingResultSet@be071a
06:55:00,090 DEBUG org.dbunit.dataset.OrderedTableNameMap:253 - getTableName(tableName=ACCOUNT) - start
06:55:00,090 DEBUG org.dbunit.dataset.OrderedTableNameMap:264 - getTableName(tableName=ACCOUNT) - end - result=ACCOUNT
06:55:00,090 ERROR org.dbunit.database.DatabaseDataSet:286 - Table 'ACCOUNT' not found in tableMap=org.dbunit.dataset.OrderedTableNameMap[_tableNames=[], _tableMap={}, _caseSensitiveTableNames=false]

Code

AbstractReportTest.java is used to drop/create the db schema and then load data via dbunit.

import java.io.File;
import java.io.IOException;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.dbunit.DatabaseUnitException;
import org.dbunit.database.DatabaseConfig;
import org.dbunit.database.DatabaseDataSourceConnection;
import org.dbunit.database.IDatabaseConnection;
import org.dbunit.dataset.DataSetException;
import org.dbunit.dataset.xml.FlatXmlDataSet;
import org.dbunit.dataset.xml.FlatXmlDataSetBuilder;
import org.dbunit.operation.DatabaseOperation;
import org.junit.Assert;
import org.junit.Before;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.core.io.Resource;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.FileCopyUtils;

@Transactional
public abstract class AbstractReportTest {
    private static final Logger LOG = LoggerFactory.getLogger(AbstractReportTest.class);

    protected static final String NEW_LINE = System.getProperty("line.separator");
    private static final String DROP_SQL = "DROP";

    @Autowired
    private DataSource datasource;

    @Autowired
    @Qualifier(value="testData")
    private Resource testData;

    @Autowired
    @Qualifier(value="createScript")
    private Resource createScript;

    @Autowired
    private JdbcTemplate template;

    protected abstract String getSchema();

    @Before
    public void init() {
        IDatabaseConnection dbUnitCon = null;
        try {
            recreateDb();

            dbUnitCon = new DatabaseDataSourceConnection(datasource, getSchema());
            dbUnitCon.getConfig().setProperty(DatabaseConfig.FEATURE_QUALIFIED_TABLE_NAMES, true);

            FlatXmlDataSetBuilder dataBuilder = new FlatXmlDataSetBuilder();
            FlatXmlDataSet dataset = dataBuilder.build(testData.getFile());

            DatabaseOperation.INSERT.execute(dbUnitCon, dataset);
        } catch(IOException ioException) {
            LOG.error("", ioException);

            Assert.fail();
        } catch(DataSetException dse) {
            LOG.error("", dse);

            Assert.fail();
        } catch (DatabaseUnitException due) {
            LOG.error("", due);

            Assert.fail();
        } catch (SQLException sqlException) {
            LOG.error("", sqlException);

            Assert.fail();
        }
    }

    private void recreateDb() throws IOException {
        File sqlFile = createScript.getFile();
        LOG.info("Loading sql file: {}", sqlFile.getName());
        String sql = new String(FileCopyUtils.copyToByteArray(sqlFile));

        String[] queries = sql.split(";" + NEW_LINE);

        LOG.info("Preparing to run {} queries.", queries.length);
        for(String currentQuery : queries) {
            try {
                template.execute(currentQuery);
            } catch (DataAccessException dae) {
                if(currentQuery.indexOf(DROP_SQL) == -1) {
                    Assert.fail("Failed creating db tables.");
                }
            }
        }

        LOG.info("Completed db setup.");
    }
}

Spring configuration:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xmlns:util="http://www.springframework.org/schema/util"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xmlns:context="http://www.springframework.org/schema/context"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
                http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.0.xsd
                http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-3.0.xsd
                http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.0.xsd
                http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd">
    <util:properties id="jdbcProps" location="classpath:/jdbc.properties" />

    <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="datasource"/>
    </bean>

    <tx:annotation-driven transaction-manager="transactionManager"/>

    <bean id="datasource" class="org.apache.commons.dbcp.BasicDataSource">
        <property name="username" value="#{jdbcProps['brandgroup.jdbc.username']}"/>
        <property name="password" value="#{jdbcProps['brandgroup.jdbc.password']}"/>
        <property name="url" value="#{jdbcProps['brandgroup.jdbc.url']}"/>
        <property name="driverClassName" value="#{jdbcProps['brandgroup.jdbc.driver']}"/>
    </bean>

    <bean id="testTxnManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="datasource"/>
    </bean>

    <bean id="testTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
         <property name="dataSource" ref="datasource"/>
    </bean>

</beans>

The drop/create code is using the same datasource as the db unit portion, so I don't think its creating a new in mem h2 instance.

Any thoughts?

Upvotes: 2

Views: 3610

Answers (1)

Nilesh
Nilesh

Reputation: 4177

I came across this one as well. When dbunit tries to delete the table it doesn't find it and hence the issue. I fixed it by adding "IF EXISTS" to my delete DDL.

Hope that helps.

Upvotes: 1

Related Questions