欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2024-03-08 18:52:53
正如一个connection对象创建statement和preparedstatement对象,它也创造了callablestatement对象这将被用来执行调用数据库存储...

正如一个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);