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

Procedures in the INVENT Schema
The Inventory class contains these SPJ methods, which are useful for tracking parts and
suppliers:
• “SUPPLIERINFO Procedure” (page 94)
• “SUPPLYNUMBERS Procedure” (page 97)
• “PARTLOCS Procedure” (page 99)
Those methods are registered as stored procedures in the INVENT schema. Example A-13 shows
the code of the Inventory.java source file.
Example A-13 Inventory.java—The Inventory Class
import java.sql.*;
import java.math.*;
public class Inventory
{
public static void supplierInfo(BigDecimal suppNum,
String[] suppName,
String[] streetAddr,
String[] cityName,
String[] stateName,
String[] postCode)
throws SQLException
{
Connection conn = DriverManager.getConnection("jdbc:default:connection");
PreparedStatement getSupplier =
conn.prepareStatement("SELECT suppname, street, city, " +
" state, postcode " +
"FROM invent.supplier " +
"WHERE suppnum = ?");
getSupplier.setBigDecimal(1, suppNum);
ResultSet rs = getSupplier.executeQuery();
rs.next();
suppName[0] = rs.getString(1);
streetAddr[0] = rs.getString(2);
cityName[0] = rs.getString(3);
stateName[0] = rs.getString(4);
postCode[0] = rs.getString(5);
rs.close();
conn.close();
} // See the “SUPPLIERINFO Procedure” (page 94).
public static void supplyQuantities(int[] avgQty,
int[] minQty,
int[] maxQty)
throws SQLException
{
Connection conn = DriverManager.getConnection("jdbc:default:connection");
PreparedStatement getQty =
conn.prepareStatement("SELECT AVG(qty_on_hand), " +
" MIN(qty_on_hand), " +
" MAX(qty_on_hand) " +
"FROM invent.partloc");
ResultSet rs = getQty.executeQuery();
rs.next();
avgQty[0] = rs.getInt(1);
minQty[0] = rs.getInt(2);
maxQty[0] = rs.getInt(3);
rs.close();
conn.close();
} // See the “SUPPLYNUMBERS Procedure” (page 97).
92 Sample SPJs