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

Procedures in the PERSNL Schema
The Payroll class contains these SPJ methods, which are useful for managing personnel data:
• “ADJUSTSALARY Procedure” (page 85)
• “EMPLOYEEJOB Procedure” (page 87)
• “PROJECTTEAM Procedure” (page 89)
• “TOPSALESREPS Procedure” (page 90)
Those methods are registered as stored procedures in the PERSNL schema. Example A-8 shows
the code of the Payroll.java source file.
Example A-8 Payroll.java—The Payroll Class
import java.sql.*;
import java.math.*;
public class Payroll
{
public static void adjustSalary(BigDecimal empNum,
double percent,
BigDecimal[] newSalary)
throws SQLException
{
Connection conn = DriverManager.getConnection("jdbc:default:connection");
PreparedStatement setSalary =
conn.prepareStatement("UPDATE persnl.employee " +
"SET salary = salary * (1 + (? / 100)) " +
"WHERE empnum = ?");
PreparedStatement getSalary =
conn.prepareStatement("SELECT salary " +
"FROM persnl.employee " +
"WHERE empnum = ?");
setSalary.setDouble(1, percent);
setSalary.setBigDecimal(2, empNum);
setSalary.executeUpdate();
getSalary.setBigDecimal(1, empNum);
ResultSet rs = getSalary.executeQuery();
rs.next();
newSalary[0] = rs.getBigDecimal(1);
rs.close();
conn.close();
} // See the “ADJUSTSALARY Procedure” (page 85).
public static void employeeJob(int empNum,
java.lang.Integer[] jobCode)
throws SQLException
{
Connection conn = DriverManager.getConnection("jdbc:default:connection");
PreparedStatement getJobcode =
conn.prepareStatement("SELECT jobcode " +
"FROM persnl.employee " +
"WHERE empnum = ?");
getJobcode.setInt(1, empNum);
ResultSet rs = getJobcode.executeQuery();
rs.next();
int num = rs.getInt(1);
if (rs.wasNull())
jobCode[0] = null;
else
jobCode[0] = new Integer(num);
rs.close();
conn.close();
} // See the “EMPLOYEEJOB Procedure” (page 87).
Procedures in the PERSNL Schema 83