この章では、Javaアプリケーションで使用できる追加機能について説明します。一部の機能でサンプル・アプリケーションに実装されていないものもありますが、パフォーマンスを向上させるためにコード内で使用できる機能拡張もあります。
この章は次の項で構成されています。
本番環境では、動的SQL、つまり即時にSQL文を生成することが常に必要です。特に、データベースに対して行われる更新の場合など、最終的な問合せが実行時までわからないことがよくあります。
異なる更新値を持つ多数の類似問合せをデータベースに対して実行する必要がある場合は、Statement
オブジェクトを拡張するOraclePreparedStatement
オブジェクトを使用できます。この場合、リテラルの更新値をバインド変数に置き換えます。また、OracleCallableStatement
オブジェクトを介してストアド・プロシージャをコールすることによって、データベースでPL/SQLストアド・ファンクションを使用することもできます。
この項では、次の項目について説明します。
データベースに対して静的SQL問合せを実行する場合は、Statement
オブジェクトを使用します。ただし、複数の類似問合せを実行したり、データベースの多数の列に影響する複数の更新を実行する場合は、それぞれの問合せをアプリケーションにハード・コードすることはできません。
同じSQL文を複数回実行する場合は、OraclePreparedStatement
を使用できます。次のような問合せについて考えてみます。
SELECT * FROM Employees WHERE ID=xyz;
この問合せのxyz
の値が変更されるたびに、SQL文を再度コンパイルする必要があります。
OraclePreparedStatement
機能を使用すると、実行するSQL文はプリコンパイルされてPreparedStatement
オブジェクトに格納されるため、実行するたびにコンパイルしなくても、何度でも実行できます。文中のデータが変化する場合は、そのデータのプレースホルダとしてバインド変数を使用し、実行時にリテラル値を指定することができます。
OraclePreparedStatement
の使用について、次に例を示します。
OraclePreparedStatement pstmt = conn.prepareStatement("UPDATE Employees SET salary = ? WHERE ID = ?"); pstmt.setBigDecimal(1, 153833.00) pstmt.setInt(2, 110592)
OraclePreparedStatement
インタフェースを使用するメリットは、次のとおりです。
OracleCallableStatement
インタフェースを使用すると、データベース上のストアド・プロシージャにアクセスできます。このインタフェースは、OraclePreparedStatement
インタフェースを拡張します。OracleCallableStatement
インタフェースは、ストアド・プロシージャをコールするために、標準的なJDBCエスケープ構文で構成されています。このインタフェースは、結果パラメータの有無にかかわらず使用できます。ただし、結果パラメータを使用する場合は、それがOUT
パラメータとして登録されている必要があります。このインタフェースでは、その他のパラメータとして、IN
またはOUT
あるいはその両方を使用できます。
これらのパラメータは、OraclePreparedStatement
インタフェースから継承されるアクセッサ・メソッドの使用によって設定されます。IN
パラメータはset
XXX
メソッドで設定し、OUT
パラメータはget
XXX
メソッドで取得します。XXX
は、パラメータのJavaデータ型です。
また、CallableStatement
は、複数のResultSet
オブジェクトを返すこともあります。
例として、次のようにOracleCallableStatement
を作成して、foo
ストアド・プロシージャをコールすることができます。
次のいずれかの方法で、文字列bar
をこのプロシージャに渡すことができます。
cs.setString(1,"bar"); // JDBC standard // or... cs.setStringAtName(X, "value"); // Oracle extension
バインド変数は、SQL文中のリテラルのかわりとなる変数です。バインド変数をOraclePreparedStatement
およびOracleCallableStatement
とともに使用して、SQL文の構築に使用されるパラメータ値を指定します。バインド変数を使用すると、本番環境でのパフォーマンスが大幅に向上します。
PL/SQLブロックまたはストアド・プロシージャ・コールでは、IN
、OUT
およびIN OUT
という修飾子を使用して、入力変数と出力変数を区別できます。入力変数の値はset
XXX
メソッドで設定し、OUT
変数の値はget
XXX
メソッドで取得します。XXX
は、値のJavaデータ型です。これは、アクセスするデータベース内の列のSQLデータ型によって異なります。
Oracle Java Database Connectivity(JDBC)ドライバでは、PL/SQLストアド・プロシージャおよび無名ブロックの処理がサポートされています。Oracle JDBCドライバでは、Oracle PL/SQLブロック構文およびほとんどのJDBCエスケープ構文がサポートされています。次のPL/SQLコールは、すべてのOracle JDBCドライバで動作します。
例6-3 ストアド・プロシージャのコール
// JDBC syntaxCallableStatement cs1 = conn.prepareCall ( "{call proc (?,?)}" ) ; // stored proc CallableStatement cs2 = conn.prepareCall ( "{? = call func (?,?)}" ) ; // stored func // Oracle PL/SQL block syntax CallableStatement cs3 = conn.prepareCall ( "begin proc (?,?); end;" ) ; // stored proc CallableStatement cs4 = conn.prepareCall ( "begin ? := func(?,?); end;" ) ; // stored func
Oracle構文の例として、ストアド・ファンクションを作成するPL/SQLコードの一部を示します。このPL/SQLファンクションでは文字列を取得して接尾辞を連結します。
次のように、Javaプログラムでこのストアド・ファンクションをコールできます。
OracleDataSource ods = new OracleDataSource(); ods.setURL("jdbc:oracle:thin:@<hoststring>"); ods.setUser("hr"); ods.setPassword("hr"); Connection conn = ods.getConnection(); CallableStatement cs = conn.prepareCall ("begin ? := foo(?); end;"); cs.registerOutParameter(1,Types.CHAR); cs.setString(2, "aa"); cs.executeUpdate(); String result = cs.getString(1);
次の項では、このマニュアルのサンプル・アプリケーションでストアド・プロシージャを使用する方法について説明します。
JDeveloperを使用すると、データベース・ナビゲータを使用して、ストアド・プロシージャをデータベースに作成できます。次の手順では、サンプル・アプリケーションで従業員レコードを挿入するための代替方法として使用できるストアド・プロシージャを作成します。
Select the 「DatabaseNavigatorName」タブを選択して、データベース・ナビゲータを表示します。
データベース接続ノード(デフォルトではConnection1)を開き、HR
データベース内のオブジェクトを表示します。
「プロシージャ」を右クリックし、「新規プロシージャ」を選択します。
「PL/SQLプロシージャ作成」ダイアログ・ボックスで、オブジェクト名としてinsert_employee
と入力します。「OK」をクリックします。
プロシージャのスケルトン・コードがソース・エディタに表示されます。
プロシージャ名の後に、次のコード行を入力します。
PROCEDURE "INSERT_EMPLOYEE" (p_first_name employees.first_name%type, p_last_name employees.last_name%type, p_email employees.email%type, p_phone_number employees.phone_number%type, p_job_id employees.job_id%type, p_salary employees.salary%type )
BEGIN
文の後のNULL
を示す行を次の内容で置き換えます。
INSERT INTO Employees VALUES (EMPLOYEES_SEQ.nextval, p_first_name , p_last_name , p_email , p_phone_number, SYSDATE, p_job_id, p_salary,.30,100,80);
この文では、DataHandler.java
クラスのaddEmployee
メソッドの最後の3行に使用される、ハードコードされた同じ値が使用されていることがわかります。
END
文にプロシージャ名を追加します。
END insert_employee;
ファイルを保存し、コンパイル・エラーがないかを確認します。
例6-6に、ストアド・プロシージャの完成したコードに示します。
例6-6 従業員データを挿入するためのPL/SQLストアド・プロシージャの作成
PROCEDURE "INSERT_EMPLOYEE" (p_first_name employees.first_name%type, p_last_name employees.last_name%type, p_email employees.email%type, p_phone_number employees.phone_number%type, p_job_id employees.job_id%type, p_salary employees.salary%type ) AS BEGIN INSERT INTO Employees VALUES (EMPLOYEES_SEQ.nextval, p_first_name , p_last_name , p_email , p_phone_number, SYSDATE, p_job_id, p_salary,.30,100,80); END insert_employee;
次の手順では、addEmployee
メソッドのかわりに使用できるメソッドをDataHandler.java
クラスに追加します。ここで追加する新しいメソッドでは、insert_employee
ストアド・プロシージャを使用します。
「アプリケーション」タブを選択して、アプリケーション・ナビゲータを表示します。
DataHandler.java
ファイルがJavaソース・エディタでまだ開いていない場合は、ダブルクリックして開きます。
次のようにCallableStatement
をインポートします。
import java.sql.CallableStatement;
addEmployee
メソッドの後に、addEmployeeSP
メソッドの宣言を追加します。
public String addEmployeeSP(String first_name, String last_name, String email, String phone_number, String job_id, int salary) throws SQLException { }
このメソッドのシグネチャはaddEmployee
のものと同じです。
このメソッド内にtry
ブロックを追加し、このブロック内でデータベースに接続します。
try { getDBConnection(); }
さらに、try
ブロック内で次のSQL文字列を作成します。
sqlString = "begin hr.insert_employee(?,?,?,?,?,?); end;";
文中の疑問符(?
)はバインド変数で、ストアド・プロシージャによって使用される、first_name
、last_name
などの値のプレースホルダの役割を果たします。
CallableStatement
を作成します。
CallableStatement callstmt = conn.prepareCall(sqlString);
IN
パラメータを設定します。
callstmt.setString(1, first_name); callstmt.setString(2, last_name); callstmt.setString(3, email); callstmt.setString(4, phone_number); callstmt.setString(5, job_id); callstmt.setInt(6, salary);
トレース・メッセージを追加し、コール可能文を実行します。
System.out.println("\nInserting with stored procedure: " + sqlString); callstmt.execute();
戻りメッセージを追加します。
return "success";
try
ブロックの後にcatch
ブロックを追加して、すべてのエラーを捕捉します。例5-5で作成したlogException
をコールします。
catch ( SQLException ex ) { System.out.println("Possible source of error: Make sure you have created the stored procedure"); logException( ex ); return "failure"; }
DataHandler.java
を保存します。
例6-7に、完成したメソッドを示します。
注意: logException() メソッド(例5-5を参照)を追加していない場合は、logException(ex) の下に表示される赤い波線によってエラーが示されます。ファイルのコンパイルに進む前に、このメソッドがDataHandler.java クラスに存在する必要があります。 |
例6-7 JavaでのPL/SQLストアド・プロシージャの使用
public String addEmployeeSP(String first_name, String last_name, String email, String phone_number, String job_id, int salary) throws SQLException { try { getDBConnection(); sqlString = "begin hr.insert_employee(?,?,?,?,?,?); end;"; CallableStatement callstmt = conn.prepareCall(sqlString); callstmt.setString(1, first_name); callstmt.setString(2, last_name); callstmt.setString(3, email); callstmt.setString(4, phone_number); callstmt.setString(5, job_id); callstmt.setInt(6, salary); System.out.println("\nInserting with stored procedure: " + sqlString); callstmt.execute(); return "success"; } catch ( SQLException ex ) { System.out.println("Possible source of error: Make sure you have created the stored procedure"); logException( ex ); return "failure"; } }
この項の手順では、insert.jsp
ページにラジオ・ボタンを追加します。従業員を挿入する場合に、ストアド・プロシージャを使用するか、またはJavaコードでSQL問合せを使用するかを、このラジオ・ボタンによって、ユーザーが選択できるようにします。
insert.jsp
を開いていない場合は、ビジュアル・エディタで開きます。
「Insert Employee Record」という見出しの後に、新しい行を作成します。この新しい行にカーソルを置き、「UseBean」をコンポーネント・パレットの「JSP」ページからドラッグしてjsp:useBean
タグをこのページに追加します。IDとしてempsbean
を入力し、「クラス」としてhr.DataHandler
を参照して選択し、「有効範囲」を「セッション」
に設定します。ページ上でUseBeanが選択されている状態で、この行のスタイルをHeading 3ではなくNone
に設定します。
「Radio Button」コンポーネントをコンポーネント・パレットの「HTML Forms」ページから表の上にあるフォーム内のページにドラッグします。「ラジオ・ボタンの挿入」ダイアログ・ボックスで、「名前」にuseSP
を入力し、「値」にfalse
を入力して、「チェック」を選択します。「OK」をクリックします。
ビジュアル・エディタでボタンの右側にカーソルを置き、「新しいレコードの追加にはJDBCのみを使用する」など、ボタンの目的を説明するテキストを入力します。
現在の行の末尾で[Enter]を押して、新しい行を作成します。
2つ目の「Radio Button」を最初のラジオ・ボタンの下にドラッグします。「ラジオ・ボタンの挿入」ダイアログ・ボックスで、「名前」にuseSP
を入力し、「値」にtrue
を入力して、「チェック」チェック・ボックスが選択されていないことを確認します。
ビジュアル・エディタでボタンの右側にカーソルを置き、「レコードの追加にはJDBCからコールするストアド・プロシージャのみを使用する」など、ボタンの目的を説明するテキストを入力します。
ページを保存します。
図6-1に、ストアド・プロシージャを使用するときのオプションとなるラジオ・ボタンのあるinsert.jsp
を示します。
この項の手順では、insert.jsp
ページ上でフォームを処理するinsert_action.jsp
ファイルを変更し、ラジオ・ボタンの選択によって、新しい従業員レコードを挿入するための適切なメソッドを選択します。
insert_action.jsp
を開いていない場合は、ビジュアル・エディタで開きます。
スクリプトレットをダブルクリックして「スクリプトレットのプロパティ」ダイアログ・ボックスを表示し、次のようにsalary変数の後に新しい変数を追加します。
String useSPFlag = request.getParameter("useSP");
「スクリプトレットのプロパティ」ダイアログ・ボックスのまま、その下で、既存のempsbean.addEmployee
行を次のコード行で置き換えます。このコード行では、addEmployeeSP
メソッドまたはpure JDBC addEmployee
メソッドを選択してレコードを挿入します。
if ( useSPFlag.equalsIgnoreCase("true")) empsbean.addEmployeeSP(first_name, last_name, email, phone_number, job_id, salary.intValue()); // otherwise use pure JDBC insert else empsbean.addEmployee(first_name, last_name, email, phone_number, job_id, salary.intValue());
insert_action.jsp
を保存します。
これで、アプリケーションを実行すると、挿入ページのラジオ・ボタンを使用して新しい従業員レコードを挿入する方法を選択できます。ブラウザでは、ページは図6-2のように表示されます。
Oracle JDBCドライバではREF
CURSOR
型のカーソル変数がサポートされますが、これはJDBC標準の一部ではありません。REF
CURSOR
型は、JDBC結果セットとしての使用がサポートされています。
カーソル変数は、問合せ作業領域の(内容でなく)メモリーの場所を保持します。カーソル変数を宣言すると、ポインタが作成されます。SQLでは、ポインタはデータ型REF
x
を取ります。ここで、REF
はREFERENCE
の省略形で、x
は参照されるエンティティを表します。次に、REF CURSOR
がカーソル変数への参照を特定します。多くの作業領域を参照する多くのカーソル変数が存在する可能性があるため、REF
CURSOR
は、異なる多くの型のカーソル変数を識別するカテゴリまたはデータ型指定子と考えることができます。基本的にREF CURSOR
は問合せ結果をカプセル化します。
Oracleは結果セットを返しません。問合せによって返されたデータにアクセスするには、CURSOR
およびREF CURSOR
を使用します。CURSOR
には、問合せ結果およびメタデータが含まれます。REF CURSOR
(CURSOR変数
)データ型には、カーソルへの参照が含まれます。RDBMSとクライアント、またはデータベースでPL/SQLとJavaの間での受け渡しが可能です。問合せまたはストアド・プロシージャから返される場合もあります。
注意: REF CURSOR インスタンスはスクロールできません。 |
この項では、次の項目について説明します。
カーソル変数を作成するには、REF
CURSOR
カテゴリに属する型を識別することから開始します。次に例を示します。
dept_cv DeptCursorTyp ...
次に、DeptCursorTyp
型であることを宣言して、カーソル変数を作成します。
REF
CURSOR
は、特定のデータ型というよりも、データ型のカテゴリです。ストアド・プロシージャは、REF
CURSOR
カテゴリのカーソル変数を返すことがあります。この出力は、データベース・カーソルまたはJDBC結果セットと同等です。
Javaでは、REF CURSOR
はResultSet
オブジェクトとしてマテリアライズされ、次のようにアクセスできます。
例6-9 JavaにおけるREF CURSORデータへのアクセス
import oracle.jdbc.*; ... CallableStatement cstmt; ResultSet cursor; // Use a PL/SQL block to open the cursor cstmt = conn.prepareCall ("begin open ? for select ename from emp; end;"); cstmt.registerOutParameter(1, OracleTypes.CURSOR); cstmt.execute(); cursor = ((OracleCallableStatement)cstmt).getCursor(1); // Use the cursor like a normal ResultSet while (cursor.next ()) {System.out.println (cursor.getString(1));}
前述の例では、次の手順が実行されます。
コール可能文によって、REF CURSOR
を返すPL/SQLプロシージャが実装されます。
通常どおり、コール可能文の出力パラメータは、その型を定義するために登録されている必要があります。REF CURSOR
には、型コードOracleTypes.CURSOR
を使用します。
コール可能文が実行され、REF CURSOR
が返されます。
getCursor
メソッドを使用するために、CallableStatement
オブジェクトがOracleCallableStatement
にキャストされます。このメソッドは、標準JDBC Application Program Interface(API)に対するOracleの拡張機能であり、REF CURSOR
をResultSet
オブジェクトに返します。
次の項では、サンプル・アプリケーションを拡張し、新しい従業員レコードを挿入するときに、動的に生成された職務IDと職務名のリストを「ジョブ」フィールドに表示します。
これを行うには、REF CURSOR
を使用して、職務の結果セットをJobs
表から取得するデータベース・ファンクションGET_JOBS
を作成します。新しいJavaメソッドgetJobs
は、このデータベース・ファンクションをコールして結果セットを取得します。
次の手順では、データベースで新しいパッケージを作成して、REF CURSOR
宣言を保持します。
「DatabaseNavigatorName」タブを選択して、ナビゲータで表示します。
Connection1ノードを開いて、データベース・オブジェクトのリストを表示します。「パッケージ」にスクロールします。「パッケージ」を右クリックし、「新規パッケージ」を選択します。
「PL/SQLパッケージ作成」ダイアログ・ボックスで、名前としてJOBSPKG
と入力します。「OK」をクリックします。パッケージの定義がソース・エディタに表示されます。
最初の行の末尾にカーソルを置き、[Enter]を押して新しい行を作成します。新しい行で、次のようにREF CURSOR
を宣言します。
TYPE ref_cursor IS REF CURSOR;
パッケージを保存します。
例6-10に、パッケージのコードを示します。
次の手順では、REF CURSOR
を使用して、職務の結果セットをJobs
表から取得するデータベース・ファンクションGET_JOBS
を作成します。
データベース・ナビゲータで、必要なノードを再度開いて、HR
データベースのオブジェクトを表示します。「ファンクション」を右クリックし、ショートカット・メニューで「新規関数」を選択します。
「PL/SQLファンクション作成」ダイアログ・ボックスで、名前としてGET_JOBS
と入力します。「OK」をクリックします。GET_JOBS
ファンクションの定義がソース・エディタに表示されます。
ファンクション定義の最初の行で、VARCHAR2
のかわりに、戻り値としてJobsPkg.ref_cursor
を指定します。
AS
キーワードの後に、次のように入力します。
jobs_cursor JobsPkg.ref_cursor;
BEGIN
ブロックで、次のコードを入力して現在の内容を置き換えます。
OPEN jobs_cursor FOR SELECT job_id, job_title FROM jobs; RETURN jobs_cursor;
ファンクションを保存します。
例6-11に、ファンクションのコードを示します。
次の手順では、GET_JOBS
ファンクションをコールして結果セットを取得するJavaメソッドgetJobs
をDataHandler
クラスに作成します。
DataHandler.java
がまだ開いていない場合は、ダブルクリックしてソース・エディタで開きます。
メソッド宣言を入力します。
public ResultSet getJobs() throws SQLException { }
メソッド本体で、データベースに接続します。
getDBConnection();
接続に続いて、新しい変数jobquery
を宣言します。
String jobquery = "begin ? := get_jobs; end;";
prepareCall
メソッドを使用して、CallableStatementを作成します。
CallableStatement callStmt = conn.prepareCall(jobquery);
Oracle固有の型を使用して、OUT
パラメータの型を登録します。
callStmt.registerOutParameter(1, OracleTypes.CURSOR);
Oracle固有の型を使用することを指定すると、[Alt]+[Enter]キーを押してoracle.jdbc.OracleTypes
をインポートすることを求めるメッセージが表示されます。[Alt]+[Enter]キーを押した後、表示されるリストでOracleTypes(oracle.jdbc
)を選択します。
文を実行して結果セットを返します。
callStmt.execute(); rset = (ResultSet)callStmt.getObject(1);
ここまでに入力したコードをtry
ブロックで囲みます。
例外を捕捉するためのcatchブロックを追加し、logExceptionメソッドもコールします。
catch ( SQLException ex ) { logException( ex ); }
catch
ブロックを閉じた後で、結果セットを返します。
return rset;
構文エラーをチェックするためのファイルを作成します。
getJobs
メソッドのコードを次に示します。
public ResultSet getJobs() throws SQLException { try { getDBConnection(); String jobquery = "begin ? := get_jobs; end;"; CallableStatement callStmt = conn.prepareCall(jobquery); callStmt.registerOutParameter(1, OracleTypes.CURSOR); callStmt.execute(); rset = (ResultSet)callStmt.getObject(1); } catch ( SQLException ex ) { logException( ex ); } return rset; }
職務IDと職務名のリストを挿入ページに表示するドロップ・ダウン・リストを作成するには、職務IDと職務名をハードコードします。次の手順では、これを前の項で作成したREF CURSOR
から提供される動的生成リストに置き換えます。
insert.jsp
が開いていない場合は、アプリケーション・ナビゲータのビジュアル・エディタでダブルクリックして開きます。
「Page Directive」をuseBean
タグの右側のページにドラッグします。「Page Directiveの挿入」ダイアログ・ボックスで「言語」にjava
を入力し、「インポート」フィールドでjava.sql.ResultSetを参照して選択します。「OK」をクリックします。
「Page Directive」の隣のページにスクリプトレットをドラッグします。「スクリプトレットの挿入」ダイアログ・ボックスで、次のコードを追加し、getJobs
メソッドを実行して、職務のリストを含む結果セットを返します。
ResultSet rset = empsbean.getJobs();
ページでListBoxコンポーネントを選択し、JSPのコンポーネント・パレットの「スクリプトレット」をクリックします。(この場合、スクリプトレットをページにドラッグ・アンド・ドロップする必要はありません。)「スクリプトレットの挿入」ダイアログ・ボックスが表示されます。
次のコードを「スクリプトレットの挿入」ダイアログ・ボックスに入力します。「OK」をクリックします。
while (rset.next ()) { out.println("<option value=" + rset.getString("job_id") + ">" + rset.getString("job_title") + "</option> " ); }
次のように、ハードコードされた値を削除します。
ListBoxコンポーネントが選択された状態のまま、「構造」ウィンドウで「ジョブ」フィールドにスクロールします。selectキーワードの下のハードコードされたオプションのリストを確認します。各オプションを削除しますが、スクリプトレットは保持されることを確認します。
ページを保存します。
ここで、アプリケーションを実行し、クリックして新しい従業員を挿入し、リストを使用して選択可能な職務のリストを表示します。図6-4に、ブラウザでの動的な職務のリストを示します。