使用Spring/JPA写入Postgres数据库的JSON列 [英] Writing to JSON column of Postgres database using Spring / JPA

问题描述

我有一个名为"test"的表，其中包含Postgres 9.3中json类型的"sample_column"列.我正在尝试使用Spring/JPA将以下内容写入该列:{"name":"Updated name"}

I have a table called "test" containing a column "sample_column" of type json in Postgres 9.3. I'm trying to write the following contents into the column using Spring / JPA: {"name":"Updated name"}

我阅读了其他文章，我需要添加一个自定义转换器以将字符串映射到json类型.这是我现在拥有的代码:

I read on other posts that I need to add a custom converter to map the string to json type. This is the code I have now:

TestDAO.java:

TestDAO.java:

@Entity
@Table(name="test")
public class TestDAO implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name="id", unique=true, nullable=false)
    private Long id;   

    @Column(name="sample_column")
    @Convert(converter = MyCustomConverter.class)
    private MyCustomClass sampleColumn;

    // Getter / Setters
}

用于映射json内容的CustomClass:

The CustomClass for mapping the json content:

public class MyCustomClass {
    @JsonProperty("name")
    public String name;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

}

最后是ConverterClass:

And finally, the ConverterClass:

@javax.persistence.Converter
public class MyCustomConverter implements AttributeConverter<MyCustomClass, String> {

    private final static ObjectMapper objectMapper = new ObjectMapper();

    @Override
    @NotNull
    public String convertToDatabaseColumn(@NotNull MyCustomClass myCustomObject) {
        try {
            return objectMapper.writeValueAsString(myCustomObject);
        } catch (Exception ex) {
            return null;
        }
    }

    @Override
    @NotNull
    public MyCustomClass convertToEntityAttribute(@NotNull String databaseDataAsJSONString) {
        try {
            return objectMapper.readValue(databaseDataAsJSONString, MyCustomClass.class);
        } catch (Exception ex) {
            return null;
        }
    }
}

现在，我正尝试将json列设置如下:

Now, I'm trying to set the json column as follows:

testDAO.getSampleColumn().setName("Updated name");
testRepository.saveAndFlush(testDAO);

但是当我尝试保存它时，出现以下错误:

But when I try to save it, I get the following error:

Caused by: org.postgresql.util.PSQLException: ERROR: column "sample_column" is of type json but expression is of type character varying
  Hint: You will need to rewrite or cast the expression.

但是，我可以使用testDAO.getSampleColumn().getName();读取JSON列 这里有什么问题?我不想在表中添加任何类型转换以自动将Varying转换为Json.

However, I am able to read the JSON column using testDAO.getSampleColumn().getName(); What is the problem here? I do not want to add any cast to the table for converting Varying to Json automatically.

谢谢.

推荐答案

您将需要在JDBC级别使用setObject，或者将PgJDBC参数stringtype=unspecified传递给允许从字符串类型隐式转换为等.

You'll need to either use setObject at the JDBC level, or pass the PgJDBC parameter stringtype=unspecified to allow implicit casts from string types to json etc.

PostgreSQL对类型转换过于严格是一个问题.

It's a problem with PostgreSQL being too strict about type casting.

这篇关于使用Spring/JPA写入Postgres数据库的JSON列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！