将postgreSQL JSON列映射到Hibernate值类型 [英] Mapping postgreSQL JSON column to Hibernate value type

问题描述

我在postgreSQL DB（9.2）中有一个json类型的表。我很难将此列映射到JPA2实体字段类型。

我试图使用String，但是当我保存实体时，我得到一个异常，它无法将字符转换为json。

处理JSON列时使用的正确值类型是什么？

  @Entity 
 public class MyEntity {
 
 private String jsonPayload; //这个映射到一个json列
 
 public MyEntity（）{
} 
} 
  

一个简单的解决方法是定义一个文本列。

解决方案

请参阅PgJDBC错误＃265 。

PostgreSQL对数据类型转换过分严格。它不会隐式地将 text 转换成类似文本的值，例如 xml 和 json 。

解决此问题的严格正确方法是编写一个使用JDBC setObject的自定义Hibernate映射类型方法。这可能会有点麻烦，所以你可能只想通过创建一个较弱的演员来减少PostgreSQL。

正如@markdsievers在评论和<本博文中的原始解决方案，回答绕过JSON验证。所以这不是你想要的。编写以下代码比较安全：

 创建或替换函数json_intext（text）RETURNS json AS $$ 
 SELECT json_in（$ 1 :: CString的）; 
 $$ LANGUAGE SQL IMMUTABLE; 
 
 CREATE CAST（text AS json）WITH FUNCTION json_intext（text）AS IMPLICIT; 
  

AS IMPLICIT 告诉PostgreSQL它可以转换被明确告知，允许这样的事情工作：

  regress =＃CREATE TABLE jsontext（x json）; 
 CREATE TABLE 
 regress =＃PREPARE test（text）AS INSERT INTO jsontext（x）VALUES（$ 1）; 
 PREPARE 
 regress =＃EXECUTE test（'{}'）
 INSERT 0 1 
  

感谢@markdsievers指出问题。

I have a table with a column of type json in my postgreSQL DB (9.2). I have a hard time to map this column to a JPA2 Entity field type.

I tried to use String but when I save the entity I get an exception that it can't convert character varying to json.

What is the correct value type to use when dealing with a JSON column?

@Entity
public class MyEntity {

    private String jsonPayload; // this maps to a json column

    public MyEntity() {
    }
}

A simple workaround would be to define a text column.

解决方案

See PgJDBC bug #265.

PostgreSQL is excessively, annoyingly strict about data type conversions. It won't implicitly cast text even to text-like values such as xml and json.

The strictly correct way to solve this problem is to write a custom Hibernate mapping type that uses the JDBC setObject method. This can be a fair bit of hassle, so you might just want to make PostgreSQL less strict by creating a weaker cast.

As noted by @markdsievers in the comments and this blog post, the original solution in this answer bypasses JSON validation. So it's not really what you want. It's safer to write:

CREATE OR REPLACE FUNCTION json_intext(text) RETURNS json AS $$
SELECT json_in($1::cstring); 
$$ LANGUAGE SQL IMMUTABLE;

CREATE CAST (text AS json) WITH FUNCTION json_intext(text) AS IMPLICIT;

AS IMPLICIT tells PostgreSQL it can convert without being explicitly told to, allowing things like this to work:

regress=# CREATE TABLE jsontext(x json);
CREATE TABLE
regress=# PREPARE test(text) AS INSERT INTO jsontext(x) VALUES ($1);
PREPARE
regress=# EXECUTE test('{}')
INSERT 0 1

Thanks to @markdsievers for pointing out the issue.

这篇关于将postgreSQL JSON列映射到Hibernate值类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！