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
– scottb, Commented Aug 27, 2015 at 0:08
A similar question has been answered. Find the link below: stackoverflow.com/a/47550896/9729935 — Akintunde
– Akintunde, Commented Feb 26, 2022 at 15:21

Craig Ringer · Accepted Answer · 2015-08-27 00:33:22Z

21

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, 2015 at 0:33

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

drunkenfist Over a year ago

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 Over a year ago

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 Over a year ago

Ok, looks like Spring-boot offers a spring.datasource.connection-properties which we can use to add any additional parameters. Thank you.

Tarion Over a year ago

Very good answer, especially together with the comments. I would like to see the answer extended with the information from the comments.

Alexey Rogulin Over a year ago

spring.datasource.connection-properties no longer works. This one works for me: spring.datasource.tomcat.connection-properties

|

abbas · Accepted Answer · 2020-03-18 08:28:14Z

13

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

answered Mar 18, 2020 at 8:28

abbas

7,2194 gold badges43 silver badges39 bronze badges

Comments

Ravi raj · Accepted Answer · 2020-07-15 19:36:12Z

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, 2020 at 19:36

Ravi raj

1361 gold badge1 silver badge8 bronze badges

1 Comment

nilfalse Over a year ago

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.

Akram · Accepted Answer · 2020-12-24 10:48:14Z

1

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

answered Dec 24, 2020 at 10:48

Akram

665 bronze badges

Collectives™ on Stack Overflow

Writing to JSON column of Postgres database using Spring / JPA

4 Answers 4

12 Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

12 Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related