NOTE: This page is a work in progress, and the information described here is subject to change.
This page presents examples of SPJs that can be deployed in a Trafodion database. The class files that contain the SPJ methods use JDBC method calls to access a sample database. See the Sample Database.
Procedures in the SALES Schema
The Sales class contains these SPJ methods, which are useful for tracking orders and managing sales:
Those methods are registered as stored procedures in the SALES schema. This example shows the code of the Sales.java source file.
Sales.java—The Sales Class
import java.sql.*; import java.math.*; public class Sales { public static void lowerPrice() throws SQLException { Connection conn = DriverManager.getConnection("jdbc:default:connection"); PreparedStatement getParts = conn.prepareStatement("SELECT p.partnum, " + " SUM(qty_ordered) AS qtyOrdered " + "FROM neo.sales.parts p " + "LEFT JOIN neo.sales.odetail o " + " ON p.partnum = o.partnum " + "GROUP BY p.partnum"); PreparedStatement updateParts = conn.prepareStatement("UPDATE neo.sales.parts " + "SET price = price * 0.9 " + "WHERE partnum = ?"); ResultSet rs = getParts.executeQuery(); while (rs.next()) { BigDecimal qtyOrdered = rs.getBigDecimal(2); if ((qtyOrdered == null) || (qtyOrdered.intValue() < 50)) { BigDecimal partnum = rs.getBigDecimal(1); updateParts.setBigDecimal(1, partnum); updateParts.executeUpdate(); } } rs.close(); conn.close(); } // See the [[LOWERPRICE Procedure]]. public static void numDailyOrders(Date date, int[] numOrders) throws SQLException { Connection conn = DriverManager.getConnection("jdbc:default:connection"); PreparedStatement getNumOrders = conn.prepareStatement("SELECT COUNT(order_date) " + "FROM neo.sales.orders " + "WHERE order_date = ?"); getNumOrders.setDate(1, date); ResultSet rs = getNumOrders.executeQuery(); rs.next(); numOrders[0] = rs.getInt(1); rs.close(); conn.close(); } // See the [[DAILYORDERS Procedure]]. public static void numMonthlyOrders(int month, int[] numOrders) throws SQLException { if ( month < 1 || month > 12 ) { throw new SQLException ("Invalid value for month. " + "Retry the CALL statement " + "using a number from 1 to 12 " + "to represent the month.", "38001" ); } Connection conn = DriverManager.getConnection("jdbc:default:connection"); PreparedStatement getNumOrders = conn.prepareStatement("SELECT COUNT(month(order_date)) " + "FROM neo.sales.orders " + "WHERE month(order_date) = ?"); getNumOrders.setInt(1, month); ResultSet rs = getNumOrders.executeQuery(); rs.next(); numOrders[0] = rs.getInt(1); rs.close(); conn.close(); } // See the [[MONTHLYORDERS Procedure]]. public static void totalPrice(BigDecimal qtyOrdered, String shippingSpeed, BigDecimal[] price) throws SQLException { BigDecimal shipcharge = new BigDecimal(0); if (shippingSpeed.equals("economy")) { shipcharge = new BigDecimal(1.95); } else if (shippingSpeed.equals("standard")) { shipcharge = new BigDecimal(4.99); } else if (shippingSpeed.equals("nextday")) { shipcharge = new BigDecimal(14.99); } else { throw new SQLException ("Invalid value for shipping speed. " + "Retry the CALL statement using " + "'economy' for 7 to 9 days," + "'standard' for 3 to 5 days, or " + "'nextday' for one day.", "38002" ); } BigDecimal subtotal = price[0].multiply(qtyOrdered); BigDecimal tax = new BigDecimal(0.0825); BigDecimal taxcharge = subtotal.multiply(tax); BigDecimal charges = taxcharge.add(shipcharge); BigDecimal totalprice = subtotal.add(charges); totalprice = totalprice.setScale(2, BigDecimal.ROUND_HALF_EVEN); price[0] = totalprice; } // See the [[TOTALPRICE Procedure]]. public static void partData(int partNum, String[] partDescription, BigDecimal[] unitPrice, int[] qtyAvailable, ResultSet[] orders, ResultSet[] locations, ResultSet[] suppliers, ResultSet[] reps) throws SQLException { Connection conn = DriverManager.getConnection("jdbc:default:connection"); // Retrieve detail about this part into the output parameters PreparedStatement getPartInfo = conn.prepareStatement( " SELECT P.partdesc, P.price, P.qty_available " + " FROM neo.sales.parts P " + " WHERE partnum = ? "); getPartInfo.setInt(1, partNum); ResultSet rs = getPartInfo.executeQuery(); rs.next(); partDescription[0] = rs.getString(1); unitPrice[0] = rs.getBigDecimal(2); qtyAvailable[0] = rs.getInt(3); rs.close(); // Return a result set of rows from the ORDERS table listing orders // that included this part. Each ORDERS row is augmented with the // quantity of this part that was ordered. PreparedStatement getOrders = conn.prepareStatement( " SELECT O.*, QTY.QTY_ORDERED " + " FROM neo.sales.orders O, " + " ( select ordernum, sum(qty_ordered) as QTY_ORDERED " + " from neo.sales.odetail " + " where partnum = ? " + " group by ordernum) QTY " + " WHERE O.ordernum = QTY.ordernum " + " ORDER BY O.ordernum "); getOrders.setInt(1, partNum); orders[0] = getOrders.executeQuery(); // Return a result set of rows from the PARTLOC table listing // locations that have this part in stock and the quantity they // have on hand. PreparedStatement getLocations = conn.prepareStatement( " SELECT * " + " FROM neo.invent.partloc " + " WHERE partnum = ? "); getLocations.setInt(1, partNum); 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 neo.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 neo.persnl.employee " + " WHERE empnum in ( SELECT O.salesrep " + " FROM neo.sales.orders O, " + " neo.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]]. 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 neo.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 neo.sales.orders o, neo.sales.odetail d " + " WHERE o.ordernum = d.ordernum " + " AND o.order_date >= CAST(? AS DATE) " + " GROUP BY o.ordernum ) amounts, " + " neo.sales.orders orders, neo.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 neo.sales.odetail d, neo.sales.parts p, neo.sales.orders O " + " WHERE d.partnum = p.partnum AND d.ordernum = o.ordernum " + " AND o.order_date >= CAST(? AS DATE) " + " ORDER BY d.ordernum "; java.sql.PreparedStatement ps3 = conn.prepareStatement(s); ps3.setString(1, onOrAfter); detail[0] = ps3.executeQuery(); } // See the [[ORDERSUMMARY Procedure]].
See the following sections for more information about each SPJ method.