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 parameterized SQL, which improves
performance by reusing the same prepared statement for multiple
executions.
Prerequisites
Before we get started, the following are the
assumptions that will be made in this document:
Minimum knowledge about JDBC, prepared statements,
OracleAS TopLink.
Must have HR schema installed on the database to run
the sample
HowTo execute parameterized
SQL using OracleAS TopLink
Parameterized SQL enables to create and
store queries that are complete except for one or more bind parameters.
These parameters are bound, while executing the query. Parameterized
SQL avoids the prepare component of SQL execution and thus improves the
performance of frequently executed SQL statements.
By default, OracleAS TopLink prints data
inlined into its generated SQL and does not use parameterized SQL,
because many JDBC drivers do not fully support parameter binding, and
have size or type limits. Execution of Parameterized SQL can be enabled
either for the whole session or for the individual query.
i) To enable parameterized SQL for the whole
session
Set the should-bind-all-parameters
and should-cache-all-statements in sessions.xml
Alternatively, this can also be done using the API
shown like below :
// Get the session from session.xml named 'ParameterizedSQLSession' DatabaseSession session = (DatabaseSession)SessionManager.getManager().getSession("ParameterizedSQLSession"); session.getProject().getLogin().bindAllParameters(); session.getProject().getLogin().cacheAllStatements();
ii) To enable parameterized SQL for individual
query
Using setShouldBindAllParameters() and
setShouldCacheStatement() methods on individual queries, enables
parameterized SQL execution. This causes OracleAS TopLink to use a
prepared statement, binding all SQL parameters and caching the prepared
statement. When the query is re-executed, the SQL preparation is
avoided, which improves performance.
... // Create expression builder. ExpressionBuilder builder = new ExpressionBuilder();
// Create parameterized expression, // 'salary' is the parameter to be bound while executing this query.
Expression exp = (builder.get("salary").greaterThan(builder.getParameter("salary"))); // Create parameterized query based on the above expression
ReadAllQuery query = new ReadAllQuery(); query.setReferenceClass(Employees.class); query.setSelectionCriteria(exp); query.addArgument("salary");
// enable parameter binding and prepared statement caching query.setShouldBindAllParameters(true); query.setShouldCacheStatement(true);
// create vector of values to bound to the query Vector value = new Vector(); value.addElement("15000"); // execute the parameterized query Vector results = (Vector) session.executeQuery(query,value); ...
Executing Parameterized RAW SQL
OralceAS TopLink supports bind parameters in RAW
SQL. Bind parameters are specified using the hash character, '#'. The
following code snippet shows how to execute parameterized RAW SQL.
... DataReadQuery dq = new DataReadQuery(); dq.setSQLString("SELECT employee_id, first_name, salary FROM employees WHERE salary > #given_salary "); dq.addArgument("given_salary"); dq.bindAllParameters();
// create vector of values to bound to the query Vector value = new Vector(); value.addElement("15000");
Bookmark
