SQL/MX Guide to Stored Procedures in Java (G06.24+, H06.03+)

Writing SPJ Methods
HP NonStop SQL/MX Guide to Stored Procedures in Java—523727-004
3-6
JDBC/MX-Based Java Method
platforms because their data source URL, "jdbc:sqlmx:", is specific to NonStop
SQL/MX.
Each JDBC connection has its own SQL context and MXCMP server. If each
invocation of an SPJ method creates a new connection object and does not explicitly
close that connection, SQL/MX resources are consumed, and performance might
diminish. To avoid large numbers of MXCMP servers handling requests from the SPJ
environment:
•
Use the JDBC/MX implementation of connection pooling, where a cache of
physical database connections is assigned to a client session and reused.
Connection pooling is ON by default in the JDBC/MX driver and is, therefore,
enabled by default in an SPJ environment. For more information, see the JDBC
Driver for SQL/MX Programmer’s Reference.
•
Explicitly close each java.sql.Connection object when it is no longer needed
instead of relying on garbage collection. This action immediately releases SQL/MX
resources, including the MXCMP server, for that connection. For pooled
connections, closing the Connection object returns it to the pool of reusable
connections.
JDBC/MX-Based Java Method
A JDBC/MX-based Java method is from a Java program that contains SQL/MX
statements in JDBC/MX method calls. You can use this type of method to create an
SPJ that performs SQL operations on an SQL/MP or SQL/MX database.
For example, the adjustSalary() method in the Payroll class adjusts an
employee’s salary in the EMPLOYEE table:
public class Payroll {
public static void adjustSalary(BigDecimal empNum,
double percent,
BigDecimal[] newSalary)
throws SQLException
{
Connection conn = DriverManager.getConnection("jdbc:sqlmx:");
PreparedStatement setSalary =
conn.prepareStatement("UPDATE samdbcat.persnl.employee " +
"SET salary = salary * (1 + (? / 100)) " +
"WHERE empnum = ?");
PreparedStatement getSalary =
conn.prepareStatement("SELECT salary " +
"FROM samdbcat.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);