详解Java的JDBC API的存储过程与SQL转义语法的使用

时间:2022-06-04 05:30:05

正如一个Connection对象创建Statement和PreparedStatement对象,它也创造了CallableStatement对象这将被用来执行调用数据库存储过程。

创建CallableStatement对象:
假设,需要执行以下Oracle存储过程:

?
1
2
3
4
5
6
7
CREATE OR REPLACE PROCEDURE getEmpName
  (EMP_ID IN NUMBER, EMP_FIRST OUT VARCHAR) AS
BEGIN
  SELECT first INTO EMP_FIRST
  FROM Employees
  WHERE ID = EMP_ID;
END;

注意: 上面已经写过Oracle存储过程,但我们正在使用MySQL数据库,写相同的存储过程对于MySQL如下,以EMP数据库中创建它:

?
1
2
3
4
5
6
7
8
9
10
11
12
DELIMITER $$
 
DROP PROCEDURE IF EXISTS `EMP`.`getEmpName` $$
CREATE PROCEDURE `EMP`.`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对象可以使用所有的三个。

这里是每个定义:

  • IN:它的值是在创建SQL语句时未知的参数。将值绑定到setXXX()方法的参数。
  • OUT:其值是由它返回的SQL语句提供的参数。你从OUT参数的getXXX()方法检索值。
  • INOUT:同时提供输入和输出值的参数。绑定setXXX()方法的变量,并与getXXX()方法检索值。

下面的代码片段显示了如何使用该Connection.prepareCall()方法实例化基于上述存储过程CallableStatement对象:

?
1
2
3
4
5
6
7
8
9
10
11
12
CallableStatement cstmt = null;
try {
  String SQL = "{call getEmpName (?, ?)}";
  cstmt = conn.prepareCall (SQL);
  . . .
}
catch (SQLException e) {
  . . .
}
finally {
  . . .
}

String变量的SQL表示存储过程,使用参数占位符。

使用CallableStatement对象是使用PreparedStatement对象。必须将值绑定到所有的参数执行该语句之前,否则将收到一个SQLException。

如果有IN参数,只要按照适用于PreparedStatement对象相同的规则和技巧;使用对应于要绑定Java数据类型的setXXX()方法。

当使用OUT和INOUT参数就必须采用额外CallableStatement方法的registerOutParameter()。registerOutParameter()方法JDBC数据类型绑定到数据类型的存储过程返回。

一旦调用存储过程,用getXXX()方法的输出参数检索值。这种方法投射SQL类型的值检索到Java数据类型。

关闭CallableStatement 对象:
正如关闭其他Statement对象,出于同样的原因,也应该关闭CallableStatement对象。

close()方法简单的调用将完成这项工作。如果关闭了Connection对象首先它会关闭CallableStatement对象为好。然而,应该始终明确关闭的CallableStatement对象,以确保正确的清除。

?
1
2
3
4
5
6
7
8
9
10
11
12
CallableStatement cstmt = null;
try {
  String SQL = "{call getEmpName (?, ?)}";
  cstmt = conn.prepareCall (SQL);
  . . .
}
catch (SQLException e) {
  . . .
}
finally {
  cstmt.close();
}

PS:CallableStatement对象实例
下面是利用CallableStatement连同下列getEmpName()的MySQL存储过程的例子:

请确定已经在EMP数据库中创建该存储过程。可以使用MySQL查询浏览器来完成它。

?
1
2
3
4
5
6
7
8
9
10
11
12
DELIMITER $$
 
DROP PROCEDURE IF EXISTS `EMP`.`getEmpName` $$
CREATE PROCEDURE `EMP`.`getEmpName`
  (IN EMP_ID INT, OUT EMP_FIRST VARCHAR(255))
BEGIN
  SELECT first INTO EMP_FIRST
  FROM Employees
  WHERE ID = EMP_ID;
END $$
 
DELIMITER ;

基于对环境和数据库安装在前面的章节中进行,这个范例程式码已被写入。

复制下面的例子中JDBCExample.java,编译并运行,如下所示:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
//STEP 1. Import required packages
import java.sql.*;
 
public class JDBCExample {
  // JDBC driver name and database URL
  static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
  static final String DB_URL = "jdbc:mysql://localhost/EMP";
 
  // Database credentials
  static final String USER = "username";
  static final String PASS = "password";
  
  public static void main(String[] args) {
  Connection conn = null;
  CallableStatement stmt = null;
  try{
   //STEP 2: Register JDBC driver
   Class.forName("com.mysql.jdbc.Driver");
 
   //STEP 3: Open a connection
   System.out.println("Connecting to database...");
   conn = DriverManager.getConnection(DB_URL,USER,PASS);
 
   //STEP 4: Execute a query
   System.out.println("Creating statement...");
   String sql = "{call getEmpName (?, ?)}";
   stmt = conn.prepareCall(sql);
   
   //Bind IN parameter first, then bind OUT parameter
   int empID = 102;
   stmt.setInt(1, empID); // This would set ID as 102
   // 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:" +
        empID + " is " + empName);
   stmt.close();
   conn.close();
  }catch(SQLException se){
   //Handle errors for JDBC
   se.printStackTrace();
  }catch(Exception e){
   //Handle errors for Class.forName
   e.printStackTrace();
  }finally{
   //finally block used to close resources
   try{
     if(stmt!=null)
      stmt.close();
   }catch(SQLException se2){
   }// nothing we can do
   try{
     if(conn!=null)
      conn.close();
   }catch(SQLException se){
     se.printStackTrace();
   }//end finally try
  }//end try
  System.out.println("Goodbye!");
}//end main
}//end JDBCExample

现在编译上面的例子如下:

?
1
C:>javac JDBCExample.java

当运行JDBCExample,它会产生以下结果:

?
1
C:>java JDBCExample
?
1
2
3
4
5
Connecting to database...
Creating statement...
Executing stored procedure...
Emp Name with ID:102 is Zaid
Goodbye!

JDBC的SQL转义语法:
转义语法使能够使用通过使用标准的JDBC方法和属性,无法使用数据库的某些特性的灵活性。

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

?
1
{keyword 'parameters'}

这里有以下这些,会发现非常有用的,而这样做的JDBC编程的转义序列:

d, t, ts 关键字:
他们帮助确定日期,时间和时间戳记文字。如所知,没有两个数据库管理系统是基于时间和日期的方式相同。此转义语法告诉驱动程序呈现在目标数据库的格式,日期或时间。实现例子:

?
1
{d 'yyyy-mm-dd'}

其中yyyy=年,mm =月,DD =日。使用这种语法 {d '2009-09-03'}是2009年3月9日。

下面是一个简单的例子说明如何插入日期表:

?
1
2
3
4
5
6
7
//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:

?
1
{t 'hh:mm:ss'}

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

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

这是上述两种语法 'd' 和  't' 来表示时间戳结合语法。

escape 关键字:
该关键字标识LIKE子句中使用的转义字符。有用使用SQL通配符%,其中匹配零个或多个字符时。例如:

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

如果使用反斜杠字符()作为转义字符,还必须使用两个反斜杠字符在Java字符串字面,因为反斜杠也是一个Java转义字符。

fn 关键字:
此关键字代表在DBMS中使用标量函数。例如,可以使用SQL length函数计算GE字符串的长度:

?
1
{fn length('Hello World')}

这将返回11,字符串 'Hello World'的长度。.

call 关键字:
此关键字是用来调用存储过程。例如,对于一个存储过程,需要一个IN参数,请使用以下语法:

?
1
{call my_procedure(?)};

对于一个存储过程,需要一个IN参数并返回一个OUT参数,使用下面的语法:

?
1
{? = call my_procedure(?)};

oj 关键字:
此关键字用来表示外部联接。其语法如下:

?
1
{oj outer-join}

外连接表={LEFT| RIGHT| FULL}外连接{表|外连接}的搜索条件。例如:

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