将JsonNode对象映射到SQL数据库JPA中的字符串字段

huangapple go评论89阅读模式
英文:

Map a JsonNode object to a String field in SQL DB JPA

问题

我有一个带有以下字段的 SQL 表:

id  uniqueidentifier 主键,
< --省略其他字段-- >
metadata  nvarchar(max)

我还有一个映射到它的实体 Entity:

@Data
public class GenericEntity implements Serializable {

  @Id
  @GeneratedValue(generator = "uuid2")
  @GenericGenerator(name = "uuid2", strategy = "org.hibernate.id.UUIDGenerator")
  @Type(type = "uuid-char")
  private UUID id;
< --省略其他属性-- >
  @Type(type = "string")
  private JsonNode metadata;
}

第二个字段是一个 JsonNode,需要能够接受不同类型的 JSON 并将其存储在表中作为字符串。

我该如何将 JsonNode 字段转换并将其存储在数据库中作为字符串,然后从表中读取时将字符串转换回 JsonNode?

负载:

{
    "id": "db8e8d4b-eee2-4507-bf30-f55c3f948724",
    < -- 省略其他属性 -- >
    "metadata": {
        "description": "Sample",
        "location": "Stack Overflow"
    }
}

目前每当我尝试保存时,我会收到以下错误:

无法确定类型:com.fasterxml.jackson.databind.JsonNode,位于表格:table_name,在列中为:[org.hibernate.mapping.Column(metadata)]
英文:

I got a Sql table with these fields:

id  uniqueidentifier primary key,
&lt;--Omitting extra fields --&gt;
metadata  nvarchar(max)

Which I also have a Entity mapped to it:

@Data
public class GenericEntity implements Serializable {

  @Id
  @GeneratedValue(generator = &quot;uuid2&quot;)
  @GenericGenerator(name = &quot;uuid2&quot;, strategy = &quot;org.hibernate.id.UUIDGenerator&quot;)
  @Type(type = &quot;uuid-char&quot;)
  private UUID id;
&lt;--Omitting extra properties --&gt;
  @Type(type = &quot;string&quot;)
  private JsonNode metadata;
}

The second field that is a JsonNode needs to be able to accept different types of json and store it in the table as a String.

How can I go about converting a JsonNode field and store it in the DB as a String then when I read from the table it converts the String back to a JsonNode?

Payload:

{
    &quot;id&quot;: &quot;db8e8d4b-eee2-4507-bf30-f55c3f948724&quot;,
    &lt;-- Omitting extra properties --&gt;
    &quot;metadata&quot;: {
        &quot;description&quot;: &quot;Sample&quot;,
        &quot;location&quot;: &quot;Stack Overflow&quot;
    }
}

Right now everytime I try to save it I get the error:

Could not determine type for: com.fasterxml.jackson.databind.JsonNode, at table: table_name, for columns: [org.hibernate.mapping.Column(metadata)]

答案1

得分: 1

我按照这些说明进行了操作:
https://www.baeldung.com/hibernate-custom-types

创建了一个 SqlTypeDescriptor

public class JsonNodeStringType extends AbstractSingleColumnStandardBasicType<JsonNode> implements DiscriminatorType<JsonNode> {

  public static final JsonNodeStringType INSTANCE = new JsonNodeStringType();

  public JsonNodeStringType() {
    super(VarcharTypeDescriptor.INSTANCE, JsonNodeStringJavaDescriptor.INSTANCE);
  }

  @Override
  public String getName() {
    return "jsonnode";
  }

  @Override
  public JsonNode stringToObject(String xml) {
    return fromString(xml);
  }

  @Override
  public String objectToSQLString(JsonNode value, Dialect dialect) {
    return '\'' + toString(value) + '\'';
  }
}

然后我创建了一个 Java 类型描述符:

public class JsonNodeStringJavaDescriptor extends AbstractTypeDescriptor<JsonNode> {

  public static final ObjectMapper mapper = new ObjectMapper();

  public static final JsonNodeStringJavaDescriptor INSTANCE = new JsonNodeStringJavaDescriptor();

  public JsonNodeStringJavaDescriptor() {
    super(JsonNode.class, ImmutableMutabilityPlan.INSTANCE);
  }

  @Override
  public String toString(JsonNode value) {
    try {
      return mapper.writeValueAsString(value);
    } catch (JsonProcessingException e) {
      throw new IllegalArgumentException("The given JsonNode object value: " + value + " cannot be transformed to a String", e);
    }
  }

  @Override
  public JsonNode fromString(String string) {
    try {
      return mapper.readTree(string);
    } catch (JsonProcessingException e) {
      throw new IllegalArgumentException("The given string value: " + string + " cannot be transformed to JsonNode object", e);
    }
  }

  @Override
  public <X> X unwrap(JsonNode value, Class<X> type, WrapperOptions options) {
    if (value == null) {
      return null;
    }
    if (String.class.isAssignableFrom(type)) {
      return (X) toString(value);
    }
    throw unknownUnwrap(type);
  }

  @Override
  public <X> JsonNode wrap(X value, WrapperOptions options) {
    if (value == null) {
      return null;
    }
    if (String.class.isInstance(value)) {
      return fromString(value.toString());
    }

    throw unknownWrap(value.getClass());
  }
}

然后将类型定义添加到实体模型中

@Data
@TypeDef(name = "jsonnode", typeClass = JsonNodeStringType.class)
public class GenericEntity implements Serializable {

  @Id
  @GeneratedValue(generator = "uuid2")
  @GenericGenerator(name = "uuid2", strategy = "org.hibernate.id.UUIDGenerator")
  @Type(type = "uuid-char")
  private UUID id;
  <--省略其他属性-->

  @Type(type = "jsonnode")
  private JsonNode metadata;
}

如果你不想手动创建这些类型描述符,你也可以按照这篇文章使用外部依赖来实现:
https://vladmihalcea.com/sql-server-json-hibernate/

英文:

I followed these instructions:
https://www.baeldung.com/hibernate-custom-types

Created a SqlTypeDescriptor

public class JsonNodeStringType extends AbstractSingleColumnStandardBasicType&lt;JsonNode&gt; implements DiscriminatorType&lt;JsonNode&gt; {
public static final JsonNodeStringType INSTANCE = new JsonNodeStringType();
public JsonNodeStringType() {
super(VarcharTypeDescriptor.INSTANCE, JsonNodeStringJavaDescriptor.INSTANCE);
}
@Override
public String getName() {
return &quot;jsonnode&quot;;
}
@Override
public JsonNode stringToObject(String xml) {
return fromString(xml);
}
@Override
public String objectToSQLString(JsonNode value, Dialect dialect) {
return &#39;\&#39;&#39; + toString(value) + &#39;\&#39;&#39;;
}
}

Then I created a Java Type Descriptor:

public class JsonNodeStringJavaDescriptor extends AbstractTypeDescriptor&lt;JsonNode&gt; {
public static final ObjectMapper mapper = new ObjectMapper();
public static final JsonNodeStringJavaDescriptor INSTANCE = new JsonNodeStringJavaDescriptor();
public JsonNodeStringJavaDescriptor() {
super(JsonNode.class, ImmutableMutabilityPlan.INSTANCE);
}
@Override
public String toString(JsonNode value) {
try {
return mapper.writeValueAsString(value);
} catch (JsonProcessingException e) {
throw new IllegalArgumentException(&quot;The given JsonNode object value: &quot; + value + &quot; cannot be transformed to a String&quot;, e);
}
}
@Override
public JsonNode fromString(String string) {
try {
return mapper.readTree(string);
} catch (JsonProcessingException e) {
throw new IllegalArgumentException(&quot;The given string value: &quot; + string + &quot; cannot be transformed to JsonNode object&quot;, e);
}
}
@Override
public &lt;X&gt; X unwrap(JsonNode value, Class&lt;X&gt; type, WrapperOptions options) {
if (value == null) {
return null;
}
if (String.class.isAssignableFrom(type)) {
return (X) toString(value);
}
throw unknownUnwrap(type);
}
@Override
public &lt;X&gt; JsonNode wrap(X value, WrapperOptions options) {
if (value == null) {
return null;
}
if (String.class.isInstance(value)) {
return fromString(value.toString());
}
throw unknownWrap(value.getClass());
}

Then add the type definition to the entity model

@Data
@TypeDef(name = &quot;jsonnode&quot;, typeClass = JsonNodeStringType.class)
public class GenericEntity implements Serializable {
@Id
@GeneratedValue(generator = &quot;uuid2&quot;)
@GenericGenerator(name = &quot;uuid2&quot;, strategy = &quot;org.hibernate.id.UUIDGenerator&quot;)
@Type(type = &quot;uuid-char&quot;)
private UUID id;
&lt;--Omitting extra properties --&gt;
@Type(type = &quot;jsonnode&quot;)
private JsonNode metadata;
}

If you don't want to manually create these type descriptors you can also follow this article to use a external dependency: https://vladmihalcea.com/sql-server-json-hibernate/

huangapple
  • 本文由 发表于 2020年8月19日 00:41:04
  • 转载请务必保留本文链接:https://go.coder-hub.com/63473056.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定