DbUnit and JSON column type support

Question

I'm wondering what's the best possible way to make DbUnit work with MySQL's JSON(B) type columns? We do have such columns here and there and whenever I am trying to feed the test data from XML file via @DatabaseSetup I get the NoSuchColumnException which prevents me form making of any sensible integration tests with entities or repos that deals with JSON:

org.dbunit.dataset.NoSuchColumnException: assistant_event.AEV_CONTEXT -  (Non-uppercase input column: aev_context) in ColumnNameToIndexes cache map. Note that the map's column names are NOT case sensitive.

I get that this is because my AEV_CONTEXT column didn't get recognized because, well, it's a JSON column:

@Type(type = "json")
@Column(name = "aev_context", columnDefinition = "json")
private Context context;

However, I'm getting a hard time while trying to workaround it. And, what's even more odd, I couldn't find such workaround anywhere here either! In fact, I don't event know if that's more Hibernate or DbUnit thing.

Is it really only me had this problem so far? Any advice would be much appreciated!

Oh, and in case you wondering, that's how I get JSON support for Hibernate:

https://vladmihalcea.com/how-to-map-json-objects-using-generic-hibernate-types/

Pessa

Answer 1

In case someone needs it this is the Java version of @Aleksi's solution for the PostgreSQL JSONB datatype:

import org.dbunit.dataset.datatype.AbstractDataType;
import org.dbunit.dataset.datatype.DataType;
import org.dbunit.dataset.datatype.DataTypeException;
import org.dbunit.dataset.datatype.TypeCastException;
import org.dbunit.ext.postgresql.PostgresqlDataTypeFactory;
import org.postgresql.util.PGobject;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

public class NewPostgresqlDataTypeFactory extends PostgresqlDataTypeFactory {
    @Override
    public DataType createDataType(int sqlType, String sqlTypeName) throws DataTypeException {
        if (sqlTypeName.equals("jsonb")) {
            return new JsonbDataType();
        } else {
            return super.createDataType(sqlType, sqlTypeName);
        }
    }

    public static class JsonbDataType extends AbstractDataType {

        public JsonbDataType() {
            super("jsonb", Types.OTHER, String.class, false);
        }

        @Override
        public Object typeCast(Object obj) throws TypeCastException {
            return obj.toString();
        }

        @Override
        public Object getSqlValue(int column, ResultSet resultSet) throws SQLException, TypeCastException {
            return resultSet.getString(column);
        }

        @Override
        public void setSqlValue(Object value,
                                int column,
                                PreparedStatement statement) throws SQLException, TypeCastException {
            final PGobject jsonObj = new PGobject();
            jsonObj.setType("json");
            jsonObj.setValue(value == null ? null : value.toString());

            statement.setObject(column, jsonObj);
        }
    }
}

Majesty

Answer 2

One potential solution is to define your own DataTypeFactory and use that. I did it to support the PostgreSQL JSONB datatype, something similar could be made for MySQL.

class NewPostgresqlDataTypeFactory : PostgresqlDataTypeFactory() {
    override fun createDataType(sqlType: Int, sqlTypeName: String?): DataType {
        return when (sqlTypeName) {
            "jsonb" -> return JsonbDataType()
            else -> super.createDataType(sqlType, sqlTypeName)
        }
    }

    class JsonbDataType : AbstractDataType("jsonb", Types.OTHER, String::class.java, false) {
        override fun typeCast(obj: Any?): Any {
            return obj.toString()
        }

        override fun getSqlValue(column: Int, resultSet: ResultSet): Any {
            return resultSet.getString(column)
        }

        override fun setSqlValue(value: Any?, column: Int, statement: PreparedStatement) {
            val jsonObj = PGobject()
            jsonObj.type = "json"
            jsonObj.value = value?.toString()
            statement.setObject(column, jsonObj)
        }
    }
}

Doubletime

Answer 3

In addition to @Ilya's answer, here is the similar solution for H2 JSON datatype:

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

import org.dbunit.dataset.datatype.AbstractDataType;
import org.dbunit.dataset.datatype.DataType;
import org.dbunit.dataset.datatype.DataTypeException;
import org.dbunit.ext.h2.H2DataTypeFactory;
import org.h2.value.Value;
import org.h2.value.ValueJson;

public class CustomH2DataTypeFactory extends H2DataTypeFactory {

        @Override
        public DataType createDataType(int sqlType, String sqlTypeName, String tableName, String columnName)
            throws DataTypeException {
            if (sqlType == 1111 && sqlTypeName.toLowerCase().startsWith("json")) {
                return new JsonDataType();
            } else {
                return super.createDataType(sqlType, sqlTypeName, tableName, columnName);
            }
        }

        public class JsonDataType extends AbstractDataType {

            public JsonDataType() {
                super("json", Types.OTHER, String.class, false);
            }

            @Override
            public Object typeCast(Object value) {
                return value.toString();
            }

            @Override
            public Object getSqlValue(int column, ResultSet resultSet) throws SQLException {
                return resultSet.getString(column);
            }

            @Override
            public void setSqlValue(Object value, int column, PreparedStatement statement) throws SQLException {
                Value object = ValueJson.fromJson(value.toString());
                statement.setObject(column, object);
            }
        }
    }

Twink