Java 我们什么时候应该使用 PreparedStatement 而不是 Statement ？

When should we use a PreparedStatement instead of a Statement?

提问by Johanna

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?

但我想知道我们什么时候使用它而不是Statement?

回答by Martin Booth

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.

2. Protected against SQL injection

   This is an advantage you almost always want hence a good reason to use a PreparedStatementeverytime. 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.

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

   如果您不使用准备好的语句，则每次运行该语句时，数据库服务器都必须解析并计算该语句的执行计划。如果您发现将多次运行相同的语句（使用不​​同的参数），那么值得一次准备该语句并重用该准备好的语句。如果您是临时查询数据库，那么这可能没有什么好处。

2. 防止 SQL 注入

   这是您几乎总是想要的优势，因此是PreparedStatement每次都使用 a 的好理由。这是必须参数化查询的结果，但它确实使运行它更安全。我能想到的唯一一次没有用的是如果您允许临时数据库查询；如果您正在为应用程序设计原型并使其更快，或者如果查询不包含任何参数，您可以简单地使用 Statement 对象。

回答by Neil Coffey

I would turn this round: in a publicly distributed app, you should generally alwaysuse 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)...

为什么？嗯，基本上是因为你给出的原因（或者至少是第二个）......

回答by bryantsai

Besides preventing SQL injection, formatting portability (which you can't get from Statement), performance is the obvious reason. However, PreparedStatementdoesn't come without any penalty. For example, it is generally slower than Statementif running only once, as there is some overhead. So the general idea is PreparedStatementshould 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 PreparedStatementover Statement, from performance consideration, should really be based on your actual experience/experiments of a specific database server.

除了防止 SQL 注入、格式化可移植性（您无法从中获得Statement）之外，性能是显而易见的原因。然而，PreparedStatement并非没有任何惩罚。例如，它通常比Statement只运行一次要慢，因为有一些开销。因此，PreparedStatement当您多次执行相同的查询时，应该使用一般的想法。然而，大部分的开销怎么数据库服务器执行的特定性很高，所以具体什么时候选择PreparedStatement了Statement，从性能考虑，确实应该根据你的实际经验/特定数据库服务器的实验。

回答by mattjames

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.

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

JDBC 中语句的使用应 100% 本地化为用于 DDL（ALTER、CREATE、GRANT 等），因为这些是唯一不能接受 BIND VARIABLES 的语句类型。

PreparedStatements 或 CallableStatements 应该用于所有其他类型的语句（DML、查询）。因为这些是接受绑定变量的语句类型。

这是一个事实、一条规则、一条法律——在任何地方使用准备好的陈述。几乎没有地方使用 STATEMENTS。

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 注入攻击的数据库应用程序？有什么缺点？

回答by Wolfgang

PreparedStatements should be used very carefully in WHERE clauses.

在 WHERE 子句中应该非常小心地使用 PreparedStatements。

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：对象ID（外键），k：属性键，v：属性值”）。

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 minutesto 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-Server 在计算执行计划时会考虑这些值。

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

问候
沃尔夫冈

回答by stumbler

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：与Statement不同，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 中编译只发生一次，但在每次调用Statement 时都会在语句中编译。

回答by amdev

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

您始终可以使用 PreparedStatement 而不是 Statment( select, insert , update, delete )。更好的性能并防止 SQL 注入。

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

但是，不要将它与动态请求一起使用，例如请求WHERE variable IN [ hundreds possibilities ]：

1. It's counter-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.

2. 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. 它会适得其反，因为每次新请求都会缓存，所以会损失性能和内存，而 PreparedStatement 不仅用于 SQL 注入，还与性能有关。在这种情况下，Statement 不会变慢。

2. 你的池有 PreparedStatment 的限制（-1 defaut 但你必须限制它），你会达到这个限制！如果你没有限制或非常大的限制，你就有内存泄漏的风险，在极端情况下会出现 OutofMemory 错误。因此，如果它是用于 3 个用户使用的小型个人项目，这并不引人注目，但是如果您在一家大公司并且您的应用程序被数千​​人和数百万个请求使用，则您不希望这样。

Some reading. IBM : Periodical OutOfMemory errors with prepared statement caching

一些阅读。 IBM：带有准备好的语句缓存的定期 OutOfMemory 错误

回答by Marcos

It's simply a Java DESIGN MISTAKE tie "prepared statement" with "parameterized query / bind variables".

它只是一个 Java DESIGN MISTAKE 将“准备好的语句”与“参数化查询/绑定变量”联系起来。

Databases does have API to accept "bind variables" in SQL code that just run once time.

数据库确实有 API 来接受只运行一次的 SQL 代码中的“绑定变量”。

It's a big resource wasting force use "prepared statement" everywhere, just to protect from SQL injection. Why not Java just let developers use databases in correct way?

到处使用“准备好的语句”是一种很大的资源浪费，只是为了防止SQL注入。为什么 Java 不让开发人员以正确的方式使用数据库？

It could be as follows:
Statement Interface- Multiples commands could be run. Not accept bind variables. One execution of SQL command. No SQL injection protection.
PreparedStatement Interface- One command could be run. Accept bind variables. Multiple executions of SQL command. SQL injection protection.
(MISSING IN JAVA!) RunOnceStatement- One command could be run. Accept bind variables. One execution of SQL command. SQL injection protection.

它可能如下：
Statement Interface- 可以运行多个命令。不接受绑定变量。执行一次 SQL 命令。没有 SQL 注入保护。
PreparedStatement Interface- 可以运行一个命令。接受绑定变量。多次执行 SQL 命令。SQL注入保护。
（在 JAVA 中丢失！） RunOnceStatement- 可以运行一个命令。接受绑定变量。执行一次 SQL 命令。SQL注入保护。

For exemple, in Postgres performance could be better, by driver mapping to:
Statement Interface- PQExec()
PreparedStatement Interface- PQPrepare() / PQExecPrepare() / ...
(MISSING IN JAVA!) RunOnceStatement- PQExecParams()

例如，在 Postgres 中性能可能会更好，通过驱动程序映射到：
Statement Interface- PQExec()
PreparedStatement Interface- PQPrepare() / PQExecPrepare() / ...
（在 JAVA 中丢失！） RunOnceStatement-PQExecParams()

Using prepared statement in SQL code that runs just once is a BIG performance problem: more processing in database, waste database memory, by maintaining plans that will not called later. Cache plans get so crowed that actual SQL commands that are executed multiple times could be deleted from cache.

在只运行一次的 SQL 代码中使用准备好的语句是一个大的性能问题：在数据库中进行更多的处理，浪费数据库内存，通过维护以后不会调用的计划。缓存计划变得如此拥挤，以至于可以从缓存中删除多次执行的实际 SQL 命令。