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

问题描述

准备好的语句是语句的稍微强大的版本，并且应该始终至少与语句一样快速和容易处理.
准备好的语句可以被参数化

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. 执行优化的查询/获取和返回数据

对于发送到数据库的每个 SQL 查询，语句将始终执行上述四个步骤.Prepared Statement 预先执行上述执行过程中的步骤(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.

现在我的问题是:

使用Prepared Statement还有其他好处吗?"

"Is there any other advantage of using Prepared Statement?"

推荐答案

PreparedStatement:

• SQL 语句的预编译和 DB 端缓存导致整体执行速度更快，并且能够在 批次.

自动预防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 字符串中的值.

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 对象的设置，例如日期、时间、Timestamp, BigDecimal, InputStream(Blob) 和阅读器(Clob).对于大多数这些类型，您不能像在简单的 Statement 中那样仅仅"执行 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屋！