Jaffa Logo
Home Contact Us FAQs Site Map
Source Forge: Homepage Bugs @ Sourceforge Mailing Lists @ Sourceforge Task Manager @ Sourceforge CVS @ Sourceforge
Jaffa Site
Jaffa Runtime
Jaffa RAD
How To / Using Database Stored Procedures

The JDBC Engine allows you to build a domain obejct that represents an interface to a database stored procedure. This guide give you an overview and example of how this can be implemented and used


  1. The Stored Procedure Class
  2. Meta Data Class
  3. Sample Code

The Stored Procedure Class

A stored procedure class encapsulates a stored procedure in the database. The client class will create an instance of this class. It will then set the input parameters, by invoking the appropriate updateXxx methods. It will then create a criteria object, passing in the stored procedure object. The query() method of the UOW will execute the corresponding stored procedure in the database. It will then set the output parameters on the same stored procedure object (the one that was used for the query), by invoking the appropriate setXxx methods.

  • A stored procedure class should extend the abstract class org.jaffa.persistence.Persistent

  • It should implement the IStoredProcedure interface

  • It should have getXxx/setXxx/updateXxx/validateXxx methods for each field

  • The getXxx and setXxx methods should be similar to regular javabean getter/setter methods

    public java.lang.String getPart() {
        return m_part;
    public void setPart(java.lang.String part) {
        m_part = part;

  • The validateXxx method should utilise the meta class for validating a field. It can be enhanced with appropriate business logic

    public void validatePart(java.lang.String part) throws ValidationException {
        FieldValidator.validate(part, (StringFieldMetaData) PartMeta.META_PART, true);
        // add additional logic

  • The updateXxx methods should invoke the validateXxx methods to perform basic validations, and finally invoke the setXxx method to set the data

    public void updatePart(java.lang.String part) throws ValidationException, ReadOnlyObjectException, AlreadyLockedObjectException {

  • The stored procedure class should provide implementation of the IStoredProcedure interface methods. For example:

    public String prepareCall() {
        // this is the JDBC/SQL required for executing the stored procedure
        return "{call ke_vcpkg1.getvoucher(?,?,?)}";
    public String[] getParameters() {
        // these are the parameters of the stored procedure. There should be corresponding fields and supporting methods for these parameters
        return new String[] {"Prefix", "Length", "Voucher"};
    public int[] getParamDirections() {
        // these are the directions of the parameters. Can be one of 'IN', 'OUT', 'BOTH'
        return new int[] {IN, IN, OUT};
    public String[] getParamSqlTypes() {
        // these are the SQL data types of the parameters
        return new String[] {"VARCHAR", "BIGINT", "VARCHAR"};


The setXxx method of the stored procedure object is only to be invoked by the persistence engine, to pass values from the database

The business class which uses a stored procedure object, should never invoke the setXxx method; it should invoke the updateXxx methods instead

Meta Data Class

  • Each stored procedure class will have a corresponding meta class. For eg, the VoucherStoredProcedure class will have VoucherStoredProcedure as the meta class
  • A meta class should have the meta data information for each field of the stored procedure object. This may include -
    • The name of the field to be used in parameters
    • The token for getting the appropriate label from the resource bundle
    • The data type
    • Is the field mandatory
    • The layout to be used for displaying the contents
    • The pattern against which the contents will be validated
    • The case type
    • The length of the field
    • The minimum and maximum values
  • The validateXxx method of the domain class should utilise the meta data information for validations
  • The presentation tier should utilise the label-token for a field, to display the appropriate label from the resource bundle

Sample Code

  • Create the stored procedure in the database

  • The sample code to invoke the stored procedure

    UOW uow = null;
    try {
        uow = new UOW();
        // create a StoredProcedure Object and set its input parameters
        VoucherStoredProcedure obj = new VoucherStoredProcedure();
        obj.updateLength(new Long(15));
        // create the criteria, add the stored procedure object, and then execute the stored procedure by invoking the query method
        Criteria c = new Criteria();
        c.addCriteria(null, obj);
        uow.query( c );
        // this will commit the updations to the database, if any were performed by the stored procedure
    } catch (UOWException e) {
        // handle the exception
    } finally {
        if (uow != null)

File: storedProcedures.html, Last Modified: Mon Jul 14 2003 at 3:27:09pm. This site has been built using PPWIZARD