I’m trying to build db queries with DatabaseClient...

# spring

[dany giguere]

I’m trying to build db queries with DatabaseClient. I have this repo:

@Repository
class UserRepository(private val databaseClient: DatabaseClient) {

    suspend fun findById(id: Long): User? =
            databaseClient
                    .sql("SELECT * FROM user WHERE id = $id")
                    .map { row ->
                        User(
                                row.get("id") as Long,
                                row.get("username") as String,
                                row.get("email") as String,
                                null.toString()
                        )
                    }.awaitOneOrNull()

    suspend fun create(username: String, email: String, password: String): User? =
            databaseClient.sql("INSERT INTO user (username, email, password) VALUES ('$username', '$email', '$password')")
                    .map { row ->
                        User(
                                row.get("id") as Long,
                                row.get("username") as String,
                                row.get("email") as String,
                                null.toString()
                        )
                    }.awaitOneOrNull()
}

The way I have it the create function is not returning a User. Anyone how I could do that ? (maybe I’ll have to do another select query ???)

[ashmelev]

I've never used a DatabaseClient - but I can tell you that an INSERT will, at best, return a count of the rows inserted (typically 1 in the case you pose). So you will need a SELECT. Trick is that you will not know the PK ID for that row until after INSERT assigns it (judging from your code) so you'll need a different unique constraint on the table - one that your code knows before the INSERT executes. Perhaps username is unique in your model?

[dany giguere]

Thanks @ashmelev. Ya email will be unique. I’ll do a select right after. Thanks again

[Robert Jaros]

The INSERT statement will also return generated ID value. You can use returnGeneratedValues statement filter. See:
https://docs.spring.io/spring-framework/reference/data-access/r2dbc.html#r2dbc-DatabaseClient-filter

[Robert Jaros]

You could also check my kotlin library for spring-data-r2dbc, which allows you to make all this mappings and filters automatically with simple DSL. See: https://github.com/rjaros/r2dbc-e4k

[ashmelev]

@Robert Jaros Thank you for clarifying

[dany giguere]

Nice I’ll check it out tonight :)

[dany giguere]

ok this is what I came up with:

@Component
class UserMapper: BiFunction<Row, Any, User> {
    override fun apply(row: Row, o: Any): User {
        return User(
                row.get("id") as Long,
                row.get("username") as String,
                row.get("email") as String,
                null.toString()
        )
    }
}


@Repository
class UserRepository(private val databaseClient: DatabaseClient,
                     private val mapper: UserMapper) {

    val selectUserById = """
        SELECT * FROM user WHERE id = :id
    """

    val insertUser = """
        INSERT INTO user (username, email, password) VALUES (:username, :email, :password)
    """

    suspend fun findById(id: Long): User? =
            databaseClient.sql(selectUserById)
                    .bind("id", id)
                    .map(mapper::apply)
                    .awaitOneOrNull()

    suspend fun create(user: User): User? =
            databaseClient.sql(insertUser)
                    .filter { statement, _ -> statement.returnGeneratedValues("id").execute() }
                    .bind("username", user.username)
                    .bind("email", user.email)
                    .bind("password", user.password)
                    .fetch()
                    .first()
                    .map { user.copy(id = it["id"] as Long) }.awaitSingleOrNull()
}

Thanks @Robert Jaros and @ashmelev