jdbc callablestatement 使用范例

jdbc callablestatement 对象用来调用数据库存储过程。

1. 存储过程范例

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 ;

2. callablestatement 使用范例

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

3. 编译运行

现在来编译上面的例子：

c:>javac jdbcexample.java
c:>

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

c:>java jdbcexample
connecting to database...
creating statement...
executing stored procedure...
emp name with id:102 is zaid
goodbye!
c:>