How to read/write postgres jsonb type with spring-data-jdbc?

Question

I'm trying to use the following with spring-data-jdbc and postgres driver (kotlin),

data class MyEntity(
 val id: UUID,
 val content: String
)

using a string fails with the following error,

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

I'm not sure how I can use the converter for String -> jsonb

• Jens Schauder: Does this answer your question? Question about Spring Data JDBC + Hikari + Postgres JSONB

Answer 1

With the help of this I implemented the following solution:

1. introduce a own class (POJO / kotlin data class) which holds the structure of the json-document (say MyContent)
2. implement a writing and a reading converter which converts the POJO into a String (say with the help of jackson)
3. specifiy the postgres column type with the help of org.postgresql.util.PGobject

Details (only pseudo code):

import org.springframework.core.convert.converter.Converter
import org.springframework.data.convert.ReadingConverter
import org.springframework.data.convert.WritingConverter
import com.fasterxml.jackson.databind.ObjectMapper
import org.postgresql.util.PGobject
@Table("my_entity")
data class MyEntity(
 @Id val id: UUID,
 val content: MyContent
) {
 @WritingConverter
 class EntityWritingConverter(
 private val objectMapper: ObjectMapper
 ) : Converter<MyContent, PGobject> {
 override fun convert(source: MyContent): PGobject? {
 val jsonObject = PGobject()
 jsonObject.type = "json"
 jsonObject.value = objectMapper.writeValueAsString(source)
 return jsonObject
 }
 }
 @ReadingConverter
 class EntityReadingConverter(
 private val objectMapper: ObjectMapper
 ) : Converter<PGobject, MyContent> {
 override fun convert(pgObject: PGobject): MyContent {
 val source = pgObject.value
 return objectMapper.readValue<MyContent>(source)
 }
 }
}

Don't forget to add the two converters to spring-data-jdbcs configuration (see JdbcCustomConversions).

This works with this corresponding postgres table:

create table if not exists my_entity
(
 id uuid not null primary key,
 content jsonb
);

• RomanMitasov: In my case this solution does not work. Spring treats custom POJO as related entity and makes query with JOIN my_content .... I did everything according to your advice, registered custom converter as it's described in Spring docs and still this does not work. Maybe you have any ideas what could be wrong?
• RomanMitasov: For the record. It is important to have both @ReadingConverter and @WritingConverter to make spring-data-jbdc treat MyContent as simple type. Otherwise sping-data will generate SQL with JOIN my_content part. More than that, in fact, only @WritingConverter counts, as inside spring-data it is used for registration of custom simple types.
• Kirill: @RomanMitasov, how have you actually made spring-data-jdbc treat an entity as a simple type?
• RomanMitasov: @Kirill registering @WritingConverter for entity type makes spring-data-jdbc treat that type as simple. I've mentioned that in previous comment.
• Kirill: Thanks, @RomanMitasov, you're correct. My problem turned out to be different: as soon as you try to use in an auto-generated repository method a field from an object that is stored as JSONB, spring-data-jdbc seems to be refusing to consider this object a simple type anymore. Otherwise what you've written above works nice. I wasn't able to "fix" this, so I resorted to custom queries.