Oracle Application Server TopLink is an advanced
object-to-relational persistence. It helps to build high performance
applications that store persistent data in a relational database. OracleAS
TopLink supports execution of Custom SQL queries and stored procedures
on the database to access raw data.
Prerequisites
Before we get started, the following are the
assumptions that are made in this document:
Minimum knowledge about SQL and OracleAS TopLink.
Must have HR schema installed on the database to
run the sample
OracleAS TopLink allows to execute custom SQL
queries. These queries are the RAW SQL queries or the stored procedure
calls on the database. This can be done in the following ways :
i) Using the executeSelectingCall() and executeNonSelectingCall()
session methods.
executeSelectingCall() method is used for
queries which return rows of data. executeNonSelectingCall()
method is used for DML queries like insert, update and delete. The following
code snippet shows the usage of these two methods.
// Executing Raw SQL using session.executeSelectingCall() Call call = new SQLCall("SELECT * FROM employees WHERE first_name LIKE 'R%'"); Vector results = (Vector) session.executeSelectingCall(call);
System.out.println(pad("",4) + "List of employees whose names starts with 'R'"); printResults(results);
session.beginTransaction(); System.out.println("Using session.executeNonSelectingCall() to update data");
// Executing Raw Non Selecting SQL using session.executeNonSelectingCall() Call updateCall = new SQLCall( "UPDATE employees SET salary = salary + 0.50 WHERE first_name LIKE 'R%'"); int rowsAffected = session.executeNonSelectingCall(updateCall); System.out.println(pad("",4) + "Salary updated for Employees whose names starts with 'R'."); System.out.println(pad("",4) + "Number of rows affected : " +rowsAffected);
ii) Using executeQuery() method
Alternatively, Custom SQL can be set on the query object
using the setSQLString() method. session.executeQuery()
method can be used to execute custom SQL queries. The following code
snippet shows the
DataReadQuery query = new DataReadQuery(); query.setSQLString("SELECT * FROM employees WHERE first_name LIKE 'R%'"); results = (Vector) session.executeQuery(query);
System.out.println(pad("",4) + "List of employees whose names starts with 'R'"); printResults(results);
Bookmark
