unitils集成dbunit与MySQL的NoSuchColumnException问题

在使用unitils集成dbunit与MySQL做单元测试的时候总是出现NoSuchColumnException,导致测试无法完成。具体错误信息如下:

org.unitils.core.UnitilsException: Error inserting test data from DbUnit dataset for method public void com.smart.dao.UserDaoTest.findUserByUserName()

	at org.unitils.dbunit.DbUnitModule.insertDataSet(DbUnitModule.java:190)
	at org.unitils.dbunit.DbUnitModule$DbUnitListener.beforeTestSetUp(DbUnitModule.java:791)
	at org.unitils.core.Unitils$UnitilsTestListener.beforeTestSetUp(Unitils.java:273)
	at org.unitils.UnitilsTestNG.unitilsBeforeTestSetUp(UnitilsTestNG.java:62)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.testng.internal.MethodInvocationHelper.invokeMethod(MethodInvocationHelper.java:84)
	at org.testng.internal.Invoker.invokeConfigurationMethod(Invoker.java:564)
	at org.testng.internal.Invoker.invokeConfigurations(Invoker.java:213)
	at org.testng.internal.Invoker.invokeMethod(Invoker.java:653)
	at org.testng.internal.Invoker.invokeTestMethod(Invoker.java:901)
	at org.testng.internal.Invoker.invokeTestMethods(Invoker.java:1231)
	at org.testng.internal.TestMethodWorker.invokeTestMethods(TestMethodWorker.java:127)
	at org.testng.internal.TestMethodWorker.run(TestMethodWorker.java:111)
	at org.testng.TestRunner.privateRun(TestRunner.java:767)
	at org.testng.TestRunner.run(TestRunner.java:617)
	at org.testng.SuiteRunner.runTest(SuiteRunner.java:334)
	at org.testng.SuiteRunner.runSequentially(SuiteRunner.java:329)
	at org.testng.SuiteRunner.privateRun(SuiteRunner.java:291)
	at org.testng.SuiteRunner.run(SuiteRunner.java:240)
	at org.testng.SuiteRunnerWorker.runSuite(SuiteRunnerWorker.java:52)
	at org.testng.SuiteRunnerWorker.run(SuiteRunnerWorker.java:86)
	at org.testng.TestNG.runSuitesSequentially(TestNG.java:1224)
	at org.testng.TestNG.runSuitesLocally(TestNG.java:1149)
	at org.testng.TestNG.run(TestNG.java:1057)
	at org.testng.IDEARemoteTestNG.run(IDEARemoteTestNG.java:72)
	at org.testng.RemoteTestNGStarter.main(RemoteTestNGStarter.java:127)
Caused by: org.unitils.core.UnitilsException: Error while executing DataSetLoadStrategy
	at org.unitils.dbunit.datasetloadstrategy.impl.BaseDataSetLoadStrategy.execute(BaseDataSetLoadStrategy.java:46)
	at org.unitils.dbunit.DbUnitModule.insertDataSet(DbUnitModule.java:342)
	at org.unitils.dbunit.DbUnitModule.insertDataSet(DbUnitModule.java:268)
	at org.unitils.dbunit.DbUnitModule.insertDataSet(DbUnitModule.java:187)
	... 28 more
Caused by: org.dbunit.dataset.NoSuchColumnException:  (Non-uppercase input column: user_id) in ColumnNameToIndexes cache map. Note that the map's column names are NOT case sensitive.
	at org.dbunit.dataset.AbstractTableMetaData.getColumnIndex(AbstractTableMetaData.java:117)
	at org.dbunit.operation.AbstractOperation.getOperationMetaData(AbstractOperation.java:89)
	at org.dbunit.operation.AbstractBatchOperation.execute(AbstractBatchOperation.java:144)
	at org.unitils.dbunit.datasetloadstrategy.impl.InsertLoadStrategy.doExecute(InsertLoadStrategy.java:45)
	at org.unitils.dbunit.datasetloadstrategy.impl.BaseDataSetLoadStrategy.execute(BaseDataSetLoadStrategy.java:44)
	... 31 more

再理下错误信息：

org.unitils.core.UnitilsException: Error inserting test data from DbUnit dataset for method

Caused by: org.unitils.core.UnitilsException: Error while executing DataSetLoadStrategy

Caused by: org.dbunit.dataset.NoSuchColumnException:

如果把log4j设为DEBUG, 在控制台可以发现在报错之前有如下信息：

2017-09-30 09:02:40,265 DEBUG (org.unitils.core.util.ConfigUtils:74) - Creating instance of interface org.dbunit.dataset.datatype.IDataTypeFactory. Implementation class org.dbunit.ext.mysql.MySqlDataTypeFactory
2017-09-30 09:02:40,265 DEBUG (org.unitils.core.util.ConfigUtils:74) - Creating instance of interface org.dbunit.dataset.datatype.IDataTypeFactory. Implementation class org.dbunit.ext.mysql.MySqlDataTypeFactory
2017-09-30 09:02:40,285 DEBUG (org.unitils.core.util.ConfigUtils:74) - Creating instance of interface org.dbunit.database.IMetadataHandler. Implementation class org.dbunit.database.DefaultMetadataHandler
2017-09-30 09:02:40,285 DEBUG (org.unitils.core.util.ConfigUtils:74) - Creating instance of interface org.dbunit.database.IMetadataHandler. Implementation class org.dbunit.database.DefaultMetadataHandler
2017-09-30 09:02:40,335 DEBUG (org.springframework.jdbc.datasource.DataSourceUtils:110) - Fetching JDBC Connection from DataSource
2017-09-30 09:02:40,335 DEBUG (org.springframework.jdbc.datasource.DataSourceUtils:110) - Fetching JDBC Connection from DataSource
2017-09-30 09:02:40,365 DEBUG (org.springframework.jdbc.datasource.DataSourceUtils:327) - Returning JDBC Connection to DataSource
2017-09-30 09:02:40,365 DEBUG (org.springframework.jdbc.datasource.DataSourceUtils:327) - Returning JDBC Connection to DataSource

这里基本可确定是在加载数据库信息的时候报错，而对应的类是DefaultMetadataHandler. 看看如下源码部分：

boolean areEqual = this.areEqualIgnoreNull(catalog, catalogName, caseSensitive) && this.areEqualIgnoreNull(schema, schemaName, caseSensitive) && this.areEqualIgnoreNull(table, tableName, caseSensitive) && this.areEqualIgnoreNull(column, columnName, caseSensitive);
        return areEqual;

DEBUG的时候发现这里的输入如下:



可确定传入的schema是有值的，而schemaName为null,导致areEqual为false,进而报错。

解决办法：重写DbUnitModule的getDbUnitDatabaseConnection方法，

public class MySqlDbUnitModule extends DbUnitModule {

    @Override
    public DbUnitDatabaseConnection getDbUnitDatabaseConnection(
            final String schemaName) {
        DbUnitDatabaseConnection result = dbUnitDatabaseConnections
                .get(schemaName);
        if (null != result) {
            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;
    }
}

然后在自定义unitils.properties文件中引用：
unitils.module.dbunit.className=org.dbunit.MySqlDbUnitModule

至此问题解决。

总结，仔细看log4j的日志肯定会发现此问题的，我在此纠结了两天，最后看控制台输出日志发现此问题。