JSONB column support in Spring Data JDBC

db.changelog.xml

<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
                   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                   xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
                   http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.8.xsd">
    <changeSet id="1" author="dustin_lo">

        <createTable tableName="properties">
            <column name="id" type="BIGSERIAL" autoIncrement="true">
                <constraints primaryKey="true"/>
            </column>
            <column name="metadata" type="JSONB"/>
        </createTable>

    </changeSet>
</databaseChangeLog>

IncomingJsonConverter.java

package com.disney.vision_schema_service.configs;

import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import org.postgresql.util.PGobject;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.convert.converter.Converter;
import org.springframework.data.convert.ReadingConverter;
import org.springframework.stereotype.Component;

@Component
@ReadingConverter
public class IncomingJsonConverter implements Converter<PGobject, JsonNode> {
    private final ObjectMapper mapper;

    @Autowired
    public IncomingJsonConverter(ObjectMapper mapper) {
        this.mapper = mapper;
    }

    @Override
    public JsonNode convert(PGobject json) {
        try {
            return this.mapper.readTree(json.getValue());
        } catch (JsonProcessingException e) {
            throw new IllegalArgumentException("Failed to convert Map to JSON String", e);
        }
    }
}

OutgoingJsonConverter.java

package com.disney.vision_schema_service.configs;

import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import org.postgresql.util.PGobject;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.convert.converter.Converter;
import org.springframework.data.convert.WritingConverter;
import org.springframework.stereotype.Component;

import java.sql.SQLException;

@Component
@WritingConverter
public class OutgoingJsonConverter implements Converter<JsonNode, PGobject> {
    private final ObjectMapper mapper;

    @Autowired
    public OutgoingJsonConverter(ObjectMapper mapper) {
        this.mapper = mapper;
    }

    @Override
    public PGobject convert(JsonNode jsonNode) {
        PGobject json = new PGobject();
        json.setType("jsonb");
        try {
            json.setValue(this.mapper.writeValueAsString(jsonNode));
            return json;
        } catch (SQLException | JsonProcessingException e) {
            throw new IllegalArgumentException("Failed to convert Map to JSON String", e);
        }
    }
}

Property.java

package com.disney.vision_schema_service.models;

import com.fasterxml.jackson.annotation.JsonIgnore;
import com.fasterxml.jackson.annotation.JsonIgnoreProperties;
import com.fasterxml.jackson.annotation.JsonProperty;
import com.fasterxml.jackson.databind.JsonNode;
import org.springframework.data.annotation.Id;
import org.springframework.data.annotation.Version;
import org.springframework.data.relational.core.mapping.Column;
import org.springframework.data.relational.core.mapping.Table;

import java.util.UUID;

@Table("properties")
@JsonIgnoreProperties(ignoreUnknown = true)
public class Property {

    @Id
    @JsonIgnore
    private Long id;

    private UUID _id;

    // use JsonNode in jsonb columns
    private JsonNode metadata;

    public JsonNode getMetadata() {
        return metadata;
    }

    public void setMetadata(JsonNode metadata) {
        this.metadata = metadata;
    }
}