Understanding EclipseLink, 2.7
  Go To Table Of Contents

About SQL Query Language

Using EclipseLink, you can express a query using the following query languages:

SQL is the most common query language for applications that use a relational database data source. In most cases, you can compose a query directly in a given query language or, preferably, you can construct a DatabaseQuery with an appropriate Call and specify selection criteria using an Expression object. Although composing a query directly in SQL appears to be the simplest approach (and for simple operations or operations on unmapped data, it is), using the DatabaseQuery approach offers the compelling advantage of confining your query to your domain object model and avoiding dependence on data source schema implementation details.

Oracle recommends that you compose your queries using Expression.

You can execute custom SQL directly using Session methods executeSelectingCall and executeNonSelectingCall, or you can construct a DatabaseQuery with an appropriate Call.

EclipseLink provides a variety of SQL Call objects for use with stored procedures and, with Oracle Database, stored functions. EclipseLink also supports PLSQL call for Oracle stored procedures with PLSQL data types.

Stored Procedures

As described in the "Stored Procedures" section of the JPA specification (http://jcp.org/en/jsr/detail?id=338), native SQL allows you to use named stored procedures either dynamically or specified by the NamedStoredProcedureQuery annotation. If you use annotations, the stored procedure must exist in the database. The annotation allows you to specify the types of all parameters to the stored procedure, their corresponding parameter modes, and the mapping of the result sets.

Metadata must be provided for all parameters by using the StoredProcedureParameter annotation. Parameters must be specified in the order in which they occur in the parameter list of the stored procedure. If parameter names are used, the parameter name is used to bind the parameter value and to extract the output value (if the parameter is an INOUT or OUT parameter).

If the stored procedure is not defined using metadata, then parameter and result set information must be provided dynamically.

EclipseLink Extensions to Stored Procedures

EclipseLink defines annotation extensions that allow the use of PLSQL stored procedures (such as @NamedPLSQLStoredProcedureQuery) and stored functions (such as @NamedPLSQLStoredFunctionQuery). The PLSQL annotations allow you to use complex PLSQL types such as RECORD and TABLE, that are not accessible from JDBC. The annotations contain attributes for specifying the function (or procedure) name, the return value of the stored function, any query hints, the parameters for the stored function, and the name of the SQLResultMapping.

Parameters for the stored function (or procedure) are specified with the @PLSQLParameter annotation. The @PLSQLRecord annotation defines a database PLSQL RECORD type for use within PLSQL procedures.

EclipseLink also defines annotation extensions that allow the use of non-PLSQL stored procedures (such as @NamedStoredProcedureQuery) and stored functions (such as @NamedStoredFunctionQuery).

For a list of the EclipseLink extensions for stored procedures and links to their descriptions, see "Stored Procedure and Function Annotations" in Java Persistence API (JPA) Extensions Reference for EclipseLink.