Configure Coherence HotCache

Coherence can really accelerate and improve your application because it's fast, high available, easy to setup and it's scalable. But when you even use it together with the JCache framework of Java 8 or the new Coherence Adapter in Oracle SOA Suite and OSB 12c it will even be more easier to use Coherence as your main HA Cache. 

Before Coherence 12.1.2 when you want to use Coherence together with JPA for the database connectivity, you must make sure that there is no batch job or application doing modifications directly in the database. This will lead to an out of sync Coherence Cache. But with Coherence 12.1.2 together with GoldenGate you can capture these database changes and send updates to the Coherence Cache. This is called Coherence HotCache.

Here you can see how it basically works.

And how it works in GoldenGate.  First GoldenGate will capture all the database changes and a datapump process will send the trails to a remote GoldenGate for Java client which will update the Coherence Cache.

In this blogpost you can follow all the steps to setup your own Coherence HotCache Cluster

I also made Vagrant / VirtualBox environment which uses Puppet to create a WebLogic 12.1.2 cluster together with GoldenGate for Java 11.2.1 and also an Oracle 11.2.0.4 Database with GoldenGate 12.1.2. You only need to download your licensed software and start it up.

In this example the Coherence HotCache uses the Oracle Database HR demo schema and I also made with OEPE 12.1.2 a Coherence JPA application which we will deploy to the Dynamic WebLogic 12.1.2 Coherence Cluster.

First we need to have a working database with a WebLogic Cluster. After this you can configure GoldenGate 12.1.2 on the database server and GoldenGate Java Adapters version 11.2.1 ( V38714-01.zip downloaded from EDelivery ) on the WebLogic Admin Server machine.

Database Configuration

Next step is to configure GoldenGate on the database server.

We will create a new goldengate admin user, enable database archiving and unlock HR schema user

Make a new archive folder for the TEST database

su - oracle
mkdir -p /oracle/archives/test

Allow the oracle user to generate spool files in the GoldenGate home

su - ggate
chmod 775 /oracle/product/12.1.2/ggate

Log in as oracle and go the goldengate home

su - oracle

export ORAENV_ASK=NO;
export ORACLE_SID=test;
. oraenv

cd /oracle/product/12.1.2/ggate/

sqlplus /nolog
connect / as sysdba

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

alter system set log_archive_dest_1='LOCATION=/oracle/archives/test' scope=both;
alter system set ENABLE_GOLDENGATE_REPLICATION=true scope=both; 
alter system set undo_retention=86400 scope=both; 

the ENABLE_GOLDENGATE_REPLICATION parameter is only for Oracle Database 11.2.0.4 or higher, this allows me to use ADD SCHEMATRANDATA in GoldenGate.

Unlock the HR demo schema

alter user hr account unlock;
alter user hr identified by hr;

Add supplemental log data

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE FORCE LOGGING;
ALTER SYSTEM SWITCH LOGFILE;

Check if everything is Ok for GoldenGate

SELECT supplemental_log_data_min, force_logging FROM v$database;

Create the goldengate admin user and grant him the necessary rights

create tablespace ggate
  logging
  datafile '/oracle/oradata/test/ggate01.dbf' 
  size 32m 
  autoextend on 
  next 32m maxsize 2048m
  extent management local;

CREATE USER GGATE_ADMIN identified by GGATE_ADMIN
DEFAULT TABLESPACE ggate
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON GGATE;

GRANT CREATE SESSION, ALTER SESSION to GGATE_ADMIN;
GRANT ALTER SYSTEM TO GGATE_ADMIN;
GRANT CONNECT, RESOURCE to GGATE_ADMIN;
GRANT SELECT ANY DICTIONARY to GGATE_ADMIN;
GRANT FLASHBACK ANY TABLE to GGATE_ADMIN;
GRANT SELECT ON DBA_CLUSTERS TO GGATE_ADMIN;
GRANT EXECUTE ON DBMS_FLASHBACK TO GGATE_ADMIN;
GRANT SELECT ANY TRANSACTION To GGATE_ADMIN;
GRANT SELECT ON SYS.V_$DATABASE TO GGATE_ADMIN;
GRANT FLASHBACK ANY TABLE TO GGATE_ADMIN;
EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('GGATE_ADMIN');
/

Add GoldenGate repository tables and enable the capture of the DDL changes.
Give GGATE_ADMIN as input to the following GoldenGate scripts ( marker_setup, ddl_setup & role_setup )

@marker_setup.sql
@ddl_setup.sql
@role_setup.sql
GRANT GGS_GGSUSER_ROLE TO GGATE_ADMIN;
@ddl_enable

Grant select on all the HR tables to the GoldenGate schema user

GRANT SELECT ON HR.REGIONS to GGATE_ADMIN;
GRANT SELECT ON HR.DEPARTMENTS to GGATE_ADMIN;
GRANT SELECT ON HR.JOBS to GGATE_ADMIN;
GRANT SELECT ON HR.EMPLOYEES to GGATE_ADMIN;
GRANT SELECT ON HR.JOB_HISTORY to GGATE_ADMIN;
GRANT SELECT ON HR.COUNTRIES to GGATE_ADMIN;
GRANT SELECT ON HR.LOCATIONS to GGATE_ADMIN;

This finishes our database part

Coherence Application

When the WebLogic Dynamic Cluster is active we can deploy the HR Coherence demo application to the WebLogic Cluster and test if it works.

In my case the cluster nodes are running on the following IP addresses 10.10.10.100 and 10.10.10.200 and I have a Employee and Department Cache which are connected with JPA to the employees and departments tables of the HR demo schema.

You can find the Eclipse workspace project here. This workspace contains the following projects

• HrClient, The client test project which uses wls12_remote_cache.xml to connect to the coherence cluster nodes. 
• HrModel, JPA project with the entities and the HrModel persistence unit. 
• HrHotCache, which is the Coherence project and will be added as a Grid Archive (GAR)
• HrHotCacheWeb, a dummy Web Application project.
• HrHotCacheEAR, Generates an Ear which the GAR, WAR and JPA jar 

The persistence.xml in the HrModel project has a resource entry called HrModel which uses test.oracle.com as service name and connects to 10.10.10.5 ( location of the Database server )

To test Coherence we need to have a Coherence client file which defines the cache entries and all the addresses of the Coherence nodes. We will also use this Coherence client file in GoldenGate

  
    
      Employee
      CustomRemoteCacheScheme
    
    
      Department
      CustomRemoteCacheScheme
    
  

  
    
      CustomRemoteCacheScheme 
   CustomExtendTcpCacheService
      
        10s
        
          
            
              

10.10.10.200
9099
            
            
               

10.10.10.100
9099
            
          

        
        
           5s
           500ms
           5s
        
      
    

  

a Java class to retrieve department with 10 as id.

package test;

import com.tangosol.net.CacheFactory;
import com.tangosol.net.NamedCache;

public class Department {

 public static void main(String[] args) {
        NamedCache department = CacheFactory.getCache("Department");
        model.Department dept =  (model.Department) department.get(10L);
        System.out.println("Department: "+dept.getDepartmentName());
 }
}

Run the Department class with an Oracle Coherence run profile ( use this coherence client file as input) and test if it works.

GoldenGate for Java configuration

Next step is to configure GoldenGate 11.2.1 for Java on the WebLogic 12.1.2 AdminServer.

Log in as the goldengate user, change directory to the goldengate home

su - ggate
cd /opt/oracle/ggate_java

export JAVA_HOME=/usr/java/jdk1.7.0_45
export PATH=${JAVA_HOME}/bin:${PATH}
export LD_LIBRARY_PATH=${JAVA_HOME}/jre/lib/amd64/server:${LD_LIBRARY_PATH}

For the GoldenGate java delivery we need to have a property file called hr-cgga.properties and add this to the dirprm folder of your GoldenGate home ( /opt/oracle/ggate_java/dirprm/hr-cgga.properties )

In the jvm.bootoptions I made the following changes:

• My WebLogic 12.1.2 Middleware home is /opt/oracle/middleware12c, you probably need to change this to your own middleware home
• The jar which contains the entities and the persistence.xml is and this is an export of the Eclipse HrModel project.
• Log4j property file, Here is a link to my Log4j properties file
• HrModel as the toplink.goldengate.persistence-unit value
• The Coherence client file, which is the same as we used to test the Coherence Cache, Here is a link to my coherence client file

# ==================================================================== 
# List of active event handlers. Handlers not in the list are ignored. 
# ==================================================================== 
gg.handlerlist=cgga
# ==================================================================== 
# Coherence cache updater
# ==================================================================== 
gg.handler.cgga.type=oracle.toplink.goldengate.CoherenceAdapter
# ==================================================================== 
# Native JNI library properties
# ==================================================================== 
goldengate.userexit.nochkpt=true
goldengate.userexit.writers=jvm
# ======================================
# Java boot options
# ====================================== 
jvm.bootoptions=-Djava.class.path=dirprm:ggjava/ggjava.jar:/vagrant/hr/bin/hrmodel.jar:/opt/oracle/middleware12c/oracle_common/modules/oracle.jdbc_11.2.0/ojdbc6.jar:/opt/oracle/middleware12c/coherence/lib/coherence.jar:/opt/oracle/middleware12c/oracle_common/modules/javax.persistence_2.0.0.0_2-0.jar:/opt/oracle/middleware12c/oracle_common/modules/oracle.toplink_12.1.2/eclipselink.jar:/opt/oracle/middleware12c/oracle_common/modules/oracle.toplink_12.1.2/toplink-grid.jar -Xmx32M -Xms32M -Dtoplink.goldengate.persistence-unit=HrModel -Dlog4j.configuration=/vagrant/hr/log4j-default.properties -Dtangosol.coherence.distributed.localstorage=false -Dtangosol.coherence.cacheconfig=/vagrant/hr/client-cache-config.xml -Dtangosol.coherence.ttl=0

Next we can configure GoldenGate on the WebLogic AdminServer, this GoldenGate will receive the trail from the GoldenGate client located at the Database Server.

./ggsci

CREATE SUBDIRS

Configure the manager.

status mgr
stop mgr


EDIT PARAMS MGR

Add the following content

PORT 16100
DYNAMICPORTLIST 16110-16120, 16130
AUTOSTART ER *
AUTORESTART ER *, RETRIES 4, WAITMINUTES 4

Start the manager

start mgr
status mgr

Configure the Java delivery process which will connect to the Coherence Cache

EDIT PARAMS HR-CGGA

Add the following content

EXTRACT HR-CGGA
SETENV ( GGS_USEREXIT_CONF     = "dirprm/hr-cgga.properties" )
SETENV ( GGS_JAVAUSEREXIT_CONF = "dirprm/hr-cgga.properties")
SOURCEDEFS dirdef/hr.def
CUserExit libggjava_ue.so CUSEREXIT PassThru IncludeUpdateBefores
GETUPDATEBEFORES
NoTcpSourceTimer
Table hr.*;

We will generate and copy the hr.def definition at a later time This process will listen to the jj trail which will be deliverd by the GoldenGate client on the database server

DELETE EXTRACT HR-CGGA
ADD EXTRACT HR-CGGA, EXTTRAILSOURCE dirdat/jj
exit

We will start the Java delivery process after the database GoldenGate configuration.

GoldenGate 12.1.2 configuration on the Database server

log in as ggate and change directory to the GoldenGate home

su - ggate
cd /oracle/product/12.1.2/ggate/

export ORAENV_ASK=NO;
export ORACLE_SID=test;
. oraenv

Initial GoldenGate configuration

./ggsci

ADD CREDENTIALSTORE
ALTER CREDENTIALSTORE ADD USER GGATE_ADMIN, PASSWORD GGATE_ADMIN, ALIAS gg1 
ADD MASTERKEY  gg1 
CREATE WALLET
OPEN WALLET
ADD MASTERKEY
INFO MASTERKEY ALL

exit

I did a silent install of GoldenGate 12.1.2 and this install will also start the manager plus create all the required GoldenGate directories

./ggsci
# mgr
status mgr
stop mgr

EDIT PARAMS MGR

Add the following content

PORT 16000
DYNAMICPORTLIST 16010-16020, 16030
AUTOSTART ER *
AUTORESTART ER *, RETRIES 4, WAITMINUTES 4
STARTUPVALIDATIONDELAY 5
USERIDALIAS gg1
PURGEOLDEXTRACTS dirdat/*, USECHECKPOINTS, MINKEEPHOURS 2

Start the manager

start mgr
status mgr

Next step is to configure a Classic Capture Extract

EDIT PARAMS HRTEST

Here I also do something extra like adding DDL capture and because the other GoldenGate client is not a 12.1.2 client I need to set the format release to 11.2

Add the following content

EXTRACT HRTEST
USERIDALIAS gg1
LOGALLSUPCOLS
DDL INCLUDE MAPPED
EXTTRAIL dirdat/st, FORMAT RELEASE 11.2
SEQUENCE hr.*;
TABLE hr.*;
BR BROFF
getUpdateBefores
TranLogOptions excludeUser hr

Add the extract and remove some old configuration or trails because I use an Oracle 11.2.0.4 Database and enabled the ENABLE_GOLDENGATE_REPLICATION init parameter so I can use ADD SCHEMATRANDATA hr.

START MGR
DBLOGIN USERIDALIAS gg1
STOP EXTRACT HRTEST
DELETE EXTRACT HRTEST
ADD SCHEMATRANDATA hr
ADD EXTRACT HRTEST, TRANLOG, BEGIN NOW
SHELL rm -f dirdat/st*
ADD EXTTRAIL dirdat/st, EXTRACT HRTEST
start HRTEST

You can check the output in dirrpt/HRTEST.rpt or do

./ggsci
info all

Now we can add a datapump which will send the trail to the GoldenGate Java adpater

EDIT PARAMS PJAVA

Here I will connect to adminwls.example.com and also need to set the expected format to 11.2

Add the following content

EXTRACT PJAVA
USERIDALIAS gg1
RMTHOST adminwls.example.com, MGRPORT 16100
RMTTRAIL dirdat/jj, FORMAT RELEASE 11.2
PASSTHRU
GETUPDATEBEFORES
TABLE hr.*;

Configure the datapump

DBLOGIN USERIDALIAS gg1
STOP EXTRACT PJAVA
DELETE EXTRACT PJAVA
ADD EXTRACT PJAVA, EXTTRAILSOURCE dirdat/st
ADD RMTTRAIL dirdat/jj, EXTRACT PJAVA, megabytes 100
START EXTRACT PJAVA

You can check the output in dirrpt/PJAVA.rpt or do

./ggsci
info all

Generate the HR defintions and copy this definition to the GoldenGate Java Adapter configuration

Make a new hrdefgen.prm file and add the following content

DEFSFILE ./dirdef/hr.def PURGE FORMAT RELEASE 11.2
USERIDALIAS gg1
TABLE hr.*;

Generate the HR table definitions

./defgen paramfile /vagrant/hrdefgen.prm

Copy /oracle/product/12.1.2/ggate/dirdef/hr.def to /opt/oracle/ggate_java/dirdef/hr.def ( located on the WebLogic AdminServer)

Start the Java delivery process

Go the GoldenGate Java Adapter home

Look at the output, it should load the dirdat/jj trail and connect to coherence cluster

su - ggate
cd /opt/oracle/ggate_java

export JAVA_HOME=/usr/java/jdk1.7.0_45
export PATH=${JAVA_HOME}/bin:${PATH}
export LD_LIBRARY_PATH=${JAVA_HOME}/jre/lib/amd64/server:${LD_LIBRARY_PATH}

./extract pf dirprm/hr-cgga.prm

Next time you can do it from ggsci and use start HR-CGGA

Test the HotCache configuration

Use the Coherence Test client to retrieve department 10 again.

Connect to the Oracle database and don't use the HR schema user ( very important, changes made by HR will be ignored cause coherence will also use this user to connect to the database). Update the department name of department 10.

Wait a few seconds and use the Coherence Test client again to retrieve department 10 and look if the department name has changed.

Publish the HR Database changes to a JMS Queue

As an extra we can also publish the HR database changes to a JMS Queue by creating a new property file called hr-oggq.properties and add this to /opt/oracle/ggate_java/dirprm

### oggq.properties ###
gg.handlerlist=oggjms
### Path to WebLogic jars ###
gg.classpath=/usr/java/jdk1.7.0_45/lib/tools.jar:/opt/oracle/middleware12c/wlserver/server/lib/weblogic_sp.jar:/opt/oracle/middleware12c/wlserver/server/lib/weblogic.jar:/opt/oracle/middleware12c/wlserver/server/lib/webservices.jar:
### JNDI properties
java.naming.provider.url=t3://adminwls.example.com:7001
java.naming.factory.initial=weblogic.jndi.WLInitialContextFactory
java.naming.security.principal=weblogic
java.naming.security.credentials=weblogic1
### JMS Handler
gg.handler=oggjms
gg.handler.oggjms.type=jms
gg.handler.oggjms.format=xml2
gg.handler.oggjms.format.mode=op
gg.handler.oggjms.destinationType=queue
gg.handler.oggjms.destination=HR-Queue
gg.handler.oggjms.connectionFactoryJndiName=HR-CF
### native library config ###
goldengate.userexit.nochkpt=TRUE
goldengate.userexit.timestamp=utc
goldengate.log.logname=cuserexit
goldengate.log.level=DEBUG
goldengate.log.tofile=TRUE
goldengate.userexit.writers=javawriter
javawriter.stats.display=TRUE
javawriter.stats.full=TRUE

Next we go back to ./ggsci and add this JMS extract and this will watch the same audit trail.

EDIT PARAMS HR-OGGQ

add the following content

EXTRACT HR-OGGQ
SOURCEDEFS dirdef/hr.def
CUserExit libggjava_ue.so CUSEREXIT PassThru IncludeUpdateBefores
GETUPDATEBEFORES
Table hr.*;

Add the extract

DELETE EXTRACT HR-OGGQ
ADD EXTRACT HR-OGGQ, EXTTRAILSOURCE dirdat/jj

Exit ggsci and start this JMS extract outside ggsci, so we can see all the log output

./extract pf dirprm/hr-oggq.prm

Coherence 12.1.2 Rest application build with OEPE

With WebLogic 12.1.2 Oracle also released a new version of Coherence and OEPE. The 12.1.2 release contains many new Coherence features like WebLogic Managed Coherence Servers and Coherence Grid Archive ( GAR ) which can be included in an normal EAR. Coherence also has some nice new REST features like direct & named queries,  Custom Query engines and new Security options.
Plus with OEPE you can develop Coherence applications in Eclipse and it has Coherence editors for all the Coherence configuration files.

In this blogpost we will test these tools and features in a demo application which uses the HR Oracle demo schema, JPA and expose these entities as Coherence REST Services.

We start by downloading OEPE Eclipse runtime bundle with WebLogic 12.1.2, Coherence and ADF http://www.oracle.com/technetwork/developer-tools/eclipse/downloads/index.html

Start OEPE and define a new workspace.

Create an Oracle Coherence Application

Provide a project name and make sure you define a target runtime.  Plus enable Add project to EAR.

Use the default Coherence options.

The Coherence application will also add a dynamic Web project.

JPA Project
For this demo I will use JPA so we can use these entities in Coherence.

Also target this to WebLogic 12.1.2 and add this project to the already existing EAR project.

Define an connection to the database this will also add a persistence unit to the JPA project.

Next choose JPA entities from tables, where I select the Departments and Employees tables for this demo

Change the JPA mapping relations between the Department and Employee entities and add for REST the XML annotations.
Like @XmlRootElement(name="Department")  and @XmlTransient on the getters to break the loop of loading the Department and Employee objects.

Coherence Project

Next step is to configure the Coherence.
We can use the OEPE Coherence Editors or go directly to the source tab.

First we change coherence-cache-config.xml file where we will define the Department and Employee cache and connect this to EclipseLink.

HrJPA is the name of the Persistence Unit ( Resource Local )

Create a new file called coherence-rest-config.xml, this contains our REST entity definitions where we add some coherence named queries and enable the direct query option.

<key-class>java.lang.Integer</key-class> must match with the primary Java Data type of the entity


The last file we need to change is the pof-config.xml and add <include>coherence-rest-pof-config.xml</include> to the user-type-list

Also upload the coherence-rest.jar to the lib folder of the CoherenceJPA project.

Web project
Last step is to enable the Web project for Coherence REST. We need to enable the Oracle Coherence Facet on this Web Project.

Remove all the Coherence files located in the src folder, we don't need this.

Add the following Coherence REST Servlet.

Also we need to add the following Jersey and Jackson jars files to the WEB-INF lib folder. ( Located  in the module folder of the oracle_common )

Also create your own servlet, so we can fill the Department and Employee Coherence cache ( else the cache will be empty )

In the Eclipse Servers tab we need to add an WebLogic Domain with a Managed Coherence Server ( maybe use right click to select an WebLogic target other than the default AdminServer.

For we Coherence REST we need to change the default EclipseLink JAXB provider.

Add these parameters to Server startup arguments to the Managed Server
-Dcom.sun.xml.ws.spi.db.BindingContextFactory=com.sun.xml.ws.db.glassfish.JAXBRIContextFactory 
-Djavax.xml.bind.JAXBContext=com.sun.xml.bind.v2.ContextFactory

Publish the EAR from OEPE which also contains the Grid Archive (GAR) to the Coherence Managed Server

Finally we can test the Rest service
Start by invoking the servlet http://wls12:7201/CoherenceJPAWeb/CoherenceServlet

Next we can use a Rest Client to test all the Rest operations.

Get all the department entries
http://wls12:7201/CoherenceJPAWeb/rest/Department

Get Deparment 100
http://wls12:7201/CoherenceJPAWeb/rest/Department/100

Add a new Department

 Delete a department

Direct query ( enabled in the coherence rest config xml )
http://wls12:7201/CoherenceJPAWeb/rest/Department?q=departmentName='Finance'

Location1700 Named query also defined in the coherence rest config xml

Location Named query with a integer parameter

Here you can download or look at the github demo project.

JDeveloper 12.1.2 EJB & Java Service Facade Datacontrol

With JDeveloper 12c (12.1.2)  Oracle added some great updates to the ADF EJB / Java Facade Datacontrol. Oracle already added the Java Facade and Named Criteria support in its previous versions of JDeveloper but now also added the so wanted List of Values feature, just like we have in ADF BC.  

In the the next part I will show you all the new features and some LOV bugs ( not everything is working yet )

When we create an EJB DataControl you get besides Range paging also Scrollable as an Access Mode option plus you can immediately generate all the ADF Entity Metadata. 

Just like JDeveloper 11gR2 we can add a Named Criteria to the ADF entity, basically it does the same as a Named Criteria in JPA.

Create a List of Values, select the base entity field ( Selecting the relation entity field won't work, so you need to copy the field and make one of them updatable and insertable ), select the list iterator and the field with matches the field of the base entity.  

Choose the default List Type and all the attributes we want to show in the list type.

We can now use this list type in a Query Panel where we can use it to lookup an manager.

But after selecting an employee then it goes totally wrong, it keeps on looping and tries to get a particular rownum. Also displaying this List item in an ADF Table goes wrong ( it just hangs).  I tried everything from removing all JPA entity relations, adding fetching hints to the JPA named criterias and changing the range size in ADF but all didn't help much.

But it at least it works in a Form page

With this as result

EJB Methods which contains parameters are now also generated as accessor iterators instead of method actions where you need to add method iterators on it. This is a big improvement and keeps the pagedef much cleaner

Accessor iterator

With Execute with params you can pass on the method parameter.

But somehow I did not get this working, I tried the following, first in a page with a parameter form and in bound task flow where I first invoke the execute with params action.


The second part is about the Java Service Facade.  In JDeveloper 12.1.2 the Java Service Facade contains less code and it can update the entity with or without auto commit and without manually calling the merge entity method.

The Persistence unit must be one with Resource Local as Transaction type. You can also choose if you want to do an autocommit.

Next step is generating an ADF Datacontrol on this Java Service Facade

Here we also can choose for Range paging or Scrollable as Access mode.

With a Java Service Facade and an explicit commit we can now also use the rollback or commit button. Plus we don't need to call the mergeEntity method when we change a record.
This is not the case with the EJB datacontrol.

Here you can download or see my example project

JPA SQL and Fetching tuning ( EclipseLink )

When you use JPA in your project and your model project contains many entities with some Eager fetching relation attributes then you probably notice that EclipseLink can fire a lot of SQL queries on the database. This works ok & fast on Dev or Test but in production these queries can lead to big problems ( higher load and more data).
In our project we also use ADF DataControls, Web Services or Entity Transient attributes which does not support Lazy loading. So we need to tune this entities fetching.

In this blogpost I will use the department and employee entity of the HR Oracle demo schema to explain the JPA options you have to control the SQL statements and the JPA relation Fetching.

These two entities has the following relations

• Department has a Manager
• Department has Employees
• Employee belongs to a Department
• Employee has a Manager
• Manager has Employees

First step is to set or leave all OneToMany relations to Lazy Fetching ( this is the default ). If you put everything on Eager then you will create an loop and EclipseLink will detect this and will throw an error.
If we only set the Department entity relations to Eager then the department named queries will be Ok but we can't retrieve everything from the Employee entity.   

But when you want to retrieve a Department with its Employees and Manager or a Manager with its Employees and Department plus you want to tune the fired SQL queries then you got the following options.

Here you see all the Department Named Queries.

Departments.findByName
select o from Departments o where o.departmentName = :name" 

this query will get all the Departments with a particular name and it retrieves all the ManyToOne relations like the department Manager. But this does not retrieve the Employees of the Department.

Departments.findByNameFetch
select o from Departments o left join fetch o.employeesList where o.departmentName = :name
If we want to fetch all the Employees of this department we can use join fetch. Fetch with join is necessary if you want to do Eager fetching. Also left join fetch ( outer join )  is necessary if you also want to query Departments without Employees. Plus this will do it in one SQL query.

Departments.findByNameFetch2
select o from Departments o where o.departmentName = :name
hints= { @QueryHint( name =QueryHints.LEFT_FETCH, value="Departments.employeesList") }
We can do the same with EclipseLink and use @QueryHint( name =QueryHints.LEFT_FETCH
This does the same as Departments.findByNameFetch , you can also use QueryHints.FETCH but this won't do an outer join.

You can also do the same on the Query, like this
em.createNamedQuery("Departments.findByName")
.setParameter("name", "Finance")
.setHint(QueryHints.LEFT_FETCH, "Departments.employeesList")
.getResultList();

Departments.findByNameFetch3
select o from Departments o where o.departmentName = :name
hints= { @QueryHint( name =QueryHints.LEFT_FETCH, value="Departments.employeesList.manager") }
EclipseLink can even do more than the standard JPA JQL query, in this example it can also retrieve the manager of the employees in the same SQL query instead of using separate queries ( this is not possible with normal JPA).

To see the all the executed SQL statements put the EclipseLink Logging to the Fine level.
This is the test client I used to test these queries
Here you can download or see all the code of my test project on github.

Easy way to access JPA with REST (JSON / XML)

With the release of EclipseLink 2.4, JPA persistence units can be accessed using REST with JSON or XML formatted messages. The 2.4 version supports JPA-RS which is a RESTful API for dealing with JPA. In this blogpost I will show you what is possible with JPA-RS, how easy it is and howto setup your own EclipseLink REST service. This is also possible when you want to expose database tables as SOAP Web Service, for more information on this topic see my blog about Eclipselink DBWS.

To test these REST Services I added the Dev HTTP Client extension to my Google Chrome browser.  Here you can download my testcases which I used in this blogpost and it is possible to import these in the Dev HTTP Client extension.

It is very important with every invocation to set the Accept HTTP Header property with application/json or application/xml as value, else you will get an error and EclipseLink won't know the requested response format.

We can start with HTTP GET and http://localhost:8080/HR-JPA-RS/persistence/ as url , This will show you all the JPA persistence units.  persistence is the mapping of the JPA-RS Servlet ( it's a web-fragment in the JPA-RS eclipselink jar ) and HR-JPA-RS is the context url of my GlassFish web application.

Now we know the Persistence Units we can ask for all the Entities of this particular HRLocal Persistence Unit. Use http://localhost:8080/HR-JPA-RS/persistence/HRLocal/metadata
This gives us the Department, Location and Employee entities of the Oracle HR Demo schema.

Next step is to retrieve the metadata of the Departments Entity. Here you can see all the entity attributes and relationships to the other entities, the supported Rest operations plus the Named Queries you can call.
http://localhost:8080/HR-JPA-RS/persistence/HRLocal/metadata/entity/Departments

We can call for example the findAll namedquery http://localhost:8080/HR-JPA-RS/persistence/HRLocal/query/Departments.findAll , a particular record http://localhost:8080/HR-JPA-RS/persistence/HRLocal/entity/Departments/190 or localhost:8080/HR-JPA-RS/persistence/HRLocal/query/Departments.findById;departmentId=200

To retrieve the manager of this department we can add manager to this url.  http://localhost:8080/HR-JPA-RS/persistence/HRLocal/entity/Departments/190/manager

When we want XML as response instead of JSON we can set the Accept HTTP Header to application/xml

Click here for more information what is possible with JPA-RS

Next step of this blogpost is about howto setup this eclipselink environment. For this I used OEPE ( Oracle Eclipse ) as IDE and GlassFish 3.12 as J2EE server. ( You can also use WebLogic 12c )

First we need to update the EclipseLink version of GlassFish to at least version 2.4.1. See this Oracle blogpost how to replace the current version of eclipselink . Also had to add org.eclipse.persistence.jpa.jpql.jar to this module folder.

This is my workspace overview. It contains a JPA and Web project and off course the EAR project.

In the web project I added the org.eclipse.persistence.jpars_2.4.xxx.jar to the lib folder of the WEB-INF.
The meta-inf folder inside this jar contains the web-fragment xml and this will automatically added to your web.xml ( if its support version 3.0 of the web-app )

The last step is to set the Persistence Units to Local Resource instead of JTA Datasource, else it won't work.

Here you can download the eclipse projects.