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?

Did you check if objects (`address.getLine2()`, `address.getState()`) are equal to `null`? Maybe they are empty instead? — Parfait, Mar 28 '20 at 15:47
Even if the `line2` and `state` values are not null and not empty, I got this error: `org.springframework.dao.InvalidDataAccessApiUsageException: No parameter specified for [line2] in query` — Walid Ammou, Mar 28 '20 at 15:53
I can be wrong but try re-assigning *bindings* object inside `if`/`else` conditions: `bindings = bindings.bindNull(...);` — Parfait, Mar 28 '20 at 17:06
Works as expected with re-assigning bindings object. Thank you @Parfait. — Walid Ammou, Mar 28 '20 at 17:44

score 6 · Accepted Answer · answered Mar 28 '20 at 18:42

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()); 
}

score 0 · Answer 2 · answered Jul 27 '23 at 17:34

0

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()))

answered Jul 27 '23 at 17:34

Sajid ali

101
7

Spring Data R2DBC parameters conditional binding for SQL query

2 Answers2