Support nullable values in GenericInsertSpec.value()

[rjaros]

Currently it's very inconvenient to use insert fluent API with Kotlin nullable variables, because you can't use GenericInsertSpec.value(...) method. Nullable values should be allowed and just passed to nullValue(...) method.

You can workaround the problem with simple Kotlin extension function:

fun <T> DatabaseClient.GenericInsertSpec<T>.safeValue(field: String, value: Any?): DatabaseClient.GenericInsertSpec<T> =
    if (value == null) {
        this.nullValue(field)
    } else {
        this.value(field, value)
    }

[mp911de]

Binding values requires either an actual value or a type when using null values. Several databases treat an INTEGER null differently than a VARCHAR null.

The only thing we can do here is accepting SettableValue as wrapper:

databaseClient.execute("SELECT * FROM table WHERE key = $1")
	.bind(0, SettableValue.fromOrEmpty(person.getNullable(), String.class));

For Kotlin, we could add an extension to leverage reified generics for a smoother experience.

[mp911de]

That's in place now. Our Kotlin extensions provide extension methods accepting nullable values and requiring a generic parameter to provide the required type information for binding values to execute and insert:

client.execute("…")
		.bind<String>("firsname", null)

client.insert()
		.into("table")
		.value<String>("firsname", null)

[rjaros]

In my opinion it's unfortunate to name the extension function with the same name as the method (value()). There is no problem when using Kotlin nullable or not-nullable types, but there is a problem with platform types (String!). For such values the original method is selected and the code fails at runtime. You can force usage of Kotlin extension function by specifying type parameter, but the IntelliJ complains about this type parameter presence. You can also import the function with different name to workaround the problem:

import org.springframework.data.r2dbc.core.value as safeValue

but it's not an ideal solution.

[mp911de]

Thanks for your feedback. If we find a smoother name than safeValue(…) then we can change to that name /cc @sdeleuze

[sdeleuze]

@mp911de As mentioned initially, I have also concerns about using the same names, I think we should change that. My proposal is to use bindNullable and nullableValue.

[rjaros]

Why do we need separate functions for nullable and not-nullable types for bind and value functions/methods, but there is no need for such distinction for the update method? It looks strange,
isn't it the same prepared statement mechanism underneath?

[mp911de]

Why do we need separate functions for nullable and not-nullable types for bind and value

Binding a value requires the value to derive the database data type. Binding a null value requires a type hint to determine the database data type for the null value.

In a few places, we can derive the null type if the operation is associated with a type and a property reference. Specifically, the update and insert methods, need to work with typed and untyped operations. We need to work out the best way to do so as we don't want to maintain two Update classes.

[mp911de]

I checked our code regarding null binding using the criteria API:

• databaseClient.update().table("table").using(Update.update("col", null)) renders UPDATE table SET col = NULL instead of using a parametrized statement UPDATE table SET col = $1
• .matching(where("id").isNull()) renders … WHERE id IS NULL.

Therefore, we're safe here. We try avoiding adding additional type information for the sake of satisfying the API when using NULL values. bindNull(…) and nullValue(…) use parametrized statements so there we require either the value or its type.

[sharmasourabh]

Binding values requires either an actual value or a type when using null values. Several databases treat an INTEGER null differently than a VARCHAR null.

The only thing we can do here is accepting SettableValue as wrapper:

databaseClient.execute("SELECT * FROM table WHERE key = $1")
	.bind(0, SettableValue.fromOrEmpty(person.getNullable(), String.class));

For Kotlin, we could add an extension to leverage reified generics for a smoother experience.

SettableValue is now deprecated with Spring r2dbc Parameter.

[kirillparfenov]

SettableValue deprecated, use org.springframework.r2dbc.core.Parameter:

databaseClient.sql("insert into users(first_name, last_name) values($1, $2)")
.bind(0, Parameter.fromOrEmpty(person.getNullableName(), String.class))
.bind(1, Parameter.fromOrEmpty(person.getNullableLastName(), String.class));