Using Prepared Statements使用事先准备好的陈述

This page covers the following topics:本页涵盖以下主题：

• Overview of Prepared Statements准备好的发言概述
• Creating a PreparedStatement Object创建PreparedStatement对象
• Supplying Values for PreparedStatement Parameters为PreparedStatement参数提供值

Overview of Prepared Statements准备好的语句概述

Sometimes it is more convenient to use a PreparedStatement object for sending SQL statements to the database. 有时，使用PreparedStatement对象向数据库发送SQL语句更方便。This special type of statement is derived from the more general class, Statement, that you already know.这种特殊类型的语句是从您已经知道的更一般的类Statement派生的。

If you want to execute a Statement object many times, it usually reduces execution time to use a PreparedStatement object instead.如果要多次执行Statement对象，通常使用PreparedStatement对象可以缩短执行时间。

The main feature of a PreparedStatement object is that, unlike a Statement object, it is given a SQL statement when it is created. PreparedStatement对象的主要特征是，与Statement对象不同，它在创建时被赋予SQL语句。The advantage to this is that in most cases, this SQL statement is sent to the DBMS right away, where it is compiled. 这样做的好处是，在大多数情况下，该SQL语句会立即发送到DBMS，并在那里进行编译。As a result, the PreparedStatement object contains not just a SQL statement, but a SQL statement that has been precompiled. 因此，PreparedStatement对象不仅包含SQL语句，还包含已预编译的SQL语句。This means that when the PreparedStatement is executed, the DBMS can just run the PreparedStatement SQL statement without having to compile it first.这意味着在执行PreparedStatement时，DBMS可以只运行PreparedStatement SQL语句，而不必首先编译它。

Although you can use PreparedStatement objects for SQL statements with no parameters, you probably use them most often for SQL statements that take parameters. 尽管您可以对不带参数的SQL语句使用PreparedStatement对象，但对于带参数的SQL语句，您可能最常使用它们。The advantage of using SQL statements that take parameters is that you can use the same statement and supply it with different values each time you execute it. 使用带参数的SQL语句的优点是，您可以使用同一语句，并在每次执行时为其提供不同的值。Examples of this are in the following sections.以下各节提供了这方面的示例。

However, the most important advantage of prepared statements is that they help prevent SQL injection attacks. 然而，预处理语句最重要的优点是它们有助于防止SQL注入攻击。SQL injection is a technique to maliciously exploit applications that use client-supplied data in SQL statements. SQL注入是一种恶意利用SQL语句中使用客户端提供的数据的应用程序的技术。Attackers trick the SQL engine into executing unintended commands by supplying specially crafted string input, thereby gaining unauthorized access to a database to view or manipulate restricted data. 攻击者通过提供巧尽心思构建的字符串输入，诱使SQL引擎执行意外命令，从而获得对数据库的未经授权访问，以查看或操作受限数据。SQL injection techniques all exploit a single vulnerability in the application: Incorrectly validated or nonvalidated string literals are concatenated into a dynamically built SQL statement and interpreted as code by the SQL engine. SQL注入技术都利用了应用程序中的一个漏洞：错误验证或未验证的字符串文本被连接到动态构建的SQL语句中，并由SQL引擎解释为代码。Prepared statements always treat client-supplied data as content of a parameter and never as a part of an SQL statement. Prepared语句始终将客户机提供的数据视为参数的内容，而不是SQL语句的一部分。See the section SQL Injection in Database PL/SQL Language Reference, part of Oracle Database documentation, for more information.有关更多信息，请参阅Oracle数据库文档的一部分“数据库PL/SQL语言参考”中的“SQL注入”一节。

The following method, CoffeesTable.updateCoffeeSales, stores the number of pounds of coffee sold in the current week in the SALES column for each type of coffee, and updates the total number of pounds of coffee sold in the TOTAL column for each type of coffee:以下方法CoffeesTable.updateCoffeeSales在SALES列中为每种类型的咖啡存储本周售出的咖啡磅数，并在TOTAL列中为每种类型的咖啡更新售出的咖啡磅总数：

public void updateCoffeeSales(HashMap<String, Integer> salesForWeek) throws SQLException {
    String updateString =
      "update COFFEES set SALES = ? where COF_NAME = ?";
    String updateStatement =
      "update COFFEES set TOTAL = TOTAL + ? where COF_NAME = ?";

    try (PreparedStatement updateSales = con.prepareStatement(updateString);
         PreparedStatement updateTotal = con.prepareStatement(updateStatement))
    
    {
      con.setAutoCommit(false);
      for (Map.Entry<String, Integer> e : salesForWeek.entrySet()) {
        updateSales.setInt(1, e.getValue().intValue());
        updateSales.setString(2, e.getKey());
        updateSales.executeUpdate();

        updateTotal.setInt(1, e.getValue().intValue());
        updateTotal.setString(2, e.getKey());
        updateTotal.executeUpdate();
        con.commit();
      }
    } catch (SQLException e) {
      JDBCTutorialUtilities.printSQLException(e);
      if (con != null) {
        try {
          System.err.print("Transaction is being rolled back");
          con.rollback();
        } catch (SQLException excep) {
          JDBCTutorialUtilities.printSQLException(excep);
        }
      }
    }
  }

Creating a PreparedStatement Object创建PreparedStatement对象

The following creates a PreparedStatement object that takes two input parameters:下面创建一个PreparedStatement对象，该对象接受两个输入参数：

String updateString =
      "update COFFEES " + "set SALES = ? where COF_NAME = ?";
	// ...
    PreparedStatement updateSales = con.prepareStatement(updateString);

Supplying Values for PreparedStatement Parameters为PreparedStatement参数提供值

You must supply values in place of the question mark placeholders (if there are any) before you can execute a PreparedStatement object. 在执行PreparedStatement对象之前，必须提供值来代替问号占位符（如果有）。Do this by calling one of the setter methods defined in the PreparedStatement class. 为此，请调用PreparedStatement类中定义的一个setter方法。The following statements supply the two question mark placeholders in the PreparedStatement named updateSales:以下语句在名为updateSales的PreparedStatement中提供了两个问号占位符：

updateSales.setInt(1, e.getValue().intValue());
updateSales.setString(2, e.getKey());

The first argument for each of these setter methods specifies the question mark placeholder. 每个setter方法的第一个参数都指定问号占位符。In this example, setInt specifies the first placeholder and setString specifies the second placeholder.在本例中，setInt指定第一个占位符，setString指定第二个占位符。

After a parameter has been set with a value, it retains that value until it is reset to another value, or the method clearParameters is called. 使用值设置参数后，它将保留该值，直到将其重置为另一个值，或者调用clearParameters方法。Using the PreparedStatement object updateSales, the following code fragment illustrates reusing a prepared statement after resetting the value of one of its parameters and leaving the other one the same:使用PreparedStatement对象updateSales，下面的代码片段演示了在重置一个参数的值并保持另一个参数不变后重用一个已准备好的语句：

// changes SALES column of French Roast
//row to 100

updateSales.setInt(1, 100);
updateSales.setString(2, "French_Roast");
updateSales.executeUpdate();

// changes SALES column of Espresso row to 100
// (the first parameter stayed 100, and the second
// parameter was reset to "Espresso")

updateSales.setString(2, "Espresso");
updateSales.executeUpdate();

Using Loops to Set Values使用循环设置值

You can often make coding easier by using a for loop or a while loop to set values for input parameters.通过使用for循环或while循环来设置输入参数的值，通常可以简化编码。

The CoffeesTable.updateCoffeeSales method uses a for-each loop to repeatedly set values in the PreparedStatement objects updateSales and updateTotal:CoffeesTable.updateCoffeeSales方法使用for-each循环重复设置PreparedStatement对象updateSales和updateTotal中的值：

for (Map.Entry<String, Integer> e : salesForWeek.entrySet()) {
  updateSales.setInt(1, e.getValue().intValue());
  updateSales.setString(2, e.getKey());
  // ...
}

The method CoffeesTable.updateCoffeeSales takes one argument, HashMap. 方法CoffeesTable.updateCoffeeSales接受一个参数HashMap。Each element in the HashMap argument contains the name of one type of coffee and the number of pounds of that type of coffee sold during the current week. HashMap参数中的每个元素都包含一种类型咖啡的名称以及该类型咖啡在当前一周内售出的磅数。The for-each loop iterates through each element of the HashMap argument and sets the appropriate question mark placeholders in updateSales and updateTotal.for-each循环遍历HashMap参数的每个元素，并在updateSales和updateTotal中设置适当的问号占位符。

Executing PreparedStatement Objects执行PreparedStatement对象

As with Statement objects, to execute a PreparedStatement object, call an execute statement: executeQuery if the query returns only one ResultSet (such as a SELECT SQL statement), executeUpdate if the query does not return a ResultSet (such as an UPDATE SQL statement), or execute if the query might return more than one ResultSet object. 与Statement对象一样，要执行PreparedStatement对象，请调用execute语句：如果查询仅返回一个ResultSet（如SELECT SQL语句），则调用executeQuery；如果查询未返回ResultSet（如UPDATE SQL语句），则调用executeUpdate；如果查询可能返回多个ResultSet对象，则调用execute语句。Both PreparedStatement objects in CoffeesTable.updateCoffeeSales contain UPDATE SQL statements, so both are executed by calling executeUpdate:CoffeesTable.updateCoffeeSales中的两个PreparedStatement对象都包含UPDATE SQL语句，因此都通过调用executeUpdate执行：

updateSales.setInt(1, e.getValue().intValue());
updateSales.setString(2, e.getKey());
updateSales.executeUpdate();

updateTotal.setInt(1, e.getValue().intValue());
updateTotal.setString(2, e.getKey());
updateTotal.executeUpdate();
con.commit();

No arguments are supplied to executeUpdate when they are used to execute updateSales and updateTotals; both PreparedStatement objects already contain the SQL statement to be executed.当用于执行updateSales和updateTotals时，不向executeUpdate提供任何参数；两个PreparedStatement对象都已包含要执行的SQL语句。

Note: At the beginning of CoffeesTable.updateCoffeeSales, the auto-commit mode is set to false:：在CoffeesTable.updateCoffeeSales的开头，自动提交模式设置为false：

con.setAutoCommit(false);

Consequently, no SQL statements are committed until the method commit is called. 因此，在调用commit方法之前，不会提交SQL语句。For more information about the auto-commit mode, see Transactions.有关自动提交模式的更多信息，请参阅事务。

Return Values for the executeUpdate Method返回executeUpdate方法的值

Whereas executeQuery returns a ResultSet object containing the results of the query sent to the DBMS, the return value for executeUpdate is an int value that indicates how many rows of a table were updated. executeQuery返回一个ResultSet对象，其中包含发送到DBMS的查询结果，而executeUpdate的返回值是一个int值，指示表中有多少行被更新。For instance, the following code shows the return value of executeUpdate being assigned to the variable n:例如，以下代码显示分配给变量n的executeUpdate的返回值：

updateSales.setInt(1, 50);
updateSales.setString(2, "Espresso");
int n = updateSales.executeUpdate();
// n = 1 because one row had a change in it

The table COFFEES is updated; the value 50 replaces the value in the column SALES in the row for Espresso. 更新了COFFEES表；值50将替换Espresso行中SALES列中的值。That update affects one row in the table, so n is equal to 1.该更新会影响表中的一行，因此n等于1。

When the method executeUpdate is used to execute a DDL (data definition language) statement, such as in creating a table, it returns the int value of 0. 当方法executeUpdate用于执行DDL（数据定义语言）语句时，例如在创建表时，它返回int值0。Consequently, in the following code fragment, which executes the DDL statement used to create the table COFFEES, n is assigned a value of 0:因此，在执行用于创建COFFEES表的DDL语句的以下代码片段中，n被赋值为0：

// n = 0
int n = executeUpdate(createTableCoffees);

Note that when the return value for executeUpdate is 0, it can mean one of two things:请注意，当executeUpdate的返回值为0时，它可能表示以下两种情况之一：

• The statement executed was an update statement that affected zero rows.执行的语句是影响零行的update语句。
• The statement executed was a DDL statement.执行的语句是DDL语句。