Javaの例でのCallableStatement

9か月 ago
陽, 向宇
7 minutes

JavaのCallableStatementは、Javaプログラムからストアドプロシージャを呼び出すために使用されます。ストアドプロシージャは、データベースでコンパイルされたいくつかのタスクのためのステートメントのグループです。ストアドプロシージャは、複雑なシナリオを持つ複数のテーブルを扱う場合に便利であり、複数のクエリをデータベースに送信する代わりに、必要なデータをストアドプロシージャに送信し、データベースサーバー自体でロジックを実行することができます。

コールブルステートメント

JDBC APIは、CallableStatementインタフェースを介してストアドプロシージャの実行にサポートを提供します。ストアドプロシージャは、データベース固有の構文で書かれる必要があり、私のチュートリアルではOracleデータベースを使用します。INパラメータとOUTパラメータを持つCallableStatementの標準機能を見ていきます。その後、Oracle固有のSTRUCTとCursorの例を見ていきます。まずは、以下のSQLクエリでCallableStatementの例プログラム用のテーブルを作成しましょう。create_employee.sql

-- For Oracle DB
CREATE TABLE EMPLOYEE
  (
    "EMPID"   NUMBER NOT NULL ENABLE,
    "NAME"    VARCHAR2(10 BYTE) DEFAULT NULL,
    "ROLE"    VARCHAR2(10 BYTE) DEFAULT NULL,
    "CITY"    VARCHAR2(10 BYTE) DEFAULT NULL,
    "COUNTRY" VARCHAR2(10 BYTE) DEFAULT NULL,
    PRIMARY KEY ("EMPID")
  );

最初に、Oracleのデータベース接続オブジェクトを取得するためのユーティリティクラスを作成しましょう。プロジェクトのビルドパスにOracleのOJDBC jarがあることを確認してください。DBConnection.java

package com.scdev.jdbc.storedproc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBConnection {

	private static final String DB_DRIVER_CLASS = "oracle.jdbc.driver.OracleDriver";
	private static final String DB_URL = "jdbc:oracle:thin:@localhost:1521:orcl";
	private static final String DB_USERNAME = "HR";
	private static final String DB_PASSWORD = "oracle";
	
	public static Connection getConnection() {
		Connection con = null;
		try {
			// load the Driver Class
			Class.forName(DB_DRIVER_CLASS);

			// create the connection now
			con = DriverManager.getConnection(DB_URL,DB_USERNAME,DB_PASSWORD);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return con;
	}
}

CallableStatementの例

シンプルなストアドプロシージャを作成して、Employeeテーブルにデータを挿入しましょう。「insertEmployee.sql」という名前のファイルに記述します。

CREATE OR REPLACE PROCEDURE insertEmployee
(in_id IN EMPLOYEE.EMPID%TYPE,
 in_name IN EMPLOYEE.NAME%TYPE,
 in_role IN EMPLOYEE.ROLE%TYPE,
 in_city IN EMPLOYEE.CITY%TYPE,
 in_country IN EMPLOYEE.COUNTRY%TYPE,
 out_result OUT VARCHAR2)
AS
BEGIN
  INSERT INTO EMPLOYEE (EMPID, NAME, ROLE, CITY, COUNTRY) 
  values (in_id,in_name,in_role,in_city,in_country);
  commit;
  
  out_result := 'TRUE';
  
EXCEPTION
  WHEN OTHERS THEN 
  out_result := 'FALSE';
  ROLLBACK;
END;

お客様からの入力をEmployeeテーブルに挿入するため、insertEmployeeプロシージャは入力を必要としています。挿入ステートメントが正常に動作する場合、TRUEを返し、例外が発生した場合はFALSEを返します。CallableStatementを使用して、プロシージャinsertEmployeeを実行して従業員データを挿入する方法を見てみましょう。JDBCStoredProcedureWrite.java

package com.scdev.jdbc.storedproc;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Scanner;

public class JDBCStoredProcedureWrite {

	public static void main(String[] args) {
		Connection con = null;
		CallableStatement stmt = null;
		
		//Read User Inputs
		Scanner input = new Scanner(System.in);
		System.out.println("Enter Employee ID (int):");
		int id = Integer.parseInt(input.nextLine());
		System.out.println("Enter Employee Name:");
		String name = input.nextLine();
		System.out.println("Enter Employee Role:");
		String role = input.nextLine();
		System.out.println("Enter Employee City:");
		String city = input.nextLine();
		System.out.println("Enter Employee Country:");
		String country = input.nextLine();
		
		try{
			con = DBConnection.getConnection();
			stmt = con.prepareCall("{call insertEmployee(?,?,?,?,?,?)}");
			stmt.setInt(1, id);
			stmt.setString(2, name);
			stmt.setString(3, role);
			stmt.setString(4, city);
			stmt.setString(5, country);
			
			//register the OUT parameter before calling the stored procedure
			stmt.registerOutParameter(6, java.sql.Types.VARCHAR);
			
			stmt.executeUpdate();
			
			//read the OUT parameter now
			String result = stmt.getString(6);
			
			System.out.println("Employee Record Save Success::"+result);
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try {
				stmt.close();
				con.close();
				input.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

}

ユーザーの入力を読み取り、Employeeテーブルに保存するために、PreparedStatementとは異なり、”{call insertEmployee(?,?,?,?,?,?)}”を使用してCallableStatementを作成し、CallableStatementのregisterOutParameter()メソッドを使用してOUTパラメータを設定しています。ストアドプロシージャを実行する前にOUTパラメータを登録する必要があります。ストアドプロシージャが実行された後は、CallableStatementのgetXXX()メソッドを使用してOUTオブジェクトデータを取得することができます。OUTパラメータの登録時には、java.sql.Typesを使用してOUTパラメータのタイプを指定する必要があります。このコードは一般的な性質を持つため、MySQLなどの他のリレーショナルデータベースでも同じストアドプロシージャを実行することができます。上記のCallableStatementの例を複数回実行した場合の出力は以下の通りです。

Enter Employee ID (int):
1
Enter Employee Name:
Pankaj
Enter Employee Role:
Developer
Enter Employee City:
Bangalore
Enter Employee Country:
India
Employee Record Save Success::TRUE

-----
Enter Employee ID (int):
2
Enter Employee Name:
Pankaj Kumar
Enter Employee Role:
CEO
Enter Employee City:
San Jose
Enter Employee Country:
USA
Employee Record Save Success::FALSE

第二の実行が失敗したことに注意してください。渡された名前が列のサイズよりも大きいためです。ストアドプロシージャでは例外を処理し、この場合はfalseを返しています。

「CallableStatementの例 – ストアドプロシージャのOUTパラメーター」

今、従業員データをIDで取得するためのストアドプロシージャを作成しましょう。ユーザーは従業員IDを入力し、プログラムは従業員情報を表示します。getEmployee.sql

create or replace
PROCEDURE getEmployee
(in_id IN EMPLOYEE.EMPID%TYPE,
 out_name OUT EMPLOYEE.NAME%TYPE,
 out_role OUT EMPLOYEE.ROLE%TYPE,
 out_city OUT EMPLOYEE.CITY%TYPE,
 out_country OUT EMPLOYEE.COUNTRY%TYPE
 )
AS
BEGIN
  SELECT NAME, ROLE, CITY, COUNTRY 
  INTO out_name, out_role, out_city, out_country
  FROM EMPLOYEE
  WHERE EMPID = in_id;
  
END;

Javaで書かれたgetEmployeeストアドプロシージャを使用して、従業員データを読み取るためのCallableStatementの例プログラムは、JDBCStoredProcedureRead.javaです。

package com.scdev.jdbc.storedproc;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Scanner;

public class JDBCStoredProcedureRead {

	public static void main(String[] args) {
		Connection con = null;
		CallableStatement stmt = null;
		
		//Read User Inputs
		Scanner input = new Scanner(System.in);
		System.out.println("Enter Employee ID (int):");
		int id = Integer.parseInt(input.nextLine());
		
		try{
			con = DBConnection.getConnection();
			stmt = con.prepareCall("{call getEmployee(?,?,?,?,?)}");
			stmt.setInt(1, id);
			
			//register the OUT parameter before calling the stored procedure
			stmt.registerOutParameter(2, java.sql.Types.VARCHAR);
			stmt.registerOutParameter(3, java.sql.Types.VARCHAR);
			stmt.registerOutParameter(4, java.sql.Types.VARCHAR);
			stmt.registerOutParameter(5, java.sql.Types.VARCHAR);
			
			stmt.execute();
			
			//read the OUT parameter now
			String name = stmt.getString(2);
			String role = stmt.getString(3);
			String city = stmt.getString(4);
			String country = stmt.getString(5);
			
			if(name !=null){
			System.out.println("Employee Name="+name+",Role="+role+",City="+city+",Country="+country);
			}else{
				System.out.println("Employee Not Found with ID"+id);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try {
				stmt.close();
				con.close();
				input.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

}

プログラムは汎用性があり、同じストアドプロシージャを持つ任意のデータベースに対して機能します。上記のCallableStatementの例プログラムを実行した場合、出力はどのようになるかを見てみましょう。

Enter Employee ID (int):
1
Employee Name=Pankaj,Role=Developer,City=Bangalore,Country=India

CallableStatementの例 – Oracleのストアドプロシージャのカーソル

IDを通じて従業員情報を読み取っているため、単一の結果が得られ、データを読み取るためにOUTパラメーターがうまく機能します。しかし、役割や国で検索すると、複数の行が得られる可能性があります。その場合には、Oracleのカーソルを使用して結果セットのように読み取ることができます。getEmployeeByRole.sql

create or replace
PROCEDURE getEmployeeByRole
(in_role IN EMPLOYEE.ROLE%TYPE,
 out_cursor_emps OUT SYS_REFCURSOR
 )
AS
BEGIN
  OPEN out_cursor_emps FOR
  SELECT EMPID, NAME, CITY, COUNTRY 
  FROM EMPLOYEE
  WHERE ROLE = in_role;
  
END;

JDBCStoredProcedureCursor.javaを日本語で自然に言い換えると、次のようになります：
JDBCStoredProcedureCursor.javaを感考える。

package com.scdev.jdbc.storedproc;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

import oracle.jdbc.OracleTypes;

public class JDBCStoredProcedureCursor {

	public static void main(String[] args) {

		Connection con = null;
		CallableStatement stmt = null;
		ResultSet rs = null;
		
		//Read User Inputs
		Scanner input = new Scanner(System.in);
		System.out.println("Enter Employee Role:");
		String role = input.nextLine();
		
		try{
			con = DBConnection.getConnection();
			stmt = con.prepareCall("{call getEmployeeByRole(?,?)}");
			stmt.setString(1, role);
			
			//register the OUT parameter before calling the stored procedure
			stmt.registerOutParameter(2, OracleTypes.CURSOR);
			
			stmt.execute();
			
			//read the OUT parameter now
			rs = (ResultSet) stmt.getObject(2);
			
			while(rs.next()){
				System.out.println("Employee ID="+rs.getInt("empId")+",Name="+rs.getString("name")+
						",Role="+role+",City="+rs.getString("city")+
						",Country="+rs.getString("country"));
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try {
				rs.close();
				stmt.close();
				con.close();
				input.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

}

このプログラムは、Oracle OJDBCの特定のクラスを使用しており、他のデータベースでは動作しません。私たちはOUTパラメータの型をOracleTypes.CURSORと設定し、それをResultSetオブジェクトに変換しています。コードの他の部分は、単純なJDBCプログラミングです。上記のCallableStatementの例プログラムを実行すると、以下の出力が得られます。

Enter Employee Role:
Developer
Employee ID=5,Name=Kumar,Role=Developer,City=San Jose,Country=USA
Employee ID=1,Name=Pankaj,Role=Developer,City=Bangalore,Country=India

従業員テーブルのデータによって、結果は異なる場合があります。

CallableStatementの例 – Oracle DBオブジェクトとSTRUCT

「insertEmployee」と「getEmployee」というストアドプロシージャを見ると、Employeeテーブルのすべてのパラメータがプロシージャ内にあります。カラムの数が増えると、混乱やエラーが増える可能性があります。Oracleデータベースでは、データベースオブジェクトを作成するオプションがあり、それらと一緒にOracle STRUCTを使用できます。まず、EmployeeテーブルのカラムのためのOracle DBオブジェクトを定義しましょう。EMPLOYEE_OBJ.sql

create or replace TYPE EMPLOYEE_OBJ AS OBJECT
(
  EMPID NUMBER,
  NAME VARCHAR2(10),
  ROLE VARCHAR2(10),
  CITY  VARCHAR2(10),
  COUNTRY  VARCHAR2(10)
  
  );

今、EMPLOYEE_OBJを使用してinsertEmployeeストアドプロシージャを書き直しましょう。insertEmployeeObject.sql

CREATE OR REPLACE PROCEDURE insertEmployeeObject
(IN_EMPLOYEE_OBJ IN EMPLOYEE_OBJ,
 out_result OUT VARCHAR2)
AS
BEGIN
  INSERT INTO EMPLOYEE (EMPID, NAME, ROLE, CITY, COUNTRY) values 
  (IN_EMPLOYEE_OBJ.EMPID, IN_EMPLOYEE_OBJ.NAME, IN_EMPLOYEE_OBJ.ROLE, IN_EMPLOYEE_OBJ.CITY, IN_EMPLOYEE_OBJ.COUNTRY);
  commit;
  
  out_result := 'TRUE';
  
EXCEPTION
  WHEN OTHERS THEN 
  out_result := 'FALSE';
  ROLLBACK;
END;

JavaプログラムでinsertEmployeeObjectストアドプロシージャをどのように呼び出すか見てみましょう。JDBCStoredProcedureOracleStruct.javaです。

package com.scdev.jdbc.storedproc;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.Scanner;

import oracle.jdbc.OracleCallableStatement;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;

public class JDBCStoredProcedureOracleStruct {

	public static void main(String[] args) {
		Connection con = null;
		OracleCallableStatement stmt = null;
		
		//Create Object Array for Stored Procedure call
		Object[] empObjArray = new Object[5];
		//Read User Inputs
		Scanner input = new Scanner(System.in);
		System.out.println("Enter Employee ID (int):");
		empObjArray[0] = Integer.parseInt(input.nextLine());
		System.out.println("Enter Employee Name:");
		empObjArray[1] = input.nextLine();
		System.out.println("Enter Employee Role:");
		empObjArray[2] = input.nextLine();
		System.out.println("Enter Employee City:");
		empObjArray[3] = input.nextLine();
		System.out.println("Enter Employee Country:");
		empObjArray[4] = input.nextLine();
		
		try{
			con = DBConnection.getConnection();
			
			StructDescriptor empStructDesc = StructDescriptor.createDescriptor("EMPLOYEE_OBJ", con);
			STRUCT empStruct = new STRUCT(empStructDesc, con, empObjArray);
			stmt = (OracleCallableStatement) con.prepareCall("{call insertEmployeeObject(?,?)}");
			
			stmt.setSTRUCT(1, empStruct);
			
			//register the OUT parameter before calling the stored procedure
			stmt.registerOutParameter(2, java.sql.Types.VARCHAR);
			
			stmt.executeUpdate();
			
			//read the OUT parameter now
			String result = stmt.getString(2);
			
			System.out.println("Employee Record Save Success::"+result);
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try {
				stmt.close();
				con.close();
				input.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

}

まず第一に、EMPLOYEE_OBJデータベースオブジェクトと同じ長さのオブジェクト配列を作成しています。次に、EMPLOYEE_OBJオブジェクト変数に応じて値を設定しています。これは非常に重要です。そうしないと、データが間違った列に挿入される可能性があります。その後、oracle.sql.StructDescriptorと私たちのオブジェクト配列を使用して、oracle.sql.STRUCTオブジェクトを作成しています。STRUCTオブジェクトが作成されたら、それをストアドプロシージャのINパラメータとして設定し、OUTパラメータを登録して実行します。このコードはOJDBC APIと密接に結びついており、他のデータベースでは動作しません。このプログラムを実行すると次のような出力が表示されます。

Enter Employee ID (int):
5
Enter Employee Name:
Kumar
Enter Employee Role:
Developer
Enter Employee City:
San Jose
Enter Employee Country:
USA
Employee Record Save Success::TRUE

データベースのオブジェクトはOUTパラメータとしても使用でき、データベースから値を取得するために読み込むことができます。これがJavaのCallableStatementを使用したストアドプロシージャの実行の例です。何かを学んでいただければ幸いです。

コメントを残す 0

Your email address will not be published. Required fields are marked *