Spring NamedParameterJDBCTemplate重用已准备好的语句 [英] Spring NamedParameterJDBCTemplate reuse of Prepared Statements

问题描述

我正在使用Spring NamedParameterJdbcTemplate从表中获取一些值.由于某种原因，与在SQL Management Studio上运行相同的查询相反，该查询在我的Java应用程序中运行非常慢.我还注意到，在探查器中，准备好的语句不会被重用.如果我在JAVA应用程序中多次运行相同的查询，则会看到执行了不同的准备语句.因此，不确定为什么不重用这些语句. 由于我在查询中使用了IN子句，因此性能是否会变慢?

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?

这是我的示例Java代码

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;

推荐答案

查看Spring NamedParameterJdbcTemplate的源代码，它将SQL解析为结构ParsedSql，然后将您的命名参数替换为问号，然后构建PreparedStatement并用您的参数填充它.

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.

它缓存ParsedSql条目，但始终构建新的PreparedStatements，因此最终它们不会在JDBC驱动程序级别被重用.

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

与常规Statement相比，PreparedStatement有两个优点:

A PreparedStatement has two advantages over a regular Statement:

1. 您可以使用方法向SQL添加参数，而不是在SQL查询本身内部进行操作.这样可以避免SQL注入攻击，也可以让驱动程序为您执行类型转换.

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.

正如您所说，同一PreparedStatement可以使用不同的参数调用，并且数据库引擎可以重用查询执行计划.

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

NamedParameterJdbcTemplate似乎可以帮助您获得第一个优势，但对于后者没有任何帮助.

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

这篇关于Spring NamedParameterJDBCTemplate重用已准备好的语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！