MappingSqlQuery : One Parameter used Multiple Times?

[dortman]

After looking through the docs, I believe the answer to this queston is "no". I figured I would make sure this conclusion was not incorrect.

I have some SQL statements which are being executed using MappingQuery. Some of these SQL statement join a handful of tables, many of which have historical data which have an effective and a term date. Something like the following is not uncommon.

AND b.PARTC_ADDR_EFF_DATE  <= ?
AND b.PARTC_ADDR_TERM_DATE  >= ?

Both of these parameters are actually supplied the same value; a single date which is passed in. At times, there may be half a dozen dates which are being compared; meaning the same date value may be used in 12 different parameters. That requires declaring the parameter 12 times, and then supplying it 12 times when the query is executed.

I started to look at using named parameters to make this easier to manage; but it seems that I still need to declare and supply the date value 12 times. The only benefit named parameters seems to offer is that the order in which parameters are declared is no longer important. The following doesn't seem possible:

AND b.EFF_DATE  <= :effDate
AND b.TERM_DATE  >= :effDate
...
AND c.EFF_DATE  <= :effDate
AND c.TERM_DATE  >= :effDate
...
AND d.PARTC_ADDR_EFF_DATE  <= :effDate
AND d.PARTC_ADDR_TERM_DATE  >= :effDate

Where I can then declare 1 parameter of type TIMESTAMP which is then used for all isntances of :effDate in the SQL statement. Is that correct?

Thanks,
-Dave

[trisberg]

You could use the NamedParameterJdbcTemplate instead - it supports multiple instances of a parameter in the sql statement.

[dortman]

You could use the NamedParameterJdbcTemplate instead - it supports multiple instances of a parameter in the sql statement.

Thanks for the reply trisberg; sorry to be so rude by not acknowledging your post for such a long period of time.

We actually already have a good amount of code which uses the MappingSqlQuery class. Our DAO classes have several select methods which utilize a class that extends MappingSqlQuery to map their results. So using NamedParameterJdbcTemplate in existing code would require rework of those classes, as we used MappingSqlQuery in lieu of the JDBCTemplate class.

NamedParameterJdbcTemplate definitely works; but it looks like it is not possible to use named parameters with thee MappingSqlQuery. Is there a good reason for that, or is it just that NamedParameters have not been added to SqlQuery?

Thanks again,
-Dave