Spring Data R2DBC parameters conditional binding for SQL query

Question

I am facing a difficulty to bind a conditional parameters to SQL query using Spring Data R2DBC DatabaseClient. Two parameters can be null. Since the DatabaseClient requires to specify explicitly that the parameter is null, I have tried the following syntax but the conditional parameters were not appended to existing ones:

public Mono<Void> createAddress(Address address) {
    DatabaseClient.GenericExecuteSpec bindings = databaseClient.execute(addressesQueries.getProperty("addresses.insert"))
            .bind("line1", address.getLine1())
            .bind("zipCode", address.getZipCode())
            .bind("city", address.getCity())
            .bind("countryId", address.getCountry())
            .bind("id", address.getId()); // UUID

            if(address.getLine2() == null) {
                bindings.bindNull("line2", String.class);
            } else {
                bindings.bind("line2", address.getLine2());
            }
            if(address.getState() == null) {
                bindings.bindNull("state", String.class);
            } else {
                bindings.bind("state", address.getState());
            }

    return bindings.fetch().rowsUpdated().then();
}

SQL query:

INSERT INTO addresses(id,line1,line2,zip_code,city,state,country) VALUES(:id,:line1,:line2,:zipCode,:city,:state,:countryId)

I know that I can split the SQL query to handle cases with/without null parameters but it will be a little bit complicated if I have more that one conditional parameter.

Do you know a solution that can help me to keep one SQL query and handle conditional parameters in Java code?

Answer 1

As commented, the bindings object is not changing with conditionals since you call bind and bindNull methods without saving such changed states back to object. Therefore line2 and state parameters are never populated with values. To fix, consider re-assigning bindings to update the object before its return:

if(address.getLine2() == null) { 
   bindings = bindings.bindNull("line2", String.class); 
} else { 
   bindings = bindings.bind("line2", address.getLine2()); 
} 

if(address.getState() == null) { 
   bindings = bindings.bindNull("state", String.class); 
} else { 
   bindings = bindings.bind("state", address.getState()); 
} 

Answer 2

You can use latest library io.r2bc.spi Parameters class and use in method similar to

client .bind("lastName", Parameters.in(R2dbcType.VARCHAR, userDetailsModel.getLastName()))