什么时候应该使用PreparedStatement而不是Statement?

I know the advantages of using PreparedStatement, which are

我知道使用PreparedStatement的好处

query is rewritten and compiled by the database server
查询由数据库服务器重写和编译
protection against SQL injection
防止SQL注入

But I want to know when we use it instead of Statement?

但是我想知道我们什么时候用它代替陈述?

8 个解决方案

#1

Query is rewritten and compiled by the database server

查询由数据库服务器重写和编译

If you don't use a prepared statement, the database server will have to parse, and compute an execution plan for the statement each time you run it. If you find that you'll run the same statement multiple times (with different parameters) then its worth preparing the statement once and reusing that prepared statement. If you are querying the database adhoc then there is probably little benefit to this.

如果不使用准备好的语句，则数据库服务器必须进行解析，并在每次运行该语句时计算该语句的执行计划。如果您发现您将多次运行相同的语句(使用不同的参数)，那么值得一次准备语句并重用已准备好的语句。如果您正在查询数据库adhoc，那么这可能没有什么好处。
Protected against SQL injection

防止SQL注入

This is an advantage you almost always want hence a good reason to use a PreparedStatement everytime. Its a consequence of having to parameterize the query but it does make running it a lot safer. The only time I can think of that this would not be useful is if you were allowing adhoc database queries; You might simply use the Statement object if you were prototyping the application and its quicker for you, or if the query contains no parameters.

这是一个你几乎总是想要的优势，因此每次都要使用一个PreparedStatement。这是必须对查询进行参数化的结果，但它确实使运行查询更加安全。我唯一能想到这没有用的是，如果您允许进行特殊的数据库查询;如果您正在原型化应用程序，并且它对您来说更快，或者如果查询不包含任何参数，那么您可以简单地使用语句对象。

#2

Ask Tom's opinion:

问汤姆的意见:

The use of a Statement in JDBC should be 100% localized to being used for DDL (ALTER, CREATE, GRANT, etc) as these are the only statement types that cannot accept BIND VARIABLES.

PreparedStatements or CallableStatements should be used for EVERY OTHER type of statement (DML, Queries). As these are the statement types that accept bind variables.

preparedstatement或callablestatement应该用于其他类型的语句(DML、查询)。因为这些是接受绑定变量的语句类型。

This is a fact, a rule, a law -- use prepared statements EVERYWHERE. Use STATEMENTS almost no where.

这是一个事实，一条规则，一条法律——在任何地方都要使用准备好的语句。几乎不使用语句。

He's specifically talking about Oracle but the same principle applies to any database that caches execution plans.

他特别谈到Oracle，但同样的原则适用于任何缓存执行计划的数据库。

Database apps that scale and prevent SQL injection attacks at the same time? What's the downside?

同时扩展和防止SQL注入攻击的数据库应用程序?的缺点是什么?

#3

I would turn this round: in a publicly distributed app, you should generally always use prepared statements unless you have a really compelling reason not to, and you should always supply parameters "properly" to the prepared statement, and not by splicing them into the query string.

我将把这一循环转变为:在一个公开发布的应用程序中，您通常应该总是使用准备好的语句，除非您有一个非常令人信服的理由，并且您应该始终将参数“适当地”提供给准备好的语句，而不是将它们插入到查询字符串中。

Why? Well, basically because of the reasons you gave (or at least, the second one)...

为什么?嗯，基本上是因为你给出的理由(或者至少是第二个理由)……

#4

PreparedStatements should be used very carefully in WHERE clauses.

在WHERE子句中，要非常小心地使用preparedstatement。

Suppose that a table is defined as:

假设表定义为:

create table t (int o, k varchar(100), v varchar(100))

(e.g. "o: object-ID (foreign key), k: attribute-key, v: attribute-value").

(如。“o: object-ID(外键)，k: attribute-key, v: attribute-value”。

Furthermore there is a (non-unique) index on v.

此外，在v上还有一个(非唯一的)索引。

create index ixt on t ( v )

Suppose that this table contains 200 million rows inserted like:

假设该表包含2亿行，如:

for (i = 0; i < 100*1000*1000; i++) {
  insert into t (o,k,v) values (i,'k1','v1');
  insert into t (o,k,v) values (i,'k2', Convert(i, varchar));
}

("Thus, every object o has attributes k1=v1 and k2=o")

(“因此，每个对象o都具有k1=v1和k2=o”)

Then you should not build queries like:

那么，您不应该构建以下查询:

select o,p,v from t as tx, t as ty where tx.o=ty.o and tx.k=? and tx.v=? and ty.k=? and ty.v=?

("find objects that have two given attributes")

(“查找具有两个给定属性的对象”)

My experience with ORACLE and MSSQL is, that those queries might need many minutes to return. This is true even if no row matches the where clause. It depends on wether the SQL-Server decides to lookup tx.v or ty.v first.

我在ORACLE和MSSQL中的经验是，这些查询可能需要很多分钟才能返回。即使没有行与where子句匹配，这也是正确的。它取决于sql server是否决定查找tx.v或ty.v。

One shoud put the values for the columns k and v directy into the statement. I think this is because the SQL-Servers take the values into account when computing the execution plan.

一个应该将列k和v的值直接放到语句中。我认为这是因为sql服务器在计算执行计划时考虑了值。

A query look like this returns always after milliseconds:

查询总是在毫秒后返回:

select o,p,v from t as tx, t as ty where tx.o=ty.o and tx.k='k1' and tx.v='v1' and ty.k='k2' and ty.v='1234'

("The SQL-Server will always search first for v='1234' and then for v='v1' ")

(“SQL-Server总是先搜索v='1234'，然后搜索v='v1'”)

Regards
Wolfgang

把沃尔夫冈

#5

I prefer use PreparedStatement if I want to execute a query. ( Insert,Select,... etc). The same rasoson that to others written above.

如果我想执行查询，我更喜欢使用PreparedStatement。(插入、选择…等等)。和上面写的一样。

But If I need to use dynamic paramters I use simply Statement because I can easly build my string SQL thhan simply execute. (inmyopinion) If you want to build dynamic query with preparedStatment you will have a huge spaghetti code that no one understand.

但是如果我需要使用动态参数，我使用简单的语句，因为我可以轻松地构建我的字符串SQL thhan。(依我之见)如果你想用preparedStatment构建动态查询，你会有一个巨大的意大利面条式代码，没人能理解。

#6

Besides preventing SQL injection, formatting portability (which you can't get from Statement), performance is the obvious reason. However, PreparedStatement doesn't come without any penalty. For example, it is generally slower than Statement if running only once, as there is some overhead. So the general idea is PreparedStatement should be used when you are performing the same query many many times. However, how much overhead is very database server implementation-specific, so exactly when to choose PreparedStatement over Statement, from performance consideration, should really be based on your actual experience/experiments of a specific database server.

除了防止SQL注入、格式化可移植性(从语句中无法获得)之外，性能是明显的原因。然而，PreparedStatement并非没有任何惩罚。例如，如果只运行一次，它通常比语句慢，因为存在一些开销。一般的想法是PreparedStatement应该在多次执行相同查询时使用。但是，多少开销是与数据库服务器实现相关的，因此，从性能考虑来看，选择PreparedStatement而不是语句的确切时间应该基于您对特定数据库服务器的实际经验/实验。

#7

Statement: Each time the sql query is running,this sql statement is sent to the DBMS where it is compiled. So, it increases the server loads and decreases the performance.

语句:每次sql查询运行时，这个sql语句都会被发送到编译它的DBMS。因此，它增加了服务器的负载并降低了性能。

connection con=null; 
  String sql="select * from employee where id=5";
Statement st=conn.createStatement();

PreparedStatement: Unlike Statement PreparedStatement is given a sql query as a parameter when it is created.

PreparedStatement:与语句PreparedStatement不同的是，当它被创建时，它被赋予一个sql查询作为参数。

connection con=null; 
String sql="select * from employee where id=?";
PreparedStatement ps=conn.prepareStatement(sql);

This sql statement is sent to Database where it is compiled. So,in preparedStatement compiled happens only once but in statement compiled happens each time Statement is called.

这个sql语句被发送到数据库，在那里编译它。因此，在preparedStatement compile中只发生一次，而in statement compile中发生一次，每次调用time语句。

#8

You can always use PreparedStatement instead of Statment( select, insert , update, delete ). Better performance and protected against SQL injection.

您总是可以使用PreparedStatement而不是Statment(选择、插入、更新、删除)。更好的性能，并防止SQL注入。

But, don't use it with a dynamic request like a request with WHERE variable IN [ hundreds possibilities ] :

但是，不要将它用于动态请求，如带有[数百种可能性]中的WHERE变量的请求:

It's contre-productive, you lost performance and memory because you cache every time a new request, and PreparedStatement are not just for SQL injection, it's about performance. In this case, Statement will not be slower.

它是动态生成的，您会丢失性能和内存，因为每次新请求时都要缓存，PreparedStatement不仅用于SQL注入，它还涉及性能。在这种情况下，语句不会变慢。
Your pool have a limit of PreparedStatment ( -1 defaut but you must limit it ), and you will reach this limit ! and if you have no limit or very large limit you have some risk of memory leak, and in extreme case OutofMemory errors. So if it's for your small personnal project used by 3 users it's not dramatic, but you don't want that if you're in a big company and that you're app is used by thousand people and million request.

你的水池有一个准备状态的极限(-1 - defaut但你必须限制它)，你会达到这个极限!如果你没有限制或很大的限制，你就会有内存泄漏的风险，在极端情况下会出现OutofMemory错误。如果你的个人项目是由3个用户使用的，这不是戏剧性的，但是如果你在一个大公司你不希望你的应用被成千上万的人使用。

Some reading. IBM : Memory utilization considerations when using prepared statement caching

一些阅读。使用准备好的语句缓存时的内存使用注意事项

#1

Query is rewritten and compiled by the database server

查询由数据库服务器重写和编译

If you don't use a prepared statement, the database server will have to parse, and compute an execution plan for the statement each time you run it. If you find that you'll run the same statement multiple times (with different parameters) then its worth preparing the statement once and reusing that prepared statement. If you are querying the database adhoc then there is probably little benefit to this.

如果不使用准备好的语句，则数据库服务器必须进行解析，并在每次运行该语句时计算该语句的执行计划。如果您发现您将多次运行相同的语句(使用不同的参数)，那么值得一次准备语句并重用已准备好的语句。如果您正在查询数据库adhoc，那么这可能没有什么好处。
Protected against SQL injection

防止SQL注入

This is an advantage you almost always want hence a good reason to use a PreparedStatement everytime. Its a consequence of having to parameterize the query but it does make running it a lot safer. The only time I can think of that this would not be useful is if you were allowing adhoc database queries; You might simply use the Statement object if you were prototyping the application and its quicker for you, or if the query contains no parameters.

这是一个你几乎总是想要的优势，因此每次都要使用一个PreparedStatement。这是必须对查询进行参数化的结果，但它确实使运行查询更加安全。我唯一能想到这没有用的是，如果您允许进行特殊的数据库查询;如果您正在原型化应用程序，并且它对您来说更快，或者如果查询不包含任何参数，那么您可以简单地使用语句对象。

#2