Search
Calendar
October 2017
S M T W T F S
« Sep    
1234567
891011121314
15161718192021
22232425262728
293031  
Your widget title
Archives

Posts Tagged ‘HQL’

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"));

    }
}