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

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

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

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

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.

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

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.