Java Mysql 简明教程

Java & MySQL - Statement

JDBC Statement 接口定义了方法和属性，以便能够向 MySQL 数据库发送 SQL 命令并从数据库中检索数据。Statement 用于一般性地访问您的数据库。当您在运行时使用静态 SQL 语句时，它非常有用。Statement 接口无法接受参数。

JDBC Statement interface defines the methods and properties to enable send SQL commands to MySQL database and retrieve data from the database. Statement is used for general-purpose access to your database. It is useful when you are using static SQL statements at runtime. The Statement interface cannot accept parameters.

在使用 Statement 对象执行 SQL 语句之前，您需要使用 Connection 对象的 createStatement() 方法创建一个对象，如下例所示：

Before you can use a Statement object to execute a SQL statement, you need to create one using the Connection object’s createStatement( ) method, as in the following example −

Statement stmt = null;
try {
   stmt = conn.createStatement( );
   . . .
}
catch (SQLException e) {
   . . .
}
finally {
   . . .
}

创建 Statement 对象后，可以使用它使用其三个 execute 方法之一来执行 SQL 语句。

Once you’ve created a Statement object, you can then use it to execute an SQL statement with one of its three execute methods.

boolean execute (String SQL) − Returns a boolean value of true if a ResultSet object can be retrieved; otherwise, it returns false. Use this method to execute SQL DDL statements or when you need to use truly dynamic SQL.
int executeUpdate (String SQL) − Returns the number of rows affected by the execution of the SQL statement. Use this method to execute SQL statements for which you expect to get a number of rows affected - for example, an INSERT, UPDATE, or DELETE statement.
ResultSet executeQuery (String SQL) − Returns a ResultSet object. Use this method when you expect to get a result set, as you would with a SELECT statement.

Closing Statement Object

就像关闭 Connection 对象以保存数据库资源一样，出于相同的原因，您还应该关闭 Statement 对象。

Just as you close a Connection object to save database resources, for the same reason you should also close the Statement object.

对 close() 方法的简单调用就可以完成此工作。如果您首先关闭 Connection 对象，它也会关闭 Statement 对象。但是，您应该始终显式关闭 Statement 对象以确保正确清理。

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

Statement stmt = null;
try {
   stmt = conn.createStatement( );
   . . .
}
catch (SQLException e) {
   . . .
}
finally {
   stmt.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.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

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=30 WHERE id=103";

   public static void main(String[] args) {
      // Open a connection
      try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
         Statement stmt = conn.createStatement();
         ) {
         // Let us check if it returns a true Result Set or not.
         Boolean ret = stmt.execute(UPDATE_QUERY);
         System.out.println("Return value is : " + ret.toString() );

         // Let us update age of the record with ID = 103;
         int rows = stmt.executeUpdate(UPDATE_QUERY);
         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: 30, First: Zaid, Last: Khan
ID: 103, Age: 30, First: Sumit, Last: Mittal
C:\>