Petr Filaretov

Posted on Oct 1

Spring Boot Avengers: Uniting Spring Data JDBC and PostgreSQL's JSONB

#spring #springboot #kotlin #postgres

TL;DR

When working with Spring Data JDBC and a jsonb database column, you may face challenges in selecting the correct type for the jsonb property of the entity, implementing converters from/to database object, and defining Spring Data JDBC queries for jsonb nested properties.

Intro

I worked with Spring Data JDBC recently. The work involved the creation of an API for an entity with part of the data saved as JSON in the jsonb column in PostgreSQL.

There is little info regarding Spring Data JDBC (not Spring Data JPA) and jsonb in the wild. So, I will share my experience and some findings on the topic today.

We will build an API to create, read, update, and delete friends with superpowers.

The full source code is available on GitHub: pfilaretov42/spring-data-jdbc-jsonb.

Alright, let's go step-by-step.

Create a project

The first step is to create a new Spring Boot project in IntelliJ IDEA. Alternatively, you can use Spring Initializr website to do the same thing.

I'm going to choose Kotlin with JDK 21 and Gradle.

Then, we will need the following dependencies:

Spring Web - to build REST API.
Spring Data JDBC - to work with a database.
PostgreSQL Driver - PostgreSQL is our storage this time.
Liquibase Migration - to deal with database changes.

Create a database table

Now we have the basic project setup, so let's create a database table.

First of all, we will need a running PostgreSQL instance, e.g., in Docker. Here is a sample docker-compose YAML (located in ./docker/local-infra.yaml) to do this:

version: '3.9'
services:
  db:
    image: postgres:16.4-alpine3.20
    shm_size: 128mb
    environment:
      POSTGRES_PASSWORD: postgres
    ports:
      - "5432:5432"

And now you can start PostgreSQL with

docker-compose -f ./docker/local-infra.yaml up

Then, let's connect to the database with postgres/postgres user/password and create a database and a user:

create database spring_data_jdbc_jsonb;
create user spring_data_jdbc_jsonb with encrypted password 'spring_data_jdbc_jsonb';
grant all privileges on database spring_data_jdbc_jsonb to spring_data_jdbc_jsonb;
alter database spring_data_jdbc_jsonb owner to spring_data_jdbc_jsonb;

Now we will need to provide database connection details in application.yaml:

spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/spring_data_jdbc_jsonb
    username: spring_data_jdbc_jsonb
    password: spring_data_jdbc_jsonb
  liquibase:
    driver-class-name: org.postgresql.Driver
    change-log: db/changelog/changelog.xml
    url: ${spring.datasource.url}
    user: ${spring.datasource.username}
    password: ${spring.datasource.password}

Here, we also defined parameters for Liquibase. So, let's create the changelog.xml...

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd">

    <include file="db/changelog/changesets/0001-create-table.xml"/>

</databaseChangeLog>

...and add the first change set 0001-create-table.xml to create a table. The major part here is the createTable changeset:

<changeSet id="create table" author="pfilaretov42">
    <createTable tableName="friends">
        <column name="id" type="uuid" defaultValueComputed="uuid_generate_v4()">
            <constraints primaryKey="true" nullable="false"/>
        </column>
        <column name="full_name" type="varchar(255)">
            <constraints nullable="false"/>
        </column>
        <column name="alias" type="varchar(255)">
            <constraints nullable="false"/>
        </column>
        <column name="superpower" type="jsonb">
            <constraints nullable="false"/>
        </column>
    </createTable>
</changeSet>

The friends table has some basic fields (id, full_name, alias) and the jsonb field superpower, which will hold characteristics of the superpower in JSON format. Let's suppose the JSON structure is fixed here, e.g.:

{
  "abilities": [
    ...
  ],
  "weapon": [
    ...
  ],
  "rating": ...
}

Add some data

Now, we can add some data using Liquibase script 0002-add-data.xml. Here is one of the records:

INSERT INTO friends(full_name, alias, superpower)
VALUES ('Peter Parker',
        'Spider-Man',
        '{
          "abilities": [
            "Superhuman strength",
            "Precognitive spider-sense",
            "Ability to cling to solid surfaces"
          ],
          "weapon": [
            "web-shooters"
          ],
          "rating": 97
        }');

Alright, we are now ready to build the first API.

Get all friends

Let's start with the API to get all friends.

We will need a REST controller FriendsController...

@RestController
@RequestMapping("/api/v1/friends")
class FriendsController(
    private val friendsService: FriendsService,
) {

    @GetMapping
    fun getAll(): FriendsResponseDto = friendsService.getAll()
}

..., a service FriendsService for it...

@Service
class FriendsService(
    private val friendsRepository: FriendsRepository,
    private val friendsMapper: FriendsMapper,
) {
    fun getAll(): FriendsResponseDto {
        val entities = friendsRepository.findAll()
        return friendsMapper.toDto(entities)
    }
}

..., a Spring Data repository FriendsRepository...

interface FriendsRepository : CrudRepository<FriendsEntity, UUID> {
    override fun findAll(): List<FriendsEntity>
}

..., and the entity FriendsEntity:

@Table("friends")
class FriendsEntity(
    val id: UUID,
    val fullName: String,
    val alias: String,
    val superpower: String,
)

Here, we defined the superpower field simply as a String. Let's see if that works.

We will also need a mapper interface FriendsMapper to map from entity to DTO...

interface FriendsMapper {
    fun toDto(entities: List<FriendsEntity>): FriendsResponseDto
}

...with the implementation FriendsMapperImpl...

@Component
class FriendsMapperImpl(
    private val objectMapper: ObjectMapper,
) : FriendsMapper {
    override fun toDto(entities: List<FriendsEntity>) = FriendsResponseDto(
        friends = entities.map { entity ->
            FriendsFullResponseDto(
                id = entity.id,
                fullName = entity.fullName,
                alias = entity.alias,
                superpower = objectMapper.readValue(entity.superpower, FriendsSuperpowerDto::class.java)
            )
        }
    )
}

Note that we need to use objectMapper to map from the superpower string field in the entity to the superpower object field in DTO.

DTO classes are simple POJOs with nothing interesting inside, so I'll skip the code for them.

Okay, we have everything we need for now, so let's start the Spring Boot app and call the API to get all friends:

GET http://localhost:8080/api/v1/friends

What do we get back? HTTP 500 with the following error in the log:

ConverterNotFoundException: No converter found capable of converting from type [org.postgresql.util.PGobject] to type [java.lang.String]

Yup, looks like simply using the String type in FriendsEntity.superpower as a jsonb field holder does not work.

Fixing `ConverterNotFoundException`

What options do we have to fix ConverterNotFoundException? Let's start with the first thing that comes to mind. "No converter found"? No problem, we will add one! (actually, two)

To do this, we will need to add a Spring configuration that extends the AbstractJdbcConfiguration class:

@Configuration
class JdbcConfig(
    private val stringWritingConverter: StringWritingConverter,
    private val stringReadingConverter: StringReadingConverter,
) : AbstractJdbcConfiguration() {
    override fun userConverters(): MutableList<*> {
        return mutableListOf(
            stringWritingConverter,
            stringReadingConverter,
        )
    }
}

By overriding the userConverters() method, we provide custom converters for writing the String field to a jsonb database column...

@Component
@WritingConverter
class StringWritingConverter : Converter<String, PGobject> {
    override fun convert(source: String): PGobject {
        val jsonObject = PGobject()
        jsonObject.type = "jsonb"
        jsonObject.value = source
        return jsonObject
    }
}

...and reading from it:

@Component
@ReadingConverter
class StringReadingConverter : Converter<PGobject, String> {
    override fun convert(pgObject: PGobject): String? {
        return pgObject.value
    }
}

That's it. Start the app and call the API to get all friends:

GET http://localhost:8080/api/v1/friends

And now we have a list of friends with superpowers! 🎉

Fixing `ConverterNotFoundException` part 2, POJO

Okay, we have our API working now, thanks to string-to-pgobject converters. However, now we have two types of conversion:

StringReadingConverter to read data from the database into FriendsEntity
objectMapper.readValue() to convert string to object during mapping from entity to DTO in FriendsMapperImpl.toDto()

Since we know that the JSON structure in the superpower field is fixed, we can change the type of the FriendsEntity.superpower field from String to its own class SuperpowerEntity:

@Table("friends")
class FriendsEntity(
    // other fields are the same

    val superpower: SuperpowerEntity,
)

class SuperpowerEntity(
    val abilities: List<String>,
    val weapon: List<String>,
    val rating: Int,
)

And then we need a new @WritingConverter and @ReadingConverter to convert from/to SuperpowerEntity instead of String:

@Component
@WritingConverter
class SuperpowerEntityWritingConverter(
    private val objectMapper: ObjectMapper,
) : Converter<SuperpowerEntity, PGobject> {
    override fun convert(source: SuperpowerEntity): PGobject {
        val jsonObject = PGobject()
        jsonObject.type = "jsonb"
        jsonObject.value = objectMapper.writeValueAsString(source)
        return jsonObject
    }
}

@Component
@ReadingConverter
class SuperpowerEntityReadingConverter(
    private val objectMapper: ObjectMapper,
) : Converter<PGobject, SuperpowerEntity> {
    override fun convert(pgObject: PGobject): SuperpowerEntity {
        val source = pgObject.value
        return objectMapper.readValue(source, SuperpowerEntity::class.java)
    }
}

Now we can update JdbcConfig with these new converters:

@Configuration
class JdbcConfig(
    private val superpowerEntityWritingConverter: SuperpowerEntityWritingConverter,
    private val superpowerEntityReadingConverter: SuperpowerEntityReadingConverter,
) : AbstractJdbcConfiguration() {
    override fun userConverters(): MutableList<*> {
        return mutableListOf(superpowerEntityWritingConverter, superpowerEntityReadingConverter)
    }
}

And replace objectMapper.readValue() conversion in FriendsMapperImpl with a simple mapping to FriendsSuperpowerDto:

@Component
class FriendsMapperImpl : FriendsMapper {
    override fun toDto(entities: List<FriendsEntity>) = FriendsResponseDto(
        friends = entities.map { entity ->
            FriendsFullResponseDto(
                id = entity.id,
                fullName = entity.fullName,
                alias = entity.alias,
                superpower = toDto(entity.superpower),
            )
        }
    )

    private fun toDto(entity: SuperpowerEntity) = FriendsSuperpowerDto(
        abilities = entity.abilities,
        weapon = entity.weapon,
        rating = entity.rating,
    )
}

Start the app, call the API...

GET http://localhost:8080/api/v1/friends

...and it still works. Nice!

Fixing `ConverterNotFoundException` part 3, Map

FriendsEntity.superpower field is strongly typed since we rely on the fixed JSON structure. But what if we need a flexible JSON with different fields for different database records?

What do we do in any unclear situation? That's right, use the Map.

So, let's add FriendsEntity.extras field that can have anything inside:

@Table("friends")
class FriendsEntity(
    // other fields are the same

    val extras: Map<String, Any?>?,
)

Here is an example of an extras field value for Spider-Man:

{
  "species": "Human mutate",
  "publisher": "Marvel Comics",
  "createdBy": [
    "Stan Lee",
    "Steve Ditko"
  ]
}

We will also need a liquibase script 0003-add-extras.xml to add a column and update data:

<changeSet id="add extras column" author="pfilaretov42">
    <addColumn tableName="friends">
        <column name="extras" type="jsonb"/>
    </addColumn>
</changeSet>

<changeSet id="update data with extras" author="pfilaretov42">
    <update tableName="friends">
        <column name="extras" value='
            {
              "species": "Human mutate",
              "publisher": "Marvel Comics",
              "createdBy": [
                "Stan Lee",
                "Steve Ditko"
              ]
            }
        '/>
        <where>alias='Spider-Man'</where>
    </update>

    <!-- Some more updates here -->
</changeSet>

Okay, let's start the app and call the API:

GET http://localhost:8080/api/v1/friends

And we get HTTP 500:

IllegalArgumentException: Expected map like structure but found class org.postgresql.util.PGobject

Okay, we still need converters for the map.

Fixing `ConverterNotFoundException` part 4, Map with converter

To fix IllegalArgumentException we need to add @WritingConverter and @ReadingConverter to convert from/to Map...

@Component
@WritingConverter
class MapWritingConverter(
    private val objectMapper: ObjectMapper,
) : Converter<Map<String, Any?>, PGobject> {
    override fun convert(source: Map<String, Any?>): PGobject {
        val jsonObject = PGobject()
        jsonObject.type = "jsonb"
        jsonObject.value = objectMapper.writeValueAsString(source)
        return jsonObject
    }
}

@Component
@ReadingConverter
class MapReadingConverter(
    private val objectMapper: ObjectMapper,
) : Converter<PGobject, Map<String, Any?>> {
    override fun convert(pgObject: PGobject): Map<String, Any?> {
        val source = pgObject.value
        return objectMapper.readValue(source, object : TypeReference<Map<String, Any?>>() {})
    }
}

...and add them to JdbcConfig, so now it looks like this:

@Configuration
class JdbcConfig(
    private val superpowerEntityWritingConverter: SuperpowerEntityWritingConverter,
    private val superpowerEntityReadingConverter: SuperpowerEntityReadingConverter,
    private val mapWritingConverter: MapWritingConverter,
    private val mapReadingConverter: MapReadingConverter,
) : AbstractJdbcConfiguration() {
    override fun userConverters(): MutableList<*> {
        return mutableListOf(
            superpowerEntityWritingConverter,
            superpowerEntityReadingConverter,
            mapWritingConverter,
            mapReadingConverter,
        )
    }
}

We also need to add extras field to FriendsFullResponseDto...

class FriendsFullResponseDto(
    // other fields are the same

    val extras: Map<String, Any?>?,
)

...and update FriendsMapperImpl.toDto() to support the new field in DTO.

And then, you know the drill: start the app, call the API...

GET http://localhost:8080/api/v1/friends

..., and now it works.

Get a friend by ID

Alright, let's add an API to get a friend by ID. We will need to add an endpoint to FriendsController...

@GetMapping("/{id}")
fun get(@PathVariable("id") id: UUID): FriendsFullResponseDto = 
    friendsService.get(id)

...and add a method to FriendsService:

fun get(id: UUID): FriendsFullResponseDto {
    val entity = friendsRepository.findByIdOrNull(id)
        ?: throw FriendsNotFoundException("Cannot find friend with id=$id")
    return friendsMapper.toDto(entity)
}

Here entity is converted to DTO using a new method in FriendsMapperImpl:

override fun toDto(entity: FriendsEntity) = FriendsFullResponseDto(
    id = entity.id,
    fullName = entity.fullName,
    alias = entity.alias,
    superpower = toDto(entity.superpower),
    extras = entity.extras,
)

And we will also need an exception class to indicate that a friend is not found...

class FriendsNotFoundException(message: String) : RuntimeException(message)

...as well as an exception handler to return HTTP 404 when FriendsNotFoundException is thrown:

@RestControllerAdvice
class RestExceptionHandler : ResponseEntityExceptionHandler() {

    @ExceptionHandler
    fun handleNotFound(e: FriendsNotFoundException): ResponseEntity<Nothing> {
        return ResponseEntity.notFound().build()
    }
}

This is it. Let's start the app, find the id of the existing friend in the friends table and call the API with existing id:

GET http://localhost:8080/api/v1/friends/9463a880-4017-43fd-951e-233fd249091c

Result:

IllegalStateException: Required identifier property not found for class dev.pfilaretov42.spring.data.jdbc.jsonb.entity.FriendsEntity

Okay, spring cannot identify id property. We need to specify it with @Id annotation on FriendsEntity.id field:

@Id
val id: UUID,

One more time - start the app, call the API:

GET http://localhost:8080/api/v1/friends/9463a880-4017-43fd-951e-233fd249091c

And now it works. And if we call it with non-existent id:

GET http://localhost:8080/api/v1/friends/9463a880-0000-0000-0000-233fd249091c

The result is HTTP 404, as expected.

Create a friend

Let's move on and add an API to create a friend. We will need a new controller endpoint FriendsController.createFriend() with corresponding DTOs...

@PostMapping
fun createFriend(@RequestBody request: FriendsRequestDto): CreateFriendResponseDto =
    friendsService.create(request)

..., a service method FriendsService.create()...

@Transactional
fun create(request: FriendsRequestDto): CreateFriendResponseDto {
    val entity = friendsMapper.fromDto(request)
    val createdEntity = friendsRepository.save(entity)
    return CreateFriendResponseDto(createdEntity.id)
}

..., and a mapper method FriendsMapper.fromDto():

override fun fromDto(dto: FriendsRequestDto) = FriendsEntity(
    id = UUID.randomUUID(),
    fullName = dto.friend.fullName,
    alias = dto.friend.alias,
    superpower = fromDto(dto.friend.superpower),
    extras = dto.friend.extras,
)

Start the app and call the new API:

POST http://localhost:8080/api/v1/friends
Content-Type: application/json

{
  "friend": {
    "fullName": "Anthony Edward Stark",
    "alias": "Iron Man",
    "superpower": {
      "abilities": [
        "Genius-level intellect",
        "Proficient scientist and engineer"
      ],
      "weapon": [
        "Powered armor suit"
      ],
      "rating": 77
    },
    "extras": {
      "publisher": "Marvel Comics",
      "firstAppearance": {
        "comicBook": "Tales of Suspense #39",
        "year": 1963
      },
      "createdBy": [
        "Stan Lee",
        "Larry Lieber"
      ]
    }
  }
}

And the result is HTTP 500:

IncorrectUpdateSemanticsDataAccessException: Failed to update entity [dev.pfilaretov42.spring.data.jdbc.jsonb.entity.FriendsEntity@4081a76e]; Id [578f74ef-9721-4230-8be6-bc88f252c820] not found in database

Hmm, that's not what we expected. And here is what happened.

Entity id is generated in FriendsMapperImpl.fromDto() as UUID.randomUUID().

When CrudRepository.save() is called, it needs to understand whether this is create or update operation on entity parameter. To do this it checks @Id field on entity:

if @Id field is null, then it creates entity
if @Id field is not null, then
- if entity implements Persistable interface, it uses Persistable.isNew() method to choose between create and update operations
- if entity is not Persistable, then it updates entity

So, in our case, entity.id is not null and FriendsEntity is not Persistable. That's why the update operation was called instead of the create one that we expected, and it failed because there is no such record in the database.

Fixing `IncorrectUpdateSemanticsDataAccessException`

To fix the IncorrectUpdateSemanticsDataAccessException during entity creation, we have the choice:

Make sure FriendsEntity.id is null when we create a new entity. To do this, we need to make the FriendsEntity.id field nullable:

@Table("friends")
class FriendsEntity(
    @Id
    val id: UUID?,
    // ...
)

This is pretty simple approach, but the caveat is that we will need to deal with nullable id during conversion of entity to DTO. But the id should always be there for existing entity, shouldn't it?

Make FriendsEntity implement the Persistable interface. Then, we can leave the FriendsEntity.id field non-nullable and generate a new id during an object creation. Here is what FriendsEntity would look like:

@Table("friends")
class FriendsEntity(
    @Id
    val id: UUID,
    // ...
) : Persistable<UUID> {
    // ...
}

I will choose the second option, because I don't like to deal with nullable fields which cannot actually hold null values. So, here is our new FriendsEntity:

@Table("friends")
class FriendsEntity(
    @Id
    @Column("id")
    val uuid: UUID,
    // ...
) : Persistable<UUID> {

    @Transient
    var isNewEntity = false

    override fun getId(): UUID = uuid

    override fun isNew(): Boolean = isNewEntity
}

Here we have a transient field isNewEntity, which should be set to true during entity creation in FriendsService.create(). And then, the isNew() method will return true for a new entity, and we should be good during entity creation.

Also, note that we had to rename the id property to uuid. This is because the Persistable interface has a getId() method to implement, and it clashes with a generated getter for the id property.

Okay, let's start the app and call the create API once again:

POST http://localhost:8080/api/v1/friends
Content-Type: application/json

{
  "friend": {
    "fullName": "Anthony Edward Stark",
    "alias": "Iron Man",
    "superpower": {
      "abilities": [
        "Genius-level intellect",
        "Proficient scientist and engineer"
      ],
      "weapon": [
        "Powered armor suit"
      ],
      "rating": 77
    },
    "extras": {
      "publisher": "Marvel Comics",
      "firstAppearance": {
        "comicBook": "Tales of Suspense #39",
        "year": 1963
      },
      "createdBy": [
        "Stan Lee",
        "Larry Lieber"
      ]
    }
  }
}

Now it works and the entity is created.

Update a friend

Let's move on and add an API to update friends. We will need a controller endpoint FriendsController.updateFriend() with corresponding DTOs...

@PutMapping("/{id}")
@ResponseStatus(HttpStatus.NO_CONTENT)
fun updateFriend(@PathVariable("id") id: UUID, @RequestBody request: FriendsRequestDto) {
    friendsService.update(id, request)
}

...and a service method FriendsService.update():

@Transactional
fun update(id: UUID, request: FriendsRequestDto) {
    val entity = friendsMapper.fromDto(id, request)
    friendsRepository.save(entity)
}

Here we leave the FriendsEntity.isNewEntity flag with the default value of false during entity creation.

And we also need to add the id parameter to FriendsMapperImpl.fromDto():

if this is an update operation, FriendsEntity.uuid is set to existing ID
if this is a create operation, FriendsEntity.uuid is set to random UUID

override fun fromDto(id: UUID?, dto: FriendsRequestDto) = FriendsEntity(
    uuid = id ?: UUID.randomUUID(),
   // ...
)

Okay, let's start the app and call the update API:

PUT http://localhost:8080/api/v1/friends/85670f8f-aae7-4feb-aa9c-a61574e8b60f
Content-Type: application/json

{
  "friend": {
    "fullName": "Tony Stark",
    "alias": "Iron Man",
    "superpower": {
      "abilities": [
        "Genius-level intellect",
        "Proficient scientist and engineer"
      ],
      "weapon": [
        "Powered armor suit"
      ],
      "rating": 77
    },
    "extras": {
      "publisher": "Marvel Comics",
      "firstAppearance": {
        "comicBook": "Tales of Suspense #39",
        "year": 1963
      },
      "createdBy": [
        "Stan Lee",
        "Larry Lieber"
      ]
    }
  }
}

And the result is HTTP 204, as expected.

Delete a friend

Now, let's quickly add an API to delete friends, as there is nothing interesting in terms of jsonb here. We will need a controller endpoint FriendsController.deleteFriend()...

@DeleteMapping("/{id}")
@ResponseStatus(HttpStatus.NO_CONTENT)
fun deleteFriend(@PathVariable("id") id: UUID) =
    friendsService.delete(id)

...and a corresponding service method FriendsService.delete():

@Transactional
fun delete(id: UUID) =
    friendsRepository.deleteById(id)

That's it. Start the app and call the API:

DELETE http://localhost:8080/api/v1/friends/29663075-8ba3-468f-839b-63fefc5059ae

And the result is HTTP 204, as expected.

Search friends

Now we have a basic set of CRUD operations. So, it's time to add an API to search friends. Let's suppose we want to find all friends that have a superpower rating greater than 50. Remember where we store the superpower rating? It's in the FriendsEntity.superpower.rating integer field:

@Table("friends")
class FriendsEntity(
    val superpower: SuperpowerEntity,
    // ...
) : Persistable<UUID> {
    // ...
}

class SuperpowerEntity(
    val rating: Int,
    // ...
)

And superpower is the jsonb database column.

So, let's build Spring Data repository method to search by superpower rating:

interface FriendsRepository : CrudRepository<FriendsEntity, UUID> {
    fun findBySuperpowerRatingGreaterThan(rating: Int): List<FriendsEntity>

    // ...
}

Now we will need a controller endpoint FriendsController.getFriendsBySuperpowerRating()...

@GetMapping("/by-superpower")
fun getFriendsBySuperpowerRating(
    @RequestParam("rating") rating: Int,
    @RequestParam("operator") operator: ComparisonOperator,
): FriendsResponseDto =
    friendsService.getBySuperpowerRating(rating, operator)

..., enum ComparisonOperator...

enum class ComparisonOperator {
    GT, GTE, LT, LTE, BETWEEN
}

..., and a service method FriendsService.getBySuperpowerRating() that uses repository method FriendsRepository.findBySuperpowerRatingGreaterThan() created earlier:

fun getBySuperpowerRating(rating: Int, operator: ComparisonOperator): FriendsResponseDto {
    val entities = when (operator) {
        ComparisonOperator.GT -> friendsRepository.findBySuperpowerRatingGreaterThan(rating)
        else -> TODO("Not implemented yet")
    }
    return friendsMapper.toDto(entities)
}

Start the app and... it fails with the following error:

BeanCreationException: Error creating bean with name 'friendsRepository'
QueryCreationException: Could not create query for public abstract List findBySuperpower_RatingGreaterThan(int);

MappingException: Couldn't find PersistentEntity for property private final dev.pfilaretov42.spring.data.jdbc.jsonb.entity.SuperpowerEntity dev.pfilaretov42.spring.data.jdbc.jsonb.entity.FriendsEntity.superpower

Apparently, Spring Data JDBC cannot build a query based on the entity structure we have.

If we try another method name, e.g. findBySuperpower_RatingGreaterThan(int), the result is the same. I did not find a way to build the method name so that it works with the jsonb database column. Please let me know in the comments if it is possible.

Fixing `MappingException`

To fix the MappingException, we can define the query for FriendsRepository.findBySuperpowerRatingGreaterThan() method manually:

@Query("select * from friends where (superpower->>'rating')::NUMERIC > :rating")
fun findBySuperpowerRatingGreaterThan(rating: Int): List<FriendsEntity>

And now the app starts without errors. And if we call the API...

GET http://localhost:8080/api/v1/friends/by-superpower?rating=50&operator=GT

..., we will get the expected result.

Conclusion

Based on Spring Boot and Spring Data JDBC, we built the API to create, update, delete, and search for friends that hold some data as a PostgreSQL jsonb column. Challenges we faced during the API implementation included:

selecting the correct type for the jsonb property of the entity (FriendsEntity.superpower, FriendsEntity.extras)
implementing converters from/to PGobject
defining Spring Data JDBC queries for jsonb nested properties (FriendsEntity.superpower.rating)

We also looked at the CrudRepository.save() method's logic for create and update operations and the choices we have to implement them properly.

Dream your code, code your dream.

DEV Community

Spring Boot Avengers: Uniting Spring Data JDBC and PostgreSQL's JSONB

TL;DR

Intro

Create a project

Create a database table

Add some data

Get all friends

Fixing `ConverterNotFoundException`

Fixing `ConverterNotFoundException` part 2, POJO

Fixing `ConverterNotFoundException` part 3, Map

Fixing `ConverterNotFoundException` part 4, Map with converter

Get a friend by ID

Create a friend

Fixing `IncorrectUpdateSemanticsDataAccessException`

Update a friend

Delete a friend

Search friends

Fixing `MappingException`

Conclusion

Top comments (0)

Read next

Day 68-70. Theme switcher

H2 и весёлые тесты в Spring

Enhance iOS Development with Kotlin Multiplatform Library

Kotlin Infix Functions vs. Java: A Grammatical Twist (Where Kotlin Breaks the Rules!)

TL;DR

Intro

Create a project

Create a database table

Add some data

Get all friends

Fixing ConverterNotFoundException

Fixing ConverterNotFoundException part 2, POJO

Fixing ConverterNotFoundException part 3, Map

Fixing ConverterNotFoundException part 4, Map with converter

Get a friend by ID

Create a friend

Fixing IncorrectUpdateSemanticsDataAccessException

Update a friend

Delete a friend

Search friends

Fixing MappingException

Conclusion

Read next

Day 68-70. Theme switcher

H2 и весёлые тесты в Spring

Enhance iOS Development with Kotlin Multiplatform Library

Kotlin Infix Functions vs. Java: A Grammatical Twist (Where Kotlin Breaks the Rules!)

Fixing `ConverterNotFoundException`

Fixing `ConverterNotFoundException` part 2, POJO

Fixing `ConverterNotFoundException` part 3, Map

Fixing `ConverterNotFoundException` part 4, Map with converter

Fixing `IncorrectUpdateSemanticsDataAccessException`

Fixing `MappingException`