JavaのCallableStatement例

Java

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

CallableStatement

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

-- 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.journaldev.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 {
			// ドライバークラスをロード
			Class.forName(DB_DRIVER_CLASS);

			// 今接続を作成する
			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;

insertEmployeeプロシージャが呼び出し元からの入力を期待しており、これらはEmployeeテーブルに挿入されます。挿入ステートメントが正常に動作すると、TRUEが返され、例外が発生した場合はFALSEが返されます。 CallableStatementを使用して、employeeデータを挿入するためにinsertEmployeeストアドプロシージャを実行する方法を見てみましょう。 JDBCStoredProcedureWrite.java

package com.journaldev.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;
		
		//ユーザー入力を読み取る
		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);
			
			//ストアドプロシージャを呼び出す前にOUTパラメーターを登録する
			stmt.registerOutParameter(6, java.sql.Types.VARCHAR);
			
			stmt.executeUpdate();
			
			//今OUTパラメーターを読み取る
			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

2回目の実行が失敗したことに注意してください。渡された名前が列サイズよりも大きいためです。ストアドプロシージャで例外を消費し、この場合には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のCallableStatementの例題プログラムを使用して、従業員データを読み取るためのgetEmployeeストアドプロシージャは、JDBCStoredProcedureRead.javaです。

package com.journaldev.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;
		
		//ユーザー入力を読み取る
		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);
			
			//ストアドプロシージャを呼び出す前にOUTパラメーターを登録する
			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();
			
			//現在OUTパラメーターを読み取る
			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 CURSOR

IDを介して従業員情報を読み取っているため、単一の結果が得られ、OUTパラメーターはデータを読み取るためにうまく機能します。ただし、役割または国で検索する場合、複数の行が得られる可能性があり、その場合はOracle CURSORを使用して結果セットのように読み取ることができます。 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

package com.journaldev.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;
		
		//ユーザー入力を読み取る
		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);
			
			//ストアドプロシージャを呼び出す前にOUTパラメーターを登録する
			stmt.registerOutParameter(2, OracleTypes.CURSOR);
			
			stmt.execute();
			
			//現在OUTパラメーターを読み取る
			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;

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

package com.journaldev.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;
		
		//ストアドプロシージャ呼び出しのためのオブジェクト配列を作成
		Object[] empObjArray = new Object[5];
		//ユーザーの入力を読み取る
		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);
			
			//ストアドプロシージャを呼び出す前にOUTパラメータを登録
			stmt.registerOutParameter(2, java.sql.Types.VARCHAR);
			
			stmt.executeUpdate();
			
			//今OUTパラメータを読み取る
			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の例は終わりです。何か学べたら幸いです。

Source:
https://www.digitalocean.com/community/tutorials/callablestatement-in-java-example