Spring NamedParameterJDBCTemplate reuse of Prepared Statements

Question

I am using the Spring NamedParameterJdbcTemplate to fetch some values from a table. For some reason, the query runs very slow in my Java app as opposed to running the same query on SQL Management Studio. I also noticed in the profiler, the prepared statements don't get reused. If I run the same query in my JAVA app multiple times, I see different prepared statements being executed. So, not sure why the statements are not getting reused. Is the performance slow because I am using a IN clause in my query?

Here is my sample java code

StringBuilder vQuery = new StringBuilder();
vQuery.append(" SELECT SUM(Qty) FROM vDemand");
vQuery.append(" WHERE ProductID = :ProductID");
vQuery.append(" AND [Date] >= :StartDate AND [Date] <= :EndDate");
vQuery.append(" AND CustomerID IN ( :CustomerID )");

MapSqlParameterSource vNamedParameters = new MapSqlParameterSource();
vNamedParameters.addValue("ProductID", aProductID);
vNamedParameters.addValue("CustomerID", aCustomerlIDs);
vNamedParameters.addValue("StartDate", aDate, Types.TIMESTAMP);
vNamedParameters.addValue("EndDate", aDate, Types.TIMESTAMP);

int vTotalQuantity = this.getNamedParameterJdbcTemplate().queryForInt(vQuery.toString(), vNamedParameters);
return vTotalQuantity;

Answer 1

Looking at the source code of Spring's NamedParameterJdbcTemplate, it parses your SQL into a structure ParsedSql, and then replaces your named parameters with question marks, then builds the PreparedStatement and fills it with your parameters.

It caches the ParsedSql entries, but always builds new PreparedStatements so ultimately these are not reused at the JDBC driver level.

A PreparedStatement has two advantages over a regular Statement:

1. You add parameters to the SQL using methods instead of doing it inside the SQL query itself. With this you avoid SQL injection attacks and also let the driver to do type conversions for you.

2. As you said, the same PreparedStatement can be called with different parameters, and the database engine can reuse the query execution plan.

It seems that NamedParameterJdbcTemplate helps you with the first advantage, but does nothing for the latter.