Neoview Guide to Stored Procedures in Java (R2.2)

ORDERSUMMARY Procedure
The ORDERSUMMARY procedure accepts a date, which is formatted as a string, and returns
this information about the orders on or after that date:
• The number of orders as an output parameter
• A result set that contains one row for each order. Each row contains fields for the order
number, order date, total dollar amount, number of parts ordered, and the name of the sales
representative.
• A result set that contains details about each order. Each order has one or more rows that
provide details about the ordered parts. Each row contains fields for the order number, part
number, unit price, quantity ordered, and part description.
Java Method: orderSummary()
Example A-7 orderSummary() Method
public static void orderSummary(java.lang.String onOrAfter,
long[] numOrders,
java.sql.ResultSet[] orders,
java.sql.ResultSet[] detail)
throws SQLException
{
java.lang.String s;
java.sql.Connection conn =
DriverManager.getConnection("jdbc:default:connection");
// Get the number of orders on or after this date
s = " SELECT COUNT(ordernum) FROM sales.orders " +
" WHERE order_date >= CAST(? AS DATE) ";
java.sql.PreparedStatement ps1 = conn.prepareStatement(s);
ps1.setString(1, onOrAfter);
java.sql.ResultSet rs = ps1.executeQuery();
rs.next();
numOrders[0] = rs.getLong(1);
rs.close();
// Open a result set for order num, order info rows
s = " SELECT amounts.*, orders.order_date, emps.last_name " +
" FROM ( SELECT o.ordernum, COUNT(d.partnum) AS num_parts, " +
" SUM(d.unit_price * d.qty_ordered) AS amount " +
" FROM sales.orders o, sales.odetail d " +
" WHERE o.ordernum = d.ordernum " +
" AND o.order_date >= CAST(? AS DATE) " +
" GROUP BY o.ordernum ) amounts, " +
" sales.orders orders, persnl.employee emps " +
" WHERE amounts.ordernum = orders.ordernum " +
" AND orders.salesrep = emps.empnum " +
" ORDER BY orders.ordernum ";
java.sql.PreparedStatement ps2 = conn.prepareStatement(s);
ps2.setString(1, onOrAfter);
orders[0] = ps2.executeQuery();
// Open a result set for order detail rows
s = " SELECT d.*, p.partdesc " +
" FROM sales.odetail d, sales.parts p, sales.orders O " +
" WHERE d.partnum = p.partnum AND d.ordernum = o.ordernum " +
" AND o.order_date >= CAST(? AS DATE) " +
" ORDER BY d.ordernum ";
88 Sample SPJs