将 PostgreSQL JSON 列映射到 Hibernate 实体属性

本文介绍了将 PostgreSQL JSON 列映射到 Hibernate 实体属性的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我的 PostgreSQL 数据库 (9.2) 中有一个包含 JSON 类型列的表.我很难将此列映射到 JPA2 实体字段类型.

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.

我尝试使用字符串，但是当我保存实体时，我收到一个异常，即它无法将不同的字符转换为 JSON.

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

处理 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.

推荐答案

参见 PgJDBC 错误 #265.

PostgreSQL 对数据类型转换过于严格，令人厌烦.它不会隐式地将 text 转换为类似文本的值，例如 xml 和 json.

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.

解决这个问题的严格正确的方法是编写一个使用JDBC setObject 方法的自定义Hibernate 映射类型.这可能有点麻烦，因此您可能只想通过创建较弱的强制转换来降低 PostgreSQL 的严格性.

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.

正如@markdsievers 在评论和 这篇博文，这个答案中的原始解决方案绕过了 JSON 验证.所以这不是你想要的.写起来更安全:

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 告诉 PostgreSQL 它可以在没有被明确告知的情况下进行转换，允许这样的事情工作:

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

感谢@markdsievers 指出问题.

Thanks to @markdsievers for pointing out the issue.