Meet ps, a simple PreparedStatement that does the job:
PreparedStatement ps = cnx.prepareStatement( "SELECT * FROM mytable WHERE ref=? AND time=>? AND time<?");
I need to add 3 optional criteria (C1,C2,C3). For clarity, they may be required or not based on runtime parameters.
Brute force tells me I can write 9 prepared statements to cover all possibilities. But what I would really like to write is more something like:
SELECT * FROM mytable WHERE ref=? AND time=>? AND time<? AND C1=? AND C2=? AND C3=?;
and use a trick like setInt(5, "ANY") or ignoreParameter(5) before executing the statement
Is there such a thing?
You have the option of using a framework, doing it yourself or doing some sql tricks. E.g. JPA has a CriteriaBuilder.
Otherwise split up your query in a static and a dynamic part. Depending on your dynamic part you would have to do the binding. You would have
SELECT * FROM mytable WHERE ref=? AND time=>? AND time<?
as your static part and add AND C1 = ?only when needed.
For SQL options you could add 3 more parameters and set them to 0 or 1.
SELECT * FROM mytable WHERE ref=? AND time=>? AND time<?
AND (CHECKC1 = ? OR C1=?) AND (CHECKC2 = ? OR C2=?) AND (CHECKC3 = ? OR C3=?);
However I wouldn't call it a good solution.