<dependency>
    <groupId>org.unitils</groupId>
    <artifactId>unitils-core</artifactId>
    <version>3.4.2</version>
  </dependency>
  <dependency>
    <groupId>org.unitils</groupId>
    <artifactId>unitils-dbunit</artifactId>
    <version>3.4.2</version>
  </dependency>
  <dependency>
    <groupId>org.unitils</groupId>
    <artifactId>unitils-io</artifactId>
    <version>3.4.2</version>
  </dependency>
  <dependency>
    <groupId>org.unitils</groupId>
    <artifactId>unitils-database</artifactId>
    <version>3.4.2</version>
  </dependency>
  <dependency>
    <groupId>org.unitils</groupId>
    <artifactId>unitils-spring</artifactId>
    <version>3.4.2</version>
  </dependency>
  <dependency>
    <groupId>org.dbunit</groupId>
    <artifactId>dbunit</artifactId>
    <version>2.5.3</version>
  </dependency>

<resources>
    <resource>
      <directory>src/test/resources</directory>
      <includes>
        <include>**/*.*</include>
      </includes>
      <filtering>false</filtering>
    </resource>
  </resources>

#啟用unitils所需模塊
unitils.modules=database,dbunit

#自定義擴(kuò)展模塊，加載Excel文件，默認(rèn)拓展模塊org.unitils.dbunit.DbUnitModule支持xml
unitils.module.dbunit.className=org.agoura.myunit.module.MyDbUnitModule

#配置數(shù)據(jù)庫(kù)連接
database.driverClassName=com.mysql.jdbc.Driver
database.url=jdbc:mysql://127.0.0.1:3306/teams?autoReconnect=true&useUnicode=true&characterEncoding=utf-8
database.userName=root
database.password=agoura
#配置為數(shù)據(jù)庫(kù)名稱
database.schemaNames=teams
#配置數(shù)據(jù)庫(kù)方言
database.dialect=mysql

#需設(shè)置false，否則我們的測(cè)試函數(shù)只有在執(zhí)行完函數(shù)體后，才將數(shù)據(jù)插入的數(shù)據(jù)表中
unitils.module.database.runAfter=false

#配置數(shù)據(jù)庫(kù)維護(hù)策略.請(qǐng)注意下面這段描述
# If set to true, the DBMaintainer will be used to update the unit test database schema. This is done once for each
# test run, when creating the DataSource that provides access to the unit test database.
updateDataBaseSchema.enabled=true

#配置數(shù)據(jù)庫(kù)表創(chuàng)建策略，是否自動(dòng)建表以及建表sql腳本存放目錄
dbMaintainer.autoCreateExecutedScriptsTable=true
dbMaintainer.keepRetryingAfterError.enabled=true
dbMaintainer.script.locations=src/main/resources/dbscripts
#dbMaintainer.script.fileExtensions=sql

#數(shù)據(jù)集加載策略
#CleanInsertLoadStrategy:先刪除dateSet中有關(guān)表的數(shù)據(jù)，然后再插入數(shù)據(jù)
#InsertLoadStrategy：只插入數(shù)據(jù)
#RefreshLoadStrategy:有同樣key的數(shù)據(jù)更新，沒有的插入
#UpdateLoadStrategy:有同樣key的數(shù)據(jù)更新，沒有的不做任何操作
DbUnitModule.DataSet.loadStrategy.default=org.unitils.dbunit.datasetloadstrategy.impl.CleanInsertLoadStrategy

#配置數(shù)據(jù)集工廠，自定義
DbUnitModule.DataSet.factory.default=org.agoura.myunit.utils.MultiSchemaXlsDataSetFactory
DbUnitModule.ExpectedDataSet.factory.default=org.agoura.myunit.utils.MultiSchemaXlsDataSetFactory

#配置事務(wù)策略 commit、rollback 和disabled；或者在代碼的方法上標(biāo)記@Transactional(value=TransactionMode.ROLLBACK)
#commit 是單元測(cè)試方法過(guò)后提交事務(wù)
#rollback 是回滾事務(wù)
#disabled 是沒有事務(wù)，默認(rèn)情況下，事務(wù)管理是disabled
DatabaseModule.Transactional.value.default=commit

#配置數(shù)據(jù)集結(jié)構(gòu)模式XSD生成路徑，可以自定義目錄，但不能為空
dataSetStructureGenerator.xsd.dirName=src/main/resources/xsd
dbMaintainer.generateDataSetStructure.enabled=true

#文件相對(duì)路徑是否是測(cè)試類文件路徑，false表示resource根目錄
dbUnit.datasetresolver.prefixWithPackageName=false

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

  <context:component-scan base-package="com.agoura.agoura"/>

  <context:property-placeholder location="classpath:jdbc_dbcp.properties"/>
  <!--<util:properties id="jdbc_dbcp" />-->

  <bean id="dataSource" class="org.unitils.database.UnitilsDataSourceFactoryBean"/>

  <!-- spring和MyBatis整合，不需要mybatis的配置映射文件 -->
  <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
    <property name="dataSource" ref="dataSource"/>
    <!-- 自動(dòng)掃描mapping.xml文件 -->
    <property name="mapperLocations" value="classpath*:com/agoura/agoura/mapper/xml/*.xml"></property>
  </bean>

  <!-- DAO接口所在包名，Spring會(huì)自動(dòng)查找其下的類 -->
  <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
    <property name="basePackage" value="com.agoura.agoura.mapper"/>
    <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"></property>
  </bean>

  <!-- (事務(wù)管理)transaction manager, use JtaTransactionManager for global tx -->
  <bean id="transactionManager"
     class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    <property name="dataSource" ref="dataSource"/>
  </bean>
</beans>

Excel文件 --> @DataSet --> DbUnitModule --> DataSetFactory --> 數(shù)據(jù)庫(kù)(MySql)

import org.dbunit.database.DatabaseConfig;
import org.dbunit.ext.mysql.MySqlDataTypeFactory;
import org.dbunit.ext.mysql.MySqlMetadataHandler;
import org.unitils.core.UnitilsException;
import org.unitils.dbmaintainer.locator.ClassPathDataLocator;
import org.unitils.dbmaintainer.locator.resourcepickingstrategie.ResourcePickingStrategie;
import org.unitils.dbunit.DbUnitModule;
import org.unitils.dbunit.datasetfactory.DataSetFactory;
import org.unitils.dbunit.util.DbUnitDatabaseConnection;
import org.unitils.dbunit.util.MultiSchemaDataSet;

import java.io.File;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

public class MyDbUnitModule extends DbUnitModule {

  //完善DbUnitDatabaseConnection連接信息
  @Override
  public DbUnitDatabaseConnection getDbUnitDatabaseConnection(final String schemaName) {
    DbUnitDatabaseConnection result = dbUnitDatabaseConnections.get(schemaName);
    if (result != null) {
      return result;
    }

    result = super.getDbUnitDatabaseConnection(schemaName);

    result.getConfig().setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, new MySqlDataTypeFactory());
    result.getConfig().setProperty(DatabaseConfig.PROPERTY_METADATA_HANDLER, new MySqlMetadataHandler());
    return result;
  }

  //Excel預(yù)處理操作，將@DataSet注釋讀取的文件返回給DataSetFactory進(jìn)行處理
  @Override
  protected File handleDataSetResource(ClassPathDataLocator locator, String nameResource, ResourcePickingStrategie strategy, Class<?> testClass) {
    String cloneResource = new String(nameResource);
    String packageName = testClass.getPackage() != null?testClass.getPackage().getName():"";
    String tempName = "";
    if(cloneResource.startsWith(packageName.replace(".", "/"))) {
      cloneResource = tempName = cloneResource.substring(packageName.length());
    } else if(cloneResource.startsWith(packageName)) {
      cloneResource = tempName = cloneResource.substring(packageName.length() + 1);
    } else {
      tempName = cloneResource;
    }

    InputStream in = locator.getDataResource(packageName.replace(".", "/") + "/" + tempName, strategy);
    File resolvedFile = null;
    if(in == null) {
      resolvedFile = this.getDataSetResolver().resolve(testClass, cloneResource);
      if(resolvedFile == null) {
        throw new UnitilsException("DataSetResource file with name '" + nameResource + "' cannot be found");
      }
    }

    return resolvedFile;
  }

  //調(diào)用DataSetFactory.createDataSet()向數(shù)據(jù)庫(kù)中注入Excel數(shù)據(jù)后，直接返回DataSet，不對(duì)DataSet執(zhí)行清零操作
  @Override
  protected MultiSchemaDataSet getDataSet(Class<?> testClass, String[] dataSetFileNames, DataSetFactory dataSetFactory) {
    List<File> dataSetFiles = new ArrayList<File>();

    ResourcePickingStrategie resourcePickingStrategie = getResourcePickingStrategie();

    for (String dataSetFileName : dataSetFileNames) {
      File dataSetFile = handleDataSetResource(new ClassPathDataLocator(), dataSetFileName, resourcePickingStrategie, testClass);
      dataSetFiles.add(dataSetFile);
    }

    MultiSchemaDataSet dataSet = dataSetFactory.createDataSet(dataSetFiles.toArray(new File[dataSetFiles.size()]));
    return dataSet;
  }
}

import org.unitils.core.UnitilsException;
import org.unitils.dbunit.datasetfactory.DataSetFactory;
import org.unitils.dbunit.util.MultiSchemaDataSet;

import java.io.File;
import java.util.*;

public class MultiSchemaXlsDataSetFactory implements DataSetFactory {
  protected String defaultSchemaName;

  public void init(Properties configuration, String s) {
    this.defaultSchemaName = s;
  }

  public MultiSchemaDataSet createDataSet(File... dataSetFiles) {
    try {
      MultiSchemaXlsDataSetReader xlsDataSetReader = new MultiSchemaXlsDataSetReader(defaultSchemaName);
      return xlsDataSetReader.readDataSetXls(dataSetFiles);
    } catch (Exception e) {
      throw new UnitilsException("創(chuàng)建數(shù)據(jù)集失?。? + Arrays.toString(dataSetFiles), e);
    }
  }

  public String getDataSetFileExtension() {
    return "xls";
  }
}

import org.dbunit.database.AmbiguousTableNameException;
import org.dbunit.dataset.DefaultDataSet;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.ITable;
import org.dbunit.dataset.excel.XlsDataSet;
import org.unitils.core.UnitilsException;
import org.unitils.dbunit.util.MultiSchemaDataSet;

import java.io.File;
import java.io.FileInputStream;
import java.util.*;

public class MultiSchemaXlsDataSetReader {
  private String pattern = ".";
  private String defaultSchemaName;

  public MultiSchemaXlsDataSetReader(String defaultSchemaName) {
    this.defaultSchemaName = defaultSchemaName;
  }

  public MultiSchemaDataSet readDataSetXls(File... dataSetFiles) {
    try {
      Map<String, List<ITable>> tbMap = getTables(dataSetFiles);
      MultiSchemaDataSet dataSets = new MultiSchemaDataSet();

      for (Map.Entry<String, List<ITable>> entry : tbMap.entrySet()) {
        List<ITable> tables = entry.getValue();
        try {
          DefaultDataSet ds = new DefaultDataSet(tables.toArray(new ITable[]{}));
          dataSets.setDataSetForSchema(entry.getKey(), ds);
        } catch (AmbiguousTableNameException e) {
          throw new UnitilsException("構(gòu)造DataSet失??！", e);
        }
      }
      return dataSets;
    } catch (Exception e) {
      throw new UnitilsException("解析Excel文件出錯(cuò)：", e);
    }
  }

  private Map<String, List<ITable>> getTables(File... dataSetFiles) {
    Map<String, List<ITable>> tableMap = new HashMap<>();
    // 需要根據(jù)schema把Table重新組合一下
    try {
      String schema, tableName;
      for (File file : dataSetFiles) {
        IDataSet dataSet = new XlsDataSet(new FileInputStream(file));
        String[] tableNames = dataSet.getTableNames();
        for (String tn : tableNames) {
          String[] temp = tn.split(pattern);
          if (temp.length == 2) {
            schema = temp[0];
            tableName = temp[1];
          } else {
            schema = this.defaultSchemaName;
            tableName = tn;
          }

          ITable table = dataSet.getTable(tn);
          if (!tableMap.containsKey(schema)) {
            tableMap.put(schema, new ArrayList<ITable>());
          }
          tableMap.get(schema).add(new XslTableWrapper(tableName, table));
        }
      }
    } catch (Exception e) {
      throw new UnitilsException("Unable to create DbUnit dataset for data set files: " + Arrays.toString(dataSetFiles), e);
    }
    return tableMap;
  }
}

public interface MembersMapper {
  int deleteByPrimaryKey(Integer id);

  int insert(Members record);

  Members selectByPrimaryKey(Integer id);

  int updateByPrimaryKey(Members record);
}

import com.agoura.entity.Members;
import org.junit.Before;
import org.junit.BeforeClass;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.test.context.ContextConfiguration;
import org.unitils.UnitilsJUnit4;
import org.unitils.UnitilsJUnit4TestClassRunner;
import org.unitils.dbunit.annotation.DataSet;

import static org.junit.Assert.assertNotNull;

@RunWith(UnitilsJUnit4TestClassRunner.class)
@ContextConfiguration(locations = {"classpath*:spring-*.xml"})
public class MembersMapperTest extends UnitilsJUnit4 {

  private MembersMapper membersMapper;
  private static ApplicationContext ctx;

  @BeforeClass
  public static void setUpBeforeClass() {
    ctx = new ClassPathXmlApplicationContext("classpath*:spring-mybatis-unitils.xml");
  }

  @Before
  public void setUp() {
    membersMapper = (MembersMapper) ctx.getBean("membersMapper");
  }

  @Test
  @DataSet(value = {"test.xls"})   //test.xlsx
  public void testSelectByPrimaryKey() throws Exception {
    Members member = membersMapper.selectByPrimaryKey(3);
    System.out.println(member);
    assertEquals("王五", member.getName());
  }
}