I don't understand how to save an entity to the database using jsonb and Spring Data JDBC

Question

I have an entity in my Spring Boot application:

@Data
@AllArgsConstructor
@Table(name = "my_files")
public class myFile {

@Id private Long id;

private String content;
}

Here is the table that corresponds to this entity:

create table if not exists my_files

(
id bigint not null primary key,
content jsonb
);

The problem is that when I try to put the entity into the database, the following error occurs:

Caused by: org.postgresql.util.PSQLException: ERROR: column "file_data" is of type jsonb but expression is of type character varying
Hint: You will need to rewrite or cast the expression.

I understand that I need to convert a string to jsonb. I found several guides on how to do this for Hibernate, but I am using Spring Data JDBC in my project and can't figure out how to solve this problem. Please help me find a solution.

I tried to find a solution, but they are all applicable to Hibernate.

Answer 1

If you are using PostgreSQL and Hikari datasource pool add this property:

spring.datasource.hikari.data-source-properties=stringtype=unspecified

The property is for PostgreSQL only.

I understand that you want to do it with JDBC only. But - just for the record - let me show how easy it would be with Hibernate (part of Spring Boot Data JPA).

Add this annotation to the field definition in the entity:

import org.hibernate.annotations.JdbcTypeCode;
import org.hibernate.type.SqlTypes;
// ...
    @JdbcTypeCode(SqlTypes.JSON)
    private String content;

This would work with any database with JSON data type (like PostgreSQL, H2, Oracle, MySQL).

Please note: content need to be a valid JSON String representation. For example:

    content = "{}"