DEV Community

Cover image for Spring Boot Avengers: Uniting Spring Data JDBC and PostgreSQL's JSONB
Petr Filaretov
Petr Filaretov

Posted on

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"
Enter fullscreen mode Exit fullscreen mode

And now you can start PostgreSQL with

docker-compose -f ./docker/local-infra.yaml up
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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}
Enter fullscreen mode Exit fullscreen mode

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>
Enter fullscreen mode Exit fullscreen mode

...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>
Enter fullscreen mode Exit fullscreen mode

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": ...
}
Enter fullscreen mode Exit fullscreen mode

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
        }');
Enter fullscreen mode Exit fullscreen mode

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()
}
Enter fullscreen mode Exit fullscreen mode

..., 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)
    }
}
Enter fullscreen mode Exit fullscreen mode

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

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

..., and the entity FriendsEntity:

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

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
}
Enter fullscreen mode Exit fullscreen mode

...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)
            )
        }
    )
}
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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]
Enter fullscreen mode Exit fullscreen mode

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,
        )
    }
}
Enter fullscreen mode Exit fullscreen mode

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
    }
}
Enter fullscreen mode Exit fullscreen mode

...and reading from it:

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

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

GET http://localhost:8080/api/v1/friends
Enter fullscreen mode Exit fullscreen mode

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:

  1. StringReadingConverter to read data from the database into FriendsEntity
  2. 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,
)
Enter fullscreen mode Exit fullscreen mode

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)
    }
}
Enter fullscreen mode Exit fullscreen mode

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)
    }
}
Enter fullscreen mode Exit fullscreen mode

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,
    )
}
Enter fullscreen mode Exit fullscreen mode

Start the app, call the API...

GET http://localhost:8080/api/v1/friends
Enter fullscreen mode Exit fullscreen mode

...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?>?,
)
Enter fullscreen mode Exit fullscreen mode

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

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

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>
Enter fullscreen mode Exit fullscreen mode

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

GET http://localhost:8080/api/v1/friends
Enter fullscreen mode Exit fullscreen mode

And we get HTTP 500:

IllegalArgumentException: Expected map like structure but found class org.postgresql.util.PGobject
Enter fullscreen mode Exit fullscreen mode

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?>>() {})
    }
}
Enter fullscreen mode Exit fullscreen mode

...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,
        )
    }
}
Enter fullscreen mode Exit fullscreen mode

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

class FriendsFullResponseDto(
    // other fields are the same

    val extras: Map<String, Any?>?,
)
Enter fullscreen mode Exit fullscreen mode

...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
Enter fullscreen mode Exit fullscreen mode

..., 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)
Enter fullscreen mode Exit fullscreen mode

...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)
}
Enter fullscreen mode Exit fullscreen mode

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,
)
Enter fullscreen mode Exit fullscreen mode

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

class FriendsNotFoundException(message: String) : RuntimeException(message)
Enter fullscreen mode Exit fullscreen mode

...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()
    }
}
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Result:

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

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

@Id
val id: UUID,
Enter fullscreen mode Exit fullscreen mode

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

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

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
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

..., 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)
}
Enter fullscreen mode Exit fullscreen mode

..., 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,
)
Enter fullscreen mode Exit fullscreen mode

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"
      ]
    }
  }
}
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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?,
    // ...
)
Enter fullscreen mode Exit fullscreen mode

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> {
    // ...
}
Enter fullscreen mode Exit fullscreen mode

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
}
Enter fullscreen mode Exit fullscreen mode

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"
      ]
    }
  }
}
Enter fullscreen mode Exit fullscreen mode

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)
}
Enter fullscreen mode Exit fullscreen mode

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

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

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(),
   // ...
)
Enter fullscreen mode Exit fullscreen mode

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"
      ]
    }
  }
}
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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

@Transactional
fun delete(id: UUID) =
    friendsRepository.deleteById(id)
Enter fullscreen mode Exit fullscreen mode

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

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

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,
    // ...
)
Enter fullscreen mode Exit fullscreen mode

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>

    // ...
}
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

..., enum ComparisonOperator...

enum class ComparisonOperator {
    GT, GTE, LT, LTE, BETWEEN
}
Enter fullscreen mode Exit fullscreen mode

..., 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)
}
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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>
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

..., 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.

Top comments (1)

Collapse
 
agosmaker profile image
agosmaker •

Great article!

Read next

kiolk profile image

Day 55. Copilot

Kiolk -

chihebabiza profile image

Understanding Transaction Isolation Levels in PostgreSQL

Chiheb Abiza -

franckpachot profile image

Aurora DSQL: Create a Serverless Cluster and Connect with PostgreSQL Client

Franck Pachot -

kiolk profile image

SwiftUI Previews don't Work for Kotlin Multiplatform Mobile (KMM) Projects.

Kiolk -