DBUnit基类和spring-test与DBUnit集成配置文件

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:aop="http://www.springframework.org/schema/aop"
  xmlns:context="http://www.springframework.org/schema/context"
  xmlns:util="http://www.springframework.org/schema/util"
  xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd  
        http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd  
        http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util.xsd  
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">

  <!-- *******************properties file******************* -->
  <!-- resource list -->
  <bean id="resourceList" class="java.util.ArrayList">
    <constructor-arg>
      <list>
        <value>classpath:application-test.properties</value>
        <!-- <value>classpath:application-test1.properties</value> <value>classpath:application-test2.properties</value> 
          <value>classpath:application-test3.properties</value> -->
      </list>
    </constructor-arg>
  </bean>
  <!-- support expression language -->
  <!-- <util:properties id="testProperties" location="classpath:bootstrap-test.properties" 
    /> -->
  <bean id="configProperties"
    class="org.springframework.beans.factory.config.PropertiesFactoryBean">
    <property name="locations" ref="resourceList">
    </property>
  </bean>
  <!-- support placeholder org.springframework.context.support.PropertySourcesPlaceholderConfigurer -->
  <!-- <context:property-placeholder location="classpath:applicationResources.properties" 
    /> -->
  <bean id="propertyPlaceholderConfigurer"
    class="org.springframework.context.support.PropertySourcesPlaceholderConfigurer">
    <property name="ignoreUnresolvablePlaceholders" value="true" />
    <property name="locations" ref="resourceList" />
    <!-- <property name="properties" ref="configProperties" /> -->
  </bean>


  <bean id="propertyPlaceholderConfigurer2"
    class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
    <property name="ignoreUnresolvablePlaceholders" value="true" />
    <property name="systemPropertiesModeName" value="SYSTEM_PROPERTIES_MODE_OVERRIDE" />
    <property name="locations" ref="resourceList" />
    <!-- <property name="properties" ref="configProperties" /> -->
  </bean>
  <!-- <context:property-placeholder location="classpath:application-test.properties" 
    /> -->
  <!--******************* bean annotation driven ******************* -->
  <context:annotation-config />
  <!-- <context:component-scan base-package="com.xxx.test" /> -->
  <!-- *******************import local src/main/resources/ ******************* -->
  <!-- <import resource="classpath:/config/xxx.beans.xml" /> -->

  <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
    destroy-method="close">
    <property name="driverClassName" value="${jdbc.driver}" />
    <property name="url" value="${jdbc.url_${env}}" />
    <property name="username" value="${jdbc.username}" />
    <property name="password" value="${jdbc.password}" />
    <property name="initialSize" value="10" />
    <property name="maxActive" value="30" />
    <property name="maxIdle" value="15" />
    <property name="minIdle" value="5" />
    <property name="maxWait" value="120000" />
    <property name="removeAbandoned" value="true" />
    <property name="removeAbandonedTimeout" value="60" />
    <property name="logAbandoned" value="true" />
    <property name="connectionProperties">
      <value>clientEncoding=utf-8</value>
    </property>
    <property name="testOnBorrow">
      <value>true</value>
    </property>
    <property name="testOnReturn">
      <value>true</value>
    </property>
    <property name="testWhileIdle">
      <value>true</value>
    </property>
    <property name="minEvictableIdleTimeMillis">
      <value>180000</value>
    </property>
    <property name="timeBetweenEvictionRunsMillis">
      <value>360000</value>
    </property>
    <property name="validationQuery">
      <value>SELECT 1 FROM SYS.DUAL</value>
    </property>
  </bean>
  <!-- DBUnit part -->
  <!-- DBUnit: replace the default DbUnit data type factory to get support 
    for custom data types(oracle) -->
  <bean id="oracle10DataTypeFactory" class="org.dbunit.ext.oracle.Oracle10DataTypeFactory"></bean>
  <!--DBUnit: ignore memory consumption issues -->
  <bean id="forwardOnlyResultSetTableFactory" class="org.dbunit.database.ForwardOnlyResultSetTableFactory"></bean>
  <bean id="dbUnitDatabaseConfig" class="com.github.springtestdbunit.bean.DatabaseConfigBean">
    <property name="skipOracleRecyclebinTables" value="true" />
    <property name="datatypeFactory" ref="oracle10DataTypeFactory" />
    <property name="resultsetTableFactory" ref="forwardOnlyResultSetTableFactory" />

  </bean>
  <bean id="dbUnitDatabaseConnection"
    class="com.github.springtestdbunit.bean.DatabaseDataSourceConnectionFactoryBean">
    <property name="databaseConfig" ref="dbUnitDatabaseConfig" />
    <property name="dataSource" ref="dataSource" />
    <!--必须指定需要访问的schema -->
    <property name="schema" value="XXX" />
  </bean>

  <!-- DB transaction -->
  <bean id="txManager"
    class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    <property name="dataSource" ref="dataSource" />
  </bean>
</beans>

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;
import java.util.TreeMap;
import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSource;
import org.dbunit.database.ForwardOnlyResultSetTable;
import org.dbunit.database.IDatabaseConnection;
import org.dbunit.database.QueryDataSet;
import org.dbunit.dataset.Column;
import org.dbunit.dataset.DataSetException;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.ITable;
import org.dbunit.dataset.ITableMetaData;
import org.dbunit.dataset.RowOutOfBoundsException;
import org.dbunit.dataset.xml.FlatXmlDataSet;
import org.dbunit.dataset.xml.XmlDataSet;
import org.dbunit.util.fileloader.FlatXmlDataFileLoader;
import org.dbunit.util.fileloader.FullXmlDataFileLoader;
import org.junit.After;
import org.junit.Before;
import org.junit.runner.RunWith;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.TestExecutionListeners;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.test.context.support.DependencyInjectionTestExecutionListener;
import org.springframework.test.context.support.DirtiesContextTestExecutionListener;
import org.springframework.transaction.annotation.Transactional;
import com.github.springtestdbunit.TransactionDbUnitTestExecutionListener;
import com.github.springtestdbunit.bean.DatabaseDataSourceConnectionFactoryBean;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = { "classpath:/applicationContext-dbUnit.xml" })
@Transactional
@TestExecutionListeners({ DependencyInjectionTestExecutionListener.class, DirtiesContextTestExecutionListener.class,
    TransactionDbUnitTestExecutionListener.class })
public abstract class DBUnitBase {
  private static final Logger logger = LoggerFactory.getLogger(DBUnitBase.class);
  @Autowired
  protected BasicDataSource dataSource;
  @Autowired
  protected DatabaseDataSourceConnectionFactoryBean dbUnitDatabaseConnection;
  private IDatabaseConnection conn;
  public static final String ROOT_URL = System.getProperty("user.dir") + "/src/test/resources/test/";

  public DataSource getDataSource() {
    return dataSource;
  }

  @Before
  public void before() throws Exception {
    conn = dbUnitDatabaseConnection.getObject();
    logger.info("Get connection, conn=" + conn);
  }

  @After
  public void after() throws Exception {
    if (conn != null) {
      conn.close();
      logger.info("Close connection, conn=" + conn);
      conn = null;
    }
  }

  final public IDatabaseConnection getDatabaseConnection() {
    return conn;
  }

  /**
   * @param tableNames
   * @return
   * @throws DataSetException
   * @throws SQLException
   */
  final public IDataSet queryAllDataSet() throws DataSetException, SQLException {
    return conn.createDataSet();
  }

  /**
   * create a file with "fileName" by the type of FlatXmlDataSet from all DB
   * tables
   * 
   * @param fileName
   * @throws DataSetException
   * @throws FileNotFoundException
   * @throws IOException
   * @throws SQLException
   */
  final public void createAllFlatXmlDataSetFile(String fileName)
      throws DataSetException, FileNotFoundException, IOException, SQLException {
    FlatXmlDataSet.write(queryAllDataSet(), new FileOutputStream(ROOT_URL + fileName));
  }

  /**
   * create a file with "fileName" by the type of XmlDataSet from all DB
   * tables
   * 
   * @param fileName
   * @throws DataSetException
   * @throws FileNotFoundException
   * @throws IOException
   * @throws SQLException
   */
  final public void createAllXmlDataSetFile(String fileName)
      throws DataSetException, FileNotFoundException, IOException, SQLException {
    XmlDataSet.write(queryAllDataSet(), new FileOutputStream(ROOT_URL + fileName));
  }

  /**
   * @param tableNames
   * @return
   * @throws DataSetException
   * @throws SQLException
   */
  final public IDataSet queryDataSet(String... tableNames) throws DataSetException, SQLException {
    return conn.createDataSet(tableNames);
  }

  /**
   * create a file with "fileName" by the type of FlatXmlDataSet from
   * different "tableNames"
   * 
   * @param fileName
   * @param tableNames
   * @throws DataSetException
   * @throws FileNotFoundException
   * @throws IOException
   * @throws SQLException
   */
  final public void createFlatXmlDataSetFile(String fileName, String... tableNames)
      throws DataSetException, FileNotFoundException, IOException, SQLException {
    FlatXmlDataSet.write(queryDataSet(tableNames), new FileOutputStream(ROOT_URL + fileName));
  }

  /**
   * create a file with "fileName" by the type of XmlDataSet from different
   * "tableNames"
   * 
   * @param fileName
   * @param tableNames
   * @throws DataSetException
   * @throws FileNotFoundException
   * @throws IOException
   * @throws SQLException
   */
  final public void createXmlDataSetFile(String fileName, String... tableNames)
      throws DataSetException, FileNotFoundException, IOException, SQLException {
    XmlDataSet.write(queryDataSet(tableNames), new FileOutputStream(ROOT_URL + fileName));
  }

  /**
   * @param tableName
   * @param sql
   * @return
   * @throws DataSetException
   * @throws FileNotFoundException
   * @throws IOException
   */
  final public QueryDataSet queryDataSet(String tableName, String sql)
      throws DataSetException, FileNotFoundException, IOException {
    QueryDataSet partialDataSet = new QueryDataSet(conn);
    partialDataSet.addTable(tableName, sql);
    return partialDataSet;
  }

  /**
   * 
   * @param tableName
   * @param sql
   * @return
   * @throws DataSetException
   * @throws SQLException
   */
  final public ITable queryTable(String tableName, String sql) throws DataSetException, SQLException {
    ITable table = conn.createQueryTable(tableName, sql);
    return table;
  }

  final public List<Map<String, Object>> queryTableList(String tableName, String sql)
      throws DataSetException, SQLException {
    ITable table = conn.createQueryTable(tableName, sql);
    return extractTable(table);
  }

  final public List<Map<String, Object>> extractTable(ITable table) throws DataSetException {
    List<Map<String, Object>> ret = new ArrayList<Map<String, Object>>();
    ITableMetaData tableMetaData = table.getTableMetaData();
    String tableName = tableMetaData.getTableName();
    if (table instanceof ForwardOnlyResultSetTable) {
      try {
        Column[] columns = tableMetaData.getColumns();
        for (int i = 0;; i++) {
          Map<String, Object> map = new TreeMap<>();
          for (Column column : columns) {
            String columnName = column.getColumnName().toUpperCase();
            Object columnValue = table.getValue(i, column.getColumnName());
            map.put(columnName, columnValue);
          }
          ret.add(map);
        }
      } catch (RowOutOfBoundsException e) {
        logger.debug("records are exhausted.");
      }
    } else {
      int count_table = table.getRowCount();
      if (count_table > 0) {
        Column[] columns = tableMetaData.getColumns();
        for (int i = 0; i < count_table; i++) {
          Map<String, Object> map = new TreeMap<>();
          for (Column column : columns) {
            String columnName = column.getColumnName().toUpperCase();
            Object columnValue = table.getValue(i, column.getColumnName());
            map.put(columnName, columnValue);
          }
          ret.add(map);
        }
      }
    }
    if (logger.isDebugEnabled()) {
      logger.debug(printResultList(tableName, ret));
    }
    return ret;
  }

  final public String printResultList(String tableName, List<Map<String, Object>> ret) {
    StringBuilder sb = new StringBuilder("\n");
    sb.append("[").append(tableName).append(" count:").append("(").append(ret.size()).append(")").append("]")
        .append("\n");
    for (Map<String, Object> map : ret) {
      for (Entry<String, Object> entry : map.entrySet()) {
        String columnName = entry.getKey();
        Object columnValue = entry.getValue();
        sb.append(columnName).append("=").append(columnValue).append(", ");
      }
      sb.append("\n");
    }
    return sb.toString();
  }

  /**
   * create a file with "fileName" by the type of FlatXmlDataSet from
   * tableName&&sql
   * 
   * @param fileName
   * @param tableName
   * @param sql
   * @throws DataSetException
   * @throws FileNotFoundException
   * @throws IOException
   * @throws SQLException
   */
  final public void createFlatXmlDataSetFile(String fileName, String tableName, String sql)
      throws DataSetException, FileNotFoundException, IOException, SQLException {
    FlatXmlDataSet.write(queryDataSet(tableName, sql), new FileOutputStream(ROOT_URL + fileName));
  }

  /**
   * create a file with "fileName" by the type of XmlDataSet from
   * tableName&&sql
   * 
   * @param fileName
   * @param tableName
   * @param sql
   * @throws DataSetException
   * @throws FileNotFoundException
   * @throws IOException
   * @throws SQLException
   */
  final public void createXmlDataSetFile(String fileName, String tableName, String sql)
      throws DataSetException, FileNotFoundException, IOException, SQLException {
    XmlDataSet.write(queryDataSet(tableName, sql), new FileOutputStream(ROOT_URL + fileName));
  }

  /**
   * @param tableName
   * @param sql
   * @return
   * @throws DataSetException
   * @throws FileNotFoundException
   * @throws IOException
   */
  final public QueryDataSet queryDataSet(Map<String, String> tableSqlMap)
      throws DataSetException, FileNotFoundException, IOException {
    QueryDataSet partialDataSet = new QueryDataSet(conn);
    Set<Entry<String, String>> entrySet = tableSqlMap.entrySet();
    for (Entry<String, String> entry : entrySet) {
      partialDataSet.addTable(entry.getKey(), entry.getValue());
    }
    return partialDataSet;
  }

  /**
   * create a file with "fileName" by the type of XmlDataSet from
   * tableName&&sql
   * 
   * @param fileName
   * @param tableSqlMap
   * @throws DataSetException
   * @throws FileNotFoundException
   * @throws IOException
   * @throws SQLException
   */
  final public void createFlatXmlDataSetFile(String fileName, Map<String, String> tableSqlMap)
      throws DataSetException, FileNotFoundException, IOException, SQLException {
    FlatXmlDataSet.write(queryDataSet(tableSqlMap), new FileOutputStream(ROOT_URL + fileName));
  }

  /**
   * create a file with "fileName" by the type of XmlDataSet from
   * tableSqlMap(key=tableName, value=sql)
   * 
   * @param fileName
   * @param tableSqlMap
   * @throws DataSetException
   * @throws FileNotFoundException
   * @throws IOException
   * @throws SQLException
   */
  final public void createXmlDataSetFile(String fileName, Map<String, String> tableSqlMap)
      throws DataSetException, FileNotFoundException, IOException, SQLException {
    XmlDataSet.write(queryDataSet(tableSqlMap), new FileOutputStream(ROOT_URL + fileName));
  }

  /**
   * fileName = "/the/package/prepData.xml"
   * 
   * @param fileName
   * @return
   */
  final public IDataSet loadFlatXmlDataSetFile(String fileName) {
    FlatXmlDataFileLoader loader = new FlatXmlDataFileLoader();
    return loader.load(fileName);
  }

  /**
   * fileName = "/the/package/prepData.xml"
   * 
   * @param fileName
   *            file location
   * @return
   */
  final public IDataSet loadXmlDataSetFile(String fileName) {
    FullXmlDataFileLoader loader = new FullXmlDataFileLoader();
    return loader.load(fileName);
  }
}

//