Search
Calendar
June 2017
S M T W T F S
« Sep    
 123
45678910
11121314151617
18192021222324
252627282930  
Your widget title
Archives

Posts Tagged ‘DBunit’

PostHeaderIcon How to export Oracle DB content to DBUnit XML flatfiles?

Case

From an Agile and TDD viewpoint, performing uni tests on DAO is a requirement. Sometimes, instead of using DBUnit datasets “out of the box”, the developper need test on actual data. In the same vein, when a bug appears on production, isolating and reproducing the issue is a smart way to investigate, and, along the way, fix it.
Therefore, how to export actual data from Oracle DB (or even MySQL, Sybase, DB2, etc.) to a DBUnit dataset as a flat XML file?

Here is a Runtime Test I wrote on this subject:

Fix

Spring

Edit the following Spring context file, setting the login, password, etc.

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd">

    <!-- don't forget to write this, otherwise the application will miss the driver class name, and therfore the test will fail-->
    <bean id="driverClassForName" class="org.springframework.beans.factory.config.MethodInvokingFactoryBean">
        <property name="targetClass" value="java.lang.Class"/>
        <property name="targetMethod" value="forName"/>
        <property name="arguments">
            <list>
                <value>oracle.jdbc.driver.OracleDriver</value>
            </list>
        </property>
    </bean>
    <bean id="connexion" class="org.springframework.beans.factory.config.MethodInvokingFactoryBean"
          depends-on="driverClassForName">
        <property name="targetClass" value="java.sql.DriverManager"/>
        <property name="targetMethod" value="getConnection"/>
        <property name="arguments">
            <list>
                <value>jdbc:oracle:thin:@host:1234:SCHEMA</value>
                <value>myLogin</value>
                <value>myPassword</value>
            </list>
        </property>
    </bean>

    <bean id="databaseConnection" class="org.dbunit.database.DatabaseConnection">
        <constructor-arg ref="connexion"/>
    </bean>
    <bean id="queryDataSet" class="org.dbunit.database.QueryDataSet">
        <constructor-arg ref="databaseConnection"/>
    </bean>
</beans>

The bean driverClassForName does not look to be used ; anyway, if Class.forName("oracle.jdbc.driver.OracleDriver") is not called, then the test will raise an exception.
To ensure driverClassForName is created before the bean connexion, I added a attribute depends-on="driverClassForName". The other beans will be created after connexion, since Spring will deduce the needed order of creation via the explicit dependency tree.

Java

public class Oracle2DBUnitExtractor extends TestCase {
    private QueryDataSet queryDataSet;

    @Before
    public void setUp() throws Exception {
        final ApplicationContext applicationContext;

        applicationContext = new ClassPathXmlApplicationContext(
                "lalou/jonathan/Oracle2DBUnitExtractor-applicationContext.xml");
        assertNotNull(applicationContext);

        queryDataSet = (QueryDataSet) applicationContext.getBean("queryDataSet");

    }

    @Test
    public void testExportTablesInFile() throws DataSetException, IOException {
    // add all the needed tables ; take care to write them in the right order, so that you don't happen to fall on dependencies issues, such as ones related to foreign keys
    
        queryDataSet.addTable("MYTABLE");
        queryDataSet.addTable("MYOTHERTABLE");
        queryDataSet.addTable("YETANOTHERTABLE");

        // Destination XML file into which data needs to be extracted
        FlatXmlDataSet.write(queryDataSet, new FileOutputStream("myProject/src/test/runtime/lalou/jonathan/output-dataset.xml"));

    }
}

PostHeaderIcon java.sql.SQLException: Wrong data type: NUMBER in statement [CREATE TABLE … (… NUMBER]

Case

In a JDBC DAO, I execute a query to retrieve an object. I get this error:

java.sql.SQLException: Wrong data type: NUMBER in statement [CREATE TABLE Jonathan_Table (TableColumn NUMBER]

Stacktrace

java.sql.SQLException: Wrong data type: NUMBER in statement [CREATE TABLE Jonathan_Table (TableColumn NUMBER]
 at org.hsqldb.jdbc.Util.throwError(Unknown Source)
 at org.hsqldb.jdbc.jdbcPreparedStatement.executeUpdate(Unknown Source)
 at com.bnpp.pb.risklayer.services.dao.jdbc.JdbcPrimeRiskServerDaoUnitTest.getDataSet(JdbcPrimeRiskServerDaoUnitTest.java:57)
 at org.dbunit.DatabaseTestCase.setUp(DatabaseTestCase.java:154)

Explanation and fix

My original DB is under Oracle, but my DBUnit tests works on HSQL. Yet, types NUMBER and VARCHAR2 are not available under HSQL, this is why the exception is raised.
To fixe the issue, rewrite your scripts, replacing NUMBER and VARCHAR2 with NUMERIC and VARCHAR.

PostHeaderIcon Table … not found in tableMap=org.dbunit.dataset.OrderedTableNameMap[_tableNames=[], _tableMap={}, _caseSensitiveTableNames=false]

Case

On a unit test with JDBC / DBUnit, extending org.dbunit.DBTestCase, I get this error:

Table 'Jonathan_Lalou_Table' not found in tableMap=org.dbunit.dataset.OrderedTableNameMap[_tableNames=[], _tableMap={}, _caseSensitiveTableNames=false]

Explanation and fix

Indeed, even when you provide a dataset through a flat XML file, DBUnit does not create the tables, but only fills them in. I know, this is paradoxal and most developpers would like to create implicitly the tables prior to filling them…

To fix the issue, add a block like this one, for instance when overriding the method getDataSet():

final PreparedStatement preparedStatement;
preparedStatement = getDatabaseTester().getConnection().getConnection().prepareStatement("CREATE TABLE Jonathan_Lalou_Table ... ");
preparedStatement.executeUpdate();

PostHeaderIcon DBUnit and Hibernate: Beware of Collisions!

Here is an interesting case I have encountered this morning.

Context

I must test a Hibernate implementation of a DAO, using DBUnit and its XML dataset files as a minimal database. A CRUD unit test fails, I get this trace:

WARN util.JDBCExceptionReporter – SQL Error: 0, SQLState: null
ERROR util.JDBCExceptionReporter – failed batch
ERROR def.AbstractFlushingEventListener – Could not synchronize database state with session
org.hibernate.exception.GenericJDBCException: Could not execute JDBC batch update

Explanation

In my dataset, I had set some primary keys at 0, 1, …, 10. Owing to my Hibernate mapping, the primary keys are generated thanks to a sequence.
On another hand, with the CRUD test, the primary keys 0, 1, …, 10 are also assigned to newly created objects.

Here is the issue: there are collisions between the XML dataset file objects and the objects created with Hibernate, because obviously two objects never share the same primary key!

PostHeaderIcon java.lang.NullPointerException at org.dbunit.dataset.DefaultTableIterator.next

Still another dumb error:

java.lang.NullPointerException at org.dbunit.dataset.DefaultTableIterator.next(DefaultTableIterator.java:59)  at org.dbunit.dataset.AbstractDataSet.getTable(AbstractDataSet.java:77)  at org.dbunit.dataset.AbstractDataSet.getTableMetaData(AbstractDataSet.java:70)  at org.dbunit.dataset.filter.SequenceTableFilter.getTableNames(SequenceTableFilter.java:94)  at org.dbunit.dataset.filter.SequenceTableFilter.iterator(SequenceTableFilter.java:110)  at org.dbunit.dataset.FilteredDataSet.createIterator(FilteredDataSet.java:74)  at org.dbunit.dataset.AbstractDataSet.iterator(AbstractDataSet.java:112)  at org.dbunit.operation.DeleteAllOperation.execute(DeleteAllOperation.java:81)

I’m laughing because I had only to check the DBUnit XML file did start with <dataset> tag… :-S