Clever way to add the same parameter to mulitple locations in a prepared-statement

Question

I have a JDBC query like

select * from table1 where col1 between x and y
union all
select * from table2 where col1 between x and y
union all
select * from table3 where col1 between x and y

I'm using a prepared-statement and am wondering if there is a cleverer way to set x and y without saying setDate(1, x);setDate(2, y);setDate(3, x);

Answer 1

I would take advantage of java's String Formatter:

String queryFormat = "select * from table1 where col1 between %1$s and %2$s " +
                     "union all " +
                     "select * from table2 where col1 between %1$s and %2$s " +
                     "union all " +
                     "select * from table3 where col1 between %1$s and %2$s";
String query = String.format(queryFormat,"5","10");

The first argument passed to the format method is the format string. The %1$s means to insert the 1st argument of type string ("5"), and the %2$s means to insert the 2nd argument of type string ("10").

The query string will then contain:

select * from table1 where col1 between 5 and 10 union all select * from table2 where col1 between 5 and 10 union all select * from table3 where col1 between 5 and 10

You can read more about the Formatter class here.

Hope this helps.

Answer 2

Cleverer. Maybe a loop? A for loop that executes three times and calls:

setDate((1*2)+1, x);
setDate((1*2)+2, y);

Answer 3

If your DB is decent, you can do:

select * from (
select * from table1 
union all
select * from table2
union all
select * from table3
)
where col1 between x and y

and pass x and y just once. The optimizer would apply the predicate to each table if appropiate.

Answer 4

why be "clever"? Clever code usually results in "clever" bugs in "clever" corner cases.

BTW if you use hibernate, your query would be:

select * from table1 where col1 between :x and :y
union all
select * from table2 where col1 between :x and :y
union all
select * from table3 where col1 between :x and :y

Java code would look like this:

Query query;
query.setParameter("x", x);
query.setParameter("y", y);