How to save java object in postgres jsonb column – Postgresql

If you using Postgres, there are two approaches you can use to map to Postgres JSON/JSONB fields.

1. use Postgres R2dbc Json type directly in Java entity class.
2. use any Java type and convert it between Json via registering custom converters.

The first one is simple and stupid.

1. Declare a json db type field, eg.

   metadata   JSON         default '{}'
   

2. Declare the Json type field in your entity class.

   class Post{
        @Column("metadata")
        private Json metadata;
   }
   

For the second the solution, similarly

1. Declare json/jsonb db type in the schema.sql.

2. Declare the field type as your custom type.eg.

   class Post{
        @Column("statistics")
        private Statistics statistics;
   
   
       record Statistics(
            Integer viewed,
            Integer bookmarked
       ) {}
   }
   

3. Declare a R2dbcCustomConversions bean.

    @Configuration
    class DataR2dbcConfig {
        @Bean
        R2dbcCustomConversions r2dbcCustomConversions(ConnectionFactory factory, ObjectMapper objectMapper) {
            R2dbcDialect dialect = DialectResolver.getDialect(factory);
            return R2dbcCustomConversions
                    .of(
                            dialect,
                            List.of(
                                    new JsonToStatisticsConverter(objectMapper),
                                    new StatisticsToJsonConverter(objectMapper)
                            )
                    );
        }
   
    }
   
    @ReadingConverter
    @RequiredArgsConstructor
    class JsonToStatisticsConverter implements Converter<Json, Post.Statistics> {
        private final ObjectMapper objectMapper;
   
        @SneakyThrows
        @Override
        public Post.Statistics convert(Json source) {
            return objectMapper.readValue(source.asString(), Post.Statistics.class);
        }
    }
   
    @WritingConverter
    @RequiredArgsConstructor
    class StatisticsToJsonConverter implements Converter<Post.Statistics, Json> {
        private final ObjectMapper objectMapper;
   
        @SneakyThrows
        @Override
        public Json convert(Post.Statistics source) {
            return Json.of(objectMapper.writeValueAsString(source));
        }
    }
   
   

The example codes is here.

Finally verify it with a @DataR2dbcTest test.

@Test
public void testInsertAndQuery() {
        var data = Post.builder()
                .title("test title")
                .content("content of test")
                .metadata(Json.of("{"tags":["spring","r2dbc"]}"))
                .statistics(new Post.Statistics(1000, 200))
                .build();
        this.template.insert(data)
                .thenMany(
                        this.posts.findByTitleContains("test%")
                )
                .log()
                .as(StepVerifier::create)
                .consumeNextWith(p -> {
                            log.info("saved post: {}", p);
                            assertThat(p.getTitle()).isEqualTo("test title");
                        }
                )
                .verifyComplete();

}