Statement和PreparedStatement之间的区别 [英] Difference between Statement and PreparedStatement

问题描述

Prepared Statement是一个稍微强大的Statement版本，并且应该始终至少与Statement一样快速和易于处理。

准备好的语句可以参数化

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

大多数关系数据库分四步处理JDBC / SQL查询：

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

1. 解析传入的SQL查询


2. 编译SQL查询


3. 计划/优化数据采集路径


4. 执行优化查询/获取并返回数据

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

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

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.

现在我的问题是 - 使用预备声明还有其他优势吗？

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

推荐答案

<$ c的优点$ c> PreparedStatement ：

• SQL的预编译和数据库端缓存声明导致整体执行速度更快，并且能够在批处理中重用相同的SQL语句。

• 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.

自动预防 SQL注入 通过内置转义引号和其他特殊字符进行攻击。请注意，这要求您使用任何 PreparedStatement setXxx（）方法来设置值

Automatic prevention of SQL injection attacks by builtin escaping of quotes and other special characters. Note that this requires that you use any of the PreparedStatement setXxx() 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();

因此不按字符串内联SQL字符串中的值-concatenating。

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

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

简化SQL字符串中非标准Java对象的设置，例如 日期 ， 时间 ， 时间戳 ， BigDecimal ， InputStream （ Blob ）和 Reader （ 的Clob ）。在大多数类型中，您不能像在简单的语句 toString（） >。您甚至可以使用 循环内的PreparedStatement #setObject（） ，如下面的实用方法所示：

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]);
    }
}

可以使用如下：

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();

这篇关于Statement和PreparedStatement之间的区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！