Java Statement 和 PreparedStatement 的区别

Difference between Statement and PreparedStatement

提问by CodeBee..

The Prepared Statement is a slightly more powerful version of a Statement, and should always be at least as quick and easy to handle as a Statement.
The Prepared Statement may be parametrized

Prepared Statement 是一个更强大的 Statement 版本，并且应该始终至少与 Statement 一样快速和容易处理。
准备好的报表可以参数化

Most relational databases handles a JDBC / SQL query in four steps:

大多数关系数据库通过四个步骤处理 JDBC/SQL 查询：

1. Parse the incoming SQL query
2. Compile the SQL query
3. Plan/optimize the data acquisition path
4. Execute the optimized query / acquire and return data

1. 解析传入的 SQL 查询
2. 编译 SQL 查询
3. 规划/优化数据采集路径
4. 执行优化的查询/获取和返回数据

A Statement will always proceed through the four steps above for each SQL query sent to the database. A Prepared Statement pre-executes steps (1) - (3) in the execution process above. Thus, when creating a Prepared Statement some pre-optimization is performed immediately. The effect is to lessen the load on the database engine at execution time.

对于发送到数据库的每个 SQL 查询，语句将始终执行上述四个步骤。Prepared Statement 预先执行上述执行过程中的步骤（1）-（3）。因此，在创建 Prepared Statement 时，会立即执行一些预优化。其效果是在执行时减轻数据库引擎的负载。

Now my question is that - "Is any other advantage of using Prepared Statement?"

现在我的问题是 - “使用准备好的语句还有其他好处吗？”

回答by duffymo

PreparedStatementis a very good defense (but not foolproof) in preventing SQL injection attacks. Binding parameter values is a good way to guarding against "little Bobby Tables"making an unwanted visit.

PreparedStatement是防止SQL 注入攻击的非常好的防御（但并非万无一失）。绑定参数值是防止“小鲍比表”进行不必要访问的好方法。

回答by mhshams

nothing much to add,

没什么可补充的，

1 - if you want to execute a query in a loop (more than 1 time), prepared statement can be faster, because of optimization that you mentioned.

1 - 如果您想在循环中执行查询（超过 1 次），准备好的语句可以更快，因为您提到了优化。

2 - parameterized query is a good way to avoid SQL Injection. Parameterized querys are only available in PreparedStatement.

2 - 参数化查询是避免 SQL 注入的好方法。参数化查询仅在 PreparedStatement 中可用。

回答by BalusC

Advantages of a PreparedStatement:

优点PreparedStatement：

• Precompilation and DB-side caching of the SQL statement leads to overall faster execution and the ability to reuse the same SQL statement in batches.

• Automatic prevention of SQL injectionattacksby builtin escaping of quotes and other special characters. Note that this requires that you use any of the PreparedStatementsetXxx()methods to set the values

  preparedStatement = connection.prepareStatement("INSERT INTO Person (name, email, birthdate, photo) VALUES (?, ?, ?, ?)");
  preparedStatement.setString(1, person.getName());
  preparedStatement.setString(2, person.getEmail());
  preparedStatement.setTimestamp(3, new Timestamp(person.getBirthdate().getTime()));
  preparedStatement.setBinaryStream(4, person.getPhoto());
  preparedStatement.executeUpdate();
  

  and thus don'tinline the values in the SQL string by string-concatenating.

  preparedStatement = connection.prepareStatement("INSERT INTO Person (name, email) VALUES ('" + person.getName() + "', '" + person.getEmail() + "'");
  preparedStatement.executeUpdate();
  

• Eases setting of non-standard Java objects in a SQL string, e.g. Date, Time, Timestamp, BigDecimal, InputStream(Blob) and Reader(Clob). On most of those types you can't "just" do a toString()as you would do in a simple Statement. You could even refactor it all to using PreparedStatement#setObject()inside a loop as demonstrated in the utility method below:

  public static void setValues(PreparedStatement preparedStatement, Object... values) throws SQLException {
      for (int i = 0; i < values.length; i++) {
          preparedStatement.setObject(i + 1, values[i]);
      }
  }
  

  Which can be used as below:

  preparedStatement = connection.prepareStatement("INSERT INTO Person (name, email, birthdate, photo) VALUES (?, ?, ?, ?)");
  setValues(preparedStatement, person.getName(), person.getEmail(), new Timestamp(person.getBirthdate().getTime()), person.getPhoto());
  preparedStatement.executeUpdate();
  

• SQL 语句的预编译和 DB 端缓存导致整体执行速度更快，并且能够批量重用相同的 SQL 语句。

• 通过内置转义引号和其他特殊字符自动防止SQL 注入攻击。请注意，这要求您使用任何PreparedStatementsetXxx()方法来设置值

  preparedStatement = connection.prepareStatement("INSERT INTO Person (name, email, birthdate, photo) VALUES (?, ?, ?, ?)");
  preparedStatement.setString(1, person.getName());
  preparedStatement.setString(2, person.getEmail());
  preparedStatement.setTimestamp(3, new Timestamp(person.getBirthdate().getTime()));
  preparedStatement.setBinaryStream(4, person.getPhoto());
  preparedStatement.executeUpdate();
  

  因此不要通过字符串连接来内联 SQL 字符串中的值。

  preparedStatement = connection.prepareStatement("INSERT INTO Person (name, email) VALUES ('" + person.getName() + "', '" + person.getEmail() + "'");
  preparedStatement.executeUpdate();
  

• 简化 SQL 字符串中非标准 Java 对象的设置，例如Date, Time, Timestamp, BigDecimal, InputStream( Blob) 和Reader( Clob)。在大多数这些类型中，您不能toString()像在简单的Statement. 您甚至可以将其全部重构为PreparedStatement#setObject()在循环内使用，如下面的实用程序方法所示：

  public static void setValues(PreparedStatement preparedStatement, Object... values) throws SQLException {
      for (int i = 0; i < values.length; i++) {
          preparedStatement.setObject(i + 1, values[i]);
      }
  }
  

  可以如下使用：

  preparedStatement = connection.prepareStatement("INSERT INTO Person (name, email, birthdate, photo) VALUES (?, ?, ?, ?)");
  setValues(preparedStatement, person.getName(), person.getEmail(), new Timestamp(person.getBirthdate().getTime()), person.getPhoto());
  preparedStatement.executeUpdate();
  

回答by orbfish

Can't do CLOBs in a Statement.

不能在语句中执行 CLOB。

And: (OraclePreparedStatement) ps

和： (OraclePreparedStatement) ps

回答by nanda

• It's easier to read
• You can easily make the query string a constant

• 更容易阅读
• 您可以轻松地将查询字符串设为常量

回答by ashish geol

sql injection is ignored by prepared statement so security is increase in prepared statement

预准备语句忽略 sql 注入，因此增加了预准备语句的安全性

回答by Bernard

Statementinterface executes static SQL statements without parameters

Statement接口执行不带参数的静态SQL语句

PreparedStatementinterface (extending Statement) executes a precompiled SQL statement with/without parameters

PreparedStatementinterface (extending Statement) 执行带/不带参数的预编译 SQL 语句

1. Efficient for repeated executions

2. It is precompiled so it's faster

1. 高效重复执行

2. 它是预编译的，所以速度更快

回答by Glen Best

1. They are pre-compiled (once), so faster for repeated execution of dynamic SQL (where parameters change)

2. Database statement caching boosts DB execution performance

   Databases store caches of execution plans for previously executed statements. This allows the database engine to reuse the plans for statements that have been executed previously. Because PreparedStatement uses parameters, each time it is executed it appears as the same SQL, the database can reuse the previous access plan, reducing processing. Statements "inline" the parameters into the SQL string and so do not appear as the same SQL to the DB, preventing cache usage.

3. Binary communications protocol means less bandwidth and faster comms calls to DB server

   Prepared statements are normally executed through a non-SQL binary protocol. This means that there is less data in the packets, so communications to the server is faster. As a rule of thumb network operations are an order of magnitude slower than disk operations which are an order of magnitude slower than in-memory CPU operations. Hence, any reduction in amount of data sent over the network will have a good effect on overall performance.

4. They protect against SQL injection, by escaping text for all the parameter values provided.

5. They provide stronger separation between the query code and the parameter values (compared to concatenated SQL strings), boosting readability and helping code maintainers quickly understand inputs and outputs of the query.

6. In java, can call getMetadata() and getParameterMetadata() to reflect on the result set fields and the parameter fields, respectively

7. In java, intelligently accepts java objects as parameter types via setObject, setBoolean, setByte, setDate, setDouble, setDouble, setFloat, setInt, setLong, setShort, setTime, setTimestamp - it converts into JDBC type format that is comprehendible to DB (not just toString() format).

8. In java, accepts SQL ARRAYs, as parameter type via setArray method

9. In java, accepts CLOBs, BLOBs, OutputStreams and Readers as parameter "feeds" via setClob/setNClob, setBlob, setBinaryStream, setCharacterStream/setAsciiStream/setNCharacterStream methods, respectively

10. In java, allows DB-specific values to be set for SQL DATALINK, SQL ROWID, SQL XML, and NULL via setURL, setRowId, setSQLXML ans setNull methods

11. In java, inherits all methods from Statement. It inherits the addBatch method, and additionally allows a set of parameter values to be added to match the set of batched SQL commands via addBatch method.

12. In java, a special type of PreparedStatement (the subclass CallableStatement) allows stored procedures to be executed - supporting high performance, encapsulation, procedural programming and SQL, DB administration/maintenance/tweaking of logic, and use of proprietary DB logic & features

1. 它们是预编译的（一次），因此可以更快地重复执行动态 SQL（其中参数更改）

2. 数据库语句缓存提升数据库执行性能

   数据库存储先前执行的语句的执行计划的缓存。这允许数据库引擎重用先前已执行的语句的计划。由于PreparedStatement使用参数，每次执行都表现为同一个SQL，数据库可以复用之前的访问计划，减少处理。语句将参数“内联”到 SQL 字符串中，因此不会作为相同的 SQL 出现在数据库中，从而防止使用缓存。

3. 二进制通信协议意味着更少的带宽和更快的对数据库服务器的通信调用

   准备好的语句通常通过非 SQL 二进制协议执行。这意味着数据包中的数据较少，因此与服务器的通信速度更快。根据经验，网络操作比磁盘操作慢一个数量级，磁盘操作比内存 CPU 操作慢一个数量级。因此，通过网络发送的数据量的任何减少都会对整体性能产生良好的影响。

4. 它们通过转义提供的所有参数值的文本来防止 SQL 注入。

5. 它们在查询代码和参数值之间提供了更强的分离（与串联的 SQL 字符串相比），提高了可读性并帮助代码维护人员快速理解查询的输入和输出。

6. 在java中，可以调用getMetadata()和getParameterMetadata()分别反映结果集字段和参数字段

7. 在java中，通过setObject、setBoolean、setByte、setDate、setDouble、setDouble、setFloat、setInt、setLong、setShort、setTime、setTimestamp智能地接受java对象作为参数类型——它转换成DB可以理解的JDBC类型格式（不仅仅是toString （） 格式）。

8. 在 java 中，通过 setArray 方法接受 SQL ARRAY 作为参数类型

9. 在 java 中，分别通过 setClob/setNClob、setBlob、setBinaryStream、setCharacterStream/setAsciiStream/setNCharacterStream 方法接受 CLOB、BLOB、OutputStreams 和 Readers 作为参数“提要”

10. 在 Java 中，允许通过 setURL、setRowId、setSQLXML 和 setNull 方法为 SQL DATALINK、SQL ROWID、SQL XML 和 NULL 设置特定于数据库的值

11. 在java中，继承Statement的所有方法。它继承了 addBatch 方法，另外还允许通过 addBatch 方法添加一组参数值以匹配一组批处理 SQL 命令。

12. 在 Java 中，一种特殊类型的 PreparedStatement（子类 CallableStatement）允许执行存储过程 - 支持高性能、封装、过程编程和 SQL、DB 管理/维护/逻辑调整以及专有 DB 逻辑和特性的使用

回答by MARA MP

Statement will be used for executing static SQL statements and it can't accept input parameters.

Statement 将用于执行静态 SQL 语句，它不能接受输入参数。

PreparedStatement will be used for executing SQL statements many times dynamically. It will accept input parameters.

PreparedStatement 将用于多次动态执行 SQL 语句。它将接受输入参数。

回答by sandeep vanama

Statement is static and prepared statment is dynamic.

语句是静态的，准备好的语句是动态的。

Statement is suitable for DDL and prepared statment for DML.

语句适用于 DDL，准备好的语句用于 DML。

Statement is slower while prepared statement is faster.

语句较慢，而准备好的语句较快。

more differences

更多差异