It is difficult to use the empty string or date of DBUnit, so fix it and use it.

1.First of all

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.

2. What's happening with DBUnit?

2.1 Reading cell information

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.

2.2 Output to cell information

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.

3. How to fix

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.

3.1 Reading cell information

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);
    }

3.2 Output to cell information

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.

4. Implementation of own class

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);

5. Use your own class

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.

6. Finally

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

It is difficult to use the empty string or date of DBUnit, so fix it and use it.
[Swift] If the support of the application is iOS 11 or later, it was not necessary to use Int and Int64 properly
Is it possible to put the library (aar) in the Android library (aar) and use it?
[Ruby] How to use the map method. How to process the value of an object and get it by hash or symbol.
[Java] Use ResolverStyle.LENIENT to handle the date and time nicely
Is it easy for the user to use when implementing general-purpose functions? Let's be aware of
The design concept of Java's Date and Time API is interesting
I tried to summarize the methods of Java String and StringBuilder
Add the date to the GC statistics acquired by gcutil and output it.
(Determine in 1 minute) About the proper use of empty ?, blank? And present?
Get the type of an array element to determine if it is an array
Since the du command used when the capacity is full is difficult to use, I tried wrapping it with ruby
If it is Ruby, it is efficient to make it a method and stock the processing.
[Java] Is it unnecessary to check "identity" in the implementation of the equals () method?
Is it mainstream not to write the closing tag of <P> tag in Javadoc?
[Java] When putting a character string in the case of a switch statement, it is necessary to make it a constant expression
Since the argument of link_to is nil (null) and an unexpected link was generated, I tried to verify it
Code that is difficult to debug and parse
Add empty data to the top of the list
[Java] The confusing part of String and StringBuilder
Output of how to use the slice method
[Java] How to use Calendar class and Date class
[Java] I studied polymorphism, so I will summarize how to use it and its merits.
A memo about the types of Java O/R mappers and how to select them
How to create a header or footer once and use it on another page
Create a package that is a common development component of Automation Anywhere A2019 # 1-First, build and use the SDK sample as it is