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

Question

I am going to start a project where I need to generate dynamic google forms. The requirement for this project is I need to use mysql 5.7 so that I could use json datatype to store/retrieve json data. I am fine with this. I know, it is possible using HQL. But what I couldn't figure out is how do I implement it with Hibernate using object relational mapping ?

Any Ideas ?

Thanks in advance !

Munger

I would guess it is possible, however I don't think there is a simple way, certainly it's a lot of work... Do you know torodb? They are doing sort of the same thing, however I guess they don't use HQL and underneath they have postgresql... Good luck. – Ultramontane

Answer 1

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 {

@Override
public int[] sqlTypes() {
    return new int[] { Types.VARCHAR };
}

@Override
public Class<Characteristics> returnedClass() {
    return Characteristics.class;
}

@Override
public 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);
    }
}

@Override
public 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);
    }
}

@Override
public 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);
    }
}

@Override
public boolean isMutable() {
    return true;
}

@Override
public Serializable disassemble(final Object value) throws HibernateException {
    return (Serializable) this.deepCopy(value);
}

@Override
public Object assemble(final Serializable cached, final Object owner) throws HibernateException {
    return this.deepCopy(cached);
}

@Override
public Object replace(final Object original, final Object target, final Object owner) throws HibernateException {
    return this.deepCopy(original);
}

@Override
public boolean equals(Object x, Object y) throws HibernateException {
    return Objects.equals(x, y);
}

@Override
public 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;
}

@Override
public 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);

}

@Override
public 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;
...
}

Mazard

Does it create a the column of type "json" or varchar? – Dramatize

It creates "json" column type. – Mazard

Java automatically calls super(). But if you want you may explicitly call it. – Mazard

Hi, Could you give me an example involving an array such as characteristics being an array. – Wideman

@Wideman I think you can just do the following inside TableClass: protected Characteristics[] characteristics; I haven't tested it though so don't quote me on that. – Rochester