Store/Retrieve Json data to/from Mysql 5.7 database using hibernate

Recently I have solved similar task. I guess it's too late, but maybe someone finds this useful.

Short answer: you should create class (like "com.test.MyJsonType") that must implement org.hibernate.usertype.UserType interface where the nullSafeGet method should deserialize JSON to java object (using Jackson), the nullSafeSet serialize POJO to JSON and some other auxiliary methods.

Then you should extend MySQLDialect and register new column type "json".

Finally you can annotate entity fields by @Type(type = "com.test.MyJsonType") which should be mapped to MySQL json columns.

You may also read about @TypeDef here if you don't want write type with package name.

For example:

public class MyJsonType implements UserType {@Overridepublic int[] sqlTypes() {    return new int[] { Types.VARCHAR };}@Overridepublic Class<Characteristics> returnedClass() {    return Characteristics.class;}@Overridepublic Object nullSafeGet(final ResultSet rs, final String[] names, final SessionImplementor session, final Object owner)        throws HibernateException, SQLException {    final String cellContent = rs.getString(names[0]);    if (cellContent == null) {        return null;    }    try {        final ObjectMapper mapper = new ObjectMapper().configure(DeserializationFeature.FAIL_ON_UNKNOWN_PROPERTIES, false);        return mapper.readValue(cellContent.getBytes("UTF-8"), returnedClass());    } catch (final Exception ex) {        throw new RuntimeException("Failed to convert String to Invoice: " + ex.getMessage(), ex);    }}@Overridepublic void nullSafeSet(final PreparedStatement ps, final Object value, final int idx, final SessionImplementor session)        throws HibernateException, SQLException {    if (value == null) {        ps.setNull(idx, Types.VARCHAR);        return;    }    try {        final ObjectMapper mapper = new ObjectMapper();        final StringWriter w = new StringWriter();        mapper.writeValue(w, value);        w.flush();        ps.setObject(idx, w.toString(), Types.VARCHAR);    } catch (final Exception ex) {        throw new RuntimeException("Failed to convert Invoice to String: " + ex.getMessage(), ex);    }}@Overridepublic Object deepCopy(final Object value) throws HibernateException {    try {        ByteArrayOutputStream bos = new ByteArrayOutputStream();        ObjectOutputStream oos = new ObjectOutputStream(bos);        oos.writeObject(value);        oos.flush();        oos.close();        bos.close();        ByteArrayInputStream bais = new ByteArrayInputStream(bos.toByteArray());        return new ObjectInputStream(bais).readObject();    } catch (ClassNotFoundException | IOException ex) {        throw new HibernateException(ex);    }}@Overridepublic boolean isMutable() {    return true;}@Overridepublic Serializable disassemble(final Object value) throws HibernateException {    return (Serializable) this.deepCopy(value);}@Overridepublic Object assemble(final Serializable cached, final Object owner) throws HibernateException {    return this.deepCopy(cached);}@Overridepublic Object replace(final Object original, final Object target, final Object owner) throws HibernateException {    return this.deepCopy(original);}@Overridepublic boolean equals(Object x, Object y) throws HibernateException {    return Objects.equals(x, y);}@Overridepublic int hashCode(Object x) throws HibernateException {    return Objects.hashCode(x);}}

POJO class:

public class Characteristics implements Serializable {private String field;public String getField() {    return field;}public void setField(String field) {    this.field= field;}@Overridepublic boolean equals(Object obj) {    if (obj == null) return false;    if (getClass() != obj.getClass()) return false;    final Characteristics other = (Characteristics) obj;    return Objects.equals(this.field, other.field);}@Overridepublic int hashCode() {    return Objects.hash(this.field);}}

Register new column type:

public class JsonMySQLDialect extends MySQLDialect {public JsonMySQLDialect() {    this.registerColumnType(Types.VARCHAR, "json");}}

Using:

@Entity@Table(name = "Table")public class TableClass {...@Column@Type(type = "com.test.MyJsonType")protected Characteristics characteristics;...}