| Oracle® Database Java開発者ガイド 12cリリース1 (12.1) B72466-05 |
|
 前 |
 次 |
この章では、ストアド・プロシージャを使用したJavaアプリケーションの作成方法について説明します。
この章では、設計フェーズから実際の実装に至るまで、サンプル・アプリケーションの開発手順を示します。
この例の目的は、顧客の発注書を管理する簡単なシステムを開発することです。このために、データベース・スキーマ計画を作成する必要があります。最初に、関連するビジネス・エンティティおよびそれらの関係を指定します。この例での基本的なエンティティは、顧客、発注書、明細項目および在庫品目です。したがって、スキーマに次の表を含めることができます。
Customers
Orders
LineItems
StockItems
顧客が1つまたは複数の注文を行うことができるのに対し、特定の発注書を発行できるのは1人の顧客のみであるため、Customer表とOrders表との間には1対多の関係があります。顧客がいない状態での発注があるため、この関係はオプションです。たとえば、以前に顧客として定義されていない顧客が発注するような場合です。
1枚の発注書には複数の在庫品目を指定する場合があり、在庫品目は複数の発注書によって指定される場合があるため、Orders表とStockItems表との間には多対多の関係があります。ただし、発注書と在庫品目の参照関係は不明です。したがって、明細項目の概念が必要です。発注書には複数の明細項目が記載される場合がありますが、特定の明細項目を記載できるのは1枚の発注書のみなので、Orders表とLineItems表との間には1対多の関係があります。
明細項目に指定できるのは1つの在庫品目のみですが、特定の在庫品目は複数の明細項目によって指定される場合があるため、LineItemsとStockItems表との間には多対1の関係があります。明細項目がない状態での在庫品目の指定があるため、この関係はオプションです。
図8-1は、複数の表の関係を示しています。スキーマ計画では、主キーと外部キーを使用してこれらの関係を設定します。
主キーは、表の各行を一意に識別する値を持つ列または列の組合せです。外部キーは、他の表の主キーと一致する値を持つ列または列の組合せです。たとえば、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) );
明細項目は、発注書および在庫品目との関係があります。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 HR@myPC:1521:orcl -v -r -t POManager.java
Password: password
initialization complete
loading : POManager
creating : POManager
resolver : resolver ( ("*" HR) ("*" 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.
