Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

dbUnit support for Postgresql Array

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?

like image 303
Brecht Vercruyce Avatar asked Mar 27 '16 16:03

Brecht Vercruyce


People also ask

Does PostgreSQL support array?

PostgreSQL allows columns of a table to be defined as variable-length multidimensional arrays. Arrays of any built-in or user-defined base type, enum type, composite type, range type, or domain can be created.

What is DbUnit?

DbUnit is a JUnit extension (also usable with Ant) targeted at database-driven projects that, among other things, puts your database into a known state between test runs.


1 Answers

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());
like image 121
Shirru Avatar answered Sep 30 '22 15:09

Shirru