Search
Calendar
March 2024
S M T W T F S
« Sep    
 12
3456789
10111213141516
17181920212223
24252627282930
31  
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"));

    }
}