Announcement Announcement Module
Collapse
No announcement yet.
Help required: A transactional DBUnit Test Case Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Help required: A transactional DBUnit Test Case

    Hello,

    I am trying to extend Spring's AbstractTransactionalSpringContextTests class with a TestCase that integrates with DBUnit. The idea is to load dbunit test data within a transaction, execute some tests, and roll back the transaction. I am having trouble integrating the DBunit insertion into a transaction. For now data inserted by dbunit is comitted to the database. Is there any way we can integrate this into a transaction that is rolled back on tear down?

    Code:
    package org.mycompany.test;
    
    import java.io.IOException;
    import java.sql.Connection;
    import java.sql.DatabaseMetaData;
    
    import javax.sql.DataSource;
    
    import org.dbunit.database.IDatabaseConnection;
    import org.dbunit.dataset.DataSetException;
    import org.dbunit.dataset.IDataSet;
    import org.dbunit.dataset.xml.XmlDataSet;
    import org.dbunit.ext.oracle.OracleConnection;
    import org.dbunit.operation.DatabaseOperation;
    import org.springframework.core.io.ClassPathResource;
    import org.springframework.test.AbstractTransactionalSpringContextTests;
    
    /**
     * @author Thomas Van de Velde
     * 
     * Business objet tests that use database data should extend this class to load
     * data in the database. 
     */
    public abstract class DatabaseTestCase extends
    		AbstractTransactionalSpringContextTests {
    
    	private static final String[] CTX_DS = { "boContext-ds.xml" };
    
    	private static final String BEAN_TEST_LOCAL_DS = "dataSource";
    
    	/*
    	 * (non-Javadoc)
    	 * 
    	 * @see junit.framework.TestCase#setUp()
    	 */
    	protected void onSetUpInTransaction() throws Exception {
    		setUpDbUnit();
    	}
    
    	/**
    	 * Populates a dataset from classpath resources.
    	 * 
    	 * @return
    	 */
    	private IDataSet[] getDataSet(String[] data) {
    		IDataSet[] dataSet = new IDataSet[data.length];
    		for &#40;int i = 0; i < dataSet.length; i++&#41; &#123;
    			ClassPathResource xmlRes = new ClassPathResource&#40;data&#91;i&#93;&#41;;
    
    			IDataSet iData;
    			try &#123;
    				iData = new XmlDataSet&#40;xmlRes.getInputStream&#40;&#41;&#41;;
    				dataSet&#91;i&#93; = iData;
    			&#125; catch &#40;DataSetException e&#41; &#123;
    				logger.error&#40;"Failed to create a DBUnit dataset.", e&#41;;
    			&#125; catch &#40;IOException e&#41; &#123;
    				logger.error&#40;"Failed to load dataset from classpath.", e&#41;;
    			&#125;
    		&#125;
    		return dataSet;
    	&#125;
    
    	/**
    	 * Loads dbunit test files .
    	 * 
    	 * @throws Exception
    	 */
    	private void setUpDbUnit&#40;&#41; throws Exception &#123;
    		
    		if &#40;getDataLocations&#40;&#41; != null&#41; &#123;
    			logger.info&#40;"Loading test data into database"&#41;;
    			Connection jdbcConnection = &#40;&#40;DataSource&#41; applicationContext
    					.getBean&#40;BEAN_TEST_LOCAL_DS&#41;&#41;.getConnection&#40;&#41;;
    			
    			DatabaseMetaData metaData = jdbcConnection.getMetaData&#40;&#41;;
    			String instance = metaData.getUserName&#40;&#41;;
    			IDatabaseConnection connection = new OracleConnection&#40;
    					jdbcConnection, instance&#41;;
    
    
    			for &#40;int i = 0; i < getDataSet&#40;getDataLocations&#40;&#41;&#41;.length; i++&#41; &#123;
    				logger.info&#40;"Loading " + getDataSet&#40;getDataLocations&#40;&#41;&#41;&#91;i&#93;&#41;;
    				DatabaseOperation.CLEAN_INSERT.execute&#40;connection,
    						getDataSet&#40;getDataLocations&#40;&#41;&#41;&#91;i&#93;&#41;;
    			&#125;
    
    		&#125;
    	&#125;
    
    	/*
    	 * &#40;non-Javadoc&#41;
    	 * 
    	 * @see org.springframework.test.AbstractDependencyInjectionSpringContextTests#getConfigLocations&#40;&#41;
    	 */
    	protected String&#91;&#93; getConfigLocations&#40;&#41; &#123;
    		if &#40;getContextConfigLocations&#40;&#41; != null&#41; &#123;
    			String&#91;&#93; contextConfigs = getContextConfigLocations&#40;&#41;;
    			String&#91;&#93; configs = new String&#91;CTX_DS.length + contextConfigs.length&#93;;
    			for &#40;int i = 0; i < CTX_DS.length; i++&#41; &#123;
    				configs&#91;i&#93; = CTX_DS&#91;i&#93;;
    			&#125;
    			for &#40;int i = 0; i < contextConfigs.length; i++&#41; &#123;
    				configs&#91;CTX_DS.length + i&#93; = contextConfigs&#91;0&#93;;
    			&#125;
    			return configs;
    		&#125;
    		return CTX_DS;
    	&#125;
    
    	/**
    	 * Subclasses must implement this method to return the classpath locations
    	 * of their data files. E.g.
    	 * classpath&#58;com/mycompany/test/data.xml
    	 * 
    	 * @return an array of data file locations
    	 */
    	protected abstract String&#91;&#93; getDataLocations&#40;&#41;;
    
    	protected abstract String&#91;&#93; getContextConfigLocations&#40;&#41;;
    
    &#125;

  • #2
    I am not sure if this would help as I did not, yet, look at DBUnit Source code. It is a good practice to use org.springframework.jdbc.datasource.DataSourceUtil s to get connections. This insure the same connection is used by DBUnit and DAOs:
    Code:
      DataSource ds = &#40;DataSource&#41; applicationContext.getBean&#40;BEAN_TEST_LOCAL_DS&#41;;
      Connection jdbcConnection = DataSourceUtils.getConnection&#40;ds&#41;;
    HTH

    Comment


    • #3
      I have developed a solution for this a while ago (but gave up on DBUnit as it was too slow for unit tests I just have the db in a known state). But it should still work...

      Code:
              DataSource ds = &#40;DataSource&#41; ctx.getBean&#40;BEAN_TEST_LOCAL_DS&#41;;
              DatabaseDataSourceConnection jdbcConnection = new SpringDatabaseDataSourceConnection&#40;ds&#41;;
              jdbcConnection.getConfig&#40;&#41;.setProperty&#40;DatabaseConfig.PROPERTY_DATATYPE_FACTORY, new OracleDataTypeFactory&#40;&#41;&#41;;
      Code:
      package com.xyz.dbunit;
      
      import java.sql.Connection;
      import java.sql.SQLException;
      
      import javax.sql.DataSource;
      
      import org.dbunit.database.DatabaseDataSourceConnection;
      import org.springframework.jdbc.datasource.DataSourceUtils;
      
      /**
       * Wrapped version of DBUnits DatabaseDataSourceConnection to enable Spring Transaction support. 
       */
      public class SpringDatabaseDataSourceConnection extends DatabaseDataSourceConnection &#123;
      
          private DataSource dataSource;
      
          /**
           * @param dataSource
           * @throws SQLException
           */
          public SpringDatabaseDataSourceConnection&#40;DataSource dataSource&#41; throws SQLException &#123;
              super&#40;dataSource&#41;;
              this.dataSource = dataSource;
          &#125;
      
          /**
           * @see org.dbunit.database.IDatabaseConnection#getConnection&#40;&#41;
           */
          public Connection getConnection&#40;&#41; throws SQLException &#123;
              Connection conn = DataSourceUtils.getConnection&#40;dataSource&#41;;
              return new SpringConnection&#40;dataSource, conn&#41;;
          &#125;
      &#125;

      Code:
      package com.xyz.dbunit;
      
      import java.sql.CallableStatement;
      import java.sql.Connection;
      import java.sql.DatabaseMetaData;
      import java.sql.PreparedStatement;
      import java.sql.SQLException;
      import java.sql.SQLWarning;
      import java.sql.Savepoint;
      import java.sql.Statement;
      import java.util.Map;
      
      import javax.sql.DataSource;
      
      import org.springframework.jdbc.datasource.DataSourceUtils;
      
      /**
       * Wrapped Connection enable Spring Transaction support. 
       */
      public class SpringConnection implements Connection &#123;
      
          private DataSource dataSource;
          private Connection conn;
          
          /**
           * @param dataSource
           * @param conn 
           * @throws SQLException
           */
          public SpringConnection&#40;DataSource dataSource, Connection conn&#41; &#123;
              this.dataSource = dataSource;
              this.conn = conn;
          &#125;
      
          /**
           * calls DataSourceUtils.closeConnectionIfNecessary rather than directly closing the connection
           * @throws java.sql.SQLException
           */
          public void close&#40;&#41; throws SQLException &#123;
              DataSourceUtils.closeConnectionIfNecessary&#40;conn, dataSource&#41;;
          &#125;
      
          /**
           * @throws java.sql.SQLException
           */
          public void clearWarnings&#40;&#41; throws SQLException &#123;
              conn.clearWarnings&#40;&#41;;
          &#125;
      
          /**
           * @throws java.sql.SQLException
           */
          public void commit&#40;&#41; throws SQLException &#123;
              //conn.commit&#40;&#41;;
          &#125;
      
          /**
           * @return
           * @throws java.sql.SQLException
           */
          public Statement createStatement&#40;&#41; throws SQLException &#123;
              return conn.createStatement&#40;&#41;;
          &#125;
      
          /**
           * @param resultSetType
           * @param resultSetConcurrency
           * @return
           * @throws java.sql.SQLException
           */
          public Statement createStatement&#40;int resultSetType, int resultSetConcurrency&#41; throws SQLException &#123;
              return conn.createStatement&#40;resultSetType, resultSetConcurrency&#41;;
          &#125;
      
          /**
           * @param resultSetType
           * @param resultSetConcurrency
           * @param resultSetHoldability
           * @return
           * @throws java.sql.SQLException
           */
          public Statement createStatement&#40;int resultSetType, int resultSetConcurrency, int resultSetHoldability&#41;
              throws SQLException &#123;
              return conn.createStatement&#40;resultSetType, resultSetConcurrency, resultSetHoldability&#41;;
          &#125;
      
          /**
           * @see java.lang.Object#equals&#40;java.lang.Object&#41;
           */
          public boolean equals&#40;Object obj&#41; &#123;
              return conn.equals&#40;obj&#41;;
          &#125;
      
          /**
           * @return
           * @throws java.sql.SQLException
           */
          public boolean getAutoCommit&#40;&#41; throws SQLException &#123;
              return conn.getAutoCommit&#40;&#41;;
          &#125;
      
          /**
           * @return
           * @throws java.sql.SQLException
           */
          public String getCatalog&#40;&#41; throws SQLException &#123;
              return conn.getCatalog&#40;&#41;;
          &#125;
      
          /**
           * @return
           * @throws java.sql.SQLException
           */
          public int getHoldability&#40;&#41; throws SQLException &#123;
              return conn.getHoldability&#40;&#41;;
          &#125;
      
          /**
           * @return
           * @throws java.sql.SQLException
           */
          public DatabaseMetaData getMetaData&#40;&#41; throws SQLException &#123;
              return conn.getMetaData&#40;&#41;;
          &#125;
      
          /**
           * @return
           * @throws java.sql.SQLException
           */
          public int getTransactionIsolation&#40;&#41; throws SQLException &#123;
              return conn.getTransactionIsolation&#40;&#41;;
          &#125;
      
          /**
           * @return
           * @throws java.sql.SQLException
           */
          public Map getTypeMap&#40;&#41; throws SQLException &#123;
              return conn.getTypeMap&#40;&#41;;
          &#125;
      
          /**
           * @return
           * @throws java.sql.SQLException
           */
          public SQLWarning getWarnings&#40;&#41; throws SQLException &#123;
              return conn.getWarnings&#40;&#41;;
          &#125;
      
          /**
           * @see java.lang.Object#hashCode&#40;&#41;
           */
          public int hashCode&#40;&#41; &#123;
              return conn.hashCode&#40;&#41;;
          &#125;
      
          /**
           * @return
           * @throws java.sql.SQLException
           */
          public boolean isClosed&#40;&#41; throws SQLException &#123;
              return conn.isClosed&#40;&#41;;
          &#125;
      
          /**
           * @return
           * @throws java.sql.SQLException
           */
          public boolean isReadOnly&#40;&#41; throws SQLException &#123;
              return conn.isReadOnly&#40;&#41;;
          &#125;
      
          /**
           * @param sql
           * @return
           * @throws java.sql.SQLException
           */
          public String nativeSQL&#40;String sql&#41; throws SQLException &#123;
              return conn.nativeSQL&#40;sql&#41;;
          &#125;
      
          /**
           * @param sql
           * @return
           * @throws java.sql.SQLException
           */
          public CallableStatement prepareCall&#40;String sql&#41; throws SQLException &#123;
              return conn.prepareCall&#40;sql&#41;;
          &#125;
      
          /**
           * @param sql
           * @param resultSetType
           * @param resultSetConcurrency
           * @return
           * @throws java.sql.SQLException
           */
          public CallableStatement prepareCall&#40;String sql, int resultSetType, int resultSetConcurrency&#41; throws SQLException &#123;
              return conn.prepareCall&#40;sql, resultSetType, resultSetConcurrency&#41;;
          &#125;
      
          /**
           * @param sql
           * @param resultSetType
           * @param resultSetConcurrency
           * @param resultSetHoldability
           * @return
           * @throws java.sql.SQLException
           */
          public CallableStatement prepareCall&#40;
              String sql,
              int resultSetType,
              int resultSetConcurrency,
              int resultSetHoldability&#41;
              throws SQLException &#123;
              return conn.prepareCall&#40;sql, resultSetType, resultSetConcurrency, resultSetHoldability&#41;;
          &#125;
      
          /**
           * @param sql
           * @return
           * @throws java.sql.SQLException
           */
          public PreparedStatement prepareStatement&#40;String sql&#41; throws SQLException &#123;
              return conn.prepareStatement&#40;sql&#41;;
          &#125;
      
          /**
           * @param sql
           * @param autoGeneratedKeys
           * @return
           * @throws java.sql.SQLException
           */
          public PreparedStatement prepareStatement&#40;String sql, int autoGeneratedKeys&#41; throws SQLException &#123;
              return conn.prepareStatement&#40;sql, autoGeneratedKeys&#41;;
          &#125;
      
          /**
           * @param sql
           * @param resultSetType
           * @param resultSetConcurrency
           * @return
           * @throws java.sql.SQLException
           */
          public PreparedStatement prepareStatement&#40;String sql, int resultSetType, int resultSetConcurrency&#41;
              throws SQLException &#123;
              return conn.prepareStatement&#40;sql, resultSetType, resultSetConcurrency&#41;;
          &#125;
      
          /**
           * @param sql
           * @param resultSetType
           * @param resultSetConcurrency
           * @param resultSetHoldability
           * @return
           * @throws java.sql.SQLException
           */
          public PreparedStatement prepareStatement&#40;
              String sql,
              int resultSetType,
              int resultSetConcurrency,
              int resultSetHoldability&#41;
              throws SQLException &#123;
              return conn.prepareStatement&#40;sql, resultSetType, resultSetConcurrency, resultSetHoldability&#41;;
          &#125;
      
          /**
           * @param sql
           * @param columnIndexes
           * @return
           * @throws java.sql.SQLException
           */
          public PreparedStatement prepareStatement&#40;String sql, int&#91;&#93; columnIndexes&#41; throws SQLException &#123;
              return conn.prepareStatement&#40;sql, columnIndexes&#41;;
          &#125;
      
          /**
           * @param sql
           * @param columnNames
           * @return
           * @throws java.sql.SQLException
           */
          public PreparedStatement prepareStatement&#40;String sql, String&#91;&#93; columnNames&#41; throws SQLException &#123;
              return conn.prepareStatement&#40;sql, columnNames&#41;;
          &#125;
      
          /**
           * @param savepoint
           * @throws java.sql.SQLException
           */
          public void releaseSavepoint&#40;Savepoint savepoint&#41; throws SQLException &#123;
              conn.releaseSavepoint&#40;savepoint&#41;;
          &#125;
      
          /**
           * @throws java.sql.SQLException
           */
          public void rollback&#40;&#41; throws SQLException &#123;
              conn.rollback&#40;&#41;;
          &#125;
      
          /**
           * @param savepoint
           * @throws java.sql.SQLException
           */
          public void rollback&#40;Savepoint savepoint&#41; throws SQLException &#123;
              //conn.rollback&#40;savepoint&#41;;
          &#125;
      
          /**
           * @param autoCommit
           * @throws java.sql.SQLException
           */
          public void setAutoCommit&#40;boolean autoCommit&#41; throws SQLException &#123;
              conn.setAutoCommit&#40;autoCommit&#41;;
          &#125;
      
          /**
           * @param catalog
           * @throws java.sql.SQLException
           */
          public void setCatalog&#40;String catalog&#41; throws SQLException &#123;
              conn.setCatalog&#40;catalog&#41;;
          &#125;
      
          /**
           * @param holdability
           * @throws java.sql.SQLException
           */
          public void setHoldability&#40;int holdability&#41; throws SQLException &#123;
              conn.setHoldability&#40;holdability&#41;;
          &#125;
      
          /**
           * @param readOnly
           * @throws java.sql.SQLException
           */
          public void setReadOnly&#40;boolean readOnly&#41; throws SQLException &#123;
              conn.setReadOnly&#40;readOnly&#41;;
          &#125;
      
          /**
           * @return
           * @throws java.sql.SQLException
           */
          public Savepoint setSavepoint&#40;&#41; throws SQLException &#123;
              return conn.setSavepoint&#40;&#41;;
          &#125;
      
          /**
           * @param name
           * @return
           * @throws java.sql.SQLException
           */
          public Savepoint setSavepoint&#40;String name&#41; throws SQLException &#123;
              return conn.setSavepoint&#40;name&#41;;
          &#125;
      
          /**
           * @param level
           * @throws java.sql.SQLException
           */
          public void setTransactionIsolation&#40;int level&#41; throws SQLException &#123;
              conn.setTransactionIsolation&#40;level&#41;;
          &#125;
      
          /**
           * @param map
           * @throws java.sql.SQLException
           */
          public void setTypeMap&#40;Map map&#41; throws SQLException &#123;
              conn.setTypeMap&#40;map&#41;;
          &#125;
      
          /**
           * @see java.lang.Object#toString&#40;&#41;
           */
          public String toString&#40;&#41; &#123;
              return conn.toString&#40;&#41;;
          &#125;
      
      &#125;
      Regards,
      Gordon.

      p.s. is it possible to add attachments to posts
      :?:

      Comment


      • #4
        This works perfectly!

        There are a few things we could improve:
        1) DBunit throws an Ambiguous table exception if you don't define your database schema when working with Oracle. Is there a way to restrict access to a single schema through configuration of the DataSource definition?
        2) We should be able to remove this testcase's dependenccy on Oracle at least by defining the OracleDataTypeFactory() elsewhere. I was thinking of a bean definition where you'd define one of the org.dbunit.ext.db2, org.dbunit.ext.mssql, org.dbunit.ext.mysql, org.dbunit.ext.oracle factories.

        Comment


        • #5
          good stuff!

          I never encountered am ambigious table exception, may be its related to yet another bug in the oracle jdbc driver - I'm using clases12.zip version 9.0.1.

          You could configure the DatabaseDataSourceConnection could be removed to the spring config -

          Add the following method to DatabaseDataSourceConnection

          Code:
              public void setDataTypeFactory&#40;IDataTypeFactory value&#41; &#123;
                  getConfig&#40;&#41;.setProperty&#40;DatabaseConfig.PROPERTY_DATATYPE_FACTORY, value&#41;;
              &#125;
          and


          Code:
          	<bean id="DatabaseDataSourceConnection" class="com.xyz.dbunit.SpringDatabaseDataSourceConnection">
          		<constructor-arg><ref bean="testDataSource"/></constructor-arg>
          		<property name="dataTypeFactory">
          			<bean class="org.dbunit.ext.oracle.OracleDataTypeFactory" />
          		</property>
          	</bean>
          I haven't tested it but it should work

          Comment


          • #6
            I am working on something along those lines and it looks promising. I'll post later on today.

            Comment


            • #7
              A Month later.....

              Just wanted to say thanks for the code post. I was having a similar problem and that fixed it!

              Thanks,

              Curtney

              Comment


              • #8
                Has this been considered for inclusion into the springframework. It works nicely, and I suspect that it is a common issue.

                Comment

                Working...
                X