I am wondering what are the differences and when to use Statement, PreparedStatement, and CallableStatement.
我想知道有什么区别以及何时使用Statement,PreparedStatement和CallableStatement。
What is the best practice and typical scenario of using each of these?
使用这些方法的最佳实践和典型方案是什么?
2 个解决方案
#1
6
Statement vs PreparedStatement
声明与PreparedStatement
-
Performance can be better with PreparedStatement but is database dependent.
PreparedStatement可以提高性能,但与数据库有关。
-
With PreparedStatement you avoid SQL injection. How does a PreparedStatement avoid or prevent SQL injection?
使用PreparedStatement可以避免SQL注入。 PreparedStatement如何避免或阻止SQL注入?
-
Better type check with preparedStatement by setInt, setString where as statement you just keep appending to the main SQL.
使用setInt更好地检查prepareStatement,setString where where语句,你只是继续附加到主SQL。
Similar Post:
Difference between Statement and PreparedStatement
Statement和PreparedStatement之间的区别
CallableStatement - Java answer to access StoredProcedures across all databases.
CallableStatement - 跨所有数据库访问StoredProcedures的Java答案。
Similar post
CallableStatement vs Statement
CallableStatement vs Statement
With PreparedStatement and Callable you already have caching, also caching is a big topic in its own, you wouldn't like to do all of that instead look at ehcache
使用PreparedStatement和Callable你已经有了缓存,缓存也是一个很大的话题,你不想做所有这些而是看看ehcache
You should almost always prefer PreparedStatement over Statement
您应该几乎总是更喜欢PreparedStatement而不是Statement
If you have to operate over StoredProcedure you have just one option CallableStatement.
如果你必须在StoredProcedure上操作,你只有一个选项CallableStatement。
#2
3
I'd recommend using PreparedStatement pretty much any time you pass parameters, whether or not you'll be re-using the statement. In practice I use PreparedStatement for everything except procedure calls and let the DB and JDBC driver decide what to cache and how. Procedure calls should use CallableStatement to handle the lack of consistent cross-database procedure call syntax.
无论你是否会重复使用该语句,我建议你在传递参数的任何时候使用PreparedStatement。在实践中,我将PreparedStatement用于除过程调用之外的所有内容,并让DB和JDBC驱动程序决定要缓存的内容以及如何缓存。过程调用应使用CallableStatement来处理缺少一致的跨数据库过程调用语法。
On PostgreSQL, the JDBC driver caches prepared statements client-side until a certain threshold of re-use is reached. At that point a server-side PREPARE is issued and future executions use the server-side prepared statement and its cached plan. This can have some ... interesting ... and unexpected effects because of PostgreSQL's statistics-based query planner. If your table has certain value distributions (or bad statistics due to lack of ANALYZE, wrong random_page_cost or too-low stats threshold) the planner might choose a different and slower query plan when it has an unknown parameter to what it would've chosen if it'd known the actual value you were searching for. If you encounter a sudden and massive slowdown in queries after the 5th (by default) repetition of a particular statement you may be being bitten by this, and can work around it by turning off server-side PREPARE in PgJDBC. There's ongoing work to detect these problem cases in the server by checking whether a particular parameter has very different stats to the unknown-value case, but AFAIK it hasn't hit HEAD yet. See also this question. Search the pgsql-general mailing list and * for more info.
在PostgreSQL上,JDBC驱动程序在客户端缓存预准备语句,直到达到某个重用阈值。此时,将发出服务器端PREPARE,并且将来的执行将使用服务器端预处理语句及其缓存计划。由于PostgreSQL基于统计的查询规划器,这可能有一些......有趣......以及意想不到的效果。如果您的表具有某些值分布(或由于缺少ANALYZE,错误的random_page_cost或太低的统计阈值)而导致统计信息错误,那么当计划程序具有未知参数时,可能会选择不同且较慢的查询计划。它知道你要搜索的实际价值。如果您在第5次(默认情况下)重复某个特定语句后遇到查询突然大幅减速,您可能会被此类操作所困扰,并且可以通过关闭PgJDBC中的服务器端PREPARE来解决此问题。通过检查特定参数是否与未知值情况具有非常不同的统计数据,正在进行检测服务器中的这些问题情况的工作正在进行中,但AFAIK尚未点击HEAD。另见这个问题。搜索pgsql-general邮件列表和*以获取更多信息。
#1
6
Statement vs PreparedStatement
声明与PreparedStatement
-
Performance can be better with PreparedStatement but is database dependent.
PreparedStatement可以提高性能,但与数据库有关。
-
With PreparedStatement you avoid SQL injection. How does a PreparedStatement avoid or prevent SQL injection?
使用PreparedStatement可以避免SQL注入。 PreparedStatement如何避免或阻止SQL注入?
-
Better type check with preparedStatement by setInt, setString where as statement you just keep appending to the main SQL.
使用setInt更好地检查prepareStatement,setString where where语句,你只是继续附加到主SQL。
Similar Post:
Difference between Statement and PreparedStatement
Statement和PreparedStatement之间的区别
CallableStatement - Java answer to access StoredProcedures across all databases.
CallableStatement - 跨所有数据库访问StoredProcedures的Java答案。
Similar post
CallableStatement vs Statement
CallableStatement vs Statement
With PreparedStatement and Callable you already have caching, also caching is a big topic in its own, you wouldn't like to do all of that instead look at ehcache
使用PreparedStatement和Callable你已经有了缓存,缓存也是一个很大的话题,你不想做所有这些而是看看ehcache
You should almost always prefer PreparedStatement over Statement
您应该几乎总是更喜欢PreparedStatement而不是Statement
If you have to operate over StoredProcedure you have just one option CallableStatement.
如果你必须在StoredProcedure上操作,你只有一个选项CallableStatement。
#2
3
I'd recommend using PreparedStatement pretty much any time you pass parameters, whether or not you'll be re-using the statement. In practice I use PreparedStatement for everything except procedure calls and let the DB and JDBC driver decide what to cache and how. Procedure calls should use CallableStatement to handle the lack of consistent cross-database procedure call syntax.
无论你是否会重复使用该语句,我建议你在传递参数的任何时候使用PreparedStatement。在实践中,我将PreparedStatement用于除过程调用之外的所有内容,并让DB和JDBC驱动程序决定要缓存的内容以及如何缓存。过程调用应使用CallableStatement来处理缺少一致的跨数据库过程调用语法。
On PostgreSQL, the JDBC driver caches prepared statements client-side until a certain threshold of re-use is reached. At that point a server-side PREPARE is issued and future executions use the server-side prepared statement and its cached plan. This can have some ... interesting ... and unexpected effects because of PostgreSQL's statistics-based query planner. If your table has certain value distributions (or bad statistics due to lack of ANALYZE, wrong random_page_cost or too-low stats threshold) the planner might choose a different and slower query plan when it has an unknown parameter to what it would've chosen if it'd known the actual value you were searching for. If you encounter a sudden and massive slowdown in queries after the 5th (by default) repetition of a particular statement you may be being bitten by this, and can work around it by turning off server-side PREPARE in PgJDBC. There's ongoing work to detect these problem cases in the server by checking whether a particular parameter has very different stats to the unknown-value case, but AFAIK it hasn't hit HEAD yet. See also this question. Search the pgsql-general mailing list and * for more info.
在PostgreSQL上,JDBC驱动程序在客户端缓存预准备语句,直到达到某个重用阈值。此时,将发出服务器端PREPARE,并且将来的执行将使用服务器端预处理语句及其缓存计划。由于PostgreSQL基于统计的查询规划器,这可能有一些......有趣......以及意想不到的效果。如果您的表具有某些值分布(或由于缺少ANALYZE,错误的random_page_cost或太低的统计阈值)而导致统计信息错误,那么当计划程序具有未知参数时,可能会选择不同且较慢的查询计划。它知道你要搜索的实际价值。如果您在第5次(默认情况下)重复某个特定语句后遇到查询突然大幅减速,您可能会被此类操作所困扰,并且可以通过关闭PgJDBC中的服务器端PREPARE来解决此问题。通过检查特定参数是否与未知值情况具有非常不同的统计数据,正在进行检测服务器中的这些问题情况的工作正在进行中,但AFAIK尚未点击HEAD。另见这个问题。搜索pgsql-general邮件列表和*以获取更多信息。