|
Oracle TopLink Developer's Guide
10g Release 3 (10.1.3) B13593-01 |
|
 Previous |
 Next |
|
Oracle TopLink Developer's Guide
10g Release 3 (10.1.3) B13593-01 |
|
|
Previous |
Next |
The TopLink expression framework enables you to define complex queries at the object level. If your application requires a more complex query or one that accesses data or stored procedures directly, you can specify a custom SQL string in an SQL Call object and provide that Call object to any query.
You can also specify an SQL string directly on DatabaseQuery. For more information, see "Specifying a Custom SQL String in a DatabaseQuery".
When using SQL calls, you can use a ReturningPolicy to control whether or not TopLink writes a parameter out or retrieves a value generated by the database. For more information, see "Configuring Returning Policy"
This section describes the following:
You can provide an SQLCall object to any query instead of an expression, but the SQL string contained in the SQLCall must return all data required to build an instance of the queried class.
The SQL string can be a complex SQL query, a stored procedure call, or a stored function call. You can specify input, output, and input/output parameters.
You can invoke an SQLCall through a session query method (as Example 98-39 illustrates) or through a DatabaseQuery.
Example 98-39 Session Read Query With Custom SQL
Employee employee = (Employee) session.executeSelectingCall(
new SQLCall("SELECT * FROM EMPLOYEE WHERE EMP_ID = 44")
);
|
WARNING: Allowing an unverified SQL string to be passed into methods makes your application vulnerable to SQL injection attacks. |
TopLink assumes that a token in the custom SQL string of an SQLCall is a parameter if it is prefixed with one or more number signs ( # ). You can bind values to these parameters using query API, as the following sections describe:
In Example 98-40, you specify last_name as an input parameter by prefixing its name with one number sign (#). Example 98-41 shows how to bind a value to this input parameter when you execute the query.
Example 98-40 Specifying an SQLCall with an Input Parameter Using the # Prefix
SQLCall sqlCall = new SQLCall( "INSERT INTO EMPLOYEE (L_NAME) VALUES (#last_name)" );
Example 98-41 Executing an SQLCall with an Input Parameter
UpdateObjectQuery query = new UpdateObjectQuery(myEmployee);
query.setCall(sqlCall);
query.addArgument("last_name"); // input
query.bindAllParameters();
Vector arguments = new Vector();
arguments.add("MacDonald");
session.executeQuery(query, arguments);
In Example 98-42, you specify employee_id as an output parameter by prefixing its name with three number signs (###). You specify the type of the output parameter with SQLCall method setCustomSQLArgumentType (see "Specifying a SQLCall Parameter Type"). You continue to specify last_name as an input parameter by prefixing its name with #.
Example 98-42 Specifying a SQLCall with an Output Parameter Using the ### Prefix
SQLCall sqlCall = new SQLCall(
"INSERT INTO EMPLOYEE (L_NAME) VALUES (#L_NAME) RETURNING EMP_ID INTO ###employee_id"
);
sqlCall.setCustomSQLArgumentType("employee_id", Integer.class);
Example 98-43 Executing a SQLCall with an Output Parameter
UpdateObjectQuery query = new UpdateObjectQuery(myEmployee);
query.setCall(sqlCall);
query.addArgument("last_name"); // input
query.addArgument("employee_id"); // output
query.bindAllParameters();
Vector args = new Vector();
args.add("MacDonald");
DatabaseRecord record = (DatabaseRecord)((Vector)getSession().executeQuery(
query, args)).firstElement();
Integer employeeID = new Integer(((Number)record.get("employee_id")).intValue());
You can also obtain results for an output parameter declared to be of type CURSOR (see "Cursors and SQLCall").
In Example 98-44, you specify in_out as an input and output parameter by prefixing its name with four number signs (####). The type of the input value determines the type of the output value. In this example, a String ("MacDonald") is passed in and the output value (for EMP_ID) is returned as a String.
Example 98-44 Specifying an Input and Output Parameter Using the #### Prefix
SQLCall sqlCall = new SQLCall( "INSERT INTO EMPLOYEE (L_NAME) VALUES (####in_out) RETURNING EMP_ID INTO ####in_out" );
Example 98-45 Executing a SQLCall with an Input and Output Parameter
UpdateObjectQuery query = new UpdateObjectQuery(myEmployee);
query.setCall(sqlCall);
query.addArgument("in_out"); // input and outpu
query.bindAllParameters();
Vector args = new Vector();
args.add("MacDonald");
DatabaseRecord record = (DatabaseRecord)((Vector)getSession().executeQuery(
query, args)).firstElement();
Integer employeeID = new Integer(record.get("in_out")); // out value has same type as input
If you map a parameter or identify it in a ReturningPolicy (see "Configuring Returning Policy"), you do not need to explicitly specify the parameter's type.
Otherwise, you must explicitly specify the parameter's type using SQLCall method setCustomSQLArgumentType.
If TopLink cannot determine the type of the parameter, it throws a ValidationException.
In Example 98-46, output parameter EMP_ID is mapped or identified in a ReturningPolicy but output parameter AGE is not: you must specify its type (Integer) explicitly.
You can provide a StoredProcedureCall object to any query instead of an expression or a SQL string, but the procedure must return all data required to build an instance of the class you query.
Example 98-47 A Read-All Query with a Stored Procedure
ReadAllQuery readAllQuery = new ReadAllQuery();
call = new StoredProcedureCall();
call.setProcedureName("Read_All_Employees");
readAllQuery.setCall(call);
Vector employees = (Vector) session.executeQuery(readAllQuery);
Using a StoredProcedureCall, you can access the following:
|
Note: You no longer need to useDatabaseQuery method bindAllParameters when using a StoredProcedureCall with OUT or INOUT parameters. However, you should always specify the Java type for all OUT and INOUT parameters. If you do not, be aware of the fact that they default to type String.
|
In Example 98-48, you specify the parameter POSTAL_CODE as an input parameter using the StoredProcedureCall method addNamedArgument, and you can specify the value of the argument using method addNamedArgumentValue.
Example 98-48 Stored Procedure Call with an Input Parameter
StoredProcedureCall call = new StoredProcedureCall();
call.setProcedureName("CHECK_VALID_POSTAL_CODE");
call.addNamedArgument("POSTAL_CODE");
call.addNamedArgumentValue("L5J1H5");
call.addNamedOutputArgument(
"IS_VALID", // procedure parameter name
"IS_VALID", // out argument field name
Integer.class // Java type corresponding to type returned by procedure
);
ValueReadQuery query = new ValueReadQuery();
query.setCall(call);
Number isValid = (Number) session.executeQuery(query);
The order in which you add arguments must correspond to the order in which you add argument values. In Example 98-49, the argument NAME is bound to the value Juliet and the argument SALARY is bound to the value 80000.
Example 98-49 Matching Arguments and Values in a Stored Procedure Call
StoredProcedureCall call = new StoredProcedureCall();
call.setProcedureName("CHECK_VALID_POSTAL_CODE");
call.addNamedArgument("NAME");
call.addNamedArgument("SALARY");
call.addNamedArgumentValue("Juliet");
call.addNamedArgumentValue(80000);
Output parameters enable the stored procedure to return additional information. You can use output parameters to define a readObjectQuery if they return all the fields required to build the object.
In Example 98-50, you specify the parameter IS_VALID as an output parameter using the StoredProcedureCall method addNamedOutputArgument.
Example 98-50 Stored Procedure Call with an Output Parameter
StoredProcedureCall call = new StoredProcedureCall();
call.setProcedureName("CHECK_VALID_POSTAL_CODE");
call.addNamedArgument("POSTAL_CODE");
call.addNamedOutputArgument(
"IS_VALID", // procedure parameter name
"IS_VALID", // out argument field name
Integer.class // Java type corresponding to type returned by procedure
);
ValueReadQuery query = new ValueReadQuery();
query.setCall(call);
query.addArgument("POSTAL_CODE");
Vector parameters = new Vector();
parameters.addElement("L5J1H5");
Number isValid = (Number) session.executeQuery(query,parameters);
|
Note: Not all databases support the use of output parameters to return data. However, because these databases generally support returning result sets from stored procedures, they do not require output parameters. |
If you are using an Oracle database, you can make use of TopLink cursor and stream query results. For more information, see "Stored Procedure Cursor Output Parameters".
In Example 98-51, you specify the parameter LENGTH as an input/output parameter and specify the value of the argument when it is passed to the stored procedure using the StoredProcedureCall method addNamedInOutputArgumentValue. If you do not want to specify a value for the argument, use method addNamedInOutputArgument.
Example 98-51 Stored Procedure Call with an Input/Output Parameter
StoredProcedureCall call = new StoredProcedureCall();
call.setProcedureName("CONVERT_FEET_TO_METERs");
call.addNamedInOutputArgumentValue(
"LENGTH", // procedure parameter name
new Integer(100), // in argument value
"LENGTH", // out argument field name
Integer.class // Java type corresponding to type returned by procedure
)
ValueReadQuery query = new ValueReadQuery();
query.setCall(call);
Integer metricLength = (Integer) session.executeQuery(query);
TopLink manages output parameter events for databases that support them. For example, if a stored procedure returns an error code that indicates that the application wants to check for an error condition, TopLink raises the session event OutputParametersDetected to allow the application to process the output parameters.
Example 98-52 Stored Procedure with Reset Set and Output Parameter Error Code
StoredProcedureCall call = new StoredProcedureCall();
call.setProcedureName("READ_EMPLOYEE");
call.addNamedArgument("EMP_ID");
call.addNamedOutputArgument(
"ERROR_CODE", // procedure parameter name
"ERROR_CODE", // out argument field name
Integer.class // Java type corresponding to type returned by procedure
);
ReadObjectQuery query = new ReadObjectQuery();
query.setCall(call);
query.addArgument("EMP_ID");
ErrorCodeListener listener = new ErrorCodeListener();
session.getEventManager().addListener(listener);
Vector args = new Vector();
args.addElement(new Integer(44));
Employee employee = (Employee) session.executeQuery(query, args);
You use a StoredProcedureCall to invoke stored procedures defined on databases that support them. You can also use a StoredFunctionCall to invoke stored functions defined on databases that support them, that is, on databases for which the DatabasePlatform method supportsStoredFunctions returns true.
In general, both stored procedures and stored functions let you specify input parameters, output parameters, and input and output parameters. For more information, see "Using a StoredProcedureCall". However, stored procedures need not return values, while stored functions always return a single value.
The StoredFunctionCall class extends StoredProcedureCall to add one new method: setResult. Use this method to specify the name (and alternatively both the name and type) under which TopLink stores the return value of the stored function.
When TopLink prepares a StoredFunctionCall, it validates its SQL and throws a ValidationException under the following circumstances:
If your current platform does not support stored functions. Stored functions are supported only for Oracle.
If you fail to specify the return type
In Example 98-53, note that the name of the stored function is set using StoredFunctionCall method setProcedureName.
Example 98-53 Creating a StoredFunctionCall
StoredFunctionCall functionCall = new StoredFunctionCall();
functionCall.setProcedureName("CHECK_VALID_EMPLOYEE");
functionCall.addNamedArgument("EMP_ID");
functionCall.setResult("FUNCTION_RESULT", String);
ValueReadQuery query = new ValueReadQuery();
query.setCall(functionCall);
query.addArgument("EMP_ID");
Vector args = new Vector();
args.addElement(new Integer(44));
String valid = (String) session.executeQuery(query, args);
