Writing to JSON column of Postgres database using Spring / JPA

Question

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"}

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:

@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
}

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;
    }

}

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;
        }
    }
}

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.

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.

Thanks.

Answer 1

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.

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

Answer 2

For people using Spring-boot there are two ways to do what @Craig Ringer said

spring.datasource.url=jdbc:postgresql://localhost:5432/postgres?stringtype=unspecified

or using properties

spring.datasource.hikari.data-source-properties.stringtype=unspecified

Answer 3

Though both works, I suggest use the PGobject feature rather setting the feature at the connection level.

final ObjectMapper objectMapper = new ObjectMapper();
PGobject languageObject = new PGobject();
languageObject.setType("json");
languageObject.setValue(objectMapper.writeValueAsString(blogPosts.getLanguages()));

Once done, pass the params to the Spring jdbctemplate to do the magic

Hope this.