使用hibernate将数据存储到/从Mysql 5.7数据库检索Json数据 [英] Store/Retrieve Json data to/from Mysql 5.7 database using hibernate
问题描述
我要开始一个项目，我需要生成动态的Google表单。这个项目的需求是我需要使用mysql 5.7，以便我可以使用json数据类型来存储/检索json数据。我对此很好。我知道，可以使用HQL。但是我无法弄清楚的是，如何使用对象关系映射来实现它？
任何想法？
预先感谢！
最近我解决了类似的任务。我猜这太迟了，但也许有人认为这很有用。
简短回答：您应该创建类（例如 com。 test.MyJsonType ）必须实现 org.hibernate.usertype.UserType 接口，其中 nullSafeGet 方法应该将JSON反序列化为java对象（使用Jackson）， nullSafeSet 将POJO序列化为JSON和一些其他辅助方法。
然后你应该扩展MySQLDialect并注册新的列类型json。最后你可以注释实体字段通过@Type（type = com.test.MyJsonType ），它应该映射到MySQL json列。
您也可以阅读关于@TypeDef here 如果你不想写包名称的类型。
例如：
public class MyJsonType implements UserType {
@Override
public int [] sqlTypes（）{
return new int [] {Types.VARCHAR};
}
@Override
public Class< Characteristics> returnedClass（）{
返回Characteristics.class;
$ b @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（无法将字符串转换为发票：+ ex.getMessage（），ex）;
$ b $覆盖$ b $ 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;
}
尝试{
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（无法将发票转换为字符串：+ ex.getMessage（），ex）;
}
}
@覆盖
公共对象deepcopy的（最终目标值）抛出HibernateException的{
尝试{
ByteArrayOutputStream BOS =新ByteArrayOutputStream （）;
ObjectOutputStream oos = new ObjectOutputStream（bos）;
oos.writeObject（value）;
oos.flush（）;
oos.close（）;
bos.close（）;
ByteArrayInputStream bais = new ByteArrayInputStream（bos.toByteArray（））;
返回新的ObjectInputStream（bais）.readObject（）;
catch（ClassNotFoundException | IOException ex）{
抛出新的HibernateException（ex）;
@Override
public boolean isMutable（）{
return true;
$ b @Override
public可串行化反汇编（final Object value）抛出HibernateException {
return（Serializable）this.deepCopy（value）;
$ b @Override
public Object assemble（最终的Serializable缓存，最终的Object所有者）抛出HibernateException {
return this.deepCopy（cached）;
$ b @Override
public Object replace（final Object Object original，final Object target，final Object owner）throws HibernateException {
return this.deepCopy（original）;
$ b @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）;
$ b POJO课程：
public class特性实现了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（））返回false;
final其他特性=（特性）obj;
返回Objects.equals（this.field，other.field）;
$ b @Override
public int hashCode（）{
return Objects.hash（this.field）;
$ b 注册新列类型： p>
public class JsonMySQLDialect extends MySQLDialect {
public JsonMySQLDialect（）{
this.registerColumnType .VARCHAR，json）;
}
}
使用：
@Entity
@Table（name =Table）
public class TableClass {
...
@Column
@Type（type =com.test.MyJsonType）
保护特性特性;
...
}
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 !
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;
...
}
这篇关于使用hibernate将数据存储到/从Mysql 5.7数据库检索Json数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
