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

详解Java的JDBC API的存储过程与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（）方法的参数。

OUT：其值是由它返回的SQL语句提供的参数。你从OUT参数的getXXX（）方法检索值。

INOUT：同时提供输入和输出值的参数。绑定setXXX（）方法的变量，并与getXXX（）方法检索值。

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

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对象，以确保正确的清除。

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查询浏览器来完成它。

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，编译并运行，如下所示：

//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

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

C:>javac JDBCExample.java

当运行JDBCExample，它会产生以下结果：

C:>java JDBCExample

Connecting to database...

Creating statement...

Executing stored procedure...

Emp Name with ID:102 is Zaid

Goodbye!

JDBC的SQL转义语法：

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

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

{keyword 'parameters'}

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

d, t, ts 关键字:

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

{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点三十分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函数计算GE字符串的长度：

{fn length('Hello World')}

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

call 关键字:

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

{call my_procedure(?)};

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

{? = call my_procedure(?)};

oj 关键字:

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

{oj outer-join}

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

String sql = "SELECT Employees

FROM {oj ThisTable RIGHT

OUTER JOIN ThatTable on id = '100'}";

stmt.execute(sql);

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。