使用存儲過程 – JAVA編程語言程序開發技術文章

很多數據庫都支持在數據庫內部執行的函數。這種方法有幾個好處,包括更快的性能和改進的安全性。這些函數稱為存儲過程。存儲過程是用來封裝SQL語句來完成一個完整的業務功能,類似於面向對象裡面方法的概念。雖然它們通常是用SQL編寫的,但也可以用數據庫支持的任何編程語言編寫。隨著Java語言日趨流行,幾個數據庫廠商—Oracle(Oracle數據庫)和IBM(db2數據庫)都起用瞭Java語言創建存儲過程,還可以在不同數據庫之間移動存儲過程。

存儲過程可以支持三種類型的參數:IN,OUT和INOUT,這對於存儲過程在數據庫內部真正能做什麼來說,帶來瞭很大的靈活性。不管存儲過程是用什麼語言編寫的,它都能以一種標準的方式從Java應用程序調用。

首先,您需要創建一個CallableStatement對象。為瞭標識存儲過程和過程需要的參數的類型和數量,還要允許使用三種類型的調用。下面的清單說明瞭這三種類型(假定我們正在調用一個名為StudentList的存儲過程):

n {call StudentList}如果過程不需要參數

n {call StudentList(?,?)}如果過程需要兩個參數

n {?=call StudentList(?,?)}如果參數需要兩個參數並返回一個

要想使用存儲過程,首先應該創建一個存儲過程!

代碼的實現

[java]
import java.sql.Connection; 
import java.sql.SQLException; 
import java.sql.Statement; 
 
import javax.naming.NamingException; 
 
public class CreateStoredProceduresofSQLServer { 
    public static void main(String[] args) { 
        Connection con = null; 
        Statement stmt = null; 
        String jndiname = "jdbcPool/mydatasource"; 
 
        try { 
            con = DBCon.getConnectionFromPooledDataSource(jndiname); 
            stmt = con.createStatement(); 
 
            // 1.創建存儲過程show_students  
            String createProcedure1 = "create procedure show_students " + "as " + "select id, name,age " + "from students " + "order by id"; 
            // 刪除數據庫中存在的同名過程  
            stmt.executeUpdate("if exists(select name from sysobjects " 
                    + "where name='show_students'and type='p') " 
                    + "drop procedure show_students"); 
            stmt.executeUpdate(createProcedure1); 
 
            // 2.創建儲存過程onestudent  
            String createProcedure2 = "create procedure onestudent " 
                    + "@stu_id int = null, " + "@name varchar(20) output, " 
                    + "@age  int  output " + "as " + "if @stu_id = null " 
                    + "BEGIN " 
                    + "  PRINT 'ERROR: You must specify a stu_id value.' " 
                    + "  RETURN " 
                    + "END " 
                    + 
                    // Get the sales for the specified cof_name and " +  
                    // assign it to the output parameter. " +  
                    "SELECT @name = name, @age = age " + "FROM coffees " 
                    + "WHERE id = @stu_id " + "RETURN "; 
            stmt.executeUpdate("if exists(select name from sysobjects " 
                    + "where name='onestudent'and type='p') " 
                    + "drop procedure onestudent"); 
            stmt.executeUpdate(createProcedure2); 
 
            // 3.創建函數  
            String createProcedure3 = "CREATE FUNCTION pubuse.ageofstu " 
                    + 
                    // Input cof_name  
                    "(@stu_name varchar(20)) " 
                    + "RETURNS int " 
                    + // return sales  
                    "AS " + "BEGIN " + "  DECLARE @age int " 
                    + "  SELECT @age = age " + "  FROM student " 
                    + "  WHERE name like @stu_name " + "  RETURN @age " 
                    + "END "; 
            stmt.executeUpdate("if exists(select name from sysobjects " 
                    + "where name='ageofstu') " 
                    + "drop function pubuse.ageofstu"); 
            stmt.executeUpdate(createProcedure3); 
            stmt.close(); 
            con.close(); 
        } catch (NamingException ex) { 
            System.err.println("Name Not Bound : " + ex.getMessage()); 
        } catch (SQLException ex) { 
            System.err.println("SQLException : " + ex.getMessage()); 
        } 
        System.out.println("程序執行結束!"); 
    } 

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

import javax.naming.NamingException;

public class CreateStoredProceduresofSQLServer {
 public static void main(String[] args) {
  Connection con = null;
  Statement stmt = null;
  String jndiname = "jdbcPool/mydatasource";

  try {
   con = DBCon.getConnectionFromPooledDataSource(jndiname);
   stmt = con.createStatement();

   // 1.創建存儲過程show_students
   String createProcedure1 = "create procedure show_students " + "as " + "select id, name,age " + "from students " + "order by id";
   // 刪除數據庫中存在的同名過程
   stmt.executeUpdate("if exists(select name from sysobjects "
     + "where name='show_students'and type='p') "
     + "drop procedure show_students");
   stmt.executeUpdate(createProcedure1);

   // 2.創建儲存過程onestudent
   String createProcedure2 = "create procedure onestudent "
     + "@stu_id int = null, " + "@name varchar(20) output, "
     + "@age  int  output " + "as " + "if @stu_id = null "
     + "BEGIN "
     + "  PRINT 'ERROR: You must specify a stu_id value.' "
     + "  RETURN "
     + "END "
     +
     // Get the sales for the specified cof_name and " +
     // assign it to the output parameter. " +
     "SELECT @name = name, @age = age " + "FROM coffees "
     + "WHERE id = @stu_id " + "RETURN ";
   stmt.executeUpdate("if exists(select name from sysobjects "
     + "where name='onestudent'and type='p') "
     + "drop procedure onestudent");
   stmt.executeUpdate(createProcedure2);

   // 3.創建函數www.aiwalls.com
   String createProcedure3 = "CREATE FUNCTION pubuse.ageofstu "
     +
     // Input cof_name
     "(@stu_name varchar(20)) "
     + "RETURNS int "
     + // return sales
     "AS " + "BEGIN " + "  DECLARE @age int "
     + "  SELECT @age = age " + "  FROM student "
     + "  WHERE name like @stu_name " + "  RETURN @age "
     + "END ";
   stmt.executeUpdate("if exists(select name from sysobjects "
     + "where name='ageofstu') "
     + "drop function pubuse.ageofstu");
   stmt.executeUpdate(createProcedure3);
   stmt.close();
   con.close();
  } catch (NamingException ex) {
   System.err.println("Name Not Bound : " + ex.getMessage());
  } catch (SQLException ex) {
   System.err.println("SQLException : " + ex.getMessage());
  }
  System.out.println("程序執行結束!");
 }
}

下面是使用存儲過程的代碼:

[cpp]
import java.sql.CallableStatement; 
import java.sql.Connection; 
import java.sql.ResultSet; 
import java.sql.SQLException; 
import java.sql.Types; 
import javax.naming.NamingException; 
 
public class InvokeStoreProcdureofSQLServer { 
    public static void main(String[] args) { 
        Connection con = null; 
        String jndiname = "jdbcPool/mydatasource"; 
        // 定義調用存儲過程和函數的 SQL 語句  
        String callSQL1 = "{call show_students}"; 
        String callSQL2 = "{call onestudent(?,?,?)}"; 
        String callSQL3 = "{? = call ageofstu(?)}"; 
 
        try { 
            con = DBCon.getConnectionFromPooledDataSource(jndiname); 
            // 調用第 1 個存儲過程  
            CallableStatement cs = con.prepareCall(callSQL1); 
            ResultSet rs = cs.executeQuery(); 
            System.out.println("第一個存儲過程調用結果"); 
            while (rs.next()) { 
                String id = rs.getString(1); 
                String name = rs.getString(2); 
                String age = rs.getString(3); 
                System.out.println(id + "  " + name + " " + age); 
            } 
            // 調用第 2 個存儲過程  
            cs = con.prepareCall(callSQL2); 
            cs.setString(1, "2"); 
            cs.registerOutParameter(2, Types.CHAR); 
            cs.registerOutParameter(3, Types.INTEGER); 
            cs.execute(); 
            String name = cs.getString(2); 
            int age = cs.getInt(3); 
            System.out.println("第二個存儲過程調用結果"); 
            System.out.println("This student's name is " + name 
                    + " and age is " + age); 
            // 調用函數  
            cs = con.prepareCall(callSQL3); 
            cs.setString(2, "小羅"); 
            cs.registerOutParameter(1, Types.INTEGER); 
            cs.execute(); 
            age = cs.getInt(1); 
            System.out.println("函數調用結果"); 
            System.out.println("This student's name is " + age + "."); 
            cs.close(); 
            con.close(); 
        } catch (NamingException ex) { 
            System.err.println("Name Not Bound : " + ex.getMessage()); 
        } catch (SQLException ex) { 
            System.err.println("SQLException : " + ex.getMessage()); 
        } 
        System.out.println("調用結束!"); 
    } 

 

摘自  Java教程
 

發佈留言