com.buzzsurf.sql
Class StoredProcedure

java.lang.Object
  com.buzzsurf.sql.BuzzSQL
      com.buzzsurf.sql.Select
          com.buzzsurf.sql.StoredProcedure

public class StoredProcedure
extends Select

A StoredProcedure object executes a stored program in a relational database. A StoredProcedure is similar to a Select object, as it can have a result set. It is also similar to an Update object, as it can have an update count. It is a combination of the underlying JDBC classes;

• java.sql.Connection
• java.sql.PreparedStatement
• java.sql.ResultSet
• java.sql.ResultSetMetaData

After execution, you may call hasResultSet() to find out of the StoredProcedure produced a result set. If it has a result set, you may iterate through the StoredProcedure object using next(), and retrieve your results in any format using one of the variety of get methods. You must call next() at least once before any get call to set the index in the result set to the first result. You may also get the number of rows updated by calling getUpdateCount() or getRowCount().

Some databases allow a stored procedure to return results in the form of OUT or INOUT parameters. BuzzSQL supports this through the use of the com.buzzsurf.sql.OutParameter and com.buzzsurf.sql.InOutParameter objects. For more detailed information see the sections on OutParameter and InOutParameter objects.

As an example, the following code fragment creates a StoredProcedure object using the automatic default connection to the database. The SQL statement is passed via constructor, and the arguments are passed using the setArgs(Object...) method. This example makes use of an OutParameter and an InOutParameter to demonstrate the usage of these classes. OUT and INOUT parameters are used only in conjunction with stored procedures. execute() is called to execute the database call, and then results are iterated through using next(). The object is then closed to cleanup any resources and return the connection to the pool.

 OutParameter outParam = new OutParameter(java.sql.Types.VARCHAR);
 InOutParameter inOutParam = new InOutParameter(java.sql.Types.INTEGER, 10);
 StoredProcedure storedProc = new StoredProcedure("call test.storedproc_test(?, ?, ?)");
 storedProc.setArgs("asdf", outParam, inOutParam);
 storedProc.execute();
 if (storedProc.hasResultSet())
 {
 while (storedProc.next())
 System.out.println(storedProc.getLine());
 }
 System.out.println(outParam.getString());
 System.out.println(inOutParam.getInt());
 storedProc.close();
 

Note: This code does not handle exceptions. You normally should wrap your calls in try/catch/finally blocks to handle any problems. It is advisable to put your close() call in a finally block to insure the connection is always released.

Author:

Paul Cowan (www.buzzsurf.com/sql)

See Also:

BuzzSQL, Select, Update, OutParameter, InOutParameter

Field Summary

protected boolean	hasResultSet
protected int	updateCount

Fields inherited from class com.buzzsurf.sql.Select

rs, rsmd

Fields inherited from class com.buzzsurf.sql.BuzzSQL

args, con, DATABASE_FORMATTER, dataSourceName, sql, stmt, usingExplicitConnection

Constructor Summary

StoredProcedure()
A zero argument constructor is provided for simplified operation with JavBeans, SOAP, and reflection scenarios where having such a constructor is necessary or convenient.

StoredProcedure(java.lang.String sql)
A single argument constructor that accepts your SQL statement and uses the default DataSource.

StoredProcedure(java.lang.String sql, java.sql.Connection con)
A dual argument constructor that accepts your SQL statement and a java.sql.Connection object.

StoredProcedure(java.lang.String sql, java.lang.String dataSourceName)
A dual argument constructor that accepts your SQL statement and the explicit name of a DataSource to use.

Method Summary

StoredProcedure	execute() During execution a database connection is obtained (if needed), SQL and arguments are merged, and the PreparedStatement is executed against the database.
int	getUpdateCount() Get the number of rows updated.
boolean	hasResultSet() Returns a boolean to indicate the form of the first result.

Methods inherited from class com.buzzsurf.sql.Select

close, getBoolean, getBoolean, getByte, getBytes, getBytes, getCalendar, getCalendar, getColumnCount, getColumnName, getDate, getDate, getDouble, getDouble, getFloat, getFloat, getInt, getInt, getLine, getLine, getLineCSV, getLong, getLong, getMetaData, getResultSet, getShort, getShort, getString, getString, next, setCalendar, setCalendar

Methods inherited from class com.buzzsurf.sql.BuzzSQL

addArgs, close, getArgs, getConnection, getDataSourceName, getReleaseInfo, getSQL, getStatement, merge, prepare, queryToString, setArgs, setConnection, setDataSourceName, setSQL, usingExplicitConnection

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Field Detail

hasResultSet

protected boolean hasResultSet

updateCount

protected int updateCount

Constructor Detail

StoredProcedure

public StoredProcedure()

A zero argument constructor is provided for simplified operation with JavBeans, SOAP, and reflection scenarios where having such a constructor is necessary or convenient. At a minimum, you must call setSQL(String) before execution when using this constructor.

StoredProcedure

public StoredProcedure(java.lang.String sql)

A single argument constructor that accepts your SQL statement and uses the default DataSource.

Parameters:

sql - The SQL statement

StoredProcedure

public StoredProcedure(java.lang.String sql,
                       java.lang.String dataSourceName)

A dual argument constructor that accepts your SQL statement and the explicit name of a DataSource to use.

Parameters:

sql - The SQL statement

dataSourceName - The explicit name of the dataSource to obtain a connecton from in DataSourceManager.

StoredProcedure

public StoredProcedure(java.lang.String sql,
                       java.sql.Connection con)

A dual argument constructor that accepts your SQL statement and a java.sql.Connection object. This constructor provides a great deal of flexibility by allowing the use of "explicit" connections that are supplied by the user rather than being obtained automatically by BuzzSQL. Using a explicit connection also allows BuzzSQL to support database transactions.

Parameters:

sql - The SQL statement

con - The explicit database Connection

Method Detail

hasResultSet

public boolean hasResultSet()

Returns a boolean to indicate the form of the first result. You must call either the method getResultSet or getUpdateCount to retrieve the result; you must call getMoreResults to move to any subsequent result(s).

Returns:

true if the first result is a ResultSet object; false if the first result is an update count or there is no result

getUpdateCount

public int getUpdateCount()

Get the number of rows updated.

Returns:

Number of rows updated

execute

public StoredProcedure execute()
                        throws java.sql.SQLException

During execution a database connection is obtained (if needed), SQL and arguments are merged, and the PreparedStatement is executed against the database. execute() throws an exception if any of these steps fails for any reason.

Typical post-execution steps are slightly different depending on the object subtype. Select based objects will obtain a ResultSet and ResultSetMetaData, while Update based objects will query and save the updated row count to a local variable.

execute() returns a reference to the current object to support method chaining. See Method Chaining for more information. You can assume execution succeeded if no exception is thrown.

Overrides:

execute in class Select

Throws:

java.sql.SQLException - if any of the executing JDBC operations failed