21

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.

Improve this question
2
  • What is your JPA provider and specification? Are you certain that your provider is JPA 2.1 compliant? Converters require JPA 2.1. Commented Aug 27, 2015 at 0:08
  • A similar question has been answered. Find the link below: stackoverflow.com/a/47550896/9729935 Commented Feb 26, 2022 at 15:21

4 Answers 4

Reset to default
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.

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

12 Comments

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?
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.
Ok, looks like Spring-boot offers a spring.datasource.connection-properties which we can use to add any additional parameters. Thank you.
Very good answer, especially together with the comments. I would like to see the answer extended with the information from the comments.
spring.datasource.connection-properties no longer works. This one works for me: spring.datasource.tomcat.connection-properties
|
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
Improve this answer

Comments

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.

Improve this answer

1 Comment

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.
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();
    }
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.