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 {
@Id
@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
The cause of the error ERROR: column “metadata” is of type jsonb but expression is of type bytea Hint: You will need to rewrite or cast the expression. if you are executing a native SQL DML statement.
The JSONB data type stores JSON (JavaScript Object Notation) data as a binary representation of the JSONB value, which eliminates whitespace, duplicate keys, and key ordering. JSONB supports GIN indexes.
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 {
@Id
@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.
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)";
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