Sunday, April 22, 2007

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 -

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


Simon Greener said...


It is great to see someone articulating such a solution. I have been wondering for quite a while now how to do this: nice to see it explained.

Most of my expertise is in Oracle Spatial (10 years) and its integration into businesses using existing GIS technologies. But the true way to integrate spatial into businesses is to use as much of the corporate infrastructure (and generic IT frameworks) as possible (eg WebSphere, OAS etc) before using stovepipe GIS vendor toolsets.

I will keep your blog on my radar now that I have found it.


PS Your link for downloading the zip file with the source code is broken.

Longitude: 147.20515
Latitude: -43.01530

Andrejus Baranovskis said...

Thanks Simon,

I'm very happy that you have found my blog interesting. I plan to post about GIS and generic IT frameworks in the future also.

Link is broken because my university account is closed, but I will solve this today or tomorrow and it will work ok.