How do I handle nullable fields using either the Mono<Connection> or the DatabaseClient provided by R2dbc in Spring?

Question

I am at a loss for how to contruct an efficient query in R2dbc (java) using spring-webflux (reactive). Using the DatabaseClient object provided by R2dbc (or alternatively, a Connection object), it seems that I am only able to call different variations of one of these two methods: bind(Object field, Object value) or bindNull(Object field, Class<?> type). If I have a schema, and a corresponding class in Java, with multiple nullable fields, how am I expected to handle this [somewhat] efficiently?

Take for example:

public Flux<Item> saveOrUpdate(Item entity) {

   Mono<Connection> connection = this.connection; 

   Flux<? extends Result> itemFlux = connection
       .doOnError(e -> e.printStackTrace())
           .flatMapMany(connect  ->  connect.createStatement(INSERT_OR_UPDATE_ITEM)
                .bind("itemId", entity.getItemId()).returnGeneratedValues("itemid")
                .bind("auditId", entity.getTx().getId())
                .bind("itemNum", entity.getItemNum())
                .bind("itemCat", entity.getItemCat()) //nullable
                 // How would I know when to use this?
                .bindNull("sourcedQty", Integer.class) //nullable
                .bind("makeQty", entity.getMakeQty())
                .bind("nameShown", entity.getNameShown()) //nullable
                .bind("price", entity.price())
                .bind("dateCreated", entity.getDateCreated()) //nullable
                .add()
                .execute())...
   ...
}

OR

public Mono<Item> saveOrUpdate(Item entity){

   Mono<Item> itemMono = databaseClient.execute.sql(INSERT_OR_UPDATE_ITEM)
      .bind("itemId", entity.getItemId()).returnGeneratedValues("itemid")
                .bind("auditId", entity.getTx().getId())
                .bind("itemNum", entity.getItemNum())
                .bind("itemCat", entity.getItemCat())
                .bind("sourcedQty", entity.getSourcedQty()) 
                .bind("makeQty", entity.getMakeQty())
                .bind("nameShown", entity.getNameShown())
                .bind("price", entity.price())
                .bind("dateCreated", entity.getDateCreated())
                .as(Item.class)
                .fetch()
                .one()...
   ...
}

For my nullable fields I can replace .bind with .bindNull of course. The problem is that if I do call bind, the value cannot be null. And if I call bindNull, the value must be null. How would I be able to call one or the other based on whether my value is actually null? I already know that I can just make a bunch of methods for each scenerio or call something along the lines of retryOnError. But if I want to do a insertOrUpdate(List<Item> items) this would be wasting a ton of time/resources. Ideally I would like to do something analogous to if (field == null) ? bindNull("field", field.class) : bind("field", myObj.field) somewhere somehow. If that is clearly off the table, I am still interested in figuring out a way to implement this is as efficiently as possible given what I'm working with. Appreciate any feedback.

Pliner

To clarify your question: You want to retain a fluent style while binding potentially null values and you're asking how to achieve that. – Justice

Sounds like an acceptable way to phrase it. My actuql dataset contains more null values than the sample I made here. I would like to make methods similar to this one nearly as efficient as executing a prepared statement with jdbc, for example. I don’t want to use hooks or wrappers or insert values that do not adhere to the conventions of the data already in the database. So I care a lot about style in that sense. But I’m open to ideas that require a bit of compromise. I’m new to both r2dbc and reactive so it’s very possible that I’ve overlooked a simple solution or a smart workaround. – Pliner

I’d also be interested to hear of a way to write the sql statement so that I could just use .bind and let the database figure the rest out. – Pliner

Answer 1

These are two questions:

1. How to bind potentially nullable values to a Statement/DatabaseClient in a fluent style?
2. How to let the database figure the rest out?

R2DBC and Spring Data R2DBC make null handling explicit by requiring either binding a value to your Statement or binding a null. There's no method of accepting a potentially nullable argument. There are two reasons for that:

1. You should deal with nullability to make obvious what happens there. That's a good habit to handle nullable values instead of making null handling implicit. The implicit nature of null is what causes the most bugs.
2. Being explicit is required by databases. Parametrized statements with placeholders consist on the execution side of two chunks: The SQL statement itself and parameters bindings (descriptors). A parameter descriptor requires an association to a placeholder, type information (VARCHAR, BIT, INT, …) and the actual value. With calling bind(…) with a value, a driver can derive the type information. When binding a null value, the driver requires an additional type of information. Otherwise, we cannot execute the query.

That being said:

1. There's no API like bindPotentiallyNull("auditId", entity.getTx().getId(), Integer.class)
2. You cannot do anything within the SQL query because binding parameter information is supplied by auxiliary methods.

We face a similar issue when talking about stored procedures, because stored procedures require additional details about in/out/in-out parameters. We discussed potential wrapper types like

Parameters.in(@Nullable T value, Class<? super T> valueType)

so these could be used as wrappers in

bind("auditId", Parameters.in(entity.getTx().getId(), Integer.class))

Further details:

• Mailing list discussion
• Postgres protocol documentation
• Microsoft SQL Server: sp_cursorprepare

Justice

Thanks for your response @mp911de. Not exactly what I wanted to hear, but still great info - definitely useful to know. I'll go ahead and accept this answer. – Pliner

Answer 2

Either setting a value or null can be done using the Parameter class as shown below:

import org.springframework.r2dbc.core.Parameter;

// rest of the code

.bind("customerId", Parameter.fromOrEmpty(o.getCustomerId(), UUID.class))

Earlier, it was SettableValue.fromOrEmpty, which is not deprecated.

Overprize