javamysqltestingdbunitspring-test-dbunit

DbUnit and JSON column type support


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/


Solution

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