Java & MySQL - 可调用语句 (CallableStatement)

---

---

CallableStatement 接口用于执行对数据库存储过程的调用。

假设您需要在 TUTORIALSPOINT 数据库中执行以下存储过程：

DELIMITER $$

DROP PROCEDURE IF EXISTS `TUTORIALSPOINT`.`getEmpName` $$
CREATE PROCEDURE `TUTORIALSPOINT`.`getEmpName` 
   (IN EMP_ID INT, OUT EMP_FIRST VARCHAR(255))
BEGIN
   SELECT first INTO EMP_FIRST
   FROM Employees
   WHERE ID = EMP_ID;
END $$

DELIMITER ;

参数存在三种类型：IN、OUT 和 INOUT。PreparedStatement 对象仅使用 IN 参数。CallableStatement 对象可以使用所有三种类型。

以下是每种类型的定义：

以下代码片段演示了如何使用Connection.prepareCall() 方法基于前面的存储过程实例化CallableStatement 对象：

CallableStatement cstmt = null;
try {
   String SQL = "{call getEmpName (?, ?)}";
   cstmt = conn.prepareCall (SQL);
   . . .
}
catch (SQLException e) {
   . . .
}
finally {
   . . .
}

字符串变量 SQL 表示存储过程，带有参数占位符。

使用 CallableStatement 对象的方式与使用 PreparedStatement 对象非常相似。您必须在执行语句之前将值绑定到所有参数，否则将收到 SQLException。

如果您有 IN 参数，只需遵循适用于 PreparedStatement 对象的相同规则和技术；使用与您正在绑定的 Java 数据类型对应的 setXXX() 方法。

当您使用 OUT 和 INOUT 参数时，必须使用另一个 CallableStatement 方法 registerOutParameter()。registerOutParameter() 方法将 JDBC 数据类型绑定到存储过程预期返回的数据类型。

调用存储过程后，您可以使用相应的 getXXX() 方法从 OUT 参数检索值。此方法将检索到的 SQL 类型的值转换为 Java 数据类型。

关闭 CallableStatement 对象

就像关闭其他 Statement 对象一样，出于同样的原因，您也应该关闭 CallableStatement 对象。

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

CallableStatement cstmt = null;
try {
   String SQL = "{call getEmpName (?, ?)}";
   cstmt = conn.prepareCall (SQL);
   . . .
}
catch (SQLException e) {
   . . .
}
finally {
   cstmt.close();
}

我们使用 try-with-resources，它会自动处理资源关闭。以下示例演示了上述所有概念。

此代码是基于上一章中完成的环境和数据库设置编写的。

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

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class TestApplication {
   static final String DB_URL = "jdbc:mysql://127.0.0.1/TUTORIALSPOINT";
   static final String USER = "guest";
   static final String PASS = "guest123";
   static final String QUERY = "{call getEmpName (?, ?)}";

   public static void main(String[] args) {
      // Open a connection
      try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
         CallableStatement stmt = conn.prepareCall(QUERY);
      ) {		      
         // Bind values into the parameters.
         stmt.setInt(1, 102);  // This would set ID
         // Because second parameter is OUT so register it
         stmt.registerOutParameter(2, java.sql.Types.VARCHAR);
         //Use execute method to run stored procedure.
         System.out.println("Executing stored procedure..." );
         stmt.execute();
         //Retrieve employee name with getXXX method
         String empName = stmt.getString(2);
         System.out.println("Emp Name with ID: 102 is " + empName);
      } catch (SQLException e) {
         e.printStackTrace();
      } 
   }
}

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

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

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

C:\>java TestApplication
Executing stored procedure...
Emp Name with ID: 102 is Zaid
C:\>

JDBC SQL 转义语法

转义语法使您可以灵活地使用使用标准 JDBC 方法和属性无法使用的数据库特定功能。

一般的 SQL 转义语法格式如下：

{keyword 'parameters'}

以下是一些在执行 JDBC 编程时会发现非常有用的转义序列：

d、t、ts 关键字

它们有助于标识日期、时间和时间戳文字。如您所知，没有两个 DBMS 以相同的方式表示时间和日期。此转义语法告诉驱动程序以目标数据库的格式呈现日期或时间。例如：

{d 'yyyy-mm-dd'}

其中 yyyy = 年，mm = 月；dd = 日。使用此语法 {d '2009-09-03'} 是 2009 年 3 月 9 日。

这是一个简单的示例，演示如何将日期插入表中：

//Create a Statement object
stmt = conn.createStatement();
//Insert data ==> ID, First Name, Last Name, DOB
String sql="INSERT INTO STUDENTS VALUES" +
             "(100,'Zara','Ali', {d '2001-12-16'})";

stmt.executeUpdate(sql);

同样，您可以使用以下两种语法之一，即t 或ts：

{t 'hh:mm:ss'}

其中 hh = 小时；mm = 分钟；ss = 秒。使用此语法 {t '13:30:29'} 是下午 1:30:29。

{ts 'yyyy-mm-dd hh:mm:ss'}

这是上面两种语法 'd' 和 't' 的组合语法，用于表示时间戳。

escape 关键字

此关键字标识在 LIKE 子句中使用的转义字符。在使用 SQL 通配符 %（匹配零个或多个字符）时很有用。例如：

String sql = "SELECT symbol FROM MathSymbols WHERE symbol LIKE '\%' {escape '\'}";
stmt.execute(sql);

如果您使用反斜杠字符 (\) 作为转义字符，则还必须在 Java 字符串文字中使用两个反斜杠字符，因为反斜杠也是 Java 转义字符。

fn 关键字

此关键字表示 DBMS 中使用的标量函数。例如，您可以使用 SQL 函数length 获取字符串的长度：

{fn length('Hello World')}

这将返回 11，即字符字符串“Hello World”的长度。

call 关键字

此关键字用于调用存储过程。例如，对于需要 IN 参数的存储过程，请使用以下语法：

{call my_procedure(?)};

对于需要 IN 参数并返回 OUT 参数的存储过程，请使用以下语法：

{? = call my_procedure(?)};

oj 关键字

此关键字用于表示外连接。语法如下：

{oj outer-join}

其中 outer-join = 表 {LEFT|RIGHT|FULL} OUTER JOIN {表 | outer-join} on 搜索条件。例如：

String sql = "SELECT Employees FROM {oj ThisTable RIGHT OUTER JOIN ThatTable on id = '100'}";
stmt.execute(sql);