Configuring Custom SQL Queries for Basic Persistence Operations

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 2.0 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").

Using TopLink Workbench

To configure custom SQL queries for basic persistence operations:

In the Navigator, select a descriptor in a relational database project.
Click the Queries tab in the Editor.
Click the Custom SQL tab.

Figure 29-3 Queries Custom SQL Tab

Description of "Figure 29-3 Queries Custom SQL Tab"

Click the appropriate SQL function tab and type your own SQL string to control these actions for a descriptor. Use the following information to complete the tab:

Tab	Description
Insert	Defines the insert SQL that TopLink uses to insert a new object's data into the database.
Update	Defines the update SQL that TopLink uses to update any changed existing object's data in the database. When you define a descriptor's update query, you must conform to the following: If the application uses optimistic locking, you must ensure that the row is not written if the version field has changed since the object was read. The update query must increment the version field if the row is written. The update string must maintain the row count of the database.
Delete	Defines the delete SQL that TopLink uses to delete an object.
Read Object	Defines the read SQL that TopLink uses in any `ReadObjectQuery`, whose selection criteria is based on the object's primary key. When you define a descriptor's read-object query, your implementation overrides any `ReadObjectQuery`, whose selection criteria is based on the object's primary key. TopLink generates dynamic SQL for all other `Session` `readObject` method signatures. To customize other `Session` `readObject` method signatures, define additional named queries and use them in your application instead of the `Session` methods.
Read All	Defines the read-all SQL that TopLink uses when you call `Session` method `readAllObjects(java.lang.Class)` passing in the `java.lang.Class` that this descriptor represents. When you define a descriptor's read-all query, your implementation overrides only the `Session` method `readAll(java.lang.Class)`, not the version that takes a `Class` and `Expression`. As a result, this query reads every single instance. TopLink generates dynamic SQL for all other `Session` `readAll` method signatures. To customize other `Session` `readAll` method signatures, define additional named queries and use them in your application instead of the Session methods.

Using Java

The DescriptorQueryManager generates default SQL for the following persistence operations:

Insert
Update
Delete
Read-object
Read-all
Does-exist

Using Java code, you can use the descriptor query manager to provide custom SQL strings to perform these functions on a class-by-class basis.

Use ClassDescriptor method getQueryManager to acquire the DescriptorQueryManager, and then use the DescriptorQueryManager methods that Table 29-2 lists.

Table 29-2 Descriptor Query Manager Methods for Configuring Custom SQL

To Change the Default SQL for ...	Use Descriptor Query Manager Method ...
Insert	`setInsertQuery (InsertObjectQuery query)`
	`setInsertSQLString (String sqlString)`
	`setInsertCall(Call call)`
Update	`setUpdateQuery (UpdateObjectQuery query)`
	`setUpdateSQLString (String sqlString)`
	`setUpdateCall(Call call)`
Delete	`setDeleteQuery (DeleteObjectQuery query)`
	`setDeleteSQLString (String sqlString)`
	`setDeleteCall(Call call)`
Read	`setReadObjectQuery (ReadObjectQuery query)`
	`setReadObjectSQLString (String sqlString)`
	`setReadObjectCall(Call call)`
Read all	`setReadAllQuery (ReadAllQuery query)`
	`setReadAllSQLString (String sqlString)`
	`setReadAllCall(Call call)`
Does exist	`setDoesExistQuery(DoesExistQuery query)`
	`setDoesExistSQLString(String sqlString)`
	`setDoesExistCall(Call call)`

Example 29-5 shows how to implement an amendment method to configure a descriptor query manager to use custom SQL strings. Alternatively, using an SQLCall, you can specify more complex SQL strings using features such as in, out, and in-out parameters and parameter types (see "Using SQL Calls").

Example 29-5 Configuring a Descriptor Query Manager with Custom SQL Strings

public static void addToDescriptor(ClassDescriptor descriptor) {

    // Read-object by primary key procedure
    descriptor.getQueryManager().setReadObjectSQLString(
        "select * from EMP where EMP_ID = #EMP_ID"
    );

    // Read-all instances procedure
    descriptor.getQueryManager().setReadAllSQLString(
        "select * from EMP"
    );

    // Insert procedure
    descriptor.getQueryManager().setInsertSQLString(
        "insert into EMP (EMP_ID, F_NAME, L_NAME, MGR_ID) values (#EMP_ID, #F_NAME, #L_NAME, #MGR_ID)"
    );

    // Update procedure
    descriptor.getQueryManager().setUpdateSQLString(
        "update EMP set (F_NAME, L_NAME, MGR_ID) values (#F_NAME, #L_NAME, #MGR_ID) where EMP_ID = #EMP_ID"
    );
}

Example 29-6 shows how to implement an amendment method to configure a descriptor query manager to use Oracle stored procedures using a StoredProcedureCall (see "Using a StoredProcedureCall"). This example uses output cursors to return the result set (see "Handling Cursor and Stream Query Results").

Example 29-6 Configuring a Descriptor Query Manager with Custom Stored Procedure Calls

public static void addToDescriptor(ClassDescriptor descriptor) {
 
    // Read-object by primary key procedure
    StoredProcedureCall readCall = new StoredProcedureCall();
    readCall.setProcedureName("READ_EMP");
    readCall.addNamedArgument("P_EMP_ID", "EMP_ID");       readCall.useNamedCursorOutputAsResultSet("RESULT_CURSOR");       descriptor.getQueryManager().setReadObjectCall(readCall);
 
    // Read-all instances procedure
    StoredProcedureCall readAllCall = new StoredProcedureCall();
    readAllCall.setProcedureName("READ_ALL_EMP");
    readAllCall.useNamedCursorOutputAsResultSet("RESULT_CURSOR");         descriptor.getQueryManager().setReadAllCall(readAllCall );
 
    // Insert procedure
    StoredProcedureCall insertCall = new StoredProcedureCall();
    insertCall.setProcedureName("INSERT_EMP");
    insertCall.addNamedArgument("P_EMP_ID", "EMP_ID"); 
    insertCall.addNamedArgument("P_F_NAME", "F_NAME");
    insertCall.addNamedArgument("P_L_NAME", "L_NAME");
    insertCall.addNamedArgument("P_MGR_ID", "MGR_ID");
    descriptor.getQueryManager().setInsertCall(insertCall);
 
    // Update procedure
    StoredProcedureCall updateCall = new StoredProcedureCall();      updateCall.setProcedureName("UPDATE_EMP");
    updateCall.addNamedArgument("P_EMP_ID", "EMP_ID"); 
    updateCall.addNamedArgument("P_F_NAME", "F_NAME");
    updateCall.addNamedArgument("P_L_NAME", "L_NAME");
    updateCall.addNamedArgument("P_MGR_ID", "MGR_ID");
    descriptor.getQueryManager().setUpdateCall(updateCall);
}