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

public static void orderSummary...
{
...
}
}
The following procedure sections show the code of each SPJ method.
LOWERPRICE Procedure
The LOWERPRICE procedure determines which items are selling poorly (that is, have less than
50 orders) and lowers the price of these items in the database by 10 percent.
Java Method: lowerPrice()
Example A-2 lowerPrice() Method
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 sales.parts p " +
"LEFT JOIN sales.odetail o " +
" ON p.partnum = o.partnum " +
"GROUP BY p.partnum");
PreparedStatement updateParts =
conn.prepareStatement("UPDATE 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();
}
Creating the Procedure: LOWERPRICE
To create this procedure in the SALES schema, upload the Sales.jar file to the Neoview
platform, navigate to the SALES schema in DB Admin, and then enter or select these values in
the Create Procedure Wizard of DB Admin. For instructions, see “Uploading SPJ JAR Files to
the Neoview Platform” (page 35) and “Creating SPJs” (page 43).
lowerpriceName:
Sales.jar > Sales > lowerPriceCode:
NoneParameters:
• Dynamic result sets: 0
• Accesses Database: selected
Attributes:
76 Sample SPJs