6

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

Santhos Ramalingam
  • 1,188
  • 10
  • 11
  • Does this answer your question? [Question about Spring Data JDBC + Hikari + Postgres JSONB](https://stackoverflow.com/questions/53392146/question-about-spring-data-jdbc-hikari-postgres-jsonb) – Jens Schauder Oct 26 '20 at 15:10

1 Answers1

8

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
);
einsA
  • 797
  • 1
  • 7
  • 19
  • 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 Dec 15 '20 at 09:28
  • 1
    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. – RomanMitasov Dec 15 '20 at 19:23
  • @RomanMitasov, how have you actually made spring-data-jdbc treat an entity as a simple type? – Kirill Aug 09 '21 at 16:02
  • @Kirill registering `@WritingConverter` for entity type makes spring-data-jdbc treat that type as simple. I've mentioned that in previous comment. – RomanMitasov Aug 11 '21 at 12:33
  • 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. – Kirill Aug 12 '21 at 13:15