Skip Headers

# Configuring Custom SQL Queries for Basic Persistence Operations

Previous

Next

You can use TopLink to define an SQL query for each basic persistence operation (insert, update, delete, read-object, read-all, or does-exist) so that when you query and modify your relational-mapped objects, the TopLink runtime will use the appropriate SQL query instead of the default SQL query.

SQL strings can include any fields that the descriptor maps, as well as arguments. You specify arguments in the SQL string using `#<arg-name>`, such as:

    select * from EMP where EMP_ID = #EMP_ID

The insert and update SQL strings can take any field that the descriptor maps as an argument.

The read-object, delete and does-exist SQL strings can only take the primary key fields as arguments.

The read-all SQL string must return all instances of the class and thus can take no arguments.

You can define a custom SQL string for insert, update, delete, read-object, and read-all using TopLink Workbench (see "Using TopLink Workbench").

You can define a custom SQL string or `Call` object for insert, update, delete, read-object, read-all, and does-exist using Java (see "Using Java"). Using a `Call`, you can define more complex SQL strings and invoke custom stored procedures.

For CMP projects, the `ejb-jar.xml` file stores query lists. You can define the queries in the file and then read them into TopLink Workbench (see "Reading From the ejb-jar.xml File"), or define them on the Queries tab and write them to the file (see "Writing to the ejb-jar.xml File").

Note:

When you customize the update persistence operation for an application that uses optimistic locking (see "Configuring Locking Policy"), the custom update string must not write the object if the row version field has changed since the initial object was read. In addition, it must increment the version field if it writes the object successfully.

For example:

    update Employee set F_NAME = #F_NAME, VERSION = VERSION + 1 where (EMP_ID = #EMP_ID) AND (VERSION = #VERSION)

The update string must also maintain the row count of the database.

Note: TopLink does not validate the SQL code that you enter. Enter the SQL code appropriate for your database platform (see "Data Source Platform Types").