[jdbi3] Support optional clauses in SQL for bound optional parameters.

[qualidafial]

There are lots of scenarios where you want to include/exclude a fragment from the SQL statement depending on what is bound to a particular parameter:

String SELECT = "SELECT * FROM something { id => WHERE id = :id }";

// these queries would be executed with the WHERE clause excluded:
handle.createQuery(SELECT).list();
handle.createQuery(SELECT).bind("id", null).list();
handle.createQuery(SELECT).bind("id", Optional.empty()).list();

// these queries would be executed with the WHERE clause included:
handle.createQuery(SELECT).bind("id", 1).list();
handle.createQuery(SELECT).bind("id", Optional.of(1)).list();

The { id => WHERE id = :id } is certainly up for debate--we'd have to ensure that whatever syntax we picked didn't conflict with valid SQL tokens.

This could be implemented in our default statement rewriters.

[christophercurrie]

YMMV, but IMO this shouldn't be a design goal. First, this is the sort of thing that the StringTemplate statement locator does very well. While StringTemplate does not know about bind specifically, I see that as a feature; I want to be alerted when I forget to bind a variable, or bind one incorrectly. And I definitely want to think of LIST ALL and LIST WITH FILTER as distinct operations, because my database will treat them differently.

Using StringTemplate, the example provided is a straightforward as:

listAll() ::= <<
  SELECT id, data FROM something
>>

listById() ::= <<
  <listAll()> WHERE id = :id
>>

No, it's not as concise, but what it loses in brevity it gains in power; I can reuse the first part of the query in multiple variations, and have them be consistent, and I get protection from my own errors of forgetting to bind or accidentally binding in the wrong use case.

[stevenschlansker]

I too am somewhat "meh" on this feature. Another option that is not as concise but doesn't require any additional support:

... WHERE (id = :id OR :id IS NULL) which means if you do an explicit bind of NULL you get an "optional" parameter.

Maybe if you really want it, it's appropriate as a different standalone statement rewriter?

[qualidafial]

The main value I was after was the ability to mix and match filters on queries, e.g. on a search form with multiple optional fields. However as @stevenschlansker pointed out, this is already possible within the SQL syntax:

SELECT FROM thing
WHERE (:createdOn.start IS NULL OR created_on >= :createdOn.start)
AND (:createdOn.end IS NULL OR created_on <= :createdOn.end)
AND (:name IS NULL OR name LIKE :name || '%')
AND (:id IS NULL OR id = :id)

Closing.

[AseedUsmani]

The main value I was after was the ability to mix and match filters on queries, e.g. on a search form with multiple optional fields. However as @stevenschlansker pointed out, this is already possible within the SQL syntax:

SELECT FROM thing
WHERE (:createdOn.start IS NULL OR created_on >= :createdOn.start)
AND (:createdOn.end IS NULL OR created_on <= :createdOn.end)
AND (:name IS NULL OR name LIKE :name || '%')
AND (:id IS NULL OR id = :id)

Closing.

I too am somewhat "meh" on this feature. Another option that is not as concise but doesn't require any additional support:

... WHERE (id = :id OR :id IS NULL) which means if you do an explicit bind of NULL you get an "optional" parameter.

Maybe if you really want it, it's appropriate as a different standalone statement rewriter?

But how optimised this query is? Will there be an extra condition check for each WHERE statement?
And how can we do this for IN query?