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.
To configure table mapping for an entity component, display the Persistence/Table Mapping subtab in the Component Properties window. Mapping properties display for the main table, specified on the Persistence/General tab, and any other table referenced in field mapping properties. Each table has mapping properties for the operations listed in Table 27-3.
Operation |
Specifies |
|---|---|
|
The database command for ejbLoad/ctsLoad operations. |
|
The database command for ejbStore/ctsStore operations. |
|
The database command for ejbCreate/ctsCreate operations. |
|
The database command for ejbRemove/ctsRemove operations. |
|
When you are using object caching and have enabled database change notification, this property specifies the message service topic name used to notify the object cache of table changes. The default is the unqualified table name. See “Entity instance and query caching” in the EAServer Performance and Tuning Guide for more information. |
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.
For update operations Specify the stored procedure call in the form:
{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.
For delete operations Specify the stored procedure call in the form:
{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}
For insert operations Specify the stored procedure call in the form:
{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}
For select operations Specify the stored procedure call in the form:
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.
| Copyright © 2005. Sybase Inc. All rights reserved. |
|
|
