StoredProcedureResult call (java.lang.String sql, Object... parameters)
For example, consider a stored procedure defined to have two IN Integer parameters, one OUT Float, and no returned query results. You could invoke it with logic like this:
Integer one = new Integer(1); 1
Integer three = new Integer(3);
Connection con = DriverManager.getConnection(...); 2
Data db = DataFactory.getData(con); 3
StoredProcedureResult spr = db.call("CALL getRatio(?, ?, ?)", one, three, null); 4
Object[] outputs = spr.getOutputParms(); 5
Float oneThird = outputs[2]; // outputs is Object[3] 6
spr.close(); // best practice 7
The code performs the following steps:
The question marks are positional parameter markers. The first corresponds to the parameter Integer one, the second to the parameter Integer three, and the last to the parameter null. The null value is a placeholder for the value of the OUT parameter of the stored procedure.
For another example, suppose that you had only partial information regarding the SQL method getRatio(). If you did not know that the getRatio() method returns no query results, and did not know whether any of its IN parameters were also OUT parameters, the logic might look more like this:
Integer one = new Integer(1); 1
Integer three = new Integer(3);
Connection con = DriverManager.getConnection(...); 2
Data db = DataFactory.getData(con); 3
StoredProcedureResult spr = db.call("CALL getRatio(?, ?, ?)", one, three, null); 4
Object[] outputs = spr.getOutputParms(); 5
for (int j = 0; j < outputs.length; j++) 6
System.out.println(j + ": " + outputs[j]);
while (spr.moveToNext()) { 7
ResultSet rsPtr = spr.getResults();
... 8
}
}
spr.close(); 9
The code performs the following steps:
When the parameters are passed to Data.call() method in either a bean or Map object, the values of the stored procedure's returned OUT and INOUT parameters appears in the Object[] array and are used to update the given bean or Map object.
For example, imagine a bean, Ratio, with properties numerator, denominator, and ratio.
Integer one = new Integer(1); 1
Integer three = new Integer(3);
Ratio rValue = new Ratio(one, three); 2
Connection con = DriverManager.getConnection(...); 3
Data db = DataFactory.getData(con); 4
StoredProcedureResult spr = db.call(
"CALL getRatio(:numerator, :denominator, :ratio)", rValue); 5
Float oneThird = rValue.getRatio(); 6
spr.close(); 7
In this example, the use of the :name parameter markers indicates that there is only one parameter, which is the bean although it could as easily have been a Map object.
The code performs the following steps:
Imagine that a stored procedure is invoked by passing in values that correspond to a DeptProject bean's properties for a project ID and a new end date for the project. The OUT parameter is an updated staff level that is needed for that department project.
One might not develop a stored procedure for just that purpose, but minor projects are contained by major projects, so an update to the end date for a single project could ripple across multiple containing projects, and that sort of cascaded operation is something stored procedures are suited for. The parameter passed to the call() method could be a DeptProject object, but it is not a good practice to use objects that one never intends to fully populate or use as one would use a fully populated instance. A Map object is a better choice for this sort of use.
In this example, a call to this stored procedure might look like this:
Map<String, Object> slipMap; 1
//... 2
Connection con = DriverManager.getConnection(...); 3
Data db = DataFactory.getData(con); 4
StoredProcedureResult spr = db.call(
"CALL newEndDate(":projectId, :endDate, :staffLevel)", slipMap ); 5
spr.close(); 6
The code performs the following steps: