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

user_816813 0
Viewed 5117

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

spring-data-jdbc spring-data spring-boot jdbc postgresql
1 Answers

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
);
user_8109440
Related