Posts Tagged ‘DBunit’
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")); } }
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
.
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();
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!
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