Monday, November 19, 2012

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.

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.

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.

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
.setParameter("name", "Finance")
.setHint(QueryHints.LEFT_FETCH, "Departments.employeesList")

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.

Tuesday, October 30, 2012

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.

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 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.

Sunday, October 28, 2012

Using JSON-REST in ADF Mobile

In the current version of ADF Mobile the ADF DataControls ( URL and WS ) only supports SOAP and JSON-XML. But this does not mean we cannot use JSON. To handle JSON we can use the  RestServiceAdapter and JSONBeanSerializationHelper classes. The RestServiceAdapter will handle the Rest Service and JSONBeanSerializationHelper helps us converting JSON to Java.

I made a little ADF Mobile demo based on the Google Maps Geocoder and use this url to test it,+Mountain+View,+CA&sensor=true

with this as result

We start by creating an Url Connection with as value

Next is a new Class which can be used a managed bean or as a Java DataControl.

Here we do the following steps.

Create the RestServiceAdapter

RestServiceAdapter restServiceAdapter = Model.createRestServiceAdapter();

Use our Url Connection

HTTP Get operartion

Append the url with our search parameters

Send and wait for the result.
response = restServiceAdapter.send("");

Next step is using the JSON deserialization, here we will use the JSONBeanSerializationHelper class.
ServiceResult responseObject = (ServiceResult)jsonHelper.fromJSON(ServiceResult.class, response); 

ServiceResult class will be used as output, too bad I can't use generics or annotations to control the JSON deserialization. So I will use JSONArray in case of 1 or more results.

import oracle.adfmf.json.JSONArray;
public class ServiceResult {
   private String status;
   private JSONArray results;

JSONBeanSerializationHelper will look for attributes called .type and if that contains a class name then it will use that class for deserialization but I can't change the Google Maps service.

So I made my own Helper class which converts all JSONArray or JSONObject to the right class and attributes.

geoResult = GeocoderHelper.transformObject(responseObject).getResults();

Here is my the helper class

Now we are ready to use it in a managed bean or generate an ADF DataControl on it.
with this as result.

You can find my demo code on Github

Here the pictures of the application in the IPad emulator.

Sunday, October 7, 2012

Build and Deploy OSB projects with Maven

2 years ago I already did the same with ANT and now I migrated these scripts to Maven. These Maven poms can still do the same like my ANT scripts.
  • Build and deploy an OSB OEPE workplace
  • Build one OSB project.
  • Export OSB projects from an OSB server and generate a customization plan.
Here you can find my code or the PS6 version
or with the new PS6 configjar tool which can create offline OSB exports without OEPE 

Also the readme contains some examples how to this from Java without the help of Maven or Ant.

I based my scripts on the following software and folders

My Environment Oracle OSB PS5 or with Maven 3.0.4

JVM                         = JDK 1.7_07 x64
Middleware home      = /opt/oracle/wls/wls11g
OSB & Oracle home = /opt/oracle/wls/wls11g/Oracle_OSB1
WebLogic home       = /opt/oracle/wls/wls11g/wlserver_10.3
Oepe home               = /opt/oracle/wls/wls11g/oepe11.1.1.8

My Maven settings.xml

run . to set all the Maven, Java variables.

To build a project or the whole OEPE workspace use this
mvn package

To deploy or export an existing OSB server use this target-env=dev so it uses the right Maven profile for the WebLogic Settings
mvn deploy -Dtarget-env=dev

Prepare a release
mvn release:prepare

Perform a release
mvn release:perform -Dtarget-env=dev

the pom.xml in the Maven_osb_ps5 folder build the whole source folder workspace, this generates a jar in the export folder with the same name as your pom definition.

the pom.xml in the Maven_osb_ps5/source/ReliableMessageWS and Maven_osb_ps5/source/XSDvalidation folder build only this project and generate a jar in the export folder with the same name as your pom definition.

the pom.xml in the Maven_osb_ps5/export folder export everything from the OSB server and puts the jar in the import folder.

Here is an example of a pom which build and deploys the whole OSB OEPE workspace.
This pom has the following plugins

  • exec-maven-plugin for building the OSB jar and deploy the jar to the OSB server, 
  • maven-assembly-plugin for adding the OSB jar as maven artifact 
  • maven-release-plugin for OSB releases. 

And here the assembly to add the generated OSB jar to Maven artifact

Thursday, August 9, 2012

Generating an EJB SDO Service Interface for Oracle SOA Suite

In Oracle SOA Suite you can use the EJB adapter as a reference or service in your composite applications. The EJB adapter has a flexible binding integration, there are 3 ways for integrating the remote interface with your composite.

First you have the java interface way which I described here this follows the JAX-WS way. It means you need to use Calendar for your Java date types and leads to one big WSDL when you add a wire to a service component.

Second way is EclipseLink Moxy this solution is great when you can't change your java interfaces. Antony Reynolds made a great blogpost how to use EclipseLink moxy in Oracle Soa Suite.

The last way is generating a Service Data Object (SDO) service interface on your EJB Session Bean, This leads to a WSDL with a operation XSD and entity XSDs which can be used / read by the EJB Adapter ( no one big WSDL ).  Use the java data types you want to use, JDeveloper can re-generate the SDO interface and no need to drag a dummy wire to refresh the EJB WSDL when you change the EJB Session bean.

In 2009 I already described this SDO feature but now with Patch Set 5 this SDO feature is finally working well. Before PS5 it was really hopeless.

So let's explain how this works in PS5. I already made a simple HR JPA Model project with a EJB Session Bean.

Before we start we need to think about lazy and eager object loading. In this Department entity I will eager retrieve the manager and employees of this department.

In the Employee entity I will use the Lazy fetch type on the department attribute.

Just like the java interface mapping way we can't have circular references between department and employees XSD.  We need to break the loop so add @XmlTransient to the getter of the department attribute of the employee entity.  ( now the department element will be skipped in the employee XSD )

Select your EJB Session Bean ( Right Click ) and select Create Service Interface.

When we want to use this SDO interface in Oracle SOA Suite we need to enable the Configure as an ADF-BC Service. This will add the necessary SDO annotations on the Remote Interface.

With this as result

The generated Department entity XSD.

The EJB SDO Session Bean WDSL

JDeveloper adds PortableWebService annotation to the EJB Session bean.

The remote interface with all the web annotations on a SDO method.

Also when you want the same behaviour as a Database adapter you can force to start a new transaction and not take part in the BPEL transaction. This way you can see if the transaction is successful at the end of the invoke instead of the end of BPEL.

JDeveloper also adds code to the EJB Session Bean to marshall and unmarshall the java or xml.

Think about which attributes can be empty else you will get some null pointer exceptions.

Also it will load all the SDO XSDs in the EJB Session Bean. If you don't have circular XSD references  then loading should be successful.

In a java client we can test the SDO interface ( this will not use the remote interface annotations) by first loading the XSDs and invoking a SDO session bean method. Loading of the XSDs is necessary else   you will get some EclipseLink objects instead of the department of employee objects.

Deploy the EJB to the SOA Suite Server and also generate an interface jar which does not contain the meta-inf folder  and the Bean.

Now is the time to configure the EJB adapter in Oracle SOA Suite. Drag the EJB Adapter to the reference side of the composite.

Lookup the jar and the WSDL. The wizard will copy the interface jar and WSDL, XSDs to your project.

Fix the jndiName bug in the reference service, go to source mode of the composite.xml , rename the uri attribute to jndiName.

Finish your composite and deploy it. When you go for a test drive you will see something like this.

Here you can download my example project.

Thursday, August 2, 2012

Return a fault from an Asynchronous Web Service

In an asynchronous web service we can't return a soap fault like a synchronous service but that does not mean you can't report back the fault to the calling asynchronous process.

basically you got three options.

  • Off course handle the fault in the error hospital and give back the response. 
  • In the response message you can add a section ( a XSD choice with success and fault section) which can be used for faults.
  • Do a callback to a common fault operation of the calling process. This can be a JMS message with the messageId/correlationId or a Web Service invocation where we use the correlation of BPEL to match the fault message with the right process. 

In this blogpost I will show how to implement the last two options in Oracle BPEL.

First we start with returning the fault in a response message.

For this I added a status element to the response and a choice for a success and a fault .

In the catch section you can also invoke the callback response and in the Assign activity you can fill the fault section of the response message.

Do a fault callback to the calling process,  for this we need to do the following.

Create an Asynchronous BPEL process.

Define a fault response schema. The EmployeeId element is necessary, we need this for the correlation of the BPEL processes.

Then make a new WSDL with the fault callback operation and add a fake endpoint. ( we will override this endpoint from BPEL, because we don't know the endpoint of the calling process).

Add a FaultCallback reference web service based on this WSDL and add a wire to the BPEL process.

In the BPEL receive activity, we need to capture the fault callback url. We can optional use this for the fault response endpoint.  Go the properties tab and capture the value of the faultToAddress property. Put the value in the faultCallBackUrl variable.

In the Catch section of the BPEL process we can invoke the fault reference service. Off course use an assign to add the EmployeeId and provide a fault message.

Open the fault invoke activity and go to the properties tab.

We need to set the endpointURI property with the faultCallBackUrl variable value.

That is all for the Asynchronous Service.

Next step is to use this Asynchronous Fault Service in an other asynchronous BPEL process.

We need to make a WSDL ( contract first ) with a portType which also contains the fault operation, this must match with the reference WSDL of the Asynchronous Fault Process. So operationName, Message and namespace must match.

Create a new BPEL process which is based on this WSDL ( expose this BPEL as a service ) and also add wire the Asynchronous Fault process.

This is the process with InvokeEmployee activity which calls the Asynchronous Service.

Open this invoke activity and go to the properties tab where we set the faultToAddress property. This is the endpoint of this BPEL process and will be used for the fault callback.

After the invoke we need to use a pick activity and add an extra onMessage to the pick activity.
First OnMessage will be used for the normal callback situation.

The second will be used for the fault Response.

The last step is to setup correlation in this BPEL for the returning fault message.

on the receive add a correlation set and initiate this ( I got a NPE when I tried to set it on the invoke ). The Set is based on the employeeId element.

Add two Property Aliases , first is based on the employeeId element of the Fault Message and second is the employeeId of the request message.

On the fault OnMessage we need to add the same correlation set and this time we don't need to initiate it.

If everything is Ok we will see the following in the Enterprise Manager.

on the invoke we set the faultToAddress, the normal response OnMessage is cancelled and the fault OnMessage gets a response with the fault message.

On github you can download this example