Oracle Spatial and TopLink 11g

This spring I was blogging about Oracle Spatial object type oracle.spatial.geometry.JGeometry support in TopLink Essentials JPA - Oracle Spatial and TopLink Essentials JPA. But time dont wait, and we already have TopLink 11g that comes with Oracle JDeveloper 11g Technical Preview 2. In TopLink 11g support for JGeometry object type is much simplified and can be used in more convenient way.

Developed sample application - SpatialTopLinkJPA11.zip, contains Model and ViewController projects. In Model part, TopLink 11g is used to build persistence layer. In ViewController part, User Interface is developed with ADF Faces Rich Client. In order to run developed sample application you need to have Oracle MapViewer demo dataset MVDEMO in your database. You can download dataset from OTN and install it into your database as separate schema.

So, what are the main changes comparing to my previous post. Main news - support for Oracle Spatial object type oracle.spatial.geometry.JGeometry mapping and querying is provided out of the box by TopLink 11g. However, its important to know that this support is available with TopLink libraries and not with TopLink Essentials. What you need to do is to:

1. Configure a StructConverter with annotation on any of your entities. This configuration is performed only once for all entities.

@StructConverter(name = "JGeometry", converter = "oracle.toplink.platform.database.oracle.converters.JGeometryConverter")

2. For any attribute that you want to map to a type converted by specified StructConverter, @Convert annotation must be used:

@Convert("JGeometry")
private JGeometry location;

You can find this configuration I have done in Cities entity:


And in fact, its everything related about what developer should care when converting oracle.sql.Struct to oracle.spatial.geometry.JGeometry. Simple, isn't it? :-)

Sample application contains the same logic as one developed previously, queryCitiesSpatially() method in Session bean receives 4 parameters from Client and invokes named query - Cities.sampleQuery:


Named query Cities.sampleQuery is defined in separate class - Cities_SessionCustomizer. This class implements SessionCustomizer and creates expression with SDO_RELATE spatial operator. Class code:


Class with named query should be declared as Session Customizer Class in persistence.xml:


In ViewController project I have used ADF Faces Rich Client that comes with JDeveloper 11g, really amazing thing. One important thing related to ViewController, when I developed everything and was trying to Run application, I got exception - oracle.spatial.geometry.JGeometry class not found. It was quite strange to me, since I have added this library in Model project. Solution was to put sdoapi.jar into this directory - jdevstudio1111\lib\java\shared\oracle.toplink.ojdbc\11.1.1.0.0.

Developed User Interface part looks pretty cool. You can enter values and get result for spatial operator in table below:


In Cities data table you can notice Detach button, this button allows to open table in separate window. However, its not pop-up functionality, its function provided by ADF Faces Rich Client. This function allows to have in the same window several screens:


I think, such simplified Oracle Spatial object type usage in Object-Relational mapping, can greatly improve Spatial integration into enterprise systems.

Presentation at Oracle Spatial SIG during Oracle OpenWorld

During Oracle OpenWorld in San Francisco I will participate in Oracle Spatial SIG meeting. My presentation title - Integrating Spatial into Enterprise with Standard Frameworks. It will be focused mostly on demonstrations of my blog content about JDeveloper, WebCenter, and MapViewer. I will show and explain how Oracle Spatial plugs into Oracle Fusion Middleware.

Meeting time: Sunday, November 11th at 10:00 am - 11:30 am
Meeting place: Moscone West, San Francisco, CA
Room: 2008
Session Code: S292934

Web Service Invocation in Oracle ADF

Some time ago I was blogging about how to access persistence layer through Web Service interface - Web Services and Oracle TopLink. I have mentioned, that I will extend developed sample and will show how it is possible to use Web Services in Oracle ADF View layer.

Frank Nimphius have posted this week how-to article on OTN - Using Web Services with Complex Return Types in ADF. In this article, Frank describes in detail how to include and use your Web Service in Oracle ADF application. I have decided to apply described steps and develop client application - WSTopLinkClient.zip for my previously developed sample Web Service - WSTopLink.zip.

In developed client application, data retrieved from Web Service, is shown in af:table component:


Data for all three columns is retrieved from Web Service, however labels for regionId attribute are provided from client application Model layer. In Oracle ADF, data from Web Service can be easily merged with data provided by Model layer.

When running sample application, don't forget to add adf-faces-impl.jar and jsf-impl.jar to application's WEB-INF\lib directory.

Web Services in Oracle JDeveloper 11g

I have received a comment for my previous post - Web Services and Oracle TopLink, about if to try similar sample on Oracle JDeveloper 11g Technical Preview release. Yes, it is interesting, just because you can compare and evaluate new powerful features that will bring JDeveloper 11g Production release. I have tested Web Services development support in 11g using developed sample application - WSTopLink11.zip, this sample implements annotation only Web Service. I have used the same business logic as for previous one.

For persistence layer I'm using JPA entity with two Named Queries - Countries.findAll and Countries.findByName. When creating EJB Session Bean, Web Service Endpoint Interface is declared in the same way as in JDeveloper 10.1.3:


Generated interface for EJB Session Bean contains @WebService annotation, that in fact allows to use EJB Session Bean as Web Service directly:


Developed application structure in Application navigator window:


Oracle JDeveloper 11g provides embedded tool, that allows to test developed Web Service without deploying it to the server - just right click on EJB Session Bean and select - Test Web Service:


HTTP Analyzer window will be opened, where you can choose available operations to test, provide values for parameters and browse through generated result. Four views are provided - SOAP structure, HTTP Content, Hex Content and Raw Message. In our case, queryCountriesFindAll() generates similar result:


And, for queryCountriesByName(parameter) only result that is correct for specified parameter is shown:

Web Services and Oracle TopLink

If you are Oracle TopLink developer and want to access persistence layer through Web Service interface, hopefully you will find an answer in this post. Developed sample - WSTopLink.zip, doesn't provide user interface, it will be tested on Oracle Application Server. I will post how to use Web Services in Oracle ADF Faces in my future posts. Current sample is based on standard HR schema and provides functionality of querying for all or one particular country. Below I will provide main aspects I have noticed, when developing access to TopLink persistence layer through Web Service.

1. You should create Oracle TopLink entity objects in the same way, like you create them always. But, when creating EJB Session Bean don't forget to include Web Service Endpoint interface.


2. When creating Java Web Service, don't check 'Autogenerate Service Endpoint Interface'. It's because endpoint interface is already generated and is represented by EJB Session Bean.


3. In the step 5 of 8 in Java Web Service dialog you can select methods accessible through Web Service. In developed sample I have enabled access only to two methods - findAllCountries and findCountryByName.


4. Finally, JDeveloper generates all needed files. Generated WSDL document should look similar in Design view:


5. Developed application structure in Application navigator window:


6. One important thing - WAR Deployment Profile Properties dialog. Deployment profile for Web Service is generated automatically, but Oracle TopLink configuration files aren't included into it by default. So, don't forget to include them.


7. Access to persistence layer through Web Service is done, it's time to test it. I have deployed developed application to Oracle Application Server and have tested using standard interface provided by AS.


8. Let's press 'Invoke' button - information about all countries available in database table will be returned.


9. I have defined two operations to be available. Second operation accepts parameter and provides information only for country specified in that parameter. For example, if parameter value is set to Albania and 'Invoke' button is pressed, information only for that country will be returned.


Result:

Oracle Spatial and TopLink Essentials JPA

It seems Oracle Spatial functionality can be used successfully with TopLink Essentials JPA also. I have reviewed in detail Doug Clarke (Principal Product Manager for Oracle TopLink) post about how it looks in theory - Oracle Spatial using TopLink Essentials JPA. And, have implemented described steps in practice. I have faced some issues, but final result is successful, I will describe below how I have created sample application for today post - SpatialTopLinkJPA.zip.

Sample application is based on standard schema - MVDEMO, schema contains tables with spatial columns. I'm using CITIES table, this table contains spatial column - LOCATION, column have MDSYS.SDO_GEOMETRY type assigned.

I have passed all three steps described in Doug blog without any problems. I have modified class for mapping customization - name for mapping attribute was changed. Also, I have changed named query definition a little bit - just to adjust it to Cities entity. Of course, you should not forget to change in Cities entity, type for location attribute from String to oracle.spatial.geometry.JGeometry. After that, I have generated Session bean and added a method similar to presented in Doug blog:


The differences are only such, that I'm passing arguments dynamically and returning result using List type. Cities.sampleQuery executes MDSYS.SDO_RELATE spatial operator.

Ok, now we can test implemented Model layer. Everything should be ok, but Oracle JDeveloper 10.1.3.2 gives error when trying to run created sample application:


Upss..., this is quite strange - class defined in persistence.xml configuration file can't be loaded. But, it is possible to solve everything - I have found Issue #: 2432 on glassfish project page, it seems this issue is directly related to my problem and it is solved in Build 37. I have downloaded glassfish JAR file from project download page. I have overwrited TopLink Essentials JPA JAR files available in jdevstudio10132\toplink\jlib directory with Build 37 JAR files. After that I have restarted Oracle JDeveloper 10.1.3.2 - and I got it, there are no errors anymore.

Additionally, I have developed View layer using Oracle ADF Faces components. In View layer it is possible to provide Xmin, Ymin, Xmax and Ymax values, according to provided values Cities data will spatially filtered and returned into table.

Now I will show how it works. At first, we need to know MBR (Minimal-Bounding-Rectangle) of data stored in CITIES table. It is possible to calculate MBR using MDSYS.SDO_AGGR_MBR function. I have executed this function in SQLPlus, also COUNT(*) on CITIES is executed - there are 195 rows in CITIES table:


It is time to start-up OC4J and test application from browser. Here I'm testing with rectangle, whose dimensions are bigger than MBR dimensions - all 195 rows are returned:


Now I have defined smaller window to show how spatial filter works - only 31 rows are returned:


So, there should not be any problems to use spatial functionality in TopLink Essentials JPA. And, this or similar support will be available in the next release of Oracle TopLink, as it is mentioned in Doug Clarke blog post.

When running sample application, don't forget to add adf-faces-impl.jar and jsf-impl.jar to application's WEB-INF\lib directory.

Many-to-Many mapping in Oracle TopLink

This kind of mapping in relational world is constructed using three tables. However, in object world only two classes are used for many-to-many. I have developed sample application - TopLinkMM.zip, that shows how many-to-many is supported in Oracle TopLink. For this sample I'm using Readers/Blogs mapping - one reader can read many blogs and one blog can be read by many readers.


SQL scripts for database schema are available in sample application lt.andrejusb.utils package under Model project.

Many-to-many mapping in Oracle TopLink is enabled by adding attribute with ValueHolderInterface type and corresponding accessors to the one of two classes involved into relationship. In developed sample I have added ValueHolderInterface type attribute - blogs, into Reader class. When accessors are created, it's time to enable many-to-many mapping using Oracle TopLink wizard in JDeveloper. To do this expand descriptor for Reader and double-click blogs element, this element is unmapped. When wizard dialog will be opened, set values for Reference Descriptor and Relation Table. Also, select - Private Owned and choose source and target references. General tab in mapping wizard should look like this:


If many-to-many will be successfully configured, JDeveloper will show appropriate icon for blogs attribue:


So, mapping is enabled, now we can test it. I have developed JUnit test suite, where insert and select is performed. Many-to-many test method code:


JUnit shows green light - there is no errors:

TopLink as a Content Repository for WebCenter Portlet

Almost two weeks ago was released Oracle JDeveloper 10.1.3.2, and Oracle WebCenter Suite was included into this release. Main new feature - possibility of using portlets in JSF, all new features are described in this document - New in Oracle JDeveloper (10.1.3.2.0). I have decided to research more deeply functionality of WebCenter Suite and to build sample application based on acquired knowledge. There are very useful tutorials on OTN for this topic - Oracle WebCenter Suite Demos and Samples, and - Oracle WebCenter Framework Tutorial. However, I have not found an answer in those documents about how to use data from Model layer as a content repository for standard portlet. So, I have developed a sample - JSFPortletTopLink.zip.

It was interesting for me to create a content repository for portlet, based on Oracle TopLink object-relational mapping. Developed sample, demonstrates how to use standard portlets with TopLink and how to integrate portlets into ADF application. Sample contains two workspaces, this means that it is based on two independent samples - PortletProvider and PortletConsumer.


PortletProvider implements a portlet along with content repository. PortletConsumer consumes implemented portlet and provides additional logic. Both parts are based on standard HR schema and use TopLink in Model layer.

It is time to describe a business logic of developed application. There are two pages - viewJobs.jspx and editJob.jspx, first page is developed using ADF Faces components and displays a list of jobs, in the second page developed portlet is used. When a job is selected for editing, ADF Faces form is displayed, where job data can be updated. Additionally, a portlet is included into editJob.jspx, this portlet provides a list of employees assigned to the selected job.

In PortletProvider, I'm retrieving employees list by provided jobId, using TopLink Session query.


This method is called from portlet's view.jsp page. So, in such way it is possible to use as a content repository for portlet not only Web page, CSV file or RSS but also data stored in the database, through object-relational mapping.

PortletConsumer consumes deployed portlet and shows it in editJob.jspx. With WebCenter it becomes possible to use portlets in the same page where ADF Faces components are used and even enable communication between a portlet and ADF Faces component. Below I'm demonstrating how it looks in practice.

Let's say we want to edit job entry data for Programmer position:


When Edit button is clicked, selected row jobId attribute value is passed to edit page, where data for edit form and portlet is retrieved using supplied jobId. In editJob.jspx ADF Faces form component and portlet are used:


If we will select another job, for example - Finance Manager:


Then only one employee will be returned by portlet:

External Transaction Service in Oracle TopLink

I have developed sample application, in order to demonstrate how to use External Transaction Service (ETS), when Oracle TopLink is used in Model layer. The business logic of this sample is to update employee salary and commission points without making explicit commit operation in EJB Session Bean. When ETS is used, Application Server thinks about when to use commit operation, not a developer.

ETS.zip sample application is based on standard HR schema, Employees table. When developing this sample, at first I have generated TopLink entity object from Employees table. The second step was to create OC4JServerPlatformConfig class, this class will configure the session's server platform. The third step was to configure sessions.xml:

1. Open sessions.xml in JDeveloper dialog window
2. In General tab select check box for External Transaction Controller and enter following class name - oracle.toplink.transaction.oc4j.Oc4jTransactionController. For Session Event Listeners add - lt.andrejusb.persistence.OC4JServerPlatformConfig class name
3. In Login tab select General tab and choose Data Source option for Data Source Location. Enter data source name in Data Source field, for example - jdbc/HrDS. Clear all other checkboxes in this tab
4. In Login tab select Options tab and set True for External Connection Pool and External Transaction Controller

The fourth step was to generate EJB Session Bean and add custom updateSalary(Employees) method into it. Do not create any Core Facade Methods, when generating EJB Session Bean.


There is no explicit commit operation in updateSalary(Employees). UnitOfWork is acquired from session.getActiveUnitOfWork(), however if there is no active UnitOfWork session.acquireUnitOfWork() is used. Salary and commission points are updated using Employees clone object.

In the View layer, two JSF pages are used - view.jspx and edit.jspx. In view.jspx user can browse through available emploees data, and in edit.jspx update salary of the selected employee. Update button of ADF Faces Form in edit.jspx is binded to updateSalary(Employees) method.

Merry Christmas everyone! :)