J F W

HOW TO : CORE

execute a stored procedure

In this example will see how to execute a stored procedure. We suppose that we have a stored procedure with name  mysp and the package of the stored procedure is mypackage.

The name of the class used to execute the stored procedure is MySP. Here is the code:

public final class MySP extends StoredProcedureGateway { public static final String IN_1 = "IN_1"; public static final String IN_2 = "IN_2"; public MySP(HashMap inputParams) { super(inputParams); } protected void setStoredProcedure() { setStoredProcedure("{call mypackage.mysp(?,?,?,?)}"); } protected void setInput() throws DatabaseException { try { CallableStatement callable = getCallableStatement(); callable.setInt(1, Integer.parseInt(getInputParam(IN_1))); callable.setString(2, getInputParam(IN_2)); } catch (Throwable t) { JFWLogger.error(className, "setInput", "", "JFW", t.toString(), t); throw new DatabaseException(t.getMessage()); } } protected void setOutput() throws DatabaseException { try { CallableStatement callable = getCallableStatement(); callable.registerOutParameter(3, java.sql.Types.INTEGER); callable.registerOutParameter(4, java.sql.Types.VARCHAR); } catch (Throwable t) { JFWLogger.error(className, "setInput", "", "JFW", t.toString(), t); throw new DatabaseException(t.getMessage()); } } public void myExecute(HashMap outputdata) throws DatabaseException { boolean commit = false; try { execute(outputdata, false); outputdata.put("out1", getCallableStatement().getString(3)); outputdata.put("out2", getCallableStatement().getString(4)); commit = true; } catch (DatabaseException dbe) { throw dbe; } catch (Throwable t) { JFWLogger.error(className, "myExecute", "", "JFW", t.toString(), t); throw new DatabaseException(t.getMessage()); } finally { close(commit); } } }

Our class extends StoredProcedureGateway. This class has two abstract methods that our class must implement.

In our example, we use the default database resource: if a different resource have to be used, then the relative constructor of StoredProcedureGateway must be called. For example, if we want to use the resource with name db2 then we have to substitute the code in the constructor with this one:

super(inputParams, "db1");

If the execution of the stored procedure is part of a transaction, then we have to implement a constructor that can take the Transaction parameter and the relative constructor of StoredProcedureGateway must be called:

public MySP(HashMap inputParams, Transaction transaction) { super(inputParams, transaction); }

The method setStoredProcedure is used to set the stored procedure:

setStoredProcedure("{call mypackage.mysp(?,?,?,?)}");

If our stored procedure has input and/or output parameters they must be declared before the execution of the stored procedure. To set the input parameters we use the method setInput:

CallableStatement callable = getCallableStatement(); callable.setInt(1, Integer.parseInt(getInputParam(IN_1))); callable.setString(2, getInputParam(IN_2));

In our example the first two parameters are of type in, the first one is an integer and the second a string.

To set the output parameters we use the method setOutput:

CallableStatement callable = getCallableStatement(); callable.registerOutParameter(3, java.sql.Types.INTEGER); callable.registerOutParameter(4, java.sql.Types.VARCHAR);

The first of the output parameters is an integer and the second a string.

The method myExecute is used for executing the stored procedure and eventually reading the returned data and/or the output parameters:

execute(outputdata, false); outputdata.put("out1", getCallableStatement().getString(3)); outputdata.put("out2", getCallableStatement().getString(4));

The boolean parameter of method execute is true when the stored procedure returns a result set (cursor). In that case the data returned will be read automatically by the method and will be saved in outputdata under the key ALL_ROWS_KEY.
Under the key RESULT_KEY will be saved a boolean with value true if no error occur or false in the other case.
The output parameters must be read explicitly using the callable statement.

To call the MySP class from an action we have to write:

HashMap data = new HashMap(); data.put(MySP.IN_1, "1"); data.put(MySP.IN_2, "ok"); MySP mySP = new MySP(data); mySP.myExecute(outputdata);