Using Oracle Spatial with TopLink

I have been working a growing number of customers assisting them with their usage of Oracle Database Spatial support through TopLink. Actually my first exposure with Spatial and TopLink dates back to 1999, long before I joined (through acquisition) Oracle. It was an extended consulting engagement assisting Oracle Consulting in their usage of TopLink on a customer's application. Those Oracle consultants are now my co-workers managing the Oracle Consulting teams I deal with and I am now product manager for TopLink. Our recent work assisting customers in their extensions of TopLink for Spatial data mapping and querying has already produced results (GE Energy Press Release).

TopLink does not offer formal support for Oracle Spatial in the current 10.1.3.X versions or earlier. TopLink can however, be extended to support custom data types and query operators/functions. This post will walk through the basics of extended the expression framework and show some examples using Spatial data types.

In order to have TopLink both read and write SDO_GEOMETRY columns as well as generate the necessary SQL to query these columns two extensions to TopLink need to be made.

1. Provide a custom database platform enabling the SDO_GEOMETRY <-> JGeometry (oracle.spatial.geometry) conversion
   
   
2. Define and use custom expression operators to allow the generation of these operators into SQL
   

I will leave it to you to read about the Oracle DB's Spatial support in the docs, on OTN or download a sample.

Struct Conversions - Custom Database Platform

The SDO_GEOMETRY object type which is used to store geometries in the database passes through JDBC as a Struct. The spatial Java library provides an implementation of JGeometry (oracle.spatial.geometry) that simplifies conversion and usage within a Java object model. In order to get TopLink to work with JGeometry types in the object model and properly handle the Struct with JDBC a custom database platform is required.

Note: TopLink's existing support for mapping custom object types (Structs) is not an option with some Oracle Advanced Data Types such as Spatial. An active JDBC connection is required for conversion and the conversions at the mapping level do not necessarily have the connection available.

Example 1: JGeometry - Struct Conversion Code public Object convertToObject(Struct struct) throws SQLException { if (struct == null) { return null; } return JGeometry.load((STRUCT)struct); } public Struct convertToStruct(Object geometry, Connection connection) throws SQLException { if (geometry == null) { return null; } return JGeometry.store((JGeometry)geometry, connection); }

To use these conversions within TopLink you must create a custom DatabasePlatform (most likely a subclass of Oracle10Platform) and incorporate these conversions into the platform.

Custom Expression Operators

TopLink's query framework makes use of expressions for defining the selection criteria as well as for specifying many query configuration options relative to the mapped object model. The expressions are basically a tree node object structure defining navigation through the mapped domain model as well as common query operators. For those of you more familiar with RedHat^TM/JBoss^TM Hibernate^TM the expression framework provides a super-set of these capabilities to their criteria API.

Using the TopLink expressions developers are able to build very complex queries and have the platform specific SQL generated for them. Expressions also allow the queries to be processed in-memory and avoid unnecessary database trips.

ExpressionOperator

All operations used in expressions are defined by an ExpressionOperator. This capability can be extended to define additional operations.

Example 2: SDO_RELATE ExpressionOperator public static ExpressionOperator RELATE_OP = relateOperator(); public static ExpressionOperator relateOperator() { ExpressionOperator exOperator = new ExpressionOperator(); exOperator.setType(ExpressionOperator.FunctionOperator); Vector v = NonSynchronizedVector.newInstance(4); v.addElement("MDSYS.SDO_RELATE("); v.addElement(", "); v.addElement(", "); v.addElement(")"); exOperator.printsAs(v); exOperator.bePrefix(); exOperator.setNodeClass(ClassConstants.FunctionExpression_Class); return exOperator; }

These operators should be defined only once and re-used as needed when defining specific named or dynamic queries. In the above example a singleton operator (RELATE_OP) is used to store the custom operator.

FunctionExpression

To use an ExpressionOperator with specific parameter values a FunctionExpression must be built and incorporated within the selection criteria expression of a TopLink query.

Example 3: Using a FunctionExpression in a Query ReadAllQuery raq = new ReadAllQuery(MyEntity.class); ExpressionBuilder eb = raq.getExpressionBuilder(); FunctionExpression relateOpExp = new FunctionExpression(); relateOpExp.setOperator(RELATE_OP); // RELATE_OP is a static holding a singleton instance of the operator defined in Example 1. relateOpExp.setBaseExpression(eb); relateOpExp.addChild(Expression.from(eb.get("geometry"), eb)); // References a geometry attribute/column relateOpExp.addChild(Expression.from(someJGeometry, eb)); // Passes in a JGeometry instance relateOpExp.addChild(Expression.from("mask=ANYINTERACT", eb)); // Provides the parameters string for the operator Expression relateExpression = relateOpExp.equal(Boolean.TRUE.toString()); raq.setSelectionCriteria(relateExpression); session.executeQuery(raq);

Preview of Future TopLink Spatial Query API

In the next major release of TopLink we will offer out of the box support for Spatial mapping and querying. The following example illustrates how the same query as above would be written.

Example 4: Spatial Queries in the future TopLink release ReadAllQuery raq = new ReadAllQuery(SimpleSpatial.class); ExpressionBuilder eb = raq.getExpressionBuilder(); SpatialParameters params = new SpatialParameters(); params.setMask(Mask.ANYINTERACT); Expression relateExp = SpatialExpressionFactory.relate(eb.get("geometry"), someJGeometry, params); raq.setSelectionCriteria(relateExp); raq.addAscendingOrdering("id"); List<SimpleSpatial> results = (List<SimpleSpatial>)session.executeQuery(raq);

This new API incorporates the same expression operator functionality within the SpatialExpressionFactory class. The SpatialParameters provides some easy to use enums for providing the parameter values instead of building the string value yourself. If you do want to provide the string value directly this is also available.

The goal of this post is to provide a simple example of how you can extend the expression framework for custom operators. When we preview the next release I am sure those of you using these extensions will find the provided functionality and simplified API a great asset.

Doug