I think there are still many cases (2020) of using DBUnit as a unit test tool in Java + RDBMS development projects. DBUnit is a very convenient and effective tool for development labor saving because it can use xls format and CSV format for initial data of DB and assertion data after execution, but it is a little difficult to use due to problems with Excel data format and POI specifications. There is a point.
Specifically, as mentioned in the subject, there are problems that it is not possible to distinguish between an empty string of variable character string type (VARCHAR type) and NULL, and that the value of date type (DATETIME, TIMESTAMP, DATE, TIME, etc.) cannot be set accurately. There is.
This article describes how to deal with this. The version of DbUnit in this article is 2.5.4.
In DBUnit, the getValue (int row, String column) method of the XlsTable class gets the value of the Excel cell and converts it to the value set in the DB.
XlsTable#Excerpt from 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);
}
As you can see from the code above, if the cell data type is numeric and in a particular format it is date type. Also, if the cell is empty, it is set to NULL.
Therefore, the difference between the date type values set in the DB is displayed depending on the accuracy of the date data in Excel. For example, in Excel, what you put in 2020/1/24 10:00 may become 2020/1/24 10:00:01 in the DATETIME type column of DB.
Also, if the cell value is empty, it will be null uniformly, so it is not possible to create empty string data.
DBUnit also provides a function to output the data read from the DB (but not limited to) to an Excel file.
Output to Excel is done by the write (IDataSet dataSet, OutputStream out) method of the XlsDataSet class.
I am getting the value to be set in the cell inside the write method.
XlsDataSet#Excerpt from 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));
}
}
}
The above code gets the value stored in ʻITable in ʻIDataSet and sets it in the cell.
If the value is null, the cell is not generated, so it will be an empty cell on the Excel display.
From the DBUnit source code seen above, you can also see that in addition to the DBUnit processing content itself, the user cannot change the conversion method of the value on the Excel cell for the data type.
Therefore, in order to use DBUnit conveniently, I will modify the source code of XlsTable and XlsDataSet to create my own class, and use DBUnit from that class.
In the XlsTable # getValue method, change the cell data type to set the date type if it matches the date type pattern even if it is a string type.
Also, if you write null as a character string on the cell, set a NULL value in the DB.
XlsTable#Fix 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:
/*Original implementation (from here)*/
String cellValue = cell.getRichStringCellValue().getString();
//The cell value is"null"Set to NULL for
if ("null".equals(cellValue)) { return null; }
//The cell value is"yyyy/MM/dd HH:mm:ss"In case of format, set by parsing to Date type
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);
}
//Other than the above, set the cell value as it is
return cellValue;
/*Original implementation (up to here)*/
case HSSFCell.CELL_TYPE_FORMULA:
throw new DataTypeException("Formula not supported at row=" +
row + ", column=" + column);
case HSSFCell.CELL_TYPE_BLANK:
//If the cell is empty, return an empty string
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);
}
If you only want to compare the initial data settings of JUnit and DB data, you only need to correct the reading of cell information, but you can also use the xls format output of DB data to streamline the creation of test data. Therefore, the output processing to the cell information is also corrected according to the correction of the cell information reading.
XlsDataSet#Fix 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());
/*Original implementation (from here)*/
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));
}
/*Original implementation (up to here)*/
}
}
If the value is null, set the cell value to the "null" string.
If the data type is Timestamp type, set the character string in yyyy / MM / dd HH: mm: ss format.
With the above policy, we actually borrow the source code of XlsDataSet and XlsTable to define our own class.
Since the modified part uses only the JDK library, I think that it can be compiled if the environment can use DBUnit.
Define the MyXlsDataSet class as a modified version of the XlsDataSet. The XlsTable class is defined as a package private class and is not accessible from external packages.
Here we define the MyXlsTable class as an inner class of MyXlsDataSet.
Regarding the date type, consider the DATE type and TIME type in addition to the DATETIME type, define a HashMap with the key DateFormat as the key Pattern, and set the Pattern of each key of the Map. If it is inspected and matches, it is parsed by the value DateFormat associated with the key. This can be a List or an array that stores a combination of Pattern and DateFormat instead of Map.
Please note that this code is for testing purposes only and does not consider synchronization.
If you want to use this class to read an Excel file from multiple threads, you need to devise whether to create SimpleDateFormat each time or store it in ThreadLocal.
MyXlsDataSet
public class MyXlsDataSet extends AbstractDataSet {
private static final Logger logger = LoggerFactory.getLogger(MyXlsDataSet.class);
/*Original implementation (from here)*/
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");
/*Original implementation (up to here)*/
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());
/*Original implementation (from here)*/
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));
}
/*Original implementation (up to here)*/
// 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 {
/*Original implementation (from here)*/
//Since it is for UT, synchronization is not considered
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"));
}
/*Original implementation (up to here)*/
// 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:
/*Original implementation (from here)*/
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;
/*Original implementation (up to here)*/
case Cell.CELL_TYPE_FORMULA:
throw new DataTypeException("Formula not supported at row=" + row + ", column=" + column);
case Cell.CELL_TYPE_BLANK:
return ""; //Original implementation
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();
}
}
}
The source code quoted was the latest 2.6.0 as of January 24, 2020, but since the version of DBUnit used for verification is 2.5.4, I commented out the settings related to Excel encoding. .. If you want to use 2.6.0 or above, please uncomment the following line.
// workbook.setSheetName(index, metaData.getTableName(), HSSFWorkbook.ENCODING_UTF_16);
// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
If the above proprietary class compiles successfully, the usage is exactly the same as XlsDataSet.
However, since DBUnit does not allow the output of blank cells by default, it is better to add a process to set DatabaseConfig.FEATURE_ALLOW_EMPTY_FIELDS to true for the acquiredDatabaseConnection.
Set Excel data in 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);
Compare Excel data and 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);
Output DB data to Excel file
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);
In a project that uses gradle, you may want to use DBUnit as a gradle task to export or import the DB. Of course that is possible, but in that case the MyXlsDataSet class above should be a jar library.
DBUnit has an LGPL license. Please note that if you want to include the above code in the implementation code, such as when distributing the test code of the created program, you need to comply with the LGPL license.
Recommended Posts