SQL/MX 3.2.1 Guide to Stored Procedures in Java (H06.26+, J06.15+)

Example 1 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 samdbcat.sales.parts p "+
"LEFT JOIN samdbcat.sales.odetailo " +
" ON p.partnum = o.partnum "+
"GROUP BY p.partnum");
PreparedStatement updateParts =
conn.prepareStatement("UPDATE samdbcat.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();
}
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 samdbcat.sales.orders " +
"WHERE order_date = ?");
getNumOrders.setDate(1, date);
ResultSet rs = getNumOrders.executeQuery();
rs.next();
numOrders[0] = rs.getInt(1);
rs.close();
conn.close();
}
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 " +
108 Sample SPJs