Collectives™ on Stack Overflow
Find centralized, trusted content and collaborate around the technologies you use most.
Learn more about Collectives
Teams
Q&A for work
Connect and share knowledge within a single location that is structured and easy to search.
Learn more about Teams
I need to map two columns of entity class as json in postgres using spring data jpa. After reading multiple stackoverflow posts and baeldung post ,
How to map a map JSON column to Java Object with JPA
https://www.baeldung.com/hibernate-persist-json-object
I did configuration as below. However, I am facing error "
ERROR: column "headers" is of type json but expression is of type character varying
"
Please provide some pointer to resolve this issue.
I have an entity class as below
@Entity
@Data
@SuperBuilder
@NoArgsConstructor
@AllArgsConstructor
public class Task {
@GeneratedValue(strategy = IDENTITY)
private Integer id;
private String url;
private String httpMethod;
@Convert(converter = HashMapConverter.class)
@Column(columnDefinition = "json")
private Map<String, String> headers;
@Convert(converter = HashMapConverter.class)
@Column(columnDefinition = "json")
private Map<String, String> urlVariables;
I have created a test class to test if entity is persisted or not. On running this junit, below test case is failing with error as below
@SpringBootTest
class TaskRepositoryTest {
private static Task randomTask = randomTask();
@Autowired
private TaskRepository taskRepository;
@BeforeEach
void setUp() {
taskRepository.deleteAll();
taskRepository.save(randomTask);
public static Task randomTask() {
return randomTaskBuilder().build();
public static TaskBuilder randomTaskBuilder() {
Map<String,String> headers = new HashMap<>();
headers.put(randomAlphanumericString(10),randomAlphanumericString(10));
Map<String,String> urlVariables = new HashMap<>();
urlVariables.put(randomAlphanumericString(10),randomAlphanumericString(10));
return builder()
.id(randomPositiveInteger())
.httpMethod(randomAlphanumericString(10))
.headers(headers)
.urlVariables(urlVariables)
.url(randomAlphanumericString(10)));
Using liquibase, I have created table in postgres DB and I could see column datatype as json.
databaseChangeLog:
- changeSet:
id: 1
author: abc
changes:
- createTable:
tableName: task
columns:
- column:
name: id
type: int
autoIncrement: true
constraints:
primaryKey: true
- column:
name: url
type: varchar(250)
constraints:
nullable: false
unique: true
- column:
name: http_method
type: varchar(50)
constraints:
nullable: false
- column:
name: headers
type: json
- column:
name: url_variables
type: json
rollback:
- dropTable:
tableName: task
–
–
–
–
For anyone who landed here because they're using JdbcTemplate and getting this error, the solution is very simple: In your SQL statement, cast the JSON argument using ::jsonb.
E.g. String INSERT_SQL = "INSERT INTO xxx (id, json_column) VALUES(?, ?)";
becomes String INSERT_SQL = "INSERT INTO xxx (id, json_column) VALUES(?, ?::jsonb)";
–
Above configuration did not work.
Hence, I followed below link to solve the use-case
https://vladmihalcea.com/how-to-map-json-objects-using-generic-hibernate-types/
"Provider com.fasterxml.jackson.module.jaxb.JaxbAnnotationModule not found" after Spring Boot Upgrade
Added additional dependencies in pom.xml
<dependency>
<groupId>com.fasterxml.jackson.module</groupId>
<artifactId>jackson-module-jaxb-annotations</artifactId>
</dependency>
<dependency>
<groupId>com.vladmihalcea</groupId>
<artifactId>hibernate-types-52</artifactId>
<version>2.9.11</version>
</dependency>
Removed HashMapConverter configuration and made below changes in entity class
@Entity
@Data
@SuperBuilder
@NoArgsConstructor
@AllArgsConstructor
@ToString(callSuper = true)
@TypeDefs({
@TypeDef(name = "json", typeClass = JsonStringType.class),
@TypeDef(name = "jsonb", typeClass = JsonBinaryType.class)
public class Task {
@GeneratedValue(strategy = IDENTITY)
private Integer id;
private String url;
private String httpMethod;
@Type(type = "jsonb")
@Column(columnDefinition = "json")
private Map<String, String> headers;
@Type(type = "jsonb")
@Column(columnDefinition = "json")
private Map<String, String> urlVariables;
After these changes, TaskRepositoryTest passed.
I ran into this issue when I migrated my projects from MySQL 8.0.21 to Postgres 13. My project uses Spring boot with the Hibernate types dependency version 2.7.1. In my case the solution was simple.
All I needed to do was change that and it worked.
Referenced from the Hibernate Types Documentation page.
Alternative solution using org.hibernate.annotations.ColumnTransformer
Sample entity
import org.hibernate.annotations.ColumnTransformer;
@Entity
@Table
public class SomeEntity {
@GeneratedValue(strategy = GenerationType.SEQUENCE)
private Long id;
@Column(name = "some_class", columnDefinition = "jsonb")
@Convert(converter = SomeClassConvertor.class)
@ColumnTransformer(write = "?::jsonb")
private SomeClass someClass;
Sample converter
import java.io.IOException;
import javax.persistence.AttributeConverter;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import com.airtel.africa.entity.SomeClass;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import lombok.extern.slf4j.Slf4j;
@Slf4j
public class SomeClassConvertor implements AttributeConverter<SomeClass, String> {
@Autowired
ObjectMapper objectMapper;
@Override
public String convertToDatabaseColumn(SomeClass someClass) {
String someClassJson = null;
try {
someClassJson = objectMapper.writeValueAsString(someClass);
} catch (final JsonProcessingException e) {
log.error("JSON writing error", e);
return someClassJson;
@Override
public SomeClass convertToEntityAttribute(String someClassJSON) {
SomeClass someClass = null;
if (StringUtils.isBlank(someClassJSON)) {
return someClass;
try {
someClass = objectMapper.readValue(someClassJSON, someClass.class);
} catch (final IOException e) {
log.error("JSON reading error", e);
return someClass;
(for posterity) There is an alternative solution, not totally safe (automatic cast from/to string), but should be just fine for non-critical or data/structures you control:
add ?stringtype=unspecified to your postgresql connectionstring:
for example:
(in your application.yml)
spring:
datasource:
url: jdbc:postgresql://localhost:5432/dbname?stringtype=unspecified
this will make postgresql try to cast your string to json automatically.
No need for any extra imports or @type definitions or custom sql-queries, the example as in the question is all you need:
@Convert(converter = HashMapConverter.class)
@Column(columnDefinition = "json")
private Map<String, String> headers;
PPS: I have not tried this with jsonb or other databasevendors.
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
