dbUnit support for Postgresql Array

Question

I'm writing some Unit tests for a database component. For this I use a dedicated test database (Postgresql, same as the production database) and dbUnit.

Now i would like to create an XML dump of my entire test database. I currently use the code from the FAQ page of dbUnit

IDatabaseConnection connection = new DatabaseConnection(conn);
connection.getConfig().setProperty("http://www.dbunit.org/properties/datatypeFactory", new PostgresqlDataTypeFactory());
IDataSet fullDataSet = connection.createDataSet();
FlatXmlDataSet.write(fullDataSet, new FileOutputStream("full.xml"));

This all works great, except for columns of the type Array. dbUnit just leaves them out. I hoped it would be fixed by adding the PostgresqlDataTypeFactory, but this doesn't change a thing.

Does someone know how I can add support for postgresql arrays in dbUnit?

Attention

Answer 1

I found this project on github: https://github.com/JarnTang/dbunit-ext

I'm using its ArrayDataType class with small modifications:

import org.dbunit.dataset.ITable;
import org.dbunit.dataset.datatype.AbstractDataType;
import org.dbunit.dataset.datatype.TypeCastException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.StringUtils;

import java.lang.invoke.MethodHandles;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class ArrayDataType extends AbstractDataType {

    private static final Logger log = LoggerFactory.getLogger(MethodHandles.lookup().lookupClass());
    private static final Class CLASS_TYPE = Array.class;

    public ArrayDataType(String name, int sqlType, boolean isNumber) {
        super(name, sqlType, CLASS_TYPE, isNumber);
    }

    @Override
    public Object typeCast(Object value) throws TypeCastException {
        if (value == null || value == ITable.NO_VALUE) {
            return null;
        }

        if (value instanceof String) {
            return new String[]{(String) value};
        }
        if (value instanceof String[]) {
            return value;
        }

        if (value instanceof Date ||
                value instanceof Time ||
                value instanceof Timestamp) {
            return new String[]{value.toString()};
        }

        if (value instanceof Boolean) {
            return new String[]{value.toString()};
        }

        if (value instanceof Number) {
            try {
                return new String[]{value.toString()};
            } catch (NumberFormatException e) {
                throw new TypeCastException(value, this, e);
            }
        }

        if (value instanceof Array) {
            try {
                Array a = (Array) value;
                return a.getArray();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        if (value instanceof Blob) {
            try {
                Blob blob = (Blob) value;
                byte[] blobValue = blob.getBytes(1, (int) blob.length());
                return typeCast(blobValue);
            } catch (SQLException e) {
                throw new TypeCastException(value, this, e);
            }
        }

        if (value instanceof Clob) {
            try {
                Clob clobValue = (Clob) value;
                int length = (int) clobValue.length();
                if (length > 0) {
                    return clobValue.getSubString(1, length);
                }
                return "";
            } catch (SQLException e) {
                throw new TypeCastException(value, this, e);
            }
        }

        log.warn("Unknown/unsupported object type '{}' - " +
                        "will invoke toString() as last fallback which " +
                        "might produce undesired results",
                value.getClass().getName());
        return value.toString();
    }

    @Override
    public Object getSqlValue(int column, ResultSet resultSet)
            throws SQLException, TypeCastException {
        if (log.isDebugEnabled())
            log.debug("getSqlValue(column={}, resultSet={}) - start", column, resultSet);

        String value = resultSet.getString(column);
        if (value == null || resultSet.wasNull()) {
            return null;
        }
        return value;
    }

    @Override
    public void setSqlValue(Object value, int column, PreparedStatement statement)
            throws SQLException, TypeCastException {
        if (log.isDebugEnabled())
            log.debug("setSqlValue(value={}, column={}, statement={}) - start",
                    value, column, statement);

        Array array = isNumber() ? statement.getConnection().createArrayOf("integer", toArray(value)) :
                statement.getConnection().createArrayOf("text", toArray(value));

        statement.setObject(column, array);
    }


    private Object[] toArray(Object value) {
        List list = new ArrayList(0);
        if (value instanceof String) {
            String valueStr = (String) value;
            if (!StringUtils.isEmpty(valueStr)) {
                valueStr = valueStr.replaceAll("[{}]", "");
                return valueStr.split(",");
            }
        }
        return list.toArray();

    }

}

Then you need to extend PostgresqlDataTypeFactory, for example:

import org.dbunit.dataset.datatype.DataType;
import org.dbunit.dataset.datatype.DataTypeException;
import org.dbunit.ext.postgresql.PostgresqlDataTypeFactory;

public class CustomPostgresqlDataTypeFactory extends PostgresqlDataTypeFactory{

    @Override
    public DataType createDataType(int sqlType, String sqlTypeName, String tableName, String columnName) throws DataTypeException {
        if (sqlType == 2003) {
            if (sqlTypeName.equals("_text"))
                return new ArrayDataType(sqlTypeName, sqlType, false);
            if (sqlTypeName.contains("int"))
                return new ArrayDataType(sqlTypeName, sqlType, true);

            throw new UnsupportedSqlTypeException("Unsupported sql type: " + sqlTypeName);
        }

        return super.createDataType(sqlType, sqlTypeName, tableName, columnName);
    }
}

And set CustomPostgresqlDataTypeFactory to IDatabaseConnection:

IDatabaseConnection conn = databaseTester.getConnection();
conn.getConfig().setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY,
                new CustomPostgresqlDataTypeFactory());

Fayalite

Great stuff. Thanks a lot boss – Emlynn