1. はじめに
Java+RDBMSの開発プロジェクトで、単体テストツールにDBUnitを使用するケースは未だ（2020年）に多いと思います。
DBUnitはDBの初期データと実行後のアサーションデータをxls形式やCSV形式を利用できることで非常に便利かつ開発省力化に有効なツールですが、Excelのデータ形式やPOIの仕様の問題で若干使いづらい点があります。
具体的には件名で挙げた通り、可変文字列型（VARCHAR型）の空文字列とNULLを分別できない点と、日付型（DATETIME、TIMESTAMP、DATE、TIME等）の値を正確に設定できない問題があります。
本稿では、この対策方法を記述します。
なお、本稿でのDbUnitのバージョンは2.5.4を使用しています。
2. DBUnitはどうなっているのか？
2.1 セル情報の読み込み
DBUnitではXlsTableクラスのgetValue(int row, String column)メソッドで、Excelのセルの値を取得してDBに設定する値に変換しています。
XlsTable#getValueから抜粋
int type = cell.getCellType();
switch (type)
{
case HSSFCell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell))
{
return cell.getDateCellValue();
}
return new BigDecimal(cell.getNumericCellValue());
case HSSFCell.CELL_TYPE_STRING:
return cell.getStringCellValue();
case HSSFCell.CELL_TYPE_FORMULA:
throw new DataTypeException("Formula not supported at row=" +
row + ", column=" + column);
case HSSFCell.CELL_TYPE_BLANK:
return null;
case HSSFCell.CELL_TYPE_BOOLEAN:
return cell.getBooleanCellValue() ? Boolean.TRUE : Boolean.FALSE;
case HSSFCell.CELL_TYPE_ERROR:
throw new DataTypeException("Error at row=" + row +
", column=" + column);
default:
throw new DataTypeException("Unsupported type at row=" + row +
", column=" + column);
}
上記のコードからわかる通り、セルのデータ型が数値型で特定のフォーマットの場合に日付型としています。また、セルが空の場合にNULLとしています。
このため、DBに設定する日付型の値はExcelの日付データの精度によって差分が表示ます。たとえばExcel上では2020/1/24 10:00と入れたものがDBのDATETIME型のカラムには2020/1/24 10:00:01になることがあります。
また、セルの値が空の場合は一律nullになるため空文字列のデータを作ることができません。
2.2 セル情報への出力
DBUnitには、DB（だけに限らないのですが）から読み取ったデータをExcelファイルに出力する機能も提供しています。
Excelへの出力はXlsDataSetクラスのwrite(IDataSet dataSet, OutputStream out)メソッドで行っています。
writeメソッドの内部でセルに設定する値の取得をしています。
XlsDataSet#writeから抜粋
// write table data
for (int j = 0; j < table.getRowCount(); j++)
{
HSSFRow row = sheet.createRow(j + 1);
for (int k = 0; k < columns.length; k++)
{
Column column = columns[k];
Object value = table.getValue(j, column.getColumnName());
if (value != null)
{
HSSFCell cell = row.createCell((short)k);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(DataType.asString(value));
}
}
}
上記のコードでIDataSet内のITableに格納されている値を取得してセルに設定をしています。
値がnullの場合はセルの生成自体をしないため、Excelの表示上は空のセルとなります。
3. どう直すのか
上記で見たDBUnitのソースコードからDBUnitの処理の内容そのものの他に、データ型に対するExcelセル上の値の変換方法を利用者側で変更することができないこともわかります。
このため、DBUnitを都合よく使うためにXlsTableとXlsDataSetのソースコードを修正して独自のクラスを作り、そのクラスからDBUnitを使用することにします。
3.1 セル情報の読み込み
XlsTable#getValueメソッドで、セルのデータ型が文字列型の場合でも日付型のパータンにマッチする場合は日付型を設定するように変更します。
また、セル上にnullと文字列を記載した場合はDBにNULL値を設定するようにします。
XlsTable#getValueを修正
int type = cell.getCellType();
switch (type)
{
case HSSFCell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell))
{
return cell.getDateCellValue();
}
return new BigDecimal(cell.getNumericCellValue());
case HSSFCell.CELL_TYPE_STRING:
/* 独自実装（ここから） */
String cellValue = cell.getRichStringCellValue().getString();
// セルの値が"null"の場合はNULLを設定
if ("null".equals(cellValue)) { return null; }
// セルの値が"yyyy/MM/dd HH:mm:ss"形式の場合はDate型にパースして設定
if (Pattern.compile("\\d{4}/\\d{2}/\\d{2} \\d{2}:\\d{2}:\\d{2}").matcher(cellValue).matches()) {
return new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").parse(cellValue);
}
// 上記以外はセルの値をそのまま設定
return cellValue;
/* 独自実装（ここまで） */
case HSSFCell.CELL_TYPE_FORMULA:
throw new DataTypeException("Formula not supported at row=" +
row + ", column=" + column);
case HSSFCell.CELL_TYPE_BLANK:
// セルが空の場合は空文字列を返却
return "";
case HSSFCell.CELL_TYPE_BOOLEAN:
return cell.getBooleanCellValue() ? Boolean.TRUE : Boolean.FALSE;
case HSSFCell.CELL_TYPE_ERROR:
throw new DataTypeException("Error at row=" + row +
", column=" + column);
default:
throw new DataTypeException("Unsupported type at row=" + row +
", column=" + column);
}
3.2 セル情報への出力
JUnitの初期データ設定とDBデータの比較だけであればセル情報の読み込みだけを直せばよいのですが、DBデータのxls形式出力も使用すると試験データの作成を効率化できます。
そのため、セル情報読み込みの修正に合わせて、セル情報への出力処理も修正します。
XlsDataSet#writeを修正
// write table data
for (int j = 0; j < table.getRowCount(); j++)
{
HSSFRow row = sheet.createRow(j + 1);
for (int k = 0; k < columns.length; k++)
{
Column column = columns[k];
Object value = table.getValue(j, column.getColumnName());
/* 独自実装（ここから） */
if (null == value) {
cell.setCellValue("null");
} else if (value instanceof java.sql.Timestamp) {
cell.setCellValue(new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").format(value));
} else {
cell.setCellValue(DataType.asString(value));
}
/* 独自実装（ここまで） */
}
}
値がnullの場合はセルの値に"null"文字列を設定するようにします。
データ型がTimestamp型の場合はyyyy/MM/dd HH:mm:ss形式の文字列を設定するようにします。
4. 独自クラスの実装
上記の方針で、実際にXlsDataSetとXlsTableのソースコードを拝借して独自クラスを定義します。
修正箇所はJDKライブラリのみを使用しているので、DBUnitを使用できる環境であればコンパイル可能だと思います。
XlsDataSetを修正したクラスとしてMyXlsDataSetクラスを定義します。XlsTableクラスはパッケージプライベートクラスとして定義されていて外部パッケージからはアクセスできないクラスとなっています。
ここでは、MyXlsTableクラスをMyXlsDataSetの内部クラスとして定義します。
日付型についてはDATETIME型の他にDATE型やTIME型も考慮して、PatternをキーDateFormatを値とするHashMapを定義して、Mapの各キーのPatternを検査してマッチした場合はキーに紐づく値のDateFormatでパースするようにしています。ここは、MapではなくてもPatternとDateFormatの組み合わせを格納しListや配列でも構いません。
このコードはあくまで試験用なので同期化を考慮していないので注意が必要です。
複数のスレッドからこのクラスを使ってExcelファイルを読み取る場合は、SimpleDateFormatを都度作成するかThreadLocalに格納するかの工夫が必要です。
MyXlsDataSet
public class MyXlsDataSet extends AbstractDataSet {
private static final Logger logger = LoggerFactory.getLogger(MyXlsDataSet.class);
/* 独自実装（ここから） */
private static final SimpleDateFormat TIMESTAMP_FORMAT = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
private static final SimpleDateFormat DATE_FORMAT = new SimpleDateFormat("yyyy/MM/dd");
private static final SimpleDateFormat TIME_FORMAT = new SimpleDateFormat("HH:mm:ss");
/* 独自実装（ここまで） */
private final ITable[] _tables;
/**
* Creates a new XlsDataSet object that loads the specified Excel document.
*/
public DateFormattedXlsDataSet(File file) throws IOException, DataSetException {
this(new FileInputStream(file));
}
/**
* Creates a new XlsDataSet object that loads the specified Excel document.
*/
public DateFormattedXlsDataSet(InputStream in) throws IOException, DataSetException {
HSSFWorkbook workbook = new HSSFWorkbook(in);
_tables = new ITable[workbook.getNumberOfSheets()];
for (int i = 0; i < _tables.length; i++) {
_tables[i] = new DateFormattedXlsTable(workbook.getSheetName(i), workbook.getSheetAt(i));
}
}
/**
* Write the specified dataset to the specified Excel document.
*/
public static void write(IDataSet dataSet, OutputStream out)
throws IOException, DataSetException {
logger.debug("write(dataSet=" + dataSet + ", out=" + out + ") - start");
HSSFWorkbook workbook = new HSSFWorkbook();
int index = 0;
ITableIterator iterator = dataSet.iterator();
while (iterator.next()) {
// create the table i.e. sheet
ITable table = iterator.getTable();
ITableMetaData metaData = table.getTableMetaData();
HSSFSheet sheet = workbook.createSheet(metaData.getTableName());
// write table metadata i.e. first row in sheet
// workbook.setSheetName(index, metaData.getTableName(), HSSFWorkbook.ENCODING_UTF_16);
workbook.setSheetName(index, metaData.getTableName());
HSSFRow headerRow = sheet.createRow(0);
Column[] columns = metaData.getColumns();
for (int j = 0; j < columns.length; j++) {
Column column = columns[j];
HSSFCell cell = headerRow.createCell((short) j);
// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(column.getColumnName());
}
// write table data
for (int j = 0; j < table.getRowCount(); j++) {
HSSFRow row = sheet.createRow(j + 1);
for (int k = 0; k < columns.length; k++) {
Column column = columns[k];
Object value = table.getValue(j, column.getColumnName());
/* 独自実装（ここから） */
HSSFCell cell = row.createCell((short) k);
if (null == value) {
cell.setCellValue("null");
} else if (value instanceof java.sql.Timestamp) {
cell.setCellValue(TIMESTAMP_FORMAT.format(value));
} else if (value instanceof java.sql.Date) {
cell.setCellValue(DATE_FORMAT.format(value));
} else if (value instanceof Time) {
cell.setCellValue(TIME_FORMAT.format(value));
} else {
cell.setCellValue(DataType.asString(value));
}
/* 独自実装（ここまで） */
// if (value != null) {
// HSSFCell cell = row.createCell((short) k);
// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
// cell.setCellValue(DataType.asString(value));
// }
}
}
index++;
}
// write xls document
workbook.write(out);
out.flush();
}
////////////////////////////////////////////////////////////////////////////
// AbstractDataSet class
protected ITableIterator createIterator(boolean reversed) throws DataSetException {
// logger.debug("createIterator(reversed=" + reversed + ") - start");
return new DefaultTableIterator(_tables, reversed);
}
private static class MyXlsTable extends AbstractTable {
/* 独自実装（ここから） */
// UT用なので同期化は考慮しない
private static final HashMap<Pattern, SimpleDateFormat> DATETIME_PATTERN_MAP =
new HashMap<Pattern, SimpleDateFormat>();
static {
DATETIME_PATTERN_MAP.put(
Pattern.compile("\\d{4}/\\d{2}/\\d{2}"), new SimpleDateFormat("yyyy/MM/dd"));
DATETIME_PATTERN_MAP.put(
Pattern.compile("\\d{4}-\\d{2}-\\d{2}"), new SimpleDateFormat("yyyy-MM-dd"));
DATETIME_PATTERN_MAP.put(
Pattern.compile("\\d{4}/\\d{2}/\\d{2} \\d{2}:\\d{2}:\\d{2}"),
new SimpleDateFormat("yyyy/MM/dd HH:mm:ss"));
DATETIME_PATTERN_MAP.put(
Pattern.compile("\\d{4}-\\d{2}-\\d{2} \\d{2}:\\d{2}:\\d{2}"),
new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"));
}
/* 独自実装（ここまで） */
// private static final Logger logger = LoggerFactory.getLogger(XlsTable.class);
private final ITableMetaData _metaData;
private final Sheet _sheet;
private final DecimalFormatSymbols symbols = new DecimalFormatSymbols();
public DateFormattedXlsTable(String sheetName, Sheet sheet) throws DataSetException {
int rowCount = sheet.getLastRowNum();
if (rowCount >= 0 && sheet.getRow(0) != null) {
_metaData = createMetaData(sheetName, sheet.getRow(0));
} else {
_metaData = new DefaultTableMetaData(sheetName, new Column[0]);
}
_sheet = sheet;
// Needed for later "BigDecimal"/"Number" conversion
symbols.setDecimalSeparator('.');
}
static ITableMetaData createMetaData(String tableName, Row sampleRow) {
logger.debug("createMetaData(tableName={}, sampleRow={}) - start", tableName, sampleRow);
List columnList = new ArrayList();
for (int i = 0; ; i++) {
Cell cell = sampleRow.getCell(i);
if (cell == null) {
break;
}
String columnName = cell.getRichStringCellValue().getString();
if (columnName != null) {
columnName = columnName.trim();
}
// Bugfix for issue ID 2818981 - if a cell has a formatting but no name also ignore it
if (columnName.length() <= 0) {
// logger.debug("The column name of column # {} is empty - will skip here assuming the
// last column was reached", String.valueOf(i));
break;
}
Column column = new Column(columnName, DataType.UNKNOWN);
columnList.add(column);
}
Column[] columns = (Column[]) columnList.toArray(new Column[0]);
return new DefaultTableMetaData(tableName, columns);
}
////////////////////////////////////////////////////////////////////////////
// ITable interface
public int getRowCount() {
logger.debug("getRowCount() - start");
return _sheet.getLastRowNum();
}
public ITableMetaData getTableMetaData() {
logger.debug("getTableMetaData() - start");
return _metaData;
}
public Object getValue(int row, String column) throws DataSetException {
if (logger.isDebugEnabled()) {
logger.debug("getValue(row={}, columnName={}) - start", Integer.toString(row), column);
}
assertValidRowIndex(row);
int columnIndex = getColumnIndex(column);
Cell cell = _sheet.getRow(row + 1).getCell(columnIndex);
if (cell == null) {
return null;
}
int type = cell.getCellType();
switch (type) {
case Cell.CELL_TYPE_NUMERIC:
CellStyle style = cell.getCellStyle();
if (DateUtil.isCellDateFormatted(cell)) {
return getDateValue(cell);
} else if (XlsDataSetWriter.DATE_FORMAT_AS_NUMBER_DBUNIT.equals(
style.getDataFormatString())) {
// The special dbunit date format
return getDateValueFromJavaNumber(cell);
} else {
return getNumericValue(cell);
}
case Cell.CELL_TYPE_STRING:
/* 独自実装（ここから） */
String cellValue = cell.getRichStringCellValue().getString();
if ("null".equals(cellValue)) {
return null;
}
Set<Pattern> patternSet = DATETIME_PATTERN_MAP.keySet();
for (Pattern pattern : patternSet) {
if (pattern.matcher(cellValue).matches()) {
SimpleDateFormat format = DATETIME_PATTERN_MAP.get(pattern);
try {
return format.parse(cellValue);
} catch (ParseException e) {
continue;
}
}
}
return cellValue;
/* 独自実装（ここまで） */
case Cell.CELL_TYPE_FORMULA:
throw new DataTypeException("Formula not supported at row=" + row + ", column=" + column);
case Cell.CELL_TYPE_BLANK:
return ""; // 独自実装
case Cell.CELL_TYPE_BOOLEAN:
return cell.getBooleanCellValue() ? Boolean.TRUE : Boolean.FALSE;
case Cell.CELL_TYPE_ERROR:
throw new DataTypeException("Error at row=" + row + ", column=" + column);
default:
throw new DataTypeException("Unsupported type at row=" + row + ", column=" + column);
}
}
protected Object getDateValueFromJavaNumber(Cell cell) {
logger.debug("getDateValueFromJavaNumber(cell={}) - start", cell);
double numericValue = cell.getNumericCellValue();
BigDecimal numericValueBd = new BigDecimal(String.valueOf(numericValue));
numericValueBd = stripTrailingZeros(numericValueBd);
return new Long(numericValueBd.longValue());
}
protected Object getDateValue(Cell cell) {
logger.debug("getDateValue(cell={}) - start", cell);
double numericValue = cell.getNumericCellValue();
Date date = DateUtil.getJavaDate(numericValue);
return new Long(date.getTime());
}
/**
* Removes all trailing zeros from the end of the given BigDecimal value up to the decimal
* point.
*
* @param value The value to be stripped
* @return The value without trailing zeros
*/
private BigDecimal stripTrailingZeros(BigDecimal value) {
if (value.scale() <= 0) {
return value;
}
String valueAsString = String.valueOf(value);
int idx = valueAsString.indexOf(".");
if (idx == -1) {
return value;
}
for (int i = valueAsString.length() - 1; i > idx; i--) {
if (valueAsString.charAt(i) == '0') {
valueAsString = valueAsString.substring(0, i);
} else if (valueAsString.charAt(i) == '.') {
valueAsString = valueAsString.substring(0, i);
// Stop when decimal point is reached
break;
} else {
break;
}
}
BigDecimal result = new BigDecimal(valueAsString);
return result;
}
protected BigDecimal getNumericValue(Cell cell) {
logger.debug("getNumericValue(cell={}) - start", cell);
String formatString = cell.getCellStyle().getDataFormatString();
String resultString = null;
double cellValue = cell.getNumericCellValue();
if ((formatString != null)) {
if (!formatString.equals("General") && !formatString.equals("@")) {
logger.debug("formatString={}", formatString);
DecimalFormat nf = new DecimalFormat(formatString, symbols);
resultString = nf.format(cellValue);
}
}
BigDecimal result;
if (resultString != null) {
try {
result = new BigDecimal(resultString);
} catch (NumberFormatException e) {
logger.debug("Exception occurred while trying create a BigDecimal. value={}",
resultString);
// Probably was not a BigDecimal format retrieved from the excel. Some
// date formats are not yet recognized by HSSF as DateFormats so that
// we could get here.
result = toBigDecimal(cellValue);
}
} else {
result = toBigDecimal(cellValue);
}
return result;
}
/**
* @param cellValue
* @return
* @since 2.4.6
*/
private BigDecimal toBigDecimal(double cellValue) {
String resultString = String.valueOf(cellValue);
// To ensure that intergral numbers do not have decimal point and trailing zero
// (to restore backward compatibility and provide a string representation consistent with
// Excel)
if (resultString.endsWith(".0")) {
resultString = resultString.substring(0, resultString.length() - 2);
}
BigDecimal result = new BigDecimal(resultString);
return result;
}
public String toString() {
StringBuilder sb = new StringBuilder();
sb.append(getClass().getName()).append("[");
sb.append("_metaData=").append(this._metaData == null ? "null" : this._metaData.toString());
sb.append(", _sheet=").append(this._sheet == null ? "null" : "" + this._sheet);
sb.append(", symbols=").append(this.symbols == null ? "null" : "" + this.symbols);
sb.append("]");
return sb.toString();
}
}
}
引用したソースコードが2020年1月24日現在で最新の2.6.0になっていましたが、検証に使用したDBUnitのバージョンは2.5.4であるため、Excelのエンコードに関する設定をコメントアウトしています。
2.6.0以上を使用する場合は下記の行のコメントを解除してください。
// workbook.setSheetName(index, metaData.getTableName(), HSSFWorkbook.ENCODING_UTF_16);
// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
5. 独自クラスを使う
上記の独自クラスのコンパイルが成功したら、使い方はXlsDataSetと全く同じです。
ただし、DBUnitは標準で空白のセルの出力を許していないため、取得したDatabaseConnectionに対してDatabaseConfig.FEATURE_ALLOW_EMPTY_FIELDSをtrueにする処理を入れた方が良いです。
ExcelデータをDBに設定
DatabaseConnection connection = new DatabaseConnection(sqlConnection, schemaName);
connection.getConfig().setProperty(DatabaseConfig.FEATURE_ALLOW_EMPTY_FIELDS, true);
IDataSet xlsDataSet = new MyXlsDataset(new File(xlsFilePath));
DatabaseOperation.CLEAN_INSERT.execute(connection, compositDataSet);
ExcelデータとDBを比較
DatabaseConnection connection = new DatabaseConnection(sqlConnection, schemaName);
connection.getConfig().setProperty(DatabaseConfig.FEATURE_ALLOW_EMPTY_FIELDS, true);
IDataSet expected= new MyXlsDataset(new File(expectedXlsFilePath));
QueryDataSet actual = new QueryDataSet(connection);
Assert.assertEquals(expected, actual);
DBデータをExcelファイルに出力
DatabaseConnection connection = new DatabaseConnection(sqlConnection, schemaName);
connection.getConfig().setProperty(DatabaseConfig.FEATURE_ALLOW_EMPTY_FIELDS, true);
QueryDataSet dbDataSet= new QueryDataSet(connection);
FileOutputStream fileOutputStream = new FileOutputStream(outputFilePath);
MyXlsDataset.write(dbDataSet, fileOutputStream);
gradleを使用しているプロジェクトでは、gradleタスクとしてDBUnitを使ってDBのエクスポートやインポートをしたくなるかもしれません。もちろんそれは可能ですが、その場合は上記のMyXlsDataSetクラスはjarライブラリにする必要があります。
6. さいごに
DBUnitはLGPLライセンスを持っています。
作成したプログラムのテストコードを含めて配布する場合など上記のコードを実装コードに含める場合は、LGPLライセンスに従う必要があるので注意をしてください。