Unexpected behavior for NamedParameterJdbcTemplate

Question

I'm using Spring's NamedParameterJdbcTemplate wrapper.

I have inserted one dealno into the (Oracle) database and verified with Oracle SQL Developer.

When I have a fixed query to select for that dealno (and a useless namedParameter), I get an expected count of 1. (Note, I also get the expected count of 1 when I use a JdbcTemplate.)

When I use a SqlParameterSource and the necessary namedParameter, I get an unexpected count of 0.

Here's a code fragment.

String sqlBegin = "select count(*) FROM " + SCHEMA_NAME + "." + TABLE_NAME + " WHERE dealno = ";
String sql = sqlBegin + ":dealno";
String dealnoStr = "24324965";
String noParamSql = sqlBegin + "'" + dealnoStr + "'"; // WHERE dealno = '24324965'

SqlParameterSource namedParameters = new MapSqlParameterSource("dealno", dealnoStr);

Integer noParamCount = parameterJdbcTemplate.queryForObject(noParamSql, namedParameters, Integer.class); // no namedParameter substitution occurring.
// Debug: noParamCount is 1

Integer count = parameterJdbcTemplate.queryForObject(sql, namedParameters, Integer.class);
// Debug: count is 0

The code used to have a queryForObject with a RowMapper and a complex object.

Answer 1

There was a great hint to answer this question at Error Inserting Java Character object value into Oracle CHAR(1) column.

My problem was the dealno did not map to a Java String because it was a CHAR(15), not a varchar.

The solution was to CAST the dealno in the SQL statement, as in:

 "WHERE DEALNO = CAST(:dealno as CHAR(15))"

So NamedParameterJdbcTemplate handles a slew of things for you, but not a conversion from a Java String to a fixed-length CHAR. Let the buyer beware.