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.