oracle – Filtering query with R2dbc – Code Utility

[

Hi guys I need do filtering query with some query param. I need that if the query param is null, the condition of the query (= or LIKE for example) is not evaluated and return me everything. I’m using R2DBC and I don’t find a way to solve.

,

A general prepared statement which would work might be:

SELECT *
FROM yourTable
WHERE col = ? or ? IS NULL;

In the event that you bind a NULL value to the ? from your obfuscation layer, the WHERE clause would always be true, returning all records in the table.

,

If you prefer doing this with a “static SQL statement” (meaning you use a single SQL string for all possible bind values), then, in Oracle, it’s probably optimal to use NVL() to profit from an Oracle optimiser feature, as explained in this article, irrespective of whether you’re using R2DBC:

SELECT *
FROM t
WHERE col = nvl(:bind, col)

However, a query like yours is often best implemented using dynamic SQL, such as supported by a third party library like jOOQ:

Flux<TRecord> result =
Flux.from(ctx
        .selectFrom(T)
        .where(bind == null ? noCondition() : T.COL.eq(bind))
     );

You can obviously do this also yourself, directly with R2DBC and your own dynamic SQL library, or any other such library.

Disclaimer: I work for the company behind jOOQ.

]