NamedParameterJdbcTemplate vs JdbcTemplate

Question

I'm a beginner to Spring3.x , I'm learning Spring DAO support. I want to know the difference between NamedParameterJdbcTemplate and JdbcTemplate. Which one is the best by means of performance. And when to go for NamedParameterJdbcTemplate and when to go for JdbcTemplate.

Answer 1

When you use JdbcTemplate you give it SQL that has a ? placeholder for each parameter you want substituted into the SQL. When you assign parameters in the code you have to pass in arguments in an array and they get used in the order in which they appear in the array, like this:

Object[] args = new Object[] {"x", "y"};
String sql = "select * from foo where a = ? and b = ?";
jdbcTemplate.query(sql, args, resultSetExtractor);

so the SQL that gets run is select * from foo where a = 'x' and b = 'y'.

NamedParameterJdbcTemplate allows you to assign names to the parameter placeholders and pass in a map so the template can match the map names to the placeholders. So your code would look like:

String sql = "select * from foo where a = :mya and b = :myb";
Map<String, Object> argMap = new HashMap<String, Object>();
argMap.put("mya", "x");
argMap.put("myb", "y");
namedParameterJdbcTemplate.query(sql, argMap, resultSetExtractor);

generating the same SQL as the first example.

Running the query is the time-intensive part, the performance of the argument insertion is a non-issue.

The idea is that matching the arguments by name is less error-prone than having to specify them in a particular order. In real-life applications I've worked on, typically we store the SQL in a separate file from the DAO code, and it may be easy to accidentally get the parameters in the wrong order.