JDBC - 存储过程

上一页
下一页

在讨论JDBC - 语句章节时，我们学习了如何在JDBC中使用存储过程。本章节与该部分内容类似，但会提供关于JDBC SQL转义语法的更多信息。

正如Connection对象创建Statement和PreparedStatement对象一样，它也创建CallableStatement对象，该对象用于执行对数据库存储过程的调用。

创建CallableStatement对象

假设，您需要执行以下Oracle存储过程：

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数据库中创建它。

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()方法将值绑定到IN参数。 仅包含IN参数的存储过程
OUT 由SQL语句返回其值的参数。您可以使用getXXX()方法从OUT参数检索值。 仅包含OUT参数的存储过程
INOUT 同时提供输入和输出值的参数。您可以使用setXXX()方法绑定变量，并使用getXXX()方法检索值。 同时包含IN和OUT参数的存储过程

以下代码片段显示了如何使用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();
}

我们在Callable - 示例代码中学习了更多细节。

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} OUTERJOIN{表|outer-join}on search-condition。例如：

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

在SQL命令示例中使用like语句

在这个例子中，我们有四个静态字符串，包含数据库连接URL、用户名、密码和带有LIKE运算符和特殊字符的SELECT查询，用于选择多个条目。现在，使用DriverManager.getConnection()方法，我们准备了一个数据库连接。连接准备就绪后，我们使用connection.createStatement()方法创建了一个Statement对象。然后使用statement.executeQuery()执行SELECT查询，并将结果存储在结果集中。然后在迭代结果集时打印所有记录。

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

import java.sql.*;

// This class demonstrates the use of mySQL 'like'
public class MySQLFunctionExample {

    static final String DB_URL = "jdbc:mysql://127.0.0.1/TUTORIALSPOINT";
    static final String USER = "guest";
    static final String PASS = "guest123";
    //Select addresses which contain ','
    static final String QUERY = "select * from students where address like '%,%'";
    
    public static void main(String args[]) {
        Connection conn = null;
        Statement stmt = null;        
        try{            
             conn = DriverManager.getConnection(DB_URL,USER,PASS);
             System.out.println("Connection to db  established.");
             
             stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(QUERY);
             
             System.out.println("Successfully executed query.");
             System.out.println("----------------------------");
             
             while(rs.next()){
                System.out.print("StudentID: " + rs.getInt(1));
                System.out.print(", FirstName: " + rs.getString(2));
                System.out.print(", LastName: " + rs.getString(3) );
                System.out.print(", Address: " + rs.getString(4));
             }
      
             rs.close();
             stmt.close();
            conn.close();
        }catch( SQLException e){
            e.printStackTrace();
        }
    }
}

输出

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

C:\>javac MySQLFunctionExample.java
C:\>

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

C:\>java MySQLFunctionExample
Connection to db  established.
Successfully executed query.
----------------------------
StudentID: 1005, FirstName: Kumar, LastName: Kishore, Address: 49 Gorakhpur Rd., Agra
StudentID: 1006, FirstName: Khan, LastName: Ganesh, Address: 34 Digha Rd., Digha
  
C:\>

在SQL命令示例中使用函数Length()

在这个例子中，我们有四个静态字符串，包含数据库连接URL、用户名、密码和一个SELECT查询，其中Length()函数计算姓氏的长度。现在，使用DriverManager.getConnection()方法，我们准备了一个数据库连接。连接准备就绪后，我们使用connection.createStatement()方法创建了一个Statement对象。然后使用statement.executeQuery()执行SELECT查询，并将结果存储在结果集中。然后在迭代结果集时打印所有记录。

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

import java.sql.*;

// This class demonstrates the use of mySQL 'like'
public class MySQLFunctionExample {

    static final String DB_URL = "jdbc:mysql://127.0.0.1/TUTORIALSPOINT";
    static final String USER = "guest";
    static final String PASS = "guest123";
    //Select addresses which contain ','
    static final String QUERY = "select StudentID, LastName, length(LastName) AS LengthOfLastName from students";
    
    public static void main(String args[]) {
        Connection conn = null;
        Statement stmt = null;        
        try{            
             conn = DriverManager.getConnection(DB_URL,USER,PASS);
             System.out.println("Connection to db  established.");
             
             stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(QUERY);
             
             System.out.println("Successfully executed query.");
             System.out.println("----------------------------");
             
             while(rs.next()){
                System.out.print("StudentID: " + rs.getInt(1));
                System.out.print(", LastName: " + rs.getString(2));
                System.out.println(", LengthOfLastName: " + rs.getInt(3) );
             }
      
             rs.close();
             stmt.close();
            conn.close();
        }catch( SQLException e){
            e.printStackTrace();
        }
    }
}

输出

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

C:\>javac MySQLFunctionExample.java
C:\>

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

C:\>java MySQLFunctionExample
Connection to db  established.
Successfully executed query.
----------------------------
StudentID: 1000, LastName: Agarwal, LengthOfLastName: 7
StudentID: 1001, LastName: Pandey, LengthOfLastName: 6
StudentID: 1002, LastName: Kumar, LengthOfLastName: 5
StudentID: 1004, LastName: Ali, LengthOfLastName: 3
StudentID: 1005, LastName: Kumar, LengthOfLastName: 5
StudentID: 1006, LastName: Khan, LengthOfLastName: 4
  
C:\>
打印页面
上一页
下一页
广告