Search
Calendar
June 2017
S M T W T F S
« Sep    
 123
45678910
11121314151617
18192021222324
252627282930  
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"));

    }
}