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.
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.
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
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With