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

locations[0] = getLocations.executeQuery();
// Return a result set of rows from the PARTSUPP table listing
// suppliers who supply this part.
PreparedStatement getSuppliers = conn.prepareStatement(
" SELECT * " +
" FROM invent.partsupp " +
" WHERE partnum = ? ");
getSuppliers.setInt(1, partNum);
suppliers[0] = getSuppliers.executeQuery();
// Return a result set of rows from the EMPLOYEE table listing
// sales reps that have sold this part.
PreparedStatement getReps = conn.prepareStatement(
" SELECT * " +
" FROM persnl.employee " +
" WHERE empnum in ( SELECT O.salesrep " +
" FROM sales.orders O, " +
" sales.odetail D " +
" WHERE D.partnum = ? " +
" AND O.ordernum = D.ordernum ) " +
" ORDER BY empnum ");
getReps.setInt(1, partNum);
reps[0] = getReps.executeQuery();
} // See the “PARTDATA Procedure” (page 77).
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 " +
68 Sample SPJs