Tag Archives: dbunit

Dbunit with JUnit 4.x and Spring for testing Oracle DB Application

DBUnit is very nice for testing database content changes made by an application. You define in XML the data including the structure of your tables (dataset.xml).



	

Simple_Data is the name of the table and each column is a attribute in the xml doc with the content value e.g. id with value 1.

The Getting Started of DBUnit work with JUnit 3.8 and self handling of the JDBC Connection.

JUnit 4.x are more comfortable with annotations based test methods and Spring comes with dependency injection for separating
configuration from implementation code.

The following approach combines DBUnit with JUnit 4.4 and Spring 2.5.6 to test comfortable a Oracle 10g database.

I use Maven 2.x to define the depending libraries used by the example (pom.xml):



	4.0.0
	de.schaeftlein.dev.dbunit
	test-dbunit
	test-dbunit
	0.0.1-SNAPSHOT
	
	
		
			org.dbunit
			dbunit
			2.4.2
		
		
			org.springframework
			spring
			2.5.6
			jar
			compile
		
		
			junit
			junit
			4.4
		
		
			commons-dbcp
			commons-dbcp
			1.2.2
		
		
			org.springframework
			spring-test
			2.5.6
		
		
			org.slf4j
			slf4j-api
			1.5.6
		
		
			org.slf4j
			log4j-over-slf4j
			1.5.6
		
		
			log4j
			log4j
			1.2.14
		
		
			org.slf4j
			slf4j-log4j12
			1.5.6
		
		
			com.oracle
			ojdbc14
			10.2.0.2.0
		
	

Keep in mind that the Oracle JDBC Driver has to be downloaded manually.
The public maven repos include only the Pom definition for the oracle driver. Generate with maven command line tool the eclipse project files:

mvn clean eclipse:clean eclipse:eclipse

The JDBC datasource is defined via Spring (applicationContext.xml):

 


  
    
    
    
    
  
 

Additionally we define the expected data as well in XML for DBUnit (expectedDataSet.xml):



	

Now we can code our JUnit 4.x Test to

  1. load data before the test method
  2. change the data via JDBC to emulate a application
  3. compare the changed data with expected data
  4. clean up the database

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations={"classpath:applicationContext.xml"}) 
public class TestDBUnitWithSpring {

	@Autowired
	private DataSource dataSource;
	
	@Before
	public void init() throws Exception{
		// insert data into db
		DatabaseOperation.CLEAN_INSERT.execute(getConnection(), getDataSet());
	}
	
	@After
	public void after() throws Exception{
		// insert data into db
		DatabaseOperation.DELETE_ALL.execute(getConnection(), getDataSet());
	}
	
	private IDatabaseConnection getConnection() throws Exception{
	// get connection
		Connection con = dataSource.getConnection();
		DatabaseMetaData  databaseMetaData = con.getMetaData();
		// oracle schema name is the user name
		IDatabaseConnection connection = new DatabaseConnection(con,databaseMetaData.getUserName().toUpperCase());
		DatabaseConfig config = connection.getConfig();
		// oracle 10g 
		config.setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, new Oracle10DataTypeFactory());
		// receycle bin
		config.setFeature(DatabaseConfig.FEATURE_SKIP_ORACLE_RECYCLEBIN_TABLES, Boolean.TRUE);
		return connection;
	}
	
	private IDataSet getDataSet() throws Exception{
		// get insert data
		File file = new File("src/test/resources/dataset.xml");
		return new FlatXmlDataSet(file);
	}
	
	@Test
	public void testSQLUpdate() throws Exception{
		Connection con = dataSource.getConnection();
		Statement stmt = con.createStatement();
		// get current data
		ResultSet rst = stmt.executeQuery("select * from simple_data where id = 1");
		if(rst.next()){
			// from dataset.xml
			assertEquals("value_before", rst.getString("content"));
			rst.close();
			
			// update via sql
			int count = stmt.executeUpdate("update simple_data set content='value_after' where id=1");

			stmt.close();
			con.close();

			// expect only one row to be updated
			assertEquals("one row should be updated", 1, count);

			// Fetch database data after executing the code
			QueryDataSet databaseSet = new QueryDataSet(getConnection());
			// filter data
			databaseSet.addTable("simple_data", "select * from simple_data where id = 1");
			ITable actualTable = databaseSet.getTables()[0];

			// Load expected data from an XML dataset
			IDataSet expectedDataSet = new FlatXmlDataSet(new File("src/test/resources/expectedDataSet.xml"));
			ITable expectedTable = expectedDataSet.getTable("simple_data");

			// filter unnecessary columns of current data by xml definition
			actualTable = DefaultColumnFilter.includedColumnsTable(actualTable, expectedTable.getTableMetaData().getColumns());

			// Assert actual database table match expected table
			assertEquals(1,expectedTable.getRowCount());
			assertEquals(expectedTable.getRowCount(), actualTable.getRowCount());
			assertEquals(expectedTable.getValue(0, "content"), actualTable.getValue(0, "content"));
			
		} else {
			fail("no rows");
			rst.close();
			stmt.close();
			con.close();
		}

	}
}

dbunit and null values

For testing database based applications with more or less fixed schema dbunit is nice for creating test data. There are several ways of defining the data in xml and loading it into the db. With the FlatXmlDataSet it looks like

The first row defines the schema for dbunit with this format. When you have self referencing data with columns linked to the pk column it gets more complicated. Think of a growing company with locations all over the world. The hq will be the primary location and all other belong to their regional hq which belongs to the global hq. In the table is a column parent_location as fk to the id column. So the global hq has no parent and all other rows have parents. With the above sample data all rows has null as value in the parent column. DBUnit’s FlatXmlDataSet ignores all other column data in subsequential rows if they are not mentioned in the first row of a table. To avoid this behavior you must provide a dtd inline or external to define the columns:




]>

  
  
  

Now the first row has a null parent value and all others the value defined in the XML file.

jdbc DataBaseMetaData + oracle 10g + dbunit + username..

If you are using DBUnit as tool for set up test environment in your oracle 10g then you have to provide the username of course. Normally it isn’t case sensitive in most tools. The jdbc driver or sqlplus let you create connection with lower case. DBUnit ask the DataBaseMetaData to get the defined tables in the schema. Schema and username are the same in the oracle world. So in DBUnit the source is

DatabaseMetaData databaseMetaData = jdbcConnection.getMetaData();
ResultSet resultSet = databaseMetaData.getTables(null, schema, "%", tableType);

where tableType is the String[] {“table”}. with schema == username lower case i get in my oracle 10.2 db nothing back. With upper case username et voila! Don’t know if this is really an standard behaviour….