How to Use NoSql Postgres with Spring Boot

We wrote 4 Java classes to support noSQL with postgreSQL in our Spring project.

• JsonString.java is our central class to support noSQL.
• JsonStringDbMapper.java maps our JsonString in Hibernate to JDBC type PGobject(type="jsonb"). This type is defined in postgreSQL JDBC driver.
• JsonbH2Dialect.java maps PGobject to the embedded H2 database which we are using in our JUnit tests only. We define this hibernate dialect in our application-test.yml.
• JsonStringDeserializer.java to use Jackson in our REST services. The JsonString raw value is integrated in the Beans which uses JsonString.

In our entity classes we are using the type JsonString like:

@Type(type = "de.project.config.JsonStringDbMapper")
private JsonString jsonData = new JsonString(null);

JsonString has methods to read and write any Java beans using Jackson. JsonString maps to the database and JsonString maps to the REST service facade.

Here are the files:

package de.project.config;

import com.fasterxml.jackson.annotation.JsonInclude;
import com.fasterxml.jackson.annotation.JsonRawValue;
import com.fasterxml.jackson.annotation.JsonValue;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.SerializationFeature;
import com.fasterxml.jackson.databind.annotation.JsonDeserialize;
import com.fasterxml.jackson.datatype.jsr310.JavaTimeModule;

import java.io.Serializable;
import java.util.Objects;

@JsonDeserialize(using = JsonStringDeserializer.class)
public class JsonString implements Serializable {

    @JsonRawValue
    @JsonValue
    private String json;

    public JsonString() { }

    public JsonString(String json){
        this.json= json;
    }

    public JsonString(Object value){
        this.json= MapperSingleton.INSTANCE.writeValue(value);
    }

    protected enum MapperSingleton {
        INSTANCE;
        private final ObjectMapper objectMapper;

        private MapperSingleton(){
            this.objectMapper= new ObjectMapper();
            this.objectMapper.registerModule(new JavaTimeModule());
            this.objectMapper.configure(SerializationFeature.WRITE_DATES_AS_TIMESTAMPS, false);
            this.objectMapper.setSerializationInclusion(JsonInclude.Include.NON_NULL);
        }

        public String writeValue(Object value){
            try {
                return this.objectMapper.writeValueAsString(value);
            } catch (JsonProcessingException ex) {
                throw new WriteException("Write Java object value to json string failed!", ex);
            }
        }

        public <T> T readValue(String jsonString, Class<T> valueType){
            try {
                return this.objectMapper.readValue(jsonString, valueType);
            } catch (JsonProcessingException ex) {
                throw new ReadException("Java object value from json string not found!", ex);
            }
        }
    }

    public String get(){
        return this.json;
    }
    
    public <T> T read(Class<T> valueType){
        return MapperSingleton.INSTANCE.readValue(this.json, valueType);
    }

    public void write(Object valueType){
        this.json= MapperSingleton.INSTANCE.writeValue(valueType);
    }

    @Override
    public boolean equals(Object obj){
        if(obj instanceof JsonString){
            var json2= (JsonString)obj;
            return Objects.equals(this.json, json2.json);
        }
        return false;
    }

    @Override
    public int hashCode() {
        if(this.json == null){
            return 42;
        }
        return this.json.hashCode();
    }

    public static class WriteException extends RuntimeException {
        public WriteException(String message, Throwable throwable){
            super(message, throwable);
        }
    }

    public static class ReadException extends RuntimeException {
        public ReadException(String message, Throwable throwable){
            super(message, throwable);
        }
    }
}


package de.project.config;

import com.fasterxml.jackson.core.JsonParser;
import com.fasterxml.jackson.databind.DeserializationContext;
import com.fasterxml.jackson.databind.deser.std.StdDeserializer;

import java.io.IOException;

public class JsonStringDeserializer extends StdDeserializer<JsonString> {

    public JsonStringDeserializer() {
        this(null);
    }

    public JsonStringDeserializer(Class<?> vc) {
        super(vc);
    }

    @Override
    public JsonString deserialize(JsonParser jsonparser, DeserializationContext context)
            throws IOException {
        String jsonData= jsonparser.getCodec().readTree(jsonparser).toString();
        return new JsonString(jsonData);
    }
}



package de.project.config;

import java.io.Serializable;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Objects;
import org.hibernate.HibernateException;
import org.hibernate.engine.spi.SharedSessionContractImplementor;
import org.hibernate.usertype.UserType;
import org.postgresql.util.PGobject;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class JsonStringDbMapper implements UserType
{
    private static final Logger LOG = LoggerFactory.getLogger(JsonStringDbMapper.class);

    /**
     * Return the SQL type codes for the columns mapped by this type. The
     * codes are defined on <tt>java.sql.Types</tt>.
     * @see java.sql.Types
     * @return int[] the typecodes
     */
    @Override
    public int[] sqlTypes() {
        return new int[]{Types.OTHER}; // We use only one column for our type JsonString
    }

    /**
     * The class returned by <tt>nullSafeGet()</tt>.
     *
     * @return Class
     */
    @Override
    public Class returnedClass() {
        return JsonString.class;
    }

    /**
     * Compare two instances of the class mapped by this type for persistence "equality".
     * Equality of the persistent state.
     *
     * @param x
     * @param y
     * @return boolean
     */
    @Override
    public boolean equals(Object o, Object o1) throws HibernateException {
        return Objects.equals(o, o1);
    }

    /**
     * Get a hashcode for the instance, consistent with persistence "equality"
     */
    @Override
    public int hashCode(Object o) throws HibernateException {
        return Objects.hashCode(o);
    }

    /**
     * Retrieve an instance of the mapped class from a JDBC resultset. Implementors
     * should handle possibility of null values.
     *
     *
     * @param rs a JDBC result set
     * @param names the column names
     * @param session
     *@param owner the containing entity  @return Object
     * @throws HibernateException
     * @throws SQLException
     */
    @Override
    public Object nullSafeGet(ResultSet rs, String[] names,
            SharedSessionContractImplementor session, Object owner)
            throws HibernateException, SQLException {
        final var dbValue0 = rs.getObject(names[0]);
        if(dbValue0 == null || rs.wasNull()){
            return null;
        }
        if(dbValue0 instanceof PGobject){
            var pgObj= (PGobject)dbValue0;
            if("jsonb".equals(pgObj.getType())){
                return new JsonString(pgObj.getValue());
            }
            throw new IllegalArgumentException("Unable to convert pgObj.type " + pgObj.getType()
                    + " into JsonString");
        }else{
            throw new ClassCastException("Failed to convert " + dbValue0.getClass().getName()
                    + " PGobject");
        }
    }

    /**
     * Write an instance of the mapped class to a prepared statement. Implementors
     * should handle possibility of null values. A multi-column type should be written
     * to parameters starting from <tt>index</tt>.
     *
     *
     * @param st a JDBC prepared statement
     * @param value the object to write
     * @param index statement parameter index
     * @param session
     * @throws HibernateException
     * @throws SQLException
     */
    @Override
    public void nullSafeSet(PreparedStatement st, Object value, int index,
            SharedSessionContractImplementor session) 
                throws HibernateException, SQLException {
        if(Objects.isNull(value)){
            st.setNull(index, Types.OTHER);
        }else{
            var pgObj = new PGobject();
            pgObj.setType("jsonb");
            try {
                var jsonString= (JsonString)value;
                pgObj.setValue(jsonString.get());
                st.setObject(index, pgObj);
            } catch (Exception ex) {
                LOG.error("value='{}'", value);
                throw new IllegalArgumentException("Unable to convert JsonString into PGobject", ex);
            }
        }
    }

    /**
     * Return a deep copy of the persistent state, stopping at entities and at
     * collections. It is not necessary to copy immutable objects, or null
     * values, in which case it is safe to simply return the argument.
     *
     * @param value the object to be cloned, which may be null
     * @return Object a copy
     */
    @Override
    public Object deepCopy(Object o) throws HibernateException {
        return o;
    }

    /**
     * Are objects of this type mutable?
     *
     * @return boolean
     */
    @Override
    public boolean isMutable() {
        return false;
    }

    /**
     * Transform the object into its cacheable representation. At the very least this
     * method should perform a deep copy if the type is mutable. That may not be enough
     * for some implementations, however; for example, associations must be cached as
     * identifier values. (optional operation)
     *
     * @param value the object to be cached
     * @return a cacheable representation of the object
     * @throws HibernateException
     */
    @Override
    public Serializable disassemble(Object value) throws HibernateException {
        return new JsonString(((JsonString)value).get());
    }

    /**
     * Reconstruct an object from the cacheable representation. At the very least this
     * method should perform a deep copy if the type is mutable. (optional operation)
     *
     * @param cached the object to be cached
     * @param owner the owner of the cached object
     * @return a reconstructed object from the cacheable representation
     * @throws HibernateException
     */
    @Override
    public Object assemble(Serializable cached, Object owner) throws HibernateException {
        return new JsonString(((JsonString)cached).get());
    }

    /**
     * During merge, replace the existing (target) value in the entity we are merging to
     * with a new (original) value from the detached entity we are merging. For immutable
     * objects, or null values, it is safe to simply return the first parameter. For
     * mutable objects, it is safe to return a copy of the first parameter. For objects
     * with component values, it might make sense to recursively replace component values.
     *
     * @param original the value from the detached entity being merged
     * @param target the value in the managed entity
     * @return the value to be merged
     */
    @Override
    public Object replace(Object original, Object target, Object o2) throws HibernateException {
        return original;
    }
    
}



package de.project.config;

import java.sql.Types;
import org.hibernate.dialect.H2Dialect;

public class JsonbH2Dialect extends H2Dialect {
    public JsonbH2Dialect(){
        super();
        // Das Datenbanksystem H2 kennt den passenden Datentyp OTHER für Java-Objekte.
        // Im HibernateDialect H2Dialect fehlt das Mapping, welches wir hier ergänzen.
        // Aktuell verwenden wir Types.OTHER für PGObject (jsonb von PostgreSQL).
        this.registerColumnType(Types.OTHER, "OTHER");
    }
}