对dbunit进行mybatis DAO层Excel单元测试(必看篇)

<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

#自定义扩展模块，加载excel文件，默认拓展模块org.unitils.dbunit.dbunitmodule支持xml
unitils.module.dbunit.classname=org.agoura.myunit.module.mydbunitmodule

#配置数据库连接
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
#配置为数据库名称
database.schemanames=teams
#配置数据库方言
database.dialect=mysql

#需设置false，否则我们的测试函数只有在执行完函数体后，才将数据插入的数据表中
unitils.module.database.runafter=false

#配置数据库维护策略.请注意下面这段描述
# 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

#配置数据库表创建策略，是否自动建表以及建表sql脚本存放目录
dbmaintainer.autocreateexecutedscriptstable=true
dbmaintainer.keepretryingaftererror.enabled=true
dbmaintainer.script.locations=src/main/resources/dbscripts
#dbmaintainer.script.fileextensions=sql

#数据集加载策略
#cleaninsertloadstrategy:先删除dateset中有关表的数据，然后再插入数据
#insertloadstrategy：只插入数据
#refreshloadstrategy:有同样key的数据更新，没有的插入
#updateloadstrategy:有同样key的数据更新，没有的不做任何操作
dbunitmodule.dataset.loadstrategy.default=org.unitils.dbunit.datasetloadstrategy.impl.cleaninsertloadstrategy

#配置数据集工厂，自定义
dbunitmodule.dataset.factory.default=org.agoura.myunit.utils.multischemaxlsdatasetfactory
dbunitmodule.expecteddataset.factory.default=org.agoura.myunit.utils.multischemaxlsdatasetfactory

#配置事务策略 commit、rollback 和disabled；或者在代码的方法上标记@transactional(value=transactionmode.rollback)
#commit 是单元测试方法过后提交事务
#rollback 是回滚事务
#disabled 是没有事务，默认情况下，事务管理是disabled
databasemodule.transactional.value.default=commit

#配置数据集结构模式xsd生成路径，可以自定义目录，但不能为空
datasetstructuregenerator.xsd.dirname=src/main/resources/xsd
dbmaintainer.generatedatasetstructure.enabled=true

#文件相对路径是否是测试类文件路径，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"/>
    <!-- 自动扫描mapping.xml文件 -->
    <property name="mapperlocations" value="classpath*:com/agoura/agoura/mapper/xml/*.xml"></property>
  </bean>

  <!-- dao接口所在包名，spring会自动查找其下的类 -->
  <bean class="org.mybatis.spring.mapper.mapperscannerconfigurer">
    <property name="basepackage" value="com.agoura.agoura.mapper"/>
    <property name="sqlsessionfactorybeanname" value="sqlsessionfactory"></property>
  </bean>

  <!-- (事务管理)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 --> 数据库(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预处理操作，将@dataset注释读取的文件返回给datasetfactory进行处理
  @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;
  }

  //调用datasetfactory.createdataset()向数据库中注入excel数据后，直接返回dataset，不对dataset执行清零操作
  @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("创建数据集失败：" + 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("构造dataset失败！", e);
        }
      }
      return datasets;
    } catch (exception e) {
      throw new unitilsexception("解析excel文件出错：", e);
    }
  }

  private map<string, list<itable>> gettables(file... datasetfiles) {
    map<string, list<itable>> tablemap = new hashmap<>();
    // 需要根据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());
  }
}