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

Contents

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

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 { validatePart(part); setPart(part); }

• 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"}; }

Notes

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

• 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

• 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.updatePrefix("V"); 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.setTable(VoucherStoredProcedureMeta.getName()); c.addCriteria(null, obj); uow.query( c ); // this will commit the updations to the database, if any were performed by the stored procedure uow.commit(); } catch (UOWException e) { // handle the exception } finally { if (uow != null) uow.rollback(); }