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);
	}
}

//