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.