Specifying finder- and ejbSelect-method queries  Using relationship components

Chapter 27: Creating Entity Components

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.

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.

Table 27-3: Table mapping operation names

Operation

Specifies

select

The database command for ejbLoad/ctsLoad operations.

update

The database command for ejbStore/ctsStore operations.

insert

The database command for ejbCreate/ctsCreate operations.

delete

The database command for ejbRemove/ctsRemove operations.

notify

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.

Configuring stored procedure invocations

For update operations Specify the stored procedure call in the form:

{call update-proc param-list}

Where:

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:

For example:

{call sp_delete_CustomerProcs @primaryKey}

For insert operations Specify the stored procedure call in the form:

{call insert-proc param-list}

Where:

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:

For example:

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

Specifying alternate query text

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. Using relationship components