Named Parameters in Jdbc

Named parameters in JDBC

JDBC does not support named parameters. Unless you are bound to using plain JDBC (which causes pain, let me tell you that) I would suggest to use Springs Excellent JDBCTemplate which can be used without the whole IoC Container.

NamedParameterJDBCTemplate supports named parameters, you can use them like that:

 NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);

 MapSqlParameterSource paramSource = new MapSqlParameterSource();
 paramSource.addValue("name", name);
 paramSource.addValue("city", city);
 jdbcTemplate.queryForRowSet("SELECT * FROM customers WHERE name = :name AND city = :city", paramSource);

Named Parameter Markers in JDBC and DB2

Seems, that this very old Db2 jcc 2.3.63 version driver (from Db2 V8.2) doesn't have support for named parameters.

Contemporary Db2 jcc drivers do support such a functionality.

It's strongly advised to use Db2 jcc drivers corresponding to the Db2 Server version.

reuse sql param when not using named parameters

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

Named parameter doesn't work with MySql LIKE statement

The solution is to add the parameter without quotes

params.addValue("search", "%" + search + "%");

and in the SQL string write

String sql = "... like :search";

---

Your first approach ('%:search%') did not work since named parameters are not recognized within string literals.

The second approach params.addValue("search", "'%" + search + "%'"); did not work since now the quotes were part of the like string, therefore asking Mysql to look for strings which start and end with a quote and contain the search term.

---

Related Topics