Oracle Spatial using TopLink Essentials JPA

As discussed in my previous post the usage of Oracle Database Spatial support is definitely growing in popularity. This post describes how TopLink Essentials, the reference implementation of JPA within GlassFish, can be extended to support mapping to MDSYS.SDO_GEOMETRY columns and leveraging the Oracle Spatial custom SQL operators within your queries.

Extending TopLink Essentials (TLE) for Spatial support involves a couple of key steps:

1. A custom database platform is required to handle the STRUCT <-> JGeometry (oracle.spatial.geometry) conversions and statement binding.
   
   
2. Customize the mappings to ensure the default serialized mapping for the unknown JGeometry type is not used.
   
   
3. Usage of custom TLE expression operators for defining the queries.
   

1. Custom Database Platform

TLE uses a target database configuration to control custom SQL generation leveraging the different dialects supported by each of the database vendors. The platform is automatically detected by default but can be specified. For our purposes we will write a custom database platform extending the shipped OraclePlatform. To configure the use of our custom platform a persistence unit property (toplink.target-database) will need to be set.

<property name="toplink.target-database" value ="oracle.toplink.essentials.extension.spatial.Oracle10SpatialPlatform"/>

The custom platform must be written and packaged where the class-loader can access it.

Here is an example of what that platform might look like. Oracle10SpatialPlatform.java

2. Customizing the Mappings

The default mapping with JPA is to consider unknown types to be serialized basic (direct to field) mappings. In the case of JGeometry attribute types it is up to the developer to remove the converter that will be configured by default. The following descriptor customizer illustrates how the converter can be easily removed.

/** * Descriptor customizer. This must be configured as a persistence unit property * for each entity type containing a JGeometry attribute. * * property name="toplink.descriptor.customizer.SimpleSpatial" value="model.jpa.spatial_simple.toplink.SimpleSpatialCustomizer" * */ public class SimpleSpatialCustomizer implements DescriptorCustomizer { public void customize(ClassDescriptor descriptor) { DirectToFieldMapping mapping = (DirectToFieldMapping)descriptor.getMappingForAttributeName("geometry"); mapping.setConverter(null); } }

3. Using Custom Expression Operators

While the above two steps are pretty straight forward with one time configurations the more involved process is using spatial operators across dynamic and named queries. Based on a set of custom expression operators the following examples illustrate how a named query definition and usage might look.

EXAMPLE: Defining a Named Query with Custom Spatial Operators /** * Session customizer. This must be configured as a persistence unit property * for each entity type containing a JGeometry attribute. * * <property name="toplink.session.customizer" value="model.jpa.spatial_simple.toplink.SS_SessionCustomizer/>" * */ public class SS_SessionCustomizer implements SessionCustomizer { public void customize(Session session) { SpatialParameters params = new SpatialParameters(); params.setMask(SpatialParameters.Mask.ANYINTERACT); params.setQueryType(SpatialParameters.QueryType.WINDOW); ReadAllQuery raq = new ReadAllQuery(SimpleSpatial.class); ExpressionBuilder eb = new ExpressionBuilder(SimpleSpatial.class); Expression relateExp = SpatialOperator.RELATE.buildExpression(eb.get("geometry"), eb.getParameter("GEOMETRY"), params); Expression idExp = eb.get("id").greaterThan(0); raq.setSelectionCriteria(idExp.and(relateExp)); raq.addArgument("GEOMETRY"); raq.addAscendingOrdering("id"); session.addQuery("SimpleSpatial.sampleQuery", raq); } }

Now to execute the above query within the application code standard JPA usage is followed.

EXAMPLE: Invoking the Named Query double[] points = new double[] { 1, 1, 1, 20, 10, 20, 20, 1, 1, 1 }; JGeometry rectangle = JGeometry.createLinearPolygon(points, 2, 0); Query query = getEntityManager().createNamedQuery("SimpleSpatial.sampleQuery"); query.setParameter("GEOMETRY", rectangle); List<SimpleSpatial> results = query.getResultList();

In order to make all of this work a couple of helper classes are required to define the spatial operators and parameters.

SpatialOperator: Captures the definition of expression operators and acts as a factory for function expressions for use in queries.

SpatialParameters: Simplifies configurations of parameters for the MDSYS spatial operators

These classes must be compiled and made available on the application's classpath or packaged within a JAR with TopLink Essentials.

This support will be available in the next release of Oracle TopLink and will also be included in the initial contribution to EclipseLink. The final API in these version may vary from what I am providing now as a sample of how to extend TopLink.

Doug