Processing SQL Statements with JDBC用JDBC处理SQL语句

In general, to process any SQL statement with JDBC, you follow these steps:通常，要使用JDBC处理任何SQL语句，请执行以下步骤：

1. Establishing a connection.建立联系。
2. Create a statement.创建一个语句。
3. Execute the query.执行查询。
4. Process the ResultSet object.处理ResultSet对象。
5. Close the connection.关闭连接。

This page uses the following method, CoffeesTables.viewTable, from the tutorial sample to demonstrate these steps.本页使用教程示例中的CoffeesTables.viewTable方法演示这些步骤。This method outputs the contents of the table COFFEES.此方法输出COFFEES表的内容。This method will be discussed in more detail later in this tutorial:本教程后面将更详细地讨论此方法：

public static void viewTable(Connection con) throws SQLException {
  String query = "select COF_NAME, SUP_ID, PRICE, SALES, TOTAL from COFFEES";
  try (Statement stmt = con.createStatement()) {
    ResultSet rs = stmt.executeQuery(query);
    while (rs.next()) {
      String coffeeName = rs.getString("COF_NAME");
      int supplierID = rs.getInt("SUP_ID");
      float price = rs.getFloat("PRICE");
      int sales = rs.getInt("SALES");
      int total = rs.getInt("TOTAL");
      System.out.println(coffeeName + ", " + supplierID + ", " + price +
                         ", " + sales + ", " + total);
    }
  } catch (SQLException e) {
    JDBCTutorialUtilities.printSQLException(e);
  }
}

Establishing Connections建立联系

First, establish a connection with the data source you want to use.首先，与要使用的数据源建立连接。A data source can be a DBMS, a legacy file system, or some other source of data with a corresponding JDBC driver.数据源可以是DBMS、遗留文件系统，也可以是具有相应JDBC驱动程序的其他数据源。This connection is represented by a Connection object.此连接由Connection对象表示。See Establishing a Connection for more information.有关更多信息，请参阅建立连接。

Creating Statements创建语句

A Statement is an interface that represents a SQL statement.Statement是表示SQL语句的接口。You execute Statement objects, and they generate ResultSet objects, which is a table of data representing a database result set.执行Statement 对象，它们生成ResultSet对象，ResultSet对象是表示数据库结果集的数据表。You need a Connection object to create a Statement object.您需要一个Connection对象来创建Statement对象。

For example, CoffeesTables.viewTable creates a Statement object with the following code:例如，CoffeesTables.viewTable使用以下代码创建Statement 对象：

stmt = con.createStatement();

There are three different kinds of statements:存在三种不同的语句：

• Statement: Used to implement simple SQL statements with no parameters.：用于实现没有参数的简单SQL语句。
• PreparedStatement: (Extends Statement.)：（扩展Statement。）Used for precompiling SQL statements that might contain input parameters.用于预编译可能包含输入参数的SQL语句。See Using Prepared Statements for more information.有关更多信息，请参阅使用准备好的语句。
• CallableStatement: (Extends PreparedStatement.)（扩展PreparedStatement。）Used to execute stored procedures that may contain both input and output parameters.用于执行可能包含输入和输出参数的存储过程。See Stored Procedures for more information.有关更多信息，请参阅存储过程。

Executing Queries执行查询

To execute a query, call an execute method from Statement such as the following:要执行查询，请从以下Statement调用execute方法：

• execute: Returns true if the first object that the query returns is a ResultSet object.：如果查询返回的第一个对象是ResultSet对象，则返回true。Use this method if the query could return one or more ResultSet objects.如果查询可以返回一个或多个ResultSet对象，请使用此方法。Retrieve the ResultSet objects returned from the query by repeatedly calling Statement.getResultSet.通过重复调用Statement.getResultSet来检索查询返回的ResultSet对象。
• executeQuery: Returns one ResultSet object.：返回一个ResultSet对象。
• executeUpdate: Returns an integer representing the number of rows affected by the SQL statement.：返回一个整数，表示受SQL语句影响的行数。Use this method if you are using INSERT, DELETE, or UPDATE SQL statements.如果使用INSERT、DELETE或UPDATE SQL语句，请使用此方法。

For example, CoffeesTables.viewTable executed a Statement object with the following code:例如，CoffeesTables.viewTable使用以下代码执行Statement对象：

ResultSet rs = stmt.executeQuery(query);

See Retrieving and Modifying Values from Result Sets for more information.有关详细信息，请参阅从结果集中检索和修改值。

Processing ResultSet Objects处理结果集对象

You access the data in a ResultSet object through a cursor.您可以通过光标访问ResultSet对象中的数据。Note that this cursor is not a database cursor.请注意，此游标不是数据库游标。This cursor is a pointer that points to one row of data in the ResultSet object.此光标是指向ResultSet对象中一行数据的指针。Initially, the cursor is positioned before the first row.最初，光标位于第一行之前。You call various methods defined in the ResultSet object to move the cursor.可以调用ResultSet对象中定义的各种方法来移动光标。

For example, CoffeesTables.viewTable repeatedly calls the method ResultSet.next to move the cursor forward by one row.例如，CoffeesTables.viewTable反复调用ResultSet.next方法，将光标向前移动一行。Every time it calls next, the method outputs the data in the row where the cursor is currently positioned:每次调用next时，该方法都会输出光标当前所在行中的数据：

ResultSet rs = stmt.executeQuery(query);
while (rs.next()) {
  String coffeeName = rs.getString("COF_NAME");
  int supplierID = rs.getInt("SUP_ID");
  float price = rs.getFloat("PRICE");
  int sales = rs.getInt("SALES");
  int total = rs.getInt("TOTAL");
  System.out.println(coffeeName + ", " + supplierID + ", " + price +
                     ", " + sales + ", " + total);
}
// ...

See Retrieving and Modifying Values from Result Sets for more information.有关详细信息，请参阅从结果集中检索和修改值。

Closing Connections关闭连接

When you are finished using a Connection, Statement, or ResultSet object, call its close method to immediately release the resources it's using.使用完Connection、Statement或ResultSet对象后，调用其close方法以立即释放其使用的资源。

Alternatively, use a try-with-resources statement to automatically close Connection, Statement, and ResultSet objects, regardless of whether an SQLException has been thrown.或者，使用try-with-resources语句自动关闭Connection、Statement和ResultSet对象，而不管是否引发了SQLException。(JDBC throws an SQLException when it encounters an error during an interaction with a data source.（JDBC在与数据源交互期间遇到错误时抛出SQLException。See Handling SQL Exceptions for more information.)有关详细信息，请参阅处理SQL异常。）An automatic resource statement consists of a try statement and one or more declared resources.自动资源语句由try语句和一个或多个声明的资源组成。For example, the CoffeesTables.viewTable method automatically closes its Statement object, as follows:例如，CoffeesTables.viewTable方法会自动关闭其Statement对象，如下所示：

public static void viewTable(Connection con) throws SQLException {
  String query = "select COF_NAME, SUP_ID, PRICE, SALES, TOTAL from COFFEES";
  try (Statement stmt = con.createStatement()) {
    ResultSet rs = stmt.executeQuery(query);
    while (rs.next()) {
      String coffeeName = rs.getString("COF_NAME");
      int supplierID = rs.getInt("SUP_ID");
      float price = rs.getFloat("PRICE");
      int sales = rs.getInt("SALES");
      int total = rs.getInt("TOTAL");
      System.out.println(coffeeName + ", " + supplierID + ", " + price +
                         ", " + sales + ", " + total);
    }
  } catch (SQLException e) {
    JDBCTutorialUtilities.printSQLException(e);
  }
}

The following statement is a try-with-resources statement, which declares one resource, stmt, that will be automatically closed when the try block terminates:以下语句是try-with-resources语句，它声明了一个资源stmt，该资源将在try块终止时自动关闭：

try (Statement stmt = con.createStatement()) {
  // ...
}

See The try-with-resources Statement in the Essential Classes trail for more information.有关更多信息，请参阅基本类跟踪中的try-with-resources语句。