6.3.7.5.5 Execute CallableStatement
To run a CallableStatement object, three methods can be used: executeQuery(), executeUp-date() and execute(). As we discussed in Section 6.3.3.3, the executeQuery() method can return a ResultSet object that contains the run or query results; however, the execute() method can-not return any run result byitself, and you need to use the associated getXXX() methods to pick up the query or run result. Another important point of using the execute() method is that it can handle an unknown result with undefined data type. Refer to Section 6.3.3.3 to get more detailed information about the execute() method.
An example of using the execute() method to run the CallableStatement object is:
String query = “{call FacultyCourse(?,?)}”; cstmt = LogInFrame.con.prepareCall(query); cstmt.setString(1, ComboName.getSelectedItem().toString()); cstmt.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR); cstmt.execute();
Now let’s handle how to retrieve the result from running a CallableStatement object.
6.3.7.5.6 Retrieve the Run Results
To pick up the run results from the execution of a CallableStatement object, one needs to use the associated getXXX() method. Two popular ways to get a run result from a CallableStatement are the getXXX() method and the getObject() method. The former is based on the returned data type of the result, and the latter is more general to get any kind of result.
All of the getXXX() and getObject() methods use the same syntax, which looks like:
getXXX(int position);
getObject(int position);
where XXX indicates the OUT value Java data type, and the position is the relative position of the OUT parameter in the query statement. The same syntax is used for the getObject() method.
An example of using the getXXX() method to pick up the run result from the execution of a CallableStatement object is shown here:
String query = “{call FacultyCourse(?,?)}”; cstmt = LogInFrame.con.prepareCall(query); cstmt.setString(1, ComboName.getSelectedItem().toString()); cstmt.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR); cstmt.execute();
String cResult = cstmt.getString(2);
Since the OUT parameter is a String and is located at position of 2, an argument of 2 is used in the getString() method to pick up the run result. An alternative way to get the same result is to use the getObject() method, which looks like:
String cResult = (String)cstmt.getObject(2);
The returned result must be cast by using the String data type, since an object can be any data type.
Ok, that is enough for the theoretical discussions. Now let’s getto our real stuff, developing the code for the Select button Click event handler to perform the CallableStatement object to call an Oracle stored procedure to query a faculty record from our Faculty Table in our sample database.