The solution in this section is organized according to technology. The organization allows developers to easily understand the different parts of the solution and choose specific parts to implement.
This section includes the following topics:
Oracle Database platform support is provided in the org.eclipse.persistence.platform.database.OraclePlatform
class, the org.eclipse.persistence.platform.database.oracle*
packages, and the org.eclipse.persistence.mappings.xdb
package for Oracle XML Database support. For details on the API, see Java API Reference for EclipseLink. For details on specific Oracle SQL types, see Oracle Database JDBC Java API Reference.
The following support is provided for the Oracle Database:
Batch writing with optimistic locking
Native SQL for byte[]
, Date
, Time
, Timestamp
and Calendar
Support for BLOB
and CLOB
database types using Oracle JDBC specific LOBLocator
for large values
Note: For non-Oracle thin JDBC drivers or applications environments where the thin driver is wrapped, it is possible to turn off |
Native support for outer join syntax (+) =
Native Sequencing (SELECT SEQ_NAME.NEXTVAL FROM DUAL
)
Native SQL/ROWNUM support for MaxRows
and FirstResult
filtering.
Hierarchical selects (connect by prior)
Returning clause
Custom expression functions (REGEXP_LIKE
, LOCATE
, ATAN2
, LOG
, CONCAT
, SYSDATE
(Date, Time, Today), EXCEPT
)
PLSQL data types, stored functions, stored procedure syntax for invoking and parameter passing, output parameters and output cursors. See Using Oracle PL/SQL With EclipseLink.
Timestamp query for use in optimistic locking using SYSDATE
and SYSTIMESTAMP
Multi-byte support of NCHAR
, NSTRING
, and NCLOB
Support of TIMESTAMP
, TIMESTAMPTZ
, and TIMESTAMPLTZ
Oracle XML Database support of XMLType
field and custom XSQL functions (extract
, extractValue
, existsNode
, isFragment
, getStringVal
, and getNumberVal
)
XDK XML parser
Flashback Querying in Historical Sessions
Object-relational Mappings (ReferenceMapping, StructureMapping, NestedTableMapping, ArrayMapping, ObjectArrayMapping)
Oracle AQ
Oracle Real Application Clusters. See Using EclipseLink with Oracle RAC.
Virtual Private Database (VPD), including Oracle Label Security. Using Oracle Virtual Private Database.
Proxy Authentication. See Using Oracle Proxy Authentication.
EclipseLink includes APIs for use with Oracle PL/SQL. The APIs are located in the org.eclipse.persistence.platform.database.oracle.plsql
package and the org.eclipse.persistence.platform.database.oracle.annotations
package.
This Section contains the following topics:
Oracle PL/SQL stored functions can be used to return complex PL/SQL data-types such as RECORD
types and TABLE
types. PL/SQL types are not supported by Oracle JDBC, so these types must be translated to Oracle OBJECT
types and VARRAY
types. OBJECT
types are returned as java.sql.Struct
and VARRAY
as java.sql.Array
types in JDBC.
Executing PL/SQL stored functions or procedures requires defining mirror OBJECT
and VARRAY
types for the RECORD
and TABLE
types. OBJECT
types can be mapped to classes annotated with either @Entity
or @Embeddable
using the @Struct
annotation. Typically, classes annotated with @Embeddable
are used, unless the OBJECT
type defines an Id
and can be stored in a table. Nested OBJECT
and VARRAY
types are mapped using the @Structure
and @Array
annotations.
Use the PLSQLStoredFunctionCall
class or the @NamedPLSQLStoredFunctionQuery
annotation to call a stored function using PL/SQL types. The PLSQLStoredProcedureCall
class and the @NamedPLSQLStoredProcedureQuery
annotation also exist for stored procedures. Use the StoredFunctionCall
class, the @NamedStoredFunctionQuery
annotation, the StoredProcedureCall
class, and the @NamedStoredProcedureQuery
annotation for stored functions and procedure that do not return complex PL/SQL types.
To execute an Oracle PL/SQL stored function:
Task 1: Create an Oracle Stored Function That Returns a PL/SQL Record Type
Task 4: Execute a PL/SQL Stored Function Using JpaEntityManager
Task 5: Define a Stored Function Using @NamedPLSQLStoredFunctionQuery
CREATE OR REPLACE PACKAGE EMP_PKG AS TYPE EMP_REC IS RECORD (F_NAME VARCHAR2(30), L_NAME VARCHAR2(30), SALARY NUMBER(10,2)); FUNCTION GET_EMP RETURN EMP_REC; END EMP_PKG; CREATE OR REPLACE PACKAGE BODY EMP_PKG AS FUNCTION GET_EMP RETURN EMP_REC AS P_EMP EMP_REC; BEGIN P_EMP.F_NAME := 'Bob'; P_EMP.F_NAME := 'Smith'; P_EMP.SALARY := 30000; RETURN P_EMP; END; END EMP_PKG;
CREATE OR REPLACE TYPE EMP_TYPE AS OBJECT (F_NAME VARCHAR2(30), L_NAME VARCHAR2(30), SALARY NUMBER(10,2))
@Embeddable @Struct(name="EMP_TYPE", fields={"F_NAME", "L_NAME", "SALARY"}) public class Employee { @Column(name="F_NAME") private String firstName; @Column(name="L_NAME") private String lastName; @Column(name="SALARY") private BigDecimal salary; ... }
import javax.persistence.Query; import org.eclipse.persistence.platform.database.orcle.plsql. PLSQLStoredFunctionCall; import org.eclipse.persistence.queries.ReadAllQuery; DataReadQuery databaseQuery = new DataReadQuery(); databaseQuery.setResultType(DataReadQuery.VALUE); PLSQLrecord record = new PLSQLrecord(); record.setTypeName("EMP_PKG.EMP_REC"); record.setCompatibleType("EMP_TYPE"); record.setJavaType(Employee.class); record.addField("F_NAME", JDBCTypes.VARCHAR_TYPE, 30); record.addField("L_NAME", JDBCTypes.VARCHAR_TYPE, 30); record.addField("SALARY", JDBCTypes.NUMERIC_TYPE, 10, 2); PLSQLStoredFunctionCall call = new PLSQLStoredFunctionCall(record); call.setProcedureName("EMP_PKG.GET_EMP"); databaseQuery.setCall(call); Query query = ((JpaEntityManager)entityManager.getDelegate()). createQuery(databaseQuery); Employee result = (Employee)query.getSingleResult();
@NamedPLSQLStoredFunctionQuery(name="getEmployee", functionName="EMP_PKG.GET_EMP", returnParameter=@PLSQLParameter(name="RESULT", databaseType="EMP_PKG.EMP_REC")) @Embeddable @Struct(name="EMP_TYPE", fields={"F_NAME", "L_NAME", "SALARY"}) @PLSQLRecord(name="EMP_PKG.EMP_REC", compatibleType="EMP_TYPE", javaType=Employee.class,fields={@PLSQLParameter(name="F_NAME"), @PLSQLParameter(name="L_NAME"), @PLSQLParameter(name="SALARY", databaseType="NUMERIC_TYPE")}) public class Employee { ... }
Query query = entityManager.createNamedQuery("getEmployee"); Employee result = (Employee)query.getSingleResult();
The standard way of handling a stored procedure is to build an instance of the StoredProcedureCall
class. However, the arguments must be compatible with the JDBC specification. To handle Oracle PL/SQL arguments (for example, BOOLEAN
, PLS_INTEGER
, PL/SQL record, and so on), use the PLSQLStoredProcedureCall
class.
Note: the |
The following example demonstrates handling PL/SQL arguments using the PLSQLStoredProcedureCall
class. The example is based on the following target procedure:
PROCEDURE bool_in_test(x IN BOOLEAN)
Example of Using the PLSQLStoredProcedureCall Class
import java.util.List; import java.util.ArrayList; import org.eclipse.persistence.logging.SessionLog; import org.eclipse.persistence.platform.database.jdbc.JDBCTypes; import org.eclipse.persistence.platform.database.oracle.Oracle10Platform; import org.eclipse.persistence.platform.database.oracle.OraclePLSQLTypes; import org.eclipse.persistence.platform.database.oracle.PLSQLStoredProcedureCall; import org.eclipse.persistence.queries.DataModifyQuery; import org.eclipse.persistence.sessions.DatabaseLogin; import org.eclipse.persistence.sessions.DatabaseSession; import org.eclipse.persistence.sessions.Project; import org.eclipse.persistence.sessions.Session; public class TestClass { public static String DATABASE_USERNAME = "username"; public static String DATABASE_PASSWORD = "password"; public static String DATABASE_URL = "jdbc:oracle:thin:@localhost:1521:ORCL"; public static String DATABASE_DRIVER = "oracle.jdbc.driver.OracleDriver"; public static void main(String[] args) { Project project = new Project(); DatabaseLogin login = new DatabaseLogin(); login.setUserName(DATABASE_USERNAME); login.setPassword(DATABASE_PASSWORD); login.setConnectionString(DATABASE_URL); login.setDriverClassName(DATABASE_DRIVER); login.setDatasourcePlatform(new Oracle10Platform()); project.setDatasourceLogin(login); Session s = project.createDatabaseSession(); s.setLogLevel(SessionLog.FINE); ((DatabaseSession)s).login(); PLSQLStoredProcedureCall call = new PLSQLStoredProcedureCall(); call.setProcedureName("bool_in_test"); call.addNamedArgument("X", OraclePLSQLTypes.PLSQLBoolean); DataModifyQuery query = new DataModifyQuery(); query.addArgument("X"); query.setCall(call); List queryArgs = new ArrayList(); queryArgs.add(Integer.valueOf(1)); s.executeQuery(query, queryArgs); } }
The following log excerpt shows the target procedure being invoked from an anonymous PL/SQL block:
...[EclipseLink Info]: 2007.11.23 01:03:23.890--DatabaseSessionImpl(15674464)-- Thread(Thread[main,5,main])-- login successful [EclipseLink Fine]: 2007.11.23 01:03:23.968--DatabaseSessionImpl(15674464)-- Connection(5807702)--Thread(Thread[main,5,main])-- DECLARE X_TARGET BOOLEAN := SYS.SQLJUTL.INT2BOOL(:1); BEGIN bool_in_test(X=>X_TARGET); END; bind => [:1 => 1]
Note: Notice the conversion of the Integer to a PL/SQL |
A Stored Procedure may have a mix of regular and non JDBC arguments. Use the PLSQLStoredProcedureCall
class when at least one argument is a non JDBC type. In addition, some additional information may be required for the JDBC type (length, scale or precision) because the target procedure is invoked from an anonymous PL/SQL block. The example is based on the following target procedure:
PROCEDURE two_arg_test(x IN BOOLEAN, y IN VARCHAR)
Example of Mixing JDBC Arguments With NonJDBC Arguments
import org.eclipse.persistence.platform.database.jdbc.JDBCTypes; ... PLSQLStoredProcedureCall call = new PLSQLStoredProcedureCall(); call.setProcedureName("two_arg_test"); call.addNamedArgument("X", OraclePLSQLTypes.PLSQLBoolean); call.addNamedArgument("Y", JDBCTypes.VARCHAR_TYPE, 40); DataModifyQuery query = new DataModifyQuery(); query.addArgument("X"); query.addArgument("Y"); query.setCall(call); List queryArgs = new ArrayList(); queryArgs.add(Integer.valueOf(0)); queryArgs.add("test"); boolean worked = false; String msg = null; s.executeQuery(query, queryArgs);
The following log excerpt shows the target procedure being invoked from an anonymous PL/SQL block:
[EclipseLink Fine]: 2007.11.23 02:54:46.109--DatabaseSessionImpl(15674464)-- Connection(5807702)--Thread(Thread[main,5,main])-- DECLARE X_TARGET BOOLEAN := SYS.SQLJUTL.INT2BOOL(:1); Y_TARGET VARCHAR(40) := :2; BEGIN two_arg_test(X=>X_TARGET, Y=>Y_TARGET); END; bind => [:1 => 0, :2 => test]
The following example demonstrates a stored procedure that contain both IN
and OUT
arguments and is based on the following target procedure:
PROCEDURE two_arg_in_out(x OUT BINARY_INTEGER, y IN VARCHAR) AS BEGIN x := 33; END;
Example of Handling IN and OUT Arguments
import static org.eclipse.persistence.platform.database.oracle.OraclePLSQLTypes. BinaryInteger; ... PLSQLStoredProcedureCall call = new PLSQLStoredProcedureCall(); call.setProcedureName("two_arg_in_out"); call.addNamedOutputArgument("X", OraclePLSQLTypes.BinaryInteger); call.addNamedArgument("Y", JDBCTypes.VARCHAR_TYPE, 40); DataReadQuery query = new DataReadQuery(); query.setCall(call); query.addArgument("Y"); List queryArgs = new ArrayList(); queryArgs.add("testsdfsdfasdfsdfsdfsdfsdfsdfdfsdfsdffds"); boolean worked = false; String msg = null; List results = (List)s.executeQuery(query, queryArgs); DatabaseRecord record = (DatabaseRecord)results.get(0); BigDecimal x = (BigDecimal)record.get("X"); if (x.intValue() != 33) { System.out.println("wrong x value"); }
The following log excerpt shows the target procedure being invoked from an anonymous PL/SQL block:
[EclipseLink Fine]: 2007.11.23 03:15:25.234--DatabaseSessionImpl(15674464)-- Connection(5807702)--Thread(Thread[main,5,main])-- DECLARE Y_TARGET VARCHAR(40) := :1; X_TARGET BINARY_INTEGER; BEGIN two_arg_in_out(X=>X_TARGET, Y=>Y_TARGET); :2 := X_TARGET; END; bind => [:1 => testsdfsdfasdfsdfsdfsdfsdfsdfdfsdfsdffds, X => :2]
Note: The order in which arguments are bound at runtime must be altered. Anonymous PL/SQL blocks must process the ordinal markers ( |
Anonymous PL/SQL blocks cannot natively handle IN OUT
arguments. The arguments must be split into two parts: an IN-half and an OUT-half. The following example demonstrates a stored procedure that handles IN OUT arguments and is based on the following target procedure:
PROCEDURE two_args_inout(x VARCHAR, y IN OUT BOOLEAN) AS BEGIN y := FALSE; END;
Example of Handling IN OUT Arguments
... PLSQLStoredProcedureCall call = new PLSQLStoredProcedureCall(); call.setProcedureName("two_args_inout"); call.addNamedArgument("X", JDBCTypes.VARCHAR_TYPE, 20); call.addNamedInOutputArgument("Y", OraclePLSQLTypes.PLSQLBoolean); DataReadQuery query = new DataReadQuery(); query.addArgument("X"); query.addArgument("Y"); query.setCall(call); List queryArgs = new ArrayList(); queryArgs.add("test"); queryArgs.add(Integer.valueOf(1)); List results = (List)s.executeQuery(query, queryArgs); DatabaseRecord record = (DatabaseRecord)results.get(0); Integer bool2int = (Integer)record.get("Y"); if (bool2int.intValue() != 0) { System.out.println("wrong bool2int value"); }
The following log excerpt shows the target procedure being invoked from an anonymous PL/SQL block:
[EclipseLink Fine]: 2007.11.23 03:39:55.000--DatabaseSessionImpl(25921812)-- Connection(33078541)--Thread(Thread[main,5,main])-- DECLARE X_TARGET VARCHAR(20) := :1; Y_TARGET BOOLEAN := SYS.SQLJUTL.INT2BOOL(:2); BEGIN two_args_inout(X=>X_TARGET, Y=>Y_TARGET); :3 := SYS.SQLJUTL.BOOL2INT(Y_TARGET); END; bind => [:1 => test, :2 => 1, Y => :3]
Note: The |
EclipseLink supports Oracle Virtual Private Database (VPD). Oracle VPD is a server-enforced, fine-grained access control mechanism. Oracle VPD ties a security policy to a table by dynamically appending SQL statements with a predicate to limit data access at the row level. You can create your own security policies, or use Oracle's custom implementation called Oracle Label Security (OLS). For details about Oracle VPD, see Oracle Database Security Guide. For details about Oracle Label Security, see Oracle Label Security Administrator's Guide.
For details about using Oracle VPD with Multitenancy, see Using VPD Multi-Tenancy.
To use the Oracle Database VPD feature in an EclipseLink application, an isolated cache should be used. Any entity that maps to a table that uses Oracle VPD should have the descriptor configured as isolated. In addition, you typically use exclusive connections.
To support Oracle VPD, you must implement session event handlers that the are invoked during the persistence context's life cycle. The session event handler you must implement depends on whether or not you are using Oracle Database proxy authentication.
Oracle VPD with Oracle Database Proxy Authentication
By using Oracle Database proxy authentication, you can set up Oracle VPD support entirely in the database. That is, rather than session event handlers to execute SQL, the database performs the required setup in an after login trigger using the proxy session_user.
For details on using Oracle proxy authentication, see Using Oracle Proxy Authentication.
Oracle VPD Without Oracle Database Proxy Authentication
If you are not using Oracle Database proxy authentication, implement session event handlers for the following session events:
postAcquireExclusiveConnection
: used to perform Oracle VPD setup at the time a dedicated connection is allocated to an isolated session and before the isolated session user uses the connection to interact with the database.
preReleaseExclusiveConnection
: used to perform Oracle VPD cleanup at the time the isolated session is released and after the user is finished interacting with the database.
In the implementation of these handlers, you can obtain the required user credentials from the associated session's properties.
JPA and EclipseLink are typically used in a middle tier/server environment with a shared connection pool. A connection pool allows database connections to be shared to avoid the cost of reconnecting to the database. Typically, the user logs into the application but does not have their own database login as a shared login is used for the connection pool. The provides a mechanism to set a proxy user on an existing database connection. This allows for a shared connection pool to be used, but to also gives the database a user context.
Oracle proxy authentication is configured using the following persistence unit properties on an EntityManager
object:
"eclipselink.oracle.proxy-type" : oracle.jdbc.OracleConnection.PROXYTYPE_USER_NAME, PROXYTYPE_CERTIFICATE, PROXYTYPE_DISTINGUISHED_NAME
oracle.jdbc.OracleConnection.PROXY_USER_NAME :
user_name
oracle.jdbc.OracleConnection.PROXY_USER_PASSWORD :
password
oracle.jdbc.OracleConnection.PROXY_DISTINGUISHED_NAME
oracle.jdbc.OracleConnection.PROXY_CERTIFICATE
oracle.jdbc.OracleConnection.PROXY_ROLES
Note: This connection is only used for writing by default; reads still use the shared connection pool. To force reads to also use the connection, the |
To setup proxy authentication, create an EntityManager
object and set the persistence unit properties. Three examples are provided:
Task: Audit Only Writes
To configure proxy authentication when auditing only writes:
Map properties = new HashMap(); properties.put("eclipselink.oracle.proxy-type", oracle.jdbc.OracleConnection.PROXYTYPE_USER_NAME); properties.put(oracle.jdbc.OracleConnection.PROXY_USER_NAME, user); properties.put(oracle.jdbc.OracleConnection.PROXY_USER_PASSWORD, password); properties.put("eclipselink.jdbc.exclusive-connection.mode", "Transactional"); properties.put("eclipselink.jdbc.exclusive-connection.is-lazy", "true"); EntityManager em = factory.createEntityManager(properties);
Task: Audit Reads and Writes
To configure proxy authentication when auditing reads and writes:
Map properties = new HashMap(); properties.put("eclipselink.oracle.proxy-type", oracle.jdbc.OracleConnection.PROXYTYPE_USER_NAME); properties.put(oracle.jdbc.OracleConnection.PROXY_USER_NAME, user); properties.put(oracle.jdbc.OracleConnection.PROXY_USER_PASSWORD, password); properties.put("eclipselink.jdbc.exclusive-connection.mode", "Always"); properties.put("eclipselink.jdbc.exclusive-connection.is-lazy", "false"); EntityManager em = factory.createEntityManager(properties);
Task: Configure Proxy Authentication in Java EE Applications
If a JEE and JTA managed entity manager is used, specifying a proxy user and password can be more difficult, as the entity manager and JDBC connection is not under the applications control. The persistence unit properties can still be specified on an EntityManager
object as long as this is done before establishing a database connection.
If using JPA 2.0, the setProperty
API can be used:
em.setProperty("eclipselink.oracle.proxy-type", oracle.jdbc.OracleConnection.PROXYTYPE_USER_NAME); em.setProperty(oracle.jdbc.OracleConnection.PROXY_USER_NAME, user); em.setProperty(oracle.jdbc.OracleConnection.PROXY_USER_PASSWORD, password); em.setProperty("eclipselink.jdbc.exclusive-connection.mode", "Always"); em.setProperty("eclipselink.jdbc.exclusive-connection.is-lazy", "false");
Otherwise, the getDelegate
API can be used:
Map properties = new HashMap(); properties.put("eclipselink.oracle.proxy-type", oracle.jdbc.OracleConnection.PROXYTYPE_USER_NAME); properties.put(oracle.jdbc.OracleConnection.PROXY_USER_NAME, user); properties.put(oracle.jdbc.OracleConnection.PROXY_USER_PASSWORD, password); properties.put("eclipselink.jdbc.exclusive-connection.mode", "Always"); properties.put("eclipselink.jdbc.exclusive-connection.is-lazy", "false"); ((org.eclipse.persistence.internal.jpa.EntityManagerImpl)em.getDelegate()). setProperties(properties);
By default, EclipseLink maintains a shared (L2) object cache. This is fine for auditing, but if Oracle VPD or user based security is used to prevent the reading of certain tables/classes, then the cache may need to be disabled for these secure classes. To disable the shared cache, see "Disabling Entity Caching".
If the database user is used to check security for reads, then set the eclipselink.jdbc.exclusive-connection.mode
property to Isolated
to only use the user connection for reads for the classes whose shared cache has been disabled (isolated).
The Oracle Virtual Private Database (VPD) feature allows for row level security within the Oracle database. Typically, database security only allows access privileges to be assigned per table. Row level security allows different users to have access to different rows within each table.
The Oracle proxy authentication features in EclipseLink can be used to support Oracle VPD. The proxy user allows for the row level security to be checked. When using Oracle VPD, it is also important to disable shared caching for the secured objects as these objects should not be shared. To disable the shared cache, see "Disabling Entity Caching".
Oracle Real Application Clusters (RAC) extends the Oracle Database so that you can store, update, and efficiently retrieve data using multiple database instances on different servers at the same time. Oracle RAC provides the software that manages multiple servers and instances as a single group. Applications use Oracle RAC features to maximize connection performance and availability and to mitigate down-time due to connection problems. Applications have different availability and performance requirements and implement Oracle RAC features accordingly. For details on Oracle RAC, see the Oracle Real Application Clusters Administration and Deployment Guide.
The Oracle Database and the Oracle WebLogic Server both provide connection pool implementations that can create connections to a RAC database and take advantage of various Oracle RAC features. The features include Fast Connection Failover (FCF), Run-Time Connection Load Balancing (RCLB), and connection affinity. In WebLogic Server, applications create JDBC data sources (Multi Data Source or GridLink Data Source) to connect to a RAC-enabled database. Standalone applications use the Universal Connection Pool (UCP) JDBC connection pool API (ucp.jar
) to create data sources. Both connection pool implementations require the Oracle Notification Service library (ons.jar
). This library is the primary means by which the connection pools register for, and listen to, RAC events. For those new to these technologies, refer to the Oracle Universal Connection Pool for JDBC Developer's Guide and the Oracle Fusion Middleware Configuring and Managing JDBC Data Sources for Oracle WebLogic Server.
This sections assumes that you have an Oracle JDBC driver and Oracle RAC-enabled database. Make sure that the RAC-enabled database is operational and that you know the connection URL. In addition, download the database Oracle Client software that contains the ons.jar
file. The ucp.jar
file is included with the Oracle Database.
The tasks in this section are used to connect to a RAC-enabled database from a persistence application implemented in Oracle WebLogic Server.
Refer to Chapter 3, "Using EclipseLink with WebLogic Server," and Oracle Fusion Middleware Configuring and Managing JDBC Data Sources for Oracle WebLogic Server for details about configuring a data source in WebLogic Server for Oracle RAC.
Edit the persistence.xml
file and include the name of the data source within a persistence unit configuration. For example:
<persistence-unit name="OrderManagement"> <jta-data-source>jdbc/MyOrderDB</jta-data-source> ... </persistence-unit>
The tasks in this section are used to connect to a RAC database from a standalone persistence application. The tasks demonstrate how to use UCP data sources which are required for advanced RAC features.
A UCP data source is used to connect to a RAC database. The data source can specify advanced RAC configuration. For details on using advanced RAC features with UCP, see Oracle Universal Connection Pool for JDBC Developer's Guide. The following example creates a data source and enables FCF and configures ONS.
PoolDataSource datasource = PoolDataSourceFactory.getPoolDataSource(); datasource.setONSConfiguration(“nodes=host1:4200,host2:4200”); datasource.setFastConnectionFailoverEnabled(true); datasource.setConnectionFactoryClassName(“oracle.jdbc.pool.OracleDataSource”); datasource.setURL(“jdbc:oracle:thin:@DESCRIPTION= (LOAD_BALANCE=on) (ADDRESS=(PROTOCOL=TCP)(HOST=host1)(PORT=1521)) (ADDRESS=(PROTOCOL=TCP)(HOST=host2)(PORT=1521)) (ADDRESS=(PROTOCOL=TCP)(HOST=host3)(PORT=1521)) (ADDRESS=(PROTOCOL=TCP)(HOST=host4)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=service_name)))”);
Applications that do not require the advanced features provided by RAC and UCP can connect to a RAC-enabled database using the native connection pool in EclipseLink. In this case, edit the persistence.xml
file for you applications and add the RAC URL connection string for a persistence unit. For example:
<persistence xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence persistence_1_0.xsd" version="1.0"> <persistence-unit name="my-app" transaction-type="RESOURCE_LOCAL"> <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider> <exclude-unlisted-classes>false</exclude-unlisted-classes> <properties> <property name="javax.persistence.jdbc.driver" value="oracle.jdbc.OracleDriver"/> <property name="javax.persistence.jdbc.url" value="jdbc:oracle:thin@(DESCRIPTION= "+ "(LOAD_BALANCE=on)"+ "(ADDRESS=(PROTOCOL=TCP)(HOST=rac_node) (PORT=1521))"+ "(ADDRESS=(PROTOCOL=TCP)(HOST=racnode2) (PORT=1521))"+ "(CONNECT_DATA=(SERVICE_NAME=service_name))")"/> <property name="javax.persistence.jdbc.user" value="user_name"/> <property name="javax.persistence.jdbc.password" value="password"/> </properties> </persistence-unit> </persistence>
To use the persistence unit, instantiate an EntityManagerFactory
as follows:
Persistence.createEntityManagerFactory("my-app");
To use the UCP data source, instantiate an EntityManagerFactory
an pass in the data source as follows:
Map properties = new HashMap(); properties.add("javax.persistence.nonJtaDataSource", datasource); Persistence.createEntityManagerFactory(properties);
EclipseLink provides added support for querying Oracle Spatial and Graph data in the Oracle Database. Oracle Spacial and Graph is used to location-enable applications. It provides advanced features for spatial data and analysis and for physical, logical, network, and social and semantic graph applications. The spatial features provide a schema and functions that facilitate the storage, retrieval, update, and query of collections of spatial features in an Oracle database. For details about developing Oracle Spacial and Graph applications, see Oracle Spatial and Graph Developer's Guide. To use Oracle Spatial and Graph within WebLogic Server, see Chapter 3, "Task 7: Extend the Domain to Use Advanced Oracle Database Features,"
EclipseLink applications can construct expressions that use Oracle Spacial and Graph operators. See the org.eclipse.persistence.expressions.spatial
API for details. For Example:
ExpressionBuilder builder = new ExpressionBuilder(); Expression withinDistance = SpatialExpressions.withinDistance(myJGeometry1, myJGeometry2, "DISTANCE=10"); session.readAllObjects(GeometryHolder.class, withinDistance);
The above expression requires a oracle.spatial.geometry.JGeometry
object. Use the EclipseLink org.eclipse.persistence.platform.database.oracle.converters.JGeometryConverter
converter to convert the JGeometry
object as it is read and written from the Oracle database. The JGeometryConverter
object must be added to the Oracle Database platform either with the addStructConverter(StructConverter)
method or specified in the sessions.xml
file. The JGeometry
type must also be available on the classpath.
The following example demonstrates how to use the FUNCTION
JPA extension to perform Oracle Spatial queries. For details on the FUNCTION
extension, see Java Persistence API (JPA) Extensions Reference for Oracle TopLink:
SELECT a FROM Asset a, Geography geo WHERE geo.id = :id AND a.id IN :id_list AND FUNCTION('ST_INTERSECTS', a.geometry, geo.geometry) = 'TRUE' SELECT s FROM SimpleSpatial s WHERE FUNCTION('MDSYS.SDO_RELATE', s.jGeometry, :otherGeometry, :params) = 'TRUE' ORDER BY s.id ASC