この章では、ストアド・プロシージャを使用したJavaアプリケーションの作成方法について説明します。この章に示す設計フェーズから実際の実装までの手順を実行することにより、独自のアプリケーションを作成できます。
この章の内容は、次のとおりです。
ここでは、顧客の発注書を管理する簡単なシステムの開発を目的とします。最初に、関連するビジネス・エンティティとその相互関係を特定する必要があります。特定するには、図8-1に示す規則と例に従ってエンティティ関連(E-R)ダイアグラムを描画する必要があります。
図8-2に示すように、この例の基本エンティティは、顧客、発注書、明細項目および在庫品目です。
Customer
(顧客)には、Purchase Order
(発注書)との間に1対多の関係があります。顧客が1つまたは複数の注文を行うことができるのに対し、特定の発注書を発行できるのは1人の顧客のみであるためです。顧客がいない状態での発注があるため、この関係はオプションです。たとえば、以前に顧客として定義されていない顧客が発注するような場合です。
Purchase Order
(発注書)には、Stock Item
(在庫品目)との間に多対多の関係があります。これは、発注書には複数の在庫品目を指定する場合があり、在庫品目は複数の発注書によって指定される場合があるためです。ただし、発注書と在庫品目の参照関係は不明です。したがって、明細項目の概念が必要です。Purchase Order
(発注書)には、Line Item
(明細項目)との間に1対多の関係があります。これは、発注書には複数の明細項目を記載する場合がありますが、特定の明細項目を記載できるのは1枚の発注書のみであるためです。
Line Item
(明細項目)には、Stock Item
(在庫品目)との間に多対1の関係があります。これは、明細項目に指定できるのは1つの在庫品目のみですが、特定の在庫品目は複数の明細項目によって指定される場合があるためです。明細項目がない状態での在庫品目の指定があるため、この関係はオプションです。
E-Rダイアグラムを描画した後、スキーマ計画を作成する必要があります。作成するには、E-Rダイアグラムを次のデータベース表に分解します。
Customers
Orders
LineItems
StockItems
たとえば、Customer
エンティティの属性は、Customers
表の列に割り当てることができます。
図8-3は、複数の表の関係を示しています。E-Rダイアグラムでは、明細項目に対する発注書と在庫品目の関係が示されていました。スキーマ計画では、主キーと外部キーを使用してこれらの関係を設定します。
主キーは、表の各行を一意に識別する値を持つ列または列の組合せです。外部キーは、他の表の主キーと一致する値を持つ列または列の組合せです。たとえば、LineItems
表のPONo
列は、Orders
表の主キーと一致する外部キーです。LineItems.PONo
列の発注書番号はすべてOrders.PONo
列にも存在する必要があります。
データベース・スキーマを計画した後は、スキーマ計画で必要なデータベース表を作成します。作成を開始するには、次のようにCustomers
表を定義します。
CREATE TABLE Customers ( CustNo NUMBER(3) NOT NULL, CustName VARCHAR2(30) NOT NULL, Street VARCHAR2(20) NOT NULL, City VARCHAR2(20) NOT NULL, State CHAR(2) NOT NULL, Zip VARCHAR2(10) NOT NULL, Phone VARCHAR2(12), PRIMARY KEY (CustNo) );
Customers
表には、顧客に関する情報が格納されます。必須情報は、NOT NULL
として定義されます。たとえば、すべての顧客には出荷先の住所が必要です。ただし、Customers
表では、顧客とその発注書との関係は管理されません。したがって、この関係は、次のように定義可能なOrders
表で管理する必要があります。
CREATE TABLE Orders ( PONo NUMBER(5), Custno NUMBER(3) REFERENCES Customers, OrderDate DATE, ShipDate DATE, ToStreet VARCHAR2(20), ToCity VARCHAR2(20), ToState CHAR(2), ToZip VARCHAR2(10), PRIMARY KEY (PONo) );
図8-2のE-Rダイアグラムでは、明細項目に対する発注書と在庫品目の関係が示されていました。LineItems
表では、外部キーを使用してこれらの関係を管理します。たとえば、LineItems
のStockNo
外部キー列が、StockItems
のStockNo
主キーを参照する場合は次のように定義できます。
CREATE TABLE StockItems ( StockNo NUMBER(4) PRIMARY KEY, Description VARCHAR2(20), Price NUMBER(6,2)) );
Orders
表は、Customers
のCustNo
主キー列を参照しているCustNo
外部キー列を使用して、顧客と発注書との関係を管理します。ただし、Orders
では、発注書とその明細項目との間の関係を管理しません。したがって、この関係は、次のように定義可能なLineItems
で管理する必要があります。
CREATE TABLE LineItems ( LineNo NUMBER(2), PONo NUMBER(5) REFERENCES Orders, StockNo NUMBER(4) REFERENCES StockItems, Quantity NUMBER(2), Discount NUMBER(4,2), PRIMARY KEY (LineNo, PONo) );
データベース表を作成した後は、発注書システムに必要な操作を考慮して、適切なJavaメソッドを作成します。前述の例で定義した表に基づいた簡単なシステムでは、顧客の登録、部品の保管、発注の入力などのメソッドが必要です。これらのメソッドは、JavaクラスPOManager
で次のように実装できます。
import java.sql.*; import java.io.*; import oracle.jdbc.*; public class POManager { public static void addCustomer (int custNo, String custName, String street, String city, String state, String zipCode, String phoneNo) throws SQLException { String sql = "INSERT INTO Customers VALUES (?,?,?,?,?,?,?)"; try { Connection conn = DriverManager.getConnection("jdbc:default:connection:"); PreparedStatement pstmt = conn.prepareStatement(sql); pstmt.setInt(1, custNo); pstmt.setString(2, custName); pstmt.setString(3, street); pstmt.setString(4, city); pstmt.setString(5, state); pstmt.setString(6, zipCode); pstmt.setString(7, phoneNo); pstmt.executeUpdate(); pstmt.close(); } catch (SQLException e) { System.err.println(e.getMessage()); } } public static void addStockItem (int stockNo, String description, float price) throws SQLException { String sql = "INSERT INTO StockItems VALUES (?,?,?)"; try { Connection conn = DriverManager.getConnection("jdbc:default:connection:"); PreparedStatement pstmt = conn.prepareStatement(sql); pstmt.setInt(1, stockNo); pstmt.setString(2, description); pstmt.setFloat(3, price); pstmt.executeUpdate(); pstmt.close(); } catch (SQLException e) { System.err.println(e.getMessage()); } } public static void enterOrder (int orderNo, int custNo, String orderDate, String shipDate, String toStreet, String toCity, String toState, String toZipCode) throws SQLException { String sql = "INSERT INTO Orders VALUES (?,?,?,?,?,?,?,?)"; try { Connection conn = DriverManager.getConnection("jdbc:default:connection:"); PreparedStatement pstmt = conn.prepareStatement(sql); pstmt.setInt(1, orderNo); pstmt.setInt(2, custNo); pstmt.setString(3, orderDate); pstmt.setString(4, shipDate); pstmt.setString(5, toStreet); pstmt.setString(6, toCity); pstmt.setString(7, toState); pstmt.setString(8, toZipCode); pstmt.executeUpdate(); pstmt.close(); } catch (SQLException e) { System.err.println(e.getMessage()); } } public static void addLineItem (int lineNo, int orderNo, int stockNo, int quantity, float discount) throws SQLException { String sql = "INSERT INTO LineItems VALUES (?,?,?,?,?)"; try { Connection conn = DriverManager.getConnection("jdbc:default:connection:"); PreparedStatement pstmt = conn.prepareStatement(sql); pstmt.setInt(1, lineNo); pstmt.setInt(2, orderNo); pstmt.setInt(3, stockNo); pstmt.setInt(4, quantity); pstmt.setFloat(5, discount); pstmt.executeUpdate(); pstmt.close(); } catch (SQLException e) { System.err.println(e.getMessage()); } } public static void totalOrders () throws SQLException { String sql = "SELECT O.PONo, ROUND(SUM(S.Price * L.Quantity)) AS TOTAL " + "FROM Orders O, LineItems L, StockItems S " + "WHERE O.PONo = L.PONo AND L.StockNo = S.StockNo " + "GROUP BY O.PONo"; try { Connection conn = DriverManager.getConnection("jdbc:default:connection:"); PreparedStatement pstmt = conn.prepareStatement(sql); ResultSet rset = pstmt.executeQuery(); printResults(rset); rset.close(); pstmt.close(); } catch (SQLException e) { System.err.println(e.getMessage()); } } static void printResults (ResultSet rset) throws SQLException { String buffer = ""; try { ResultSetMetaData meta = rset.getMetaData(); int cols = meta.getColumnCount(), rows = 0; for (int i = 1; i <= cols; i++) { int size = meta.getPrecision(i); String label = meta.getColumnLabel(i); if (label.length() > size) size = label.length(); while (label.length() < size) label += " "; buffer = buffer + label + " "; } buffer = buffer + "\n"; while (rset.next()) { rows++; for (int i = 1; i <= cols; i++) { int size = meta.getPrecision(i); String label = meta.getColumnLabel(i); String value = rset.getString(i); if (label.length() > size) size = label.length(); while (value.length() < size) value += " "; buffer = buffer + value + " "; } buffer = buffer + "\n"; } if (rows == 0) buffer = "No data found!\n"; System.out.println(buffer); } catch (SQLException e) { System.err.println(e.getMessage()); } } public static void checkStockItem (int stockNo) throws SQLException { String sql = "SELECT O.PONo, O.CustNo, L.StockNo, " + "L.LineNo, L.Quantity, L.Discount " + "FROM Orders O, LineItems L " + "WHERE O.PONo = L.PONo AND L.StockNo = ?"; try { Connection conn = DriverManager.getConnection("jdbc:default:connection:"); PreparedStatement pstmt = conn.prepareStatement(sql); pstmt.setInt(1, stockNo); ResultSet rset = pstmt.executeQuery(); printResults(rset); rset.close(); pstmt.close(); } catch (SQLException e) { System.err.println(e.getMessage()); } } public static void changeQuantity (int newQty, int orderNo, int stockNo) throws SQLException { String sql = "UPDATE LineItems SET Quantity = ? " + "WHERE PONo = ? AND StockNo = ?"; try { Connection conn = DriverManager.getConnection("jdbc:default:connection:"); PreparedStatement pstmt = conn.prepareStatement(sql); pstmt.setInt(1, newQty); pstmt.setInt(2, orderNo); pstmt.setInt(3, stockNo); pstmt.executeUpdate(); pstmt.close(); } catch (SQLException e) { System.err.println(e.getMessage()); } } public static void deleteOrder (int orderNo) throws SQLException { String sql = "DELETE FROM LineItems WHERE PONo = ?"; try { Connection conn = DriverManager.getConnection("jdbc:default:connection:"); PreparedStatement pstmt = conn.prepareStatement(sql); pstmt.setInt(1, orderNo); pstmt.executeUpdate(); sql = "DELETE FROM Orders WHERE PONo = ?"; pstmt = conn.prepareStatement(sql); pstmt.setInt(1, orderNo); pstmt.executeUpdate(); pstmt.close(); } catch (SQLException e) { System.err.println(e.getMessage()); } } }
Javaクラスを作成した後は、loadjava
ツールを使用して、次のようにJavaストアド・プロシージャをOracle Databaseにアップロードします。
> loadjava -u scott@myPC:1521:orcl -v -r -t POManager.java
Password: password
initialization complete
loading : POManager
creating : POManager
resolver : resolver ( ("*" scott) ("*" public) ("*" -) )
resolving: POManager
-v
オプションを指定すると、冗長モードになります。-r
オプションを指定すると、アップロードしたJavaソース・ファイルがコンパイルされ、そのクラスの外部参照が解決されます。-t
オプションを指定すると、loadjava
ツールは、クライアント側のJDBC Thinドライバを使用してデータベースに接続します。
Javaクラスをロードした後は、Javaストアド・プロシージャをOracleデータ・ディクショナリに公開します。これを行うには、Javaメソッド名、パラメータ・タイプおよび戻り型を、対応するSQLにマップするコール仕様を記述する必要があります。
POManager
Javaクラスのメソッドは論理的に関連付けられています。そのため、PL/SQLパッケージ内でコール仕様をグループ化できます。グループ化するには、最初に、次のようにパッケージ仕様部を作成します。
CREATE OR REPLACE PACKAGE po_mgr AS PROCEDURE add_customer (cust_no NUMBER, cust_name VARCHAR2, street VARCHAR2, city VARCHAR2, state CHAR, zip_code VARCHAR2, phone_no VARCHAR2); PROCEDURE add_stock_item (stock_no NUMBER, description VARCHAR2, price NUMBER); PROCEDURE enter_order (order_no NUMBER, cust_no NUMBER, order_date VARCHAR2, ship_date VARCHAR2, to_street VARCHAR2, to_city VARCHAR2, to_state CHAR, to_zip_code VARCHAR2); PROCEDURE add_line_item (line_no NUMBER, order_no NUMBER, stock_no NUMBER, quantity NUMBER, discount NUMBER); PROCEDURE total_orders; PROCEDURE check_stock_item (stock_no NUMBER); PROCEDURE change_quantity (new_qty NUMBER, order_no NUMBER, stock_no NUMBER); PROCEDURE delete_order (order_no NUMBER); END po_mgr;
次に、Javaメソッドのコール仕様を次のように記述してパッケージ本体を作成します。
CREATE OR REPLACE PACKAGE BODY po_mgr AS PROCEDURE add_customer (cust_no NUMBER, cust_name VARCHAR2, street VARCHAR2, city VARCHAR2, state CHAR, zip_code VARCHAR2, phone_no VARCHAR2) AS LANGUAGE JAVA NAME 'POManager.addCustomer(int, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String)'; PROCEDURE add_stock_item (stock_no NUMBER, description VARCHAR2, price NUMBER) AS LANGUAGE JAVA NAME 'POManager.addStockItem(int, java.lang.String, float)'; PROCEDURE enter_order (order_no NUMBER, cust_no NUMBER, order_date VARCHAR2, ship_date VARCHAR2, to_street VARCHAR2, to_city VARCHAR2, to_state CHAR, to_zip_code VARCHAR2) AS LANGUAGE JAVA NAME 'POManager.enterOrder(int, int, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String)'; PROCEDURE add_line_item (line_no NUMBER, order_no NUMBER, stock_no NUMBER, quantity NUMBER, discount NUMBER) AS LANGUAGE JAVA NAME 'POManager.addLineItem(int, int, int, int, float)'; PROCEDURE total_orders AS LANGUAGE JAVA NAME 'POManager.totalOrders()'; PROCEDURE check_stock_item (stock_no NUMBER) AS LANGUAGE JAVA NAME 'POManager.checkStockItem(int)'; PROCEDURE change_quantity (new_qty NUMBER, order_no NUMBER, stock_no NUMBER) AS LANGUAGE JAVA NAME 'POManager.changeQuantity(int, int, int)'; PROCEDURE delete_order (order_no NUMBER) AS LANGUAGE JAVA NAME 'POManager.deleteOrder(int)'; END po_mgr;
Javaクラスを公開した後は、トップレベル、データベース・トリガー、SQLデータ操作言語(DML)文およびPL/SQLブロックから、Javaストアド・プロシージャをコールします。po_mgr
パッケージ内のストアド・プロシージャを参照するには、ドット表記法を使用します。
無名PL/SQLブロックからは、次のように部品を保管することで、新しい発注書システムを開始できます。
BEGIN po_mgr.add_stock_item(2010, 'camshaft', 245.00); po_mgr.add_stock_item(2011, 'connecting rod', 122.50); po_mgr.add_stock_item(2012, 'crankshaft', 388.25); po_mgr.add_stock_item(2013, 'cylinder head', 201.75); po_mgr.add_stock_item(2014, 'cylinder sleeve', 73.50); po_mgr.add_stock_item(2015, 'engine bearning', 43.85); po_mgr.add_stock_item(2016, 'flywheel', 155.00); po_mgr.add_stock_item(2017, 'freeze plug', 17.95); po_mgr.add_stock_item(2018, 'head gasket', 36.75); po_mgr.add_stock_item(2019, 'lifter', 96.25); po_mgr.add_stock_item(2020, 'oil pump', 207.95); po_mgr.add_stock_item(2021, 'piston', 137.75); po_mgr.add_stock_item(2022, 'piston ring', 21.35); po_mgr.add_stock_item(2023, 'pushrod', 110.00); po_mgr.add_stock_item(2024, 'rocker arm', 186.50); po_mgr.add_stock_item(2025, 'valve', 68.50); po_mgr.add_stock_item(2026, 'valve spring', 13.25); po_mgr.add_stock_item(2027, 'water pump', 144.50); COMMIT; END;
次のように顧客を登録します。
BEGIN po_mgr.add_customer(101, 'A-1 Automotive', '4490 Stevens Blvd', 'San Jose', 'CA', '95129', '408-555-1212'); po_mgr.add_customer(102, 'AutoQuest', '2032 America Ave', 'Hayward', 'CA', '94545', '510-555-1212'); po_mgr.add_customer(103, 'Bell Auto Supply', '305 Cheyenne Ave', 'Richardson', 'TX', '75080', '972-555-1212'); po_mgr.add_customer(104, 'CarTech Auto Parts', '910 LBJ Freeway', 'Dallas', 'TX', '75234', '214-555-1212'); COMMIT; END;
次のように、様々な顧客による発注書を入力します。
BEGIN po_mgr.enter_order(30501, 103, '14-SEP-1998', '21-SEP-1998', '305 Cheyenne Ave', 'Richardson', 'TX', '75080'); po_mgr.add_line_item(01, 30501, 2011, 5, 0.02); po_mgr.add_line_item(02, 30501, 2018, 25, 0.10); po_mgr.add_line_item(03, 30501, 2026, 10, 0.05); po_mgr.enter_order(30502, 102, '15-SEP-1998', '22-SEP-1998', '2032 America Ave', 'Hayward', 'CA', '94545'); po_mgr.add_line_item(01, 30502, 2013, 1, 0.00); po_mgr.add_line_item(02, 30502, 2014, 1, 0.00); po_mgr.enter_order(30503, 104, '15-SEP-1998', '23-SEP-1998', '910 LBJ Freeway', 'Dallas', 'TX', '75234'); po_mgr.add_line_item(01, 30503, 2020, 5, 0.02); po_mgr.add_line_item(02, 30503, 2027, 5, 0.02); po_mgr.add_line_item(03, 30503, 2021, 15, 0.05); po_mgr.add_line_item(04, 30503, 2022, 15, 0.05); po_mgr.enter_order(30504, 101, '16-SEP-1998', '23-SEP-1998', '4490 Stevens Blvd', 'San Jose', 'CA', '95129'); po_mgr.add_line_item(01, 30504, 2025, 20, 0.10); po_mgr.add_line_item(02, 30504, 2026, 20, 0.10); COMMIT; END;
出力をSQL*Plusテキスト・バッファにリダイレクトした後は、SQL*Plus内で次のようにtotalOrders()
メソッドをコールできます。
SQL> SET SERVEROUTPUT ON SQL> CALL dbms_java.set_output(2000); ... SQL> CALL po_mgr.total_orders(); PONO TOTAL 30501 1664 30502 275 30503 4149 30504 1635 Call completed.