INSERT contains no values with spring-data-r2dbc

Question

When I try to insert an entity with only an id using spring r2dbc data I got the following exception:

Exception while fetching data (addChat) : INSERT contains no values

java.lang.IllegalStateException: INSERT contains no values
    at org.springframework.data.r2dbc.core.DefaultStatementMapper.getMappedObject(DefaultStatementMapper.java:159) ~[spring-data-r2dbc-1.0.0.RC1.jar:1.0.0.RC1]
    at org.springframework.data.r2dbc.core.DefaultStatementMapper.getMappedObject(DefaultStatementMapper.java:144) ~[spring-data-r2dbc-1.0.0.RC1.jar:1.0.0.RC1]

Database postgres:

create table chats
(
    id serial not null
        constraint chats_pkey
            primary key
);

Entity

import org.springframework.data.annotation.Id
import org.springframework.data.relational.core.mapping.Table

@Table("chats")
data class ChatEntity(
        @Id val id: Int? = null
)

Repository:

import org.springframework.data.r2dbc.repository.R2dbcRepository
import org.springframework.stereotype.Repository


@Repository
interface ChatsRepository : R2dbcRepository<ChatEntity, Int>

Service:

//Service
val chatAdded = chatsRepository.save(ChatEntity(id = null)).awaitFirst()

On the same project I have other entities with id and other columns that works fine. Any idea why I have this error?

Answer 1

If you are using postgres, you can execute(…) like below and return inserted values.

  val chatAdded = db.execute("INSERT INTO chats\n" +
                "        DEFAULT VALUES\n" +
                "        RETURNING *")
                .map { row -> ChatEntity(id = row.get("id") as Int) }
                .awaitOne()

Answer 2

I faced the same issue, and got it persisted with my entity implementing org.springframework.data.domain.Persistable<T> where T is the entity id type.

here is how I implemented it in kotlin and coroutines. I assume your context configuration is ok, and you configured properly org.springframework.r2dbc.core.DatabaseClient

Entity:

import org.springframework.data.relational.core.mapping.Table
import org.springframework.data.annotation.*
import org.springframework.data.domain.Persistable
import javax.validation.constraints.NotNull
import javax.validation.constraints.Size

@Table("`authority`")
data class Authority(
        @Id val role: @NotNull @Size(max = 50) String
) : Persistable<String> {
    override fun isNew() = true
    override fun getId() = role
}

DAO:

import org.springframework.data.repository.kotlin.CoroutineCrudRepository

@Repository("authorityRepository")
interface AuthorityRepository : CoroutineCrudRepository<Authority, String>

functional test

import kotlinx.coroutines.runBlocking
import kotlin.test.assertEquals
import org.junit.jupiter.api.Test
import org.springframework.beans.factory.annotation.Autowired
import Authority
import org.springframework.boot.test.context.SpringBootTest
import org.springframework.r2dbc.core.DatabaseClient
import org.springframework.r2dbc.core.awaitSingle

@SpringBootTest
class AuthorityRepositoryFuncTest {
    @Autowired
    lateinit var db: DatabaseClient
    @Autowired
    private lateinit var authorityRepository: AuthorityRepository

    suspend fun countAuthority(): Long = db
            .sql("SELECT COUNT(*) FROM `authority`")
            .fetch()
            .awaitSingle()
            .values
            .first() as Long

    @Test
    fun `test save authority`() = runBlocking {
        val countAuthorityBeforeSave = countAuthority()
        val authorityTestValue = "ROLE_TEST"
        authorityRepository.save(Authority(role = authorityTestValue))
        assertEquals(countAuthorityBeforeSave + 1, countAuthority())
    }
}