Java Mysql 简明教程

Java & MySQL - PreparedStatement

PreparedStatement 接口扩展了 Statement 接口，它为您提供了与通用 Statement 对象相比具有一些优势的附加功能。

The PreparedStatement interface extends the Statement interface, which gives you added functionality with a couple of advantages over a generic Statement object.

此语句使您能够灵活地动态提供参数。

This statement gives you the flexibility of supplying arguments dynamically.

Creating PreparedStatement Object

PreparedStatement pstmt = null;
try {
   String SQL = "Update Employees SET age = ? WHERE id = ?";
   pstmt = conn.prepareStatement(SQL);
   . . .
}
catch (SQLException e) {
   . . .
}
finally {
   . . .
}

JDBC 中的所有参数都由 ? 符号表示，该符号称为参数标记。在执行 SQL 语句之前，您必须为每个参数提供值。

All parameters in JDBC are represented by the ? symbol, which is known as the parameter marker. You must supply values for every parameter before executing the SQL statement.

setXXX() 方法将值绑定到参数，其中 XXX 表示要绑定到输入参数的值的 Java 数据类型。如果您忘记提供值，您将收到一个 SQLException。

The setXXX() methods bind values to the parameters, where XXX represents the Java data type of the value you wish to bind to the input parameter. If you forget to supply the values, you will receive an SQLException.

每个参数标记由其顺序位置指代。第一个标记表示位置 1，下一个位置 2，以此类推。此方法不同于 Java 数组索引，后者从 0 开始。

Each parameter marker is referred by its ordinal position. The first marker represents position 1, the next position 2, and so forth. This method differs from that of Java array indices, which starts at 0.

所有用于与数据库交互的 Statement object’s 方法 (a) execute()、(b) executeQuery() 和 (c) executeUpdate() 也可与 PreparedStatement 对象一起使用。但是，这些方法已修改为使用可以输入参数的 SQL 语句。

All of the Statement object’s methods for interacting with the database (a) execute(), (b) executeQuery(), and (c) executeUpdate() also work with the PreparedStatement object. However, the methods are modified to use SQL statements that can input the parameters.

Closing PreparedStatement Object

正如你关闭一个 Statement 对象一样，由于同样的原因，你应该关闭 PreparedStatement 对象。

Just as you close a Statement object, for the same reason you should also close the PreparedStatement object.

只需调用一次 close() 方法即可完成此任务。如果你先关闭 Connection 对象，它也会关闭 PreparedStatement 对象。但是，你应该始终显式关闭 PreparedStatement 对象，以确保正确清理。

A simple call to the close() method will do the job. If you close the Connection object first, it will close the PreparedStatement object as well. However, you should always explicitly close the PreparedStatement object to ensure proper cleanup.

PreparedStatement pstmt = null;
try {
   String SQL = "Update Employees SET age = ? WHERE id = ?";
   pstmt = conn.prepareStatement(SQL);
   . . .
}
catch (SQLException e) {
   . . .
}
finally {
   pstmt.close();
}

我们使用 try 和 resources（它会自动处理资源关闭）。下面的示例演示了所有上述概念。

We’re using try with resources which handles the resource closure automatically. Following example demonstrates all of the above said concepts.

这段代码是根据前一章完成的环境和数据库设置编写的。

This code has been written based on the environment and database setup done in the previous chapter.

将以下示例复制并粘贴到 TestApplication.java 中，如下进行编译和运行：

Copy and paste the following example in TestApplication.java, compile and run as follows −

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TestApplication {
   static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT";
   static final String USER = "guest";
   static final String PASS = "guest123";
   static final String QUERY = "SELECT id, first, last, age FROM Employees";
   static final String UPDATE_QUERY = "UPDATE Employees set age=? WHERE id=?";

   public static void main(String[] args) {
      // Open a connection
      try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
         PreparedStatement stmt = conn.prepareStatement(UPDATE_QUERY);
      ) {
         // Bind values into the parameters.
         stmt.setInt(1, 35);  // This would set age
         stmt.setInt(2, 102); // This would set ID

         // Let us update age of the record with ID = 102;
         int rows = stmt.executeUpdate();
         System.out.println("Rows impacted : " + rows );

         // Let us select all the records and display them.
         ResultSet rs = stmt.executeQuery(QUERY);

         // Extract data from result set
         while (rs.next()) {
            // Retrieve by column name
            System.out.print("ID: " + rs.getInt("id"));
            System.out.print(", Age: " + rs.getInt("age"));
            System.out.print(", First: " + rs.getString("first"));
            System.out.println(", Last: " + rs.getString("last"));
         }
         rs.close();
      } catch (SQLException e) {
         e.printStackTrace();
      }
   }
}

现在让我们如下编译上述示例：

Now let us compile the above example as follows −

C:\>javac TestApplication.java
C:\>

运行 TestApplication 时，它会生成以下结果：

When you run TestApplication, it produces the following result −

C:\>java TestApplication
Return value is : false
Rows impacted : 1
ID: 100, Age: 18, First: Zara, Last: Ali
ID: 101, Age: 25, First: Mehnaz, Last: Fatma
ID: 102, Age: 35, First: Zaid, Last: Khan
ID: 103, Age: 30, First: Sumit, Last: Mittal
C:\>