THIS IS A TEST INSTANCE. ALL YOUR CHANGES WILL BE LOST!!!!
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.