reuse sql param when not using named parameters

Question

I have a query that gets called with JdbcTemplate but only one param is getting sent and I need to use that one param in two where conditions.

The Query

String sql = "select * from employee where salary > ? and netpay > ?";

The Call

The param here is only one. I.E. if the id is TEST123 the query needs to be

select * from employee where id = TEST123 and name = TEST123 even though one param is getting passed.

getJdbcTemplate().query(sql, new Object[]{"TEST123"}, CustomResultSetExtractor());

Is there any way to do this from the query side instead of passing two params?

NOTE

I do not have access to change the way the query is called, hence I cannot add named params, or just pass an additional parameter.

Answer 1

Use NamedParameterJdbcTemplate, a JdbcTemplate wrapper:

Template class with a basic set of JDBC operations, allowing the use of named parameters rather than traditional '?' placeholders.

This class delegates to a wrapped JdbcTemplate once the substitution from named parameters to JDBC style '?' placeholders is done at execution time.

Your SQL will be with 1 parameter:

select * from employee where id = (:id) and name = (:id)

And code will be :

MapSqlParameterSource args = new MapSqlParameterSource();
args.addValue("id", TEST123);
return new NamedParameterJdbcTemplate(getJdbcTemplate()).query(sql , args, youRowMapper);

If you can't change it, you can change your query to:

 select * from employee where id = ? and id = name