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

A Sample SPJs
This appendix presents the SPJs that are shown in examples throughout this manual. The class
files that contain the SPJ methods use JDBC method calls to access a sample Neoview database.
For information about the sample database, see Appendix B (page 101).
• “Procedures in the SALES Schema” (page 65)
• “Procedures in the PERSNL Schema” (page 83)
• “Procedures in the INVENT Schema” (page 92)
Procedures in the SALES Schema
The Sales class contains these SPJ methods, which are useful for tracking orders and managing
sales:
• “LOWERPRICE Procedure” (page 69)
• “DAILYORDERS Procedure” (page 71)
• “MONTHLYORDERS Procedure” (page 73)
• “TOTALPRICE Procedure” (page 75)
• “PARTDATA Procedure” (page 77)
• “ORDERSUMMARY Procedure” (page 81)
Those methods are registered as stored procedures in the SALES schema. Example A-1 shows
the code of the Sales.java source file.
Example A-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 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();
} // See the “LOWERPRICE Procedure” (page 69).
Procedures in the SALES Schema 65