Using a Stored Procedure


CallableStatment cs =    conn.prepareCall("{ call ListAllUsers }"); ResultSet rs = cs.executeQuery( );



Stored procedures are database programs that are stored and maintained within the database itself. You can call one of these stored procedures from within Java using the CallableStatement interface and the prepareCall() method of the Connection object. A CallableStatement returns a ResultSet object just as a Statement or PreparedStatement does. In this phrase, we call the stored procedure ListAllUsers with no parameters.

A CallableStatement object can take input parameters also. Input parameters are handled exactly as they are when using a Prepared Statement. For example, here we show how you might call a stored procedure that uses input parameters:

CallableStatment cs =    conn.prepareCall("{ call AddInts(?,?) }"); cs.setInt(1,10); cs.setInt(2,50); ResultSet rs = cs.executeQuery( );


Unlike other kinds of JDBC statements, a CallableStatement can also return parameters. These are referred to as OUT parameters. When using OUT parameters, the JDBC type of each OUT parameter must be registered before the CallableStatement object can be executed. The OUT parameters are registered using the registerOutParameter() method. After the statement has been executed, the OUT parameters can be retrieved using the CallableStatement's getter methods.

CallableStatement cs =    con.prepareCall("{call getData(?, ?)}"); cs.registerOutParameter(1, java.sql.Types.INT); cs.registerOutParameter(2, java.sql.Types.STRING); ResultSet rs = cs.executeQuery(); int intVal = cs.getInt(1); String strVal = cs.getString(2);


In this example, we call a stored procedure named getdata() which has two OUT parameters. One of these OUT parameters is an int value, and the other is a String value. After registering both of these parameters, we execute the query, and then get their values using the getInt() and getString() methods.

Another difference of functionality when using Stored Procedures is that a stored procedure can return multiple result sets. If a stored procedure returns more than one result set, the getMoreResults() method of the CallableStatement class is used to close the current result set and point to the next result set. The getresultSet() method is called after calling the getMoreResults() method to retrieve the result set being pointed to. An example that returns multiple result sets and uses these methods to retrieve each result set is shown below:

int i; String s; callablestmt.execute(); rs = callablestmt.getResultSet(); while (rs.next()) {    i = rs.getInt(1); } callablestmt.getMoreResults(); rs = callablestmt.getResultSet(); while (rs.next()) {    s = rs.getString(1); } rs.close(); callablestmt.close();


In this example, we set the int value i with results from the first result set and the String variable s with results from the second result set.




JavaT Phrasebook. Essential Code and Commands
Java Phrasebook
ISBN: 0672329077
EAN: 2147483647
Year: 2004
Pages: 166

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net