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.

What is your JPA provider and specification? Are you certain that your provider is JPA 2.1 compliant? Converters require JPA 2.1. — scottb, Aug 27 '15 at 00:08
A similar question has been answered. Find the link below: https://stackoverflow.com/a/47550896/9729935 — Akintunde, Feb 26 '22 at 15:21

score 20 · Accepted Answer · answered Aug 27 '15 at 00:33

20

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.

answered Aug 27 '15 at 00:33

Craig Ringer

307,061
76
688
778

1

Where should I be using `setObject()`? Sorry, I'm new to JPA. Can you provide some sample code? Or at least point me to some resources? – drunkenfist Aug 27 '15 at 05:52
@drunkenfist You probably have to write a custom type handler for the underlying persistence implementation like Hibernate. I suggest using the jdbc driver parameter instead. – Craig Ringer Aug 27 '15 at 05:54
7

I'm using Spring-boot, where I set all DB properties in application.properties file. Is there a specific property I can use to set `stringtype=unspecified`? Right now, I'm just adding it as a param to `spring.datasource.url` (i.e) `spring.datasource.url=jdbc:postgresql://localhost:5432/dbname?stringtype=unspecified`. Doing this works. But I do not want to add it to the datasource url. I would prefer if it was a separate property. – drunkenfist Aug 27 '15 at 06:19
1

@drunkenfist That depends on spring boot really. If it offers you access to other jdbc driver options like 'user' and 'password' you should be able to set it in the same place. – Craig Ringer Aug 27 '15 at 06:55
9

Ok, looks like Spring-boot offers a `spring.datasource.connection-properties` which we can use to add any additional parameters. Thank you. – drunkenfist Aug 27 '15 at 15:53
5

Very good answer, especially together with the comments. I would like to see the answer extended with the information from the comments. – Tarion Nov 11 '15 at 23:39
1

What holds me back from doing this: What are the downsides? I'm sure this setting has many, probably undesired, side effects. – Michel Jung Dec 21 '16 at 11:57
few actually. possibly some increased risk of failure to resolve overloaded functions. not much else. – Craig Ringer Dec 21 '16 at 12:47
2

`spring.datasource.connection-properties` [no longer works](http://stackoverflow.com/questions/40250678/set-jpa-utf-8-encoding-in-application-properties/40255073#comment67774997_40255073). This one works for me: `spring.datasource.tomcat.connection-properties` – Alexey Rogulin Apr 19 '17 at 22:48
Im confused. How do I pass the PgJDBC parameter `stringtype=unspecified`? Do I set it in my model class like this: `@Column(name = "blob") @Convert(stringtype=json) private String blob;` – Stephen Kuehl May 30 '17 at 19:48
@StephenKuehl It's a JDBC connection parameter, for the JDBC URL. – Craig Ringer May 31 '17 at 01:58
The DB URL trick works, while Spring boot properties does not. Thanks – WesternGun Feb 04 '19 at 09:21

score 12 · Answer 2 · answered Mar 18 '20 at 08:28

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

score 3 · Answer 3 · answered Jul 15 '20 at 19:36

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.

answered Jul 15 '20 at 19:36

Ravi raj

136
1
1
7

1

Is there any way to use this approach within `convertToDatabaseColumn()` as per the author's question? I'm new to JPA and have been struggling with a similar issue. If I just blindly put this in `convertToDatabaseColumn` Postgres starts complaining about `bytea` instead. – nilfalse Oct 25 '20 at 18:53

score 1 · Answer 4 · answered Dec 24 '20 at 10:48

You need to convert your JSON to String and then create a PGobject out of it. This solution works for me:

PGobject pGobject = new PGobject();
pGobject.setType("json");
pGobject.setValue(vehicleJsonData);

    try {
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(connection -> {
            PreparedStatement ps = connection.prepareStatement(SQL_CREATE, Statement.NO_GENERATED_KEYS);
            ps.setLong(1, id);
            ps.setObject(2, pGobject);
            return ps;
        }, keyHolder);

    } catch (Exception e) {
        e.printStackTrace();
    }

Writing to JSON column of Postgres database using Spring / JPA

4 Answers4

Linked