Configuring table-mapping properties

Table mapping allows you to customize the DBMS queries and DML statements used to support ejbCreate/ctsCreate, ejbLoad/ctsLoad, ejbStore/ctsStore and ejbRemove/ctsRemove.

The default table mappings suffice for direct access to tables in standard SQL databases. You can customize the default SQL commands. For example, you might optimize the select query to force the use of an index by adding proprietary DBMS keywords.

You must configure explicit table access commands if you use stored procedures for data access or a non-SQL database.

For select, update, insert, and delete operations, the mapping can be [default], to specify that standard SQL commands be used, a stored procedure call, or alternate query text.

{call update-proc param-list}

Where:

• update-proc is the stored procedure name. The procedure must perform the update given the supplied input parameters, and return no data. EAServer checks the JDBC row count to determine whether the update succeeded.

• param-list is the parameter list, which must include all container-managed fields. If a timestamp or version counter is used for concurrency control, it must also be in the parameter list. You can format parameters as:

  • @fieldName to specify the value of field fieldName.

  • @fieldName.subfield  to specify the value of subfield subField where fieldName is a container-managed field that takes structured types.

  • @old.fieldName or @new.fieldName to specify the old (last read) or new (updated) value for field fieldName. If no old or  new prefix is used, the new value is assumed. The old and new prefixes cannot be applied to primary key fields, because an instance is not allowed to change the primary key.

For example:

{call sp_update_CustomerProcs @primaryKey, @firstName, @lastName, @old.ts}

The update procedure must contain logic to perform concurrency control. If using a timestamp column, make sure the timestamp value is passed to the procedure and used in the update statement. If using OCC with value comparisons, make sure the procedure accepts all old values as well as new values, and contains the value comparison logic.

{call delete-proc param-list}

Where:

• delete-proc is the stored procedure name.

• param-list is the parameter list, which must contain parameter values for the primary key columns, using placeholders as described for update procedure.

For example:

{call sp_delete_CustomerProcs @primaryKey}

{call insert-proc param-list}

Where:

• insert-proc is the stored procedure name. If the component uses generated primary keys, the procedure must return a result set containing the new key. Otherwise, the procedure must not return any data.

• param-list is the parameter list, which must contain all values for the new row, unless using automatic key generation. If keys are generated, omit the key from the parameter list. The parameter format is the same as for the update procedure, except that the old and new prefixes are not supported.

For example:

{call sp_insert_CustomerProcs @primaryKey, @firstName, @lastName}

select read-param-list from {call select-proc key-param-list}

Where:

• read-param-list contains placeholders for the field values and specifies their positions in the row returned by the procedure. The parameter format is the same as for the update procedure, except that the old and new prefixes are not supported. The stored procedure does not need to return the key value.

• select-proc is the stored procedure name.

• key-param-list is a parameter list that specifies all the primary key columns. The parameter format is the same as for the update procedure, except that the old and new prefixes are not supported.

For example:

select @firstName, @lastName, @ts from {call sp_select_CustomerProcs @primaryKey}

You can enter query text for the insert, delete, update, and select operations, using the same parameter placeholder format as used for stored procedures.

Special syntax is required also for ordinary SQL select statements to specify the mapping of fields to expected result set columns. For example, if storing customer names in a separate table and not using stored procedures, you might specify the select operation as:

select=select @firstName = firstName, @lastName = lastName from TestCMP_Customer where primaryKey = @primaryKey

When this query is issued to the JDBC driver, it will be in the form of a JDBC prepared statement, such as:

select firstName, lastName from TestCMP_Customer where primaryKey = ?

The persistence engine removes the “@field” references from the query, allowing the proprietary syntax of the target DBMS to be used effectively. For example, the query could be modified to force the use of an index by using proprietary DBMS keywords.