When to use Map and SqlParameterSource in namedParameterJdbcTemplate?

Question

String SQL = "INSERT INTO Employee (name, age, salary) VALUES (:name,:age,:salary)";
Map namedParameters = new HashMap();   
namedParameters.put("name", name);   
namedParameters.put("age", age);
namedParameters.put("salary", salary);
namedParameterJdbcTemplate.update(SQL, namedParameters);

String SQL = "UPDATE Employee SET age = :age WHERE empid = :empid";
SqlParameterSource namedParameters = new MapSqlParameterSource();
namedParameters.addValue("age", age);
namedParameters.addValue("empid", empid);
namedParameterJdbcTemplate.update(SQL, namedParameters);

Seems both Map and SqlParameterSource are same. But why did API developers added these API's ? Is there any particular scenario to use Map or SqlParameterSource which makes execution faster? Please explain me clearly. Thanks in advance.

Answer 1

Using a Map is fine for simple cases, but there are two benefits to using SqlParamaterSource over a Map.

The first is simply the builder pattern allowing you to add multiple values inline (namedParameters.addValue().addValue().addValue() etc).

The second is more powerful. The jdbcTemplate will auto-determine the sqlType of your map values while the SqlParamaterSource allows you to explicitly use the sqlType of your choice. This can be an issue depending on your database, indexes and parameters.

An example would be Integers and Longs with an Oracle database. The jdbc template will add these objects to your query with surrounding quotes '' making them effectively strings in your database query. If you have a number in your database with leading 0's it will not be found because '0XXXX' will not match 'XXXX'. If you pass in the right sqlType, the jdbc template will do a number comparison without quotes so XXXX will equal XXXX.