Using Hibernate with Oracle Spatial

February 5, 2007 by Michael

Oracle Spatial Datatype JGeometry from the spatial api (which can be found here) can easily be used with hibernate through a custom dialect and a custom type that delegates to an instance from oracle sdo api. Everything that is needed is here:

Mapping Spatial Oracle type SDO_GEOMETRY to JGeometry

The only quirk with this solution as of February 2007 is the fact, it won’t work with null columns. The fault lies in nullSafeSet. Either can the JGeometry delegate be null or, what is worse, preparedStatement.setNull( i, Types.OTHER); will fail with an invalid column type. The correct version of this method is as follows:

public void nullSafeSet( PreparedStatement preparedStatement, Object o, int i) throws HibernateException, SQLException {
  if( o == null) {
    preparedStatement.setNull(i, Types.STRUCT, "MDSYS.SDO_GEOMETRY");
  } else {
    if( o instanceof JGeometryType) {
      JGeometryType gt = (JGeometryType) o;
      OracleConnection oc = (OracleConnection) preparedStatement.getConnection().getMetaData().getConnection();
      if(gt.getJGeometry() == null)
        preparedStatement.setNull(i, Types.STRUCT, "MDSYS.SDO_GEOMETRY");
      else
        preparedStatement.setObject( i, JGeometry.store( (JGeometry) (gt).getJGeometry(), oc));   
    }
  }
}

Furthermore, i think the spatial dialect should be registered with the following class:

public class OracleSpatialDialect extends Oracle9Dialect {
	public OracleSpatialDialect() {
    super();
      registerColumnType( Types.OTHER, "MDSYS.SDO_GEOMETRY");
  }
}

Kudos to Joel Schuster from Navisys for the adapter class!

Update 2013-12-17

The linked JGeometryType is broken regarding it’s equals and hashCode contract.

I’m using the following implementation:

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Arrays;
 
import javax.sql.PooledConnection;
 
import oracle.jdbc.driver.OracleConnection;
import oracle.spatial.geometry.JGeometry;
import oracle.sql.STRUCT;
 
import org.hibernate.HibernateException;
import org.hibernate.engine.spi.SessionImplementor;
import org.hibernate.usertype.UserType;
 
/**
 * This object is a UserType that represents an SDO_GEOMETRY type for use with
 * Oracle 10g databases and the Oracle Spatial Libraries
 * 
 * It represents an SDO_GEOMETRY database type by wrapping the
 * oracle.spatial.geometry.JGeometry type and implementing
 * org.hibernate.usertype.UserType.
 * 
 * This class should be used with the OracleSpacialDialect class.
 * 
 * (NOTE: I tried just extending the JGeometry instead of aggregating it, that
 * doesn't work. The static load returns a JGeometry that can't be cast to the
 * sub-class)
 * 
 * {@linkplain http://info.michael-simons.eu/2007/02/05/using-hibernate-with-oracle-spatial/}
 * {@linkplain http://community.jboss.org/wiki/mappingspatialoracletypesdogeometrytojgeometry}
 * 
 * @author Joel Schuster - The NAVSYS Corporation
 * @author michael.simons
 */
public class JGeometryType implements UserType, Serializable {
	private static final long serialVersionUID = 1L;
	private JGeometry geometryInstance = null;
 
	/**
	 * This default constructor does create an instance of 1 point at origin
	 */
	public JGeometryType() {
		geometryInstance = new JGeometry( 0, 0, 0);
	}
 
	public JGeometryType( JGeometry geometryInstance) {
		this.geometryInstance = geometryInstance;
	}
 
	public JGeometryType( JGeometryType geometryType) {
		this.geometryInstance = geometryType.getJGeometry();
	}
	/* User Type Information */
	/*
	 * Note that the type that is reported is OTHER (1111) not STRUCT (2002),
	 * see: org.hibernate.dialect.Dialect
	 */
	private static final int[] SQL_TYPES = { Types.OTHER };
 
	public int[] sqlTypes() {
		return SQL_TYPES;
	}
 
	public Class<? extends Object> returnedClass() {
		return JGeometryType.class;
	}
 
	/**
	 * This method gives back the equals functionality that was deprecated by
	 * using the equals that's needed for the UserType
	 */
	@Override
	public boolean equals( Object arg0) {
		return equals( this, arg0);
	}
 
	public boolean equals( Object obj1, Object obj2) throws HibernateException {
		boolean rv = false;
		if(obj1==null && obj2 == null) {
			rv = true;
		// check we are dealing with non-null objects of the correct type 
		} else if( obj1 instanceof JGeometryType && obj2 instanceof JGeometryType && obj1 != null && obj2 != null) {
			JGeometry geo1 = ( (JGeometryType) obj1).getJGeometry();
			JGeometry geo2 = ( (JGeometryType) obj2).getJGeometry();
 
			// check that they are the same type 
			if( geo1.getType() != geo2.getType()) {
				rv = false;
			} else { // go through the different types and check for equality 
				switch( geo1.getType()) {
				case JGeometry.GTYPE_POINT:
					rv = geo1.getJavaPoint().equals( geo2.getJavaPoint());
					break;
				case JGeometry.GTYPE_MULTIPOINT:				
				case JGeometry.GTYPE_MULTICURVE:
				case JGeometry.GTYPE_MULTIPOLYGON:
				case JGeometry.GTYPE_POLYGON:
				case JGeometry.GTYPE_CURVE: 
					rv = Arrays.equals( geo1.getOrdinatesOfElements(), geo2.getOrdinatesOfElements());
					break;
				default: 
					throw new UnsupportedOperationException(String.format("Geometry type %d is not supported", geo1.getType()));
				}			
			}
		} 
 
		return rv;
	}
 
	public int hashCode(Object o) throws HibernateException {
		final JGeometry geo1 = ((JGeometryType) o).geometryInstance;		
		int rv = -1;
 
		switch(geo1.getType()) {
		case JGeometry.GTYPE_POINT: 
			rv = geo1.getJavaPoint().hashCode();
			break;
		case JGeometry.GTYPE_MULTIPOINT:				
		case JGeometry.GTYPE_MULTICURVE:
		case JGeometry.GTYPE_MULTIPOLYGON:
		case JGeometry.GTYPE_POLYGON:
		case JGeometry.GTYPE_CURVE:
			rv = Arrays.hashCode(geo1.getOrdinatesOfElements());
			break;
		default: 
			throw new UnsupportedOperationException(String.format("Geometry type %d is not supported", geo1.getType()));
		}			
		return rv;
	}
 
	/* calls the load method */
	@Override
	public Object nullSafeGet( ResultSet resultSet, String[] strings, final SessionImplementor sessionImplementor, Object o) throws HibernateException, SQLException {
		final STRUCT geometry = (STRUCT) resultSet.getObject( strings[0]);
		return resultSet.wasNull() || geometry == null ? null : new JGeometryType(JGeometry.load( geometry));
	}
 
	/* calls the store method */
	@Override
	public void nullSafeSet( PreparedStatement preparedStatement, Object o, int i, SessionImplementor sessionImplementor) throws HibernateException, SQLException {
		if( o == null) {
			preparedStatement.setNull(i, Types.STRUCT, "MDSYS.SDO_GEOMETRY");
		} else {
			if( o instanceof JGeometryType) {
				JGeometryType gt = (JGeometryType) o;				
				final Connection hlp = preparedStatement.getConnection().getMetaData().getConnection();
				OracleConnection oc = null;
				if(hlp instanceof OracleConnection)
					oc = (OracleConnection) hlp;
				else if(hlp instanceof PooledConnection)
					oc = (OracleConnection) ((PooledConnection)hlp).getConnection();
				if(gt.getJGeometry() == null)
					preparedStatement.setNull(i, Types.STRUCT, "MDSYS.SDO_GEOMETRY");
				else
					preparedStatement.setObject( i, JGeometry.store( (JGeometry) (gt).getJGeometry(), oc));   
			}
		}
	}
 
	/* uses the 'copy' constructor */
	public Object deepCopy( Object o) throws HibernateException {
		if( o == null)
			return null;
		if( o instanceof JGeometryType) {
			return new JGeometryType( ( (JGeometryType) o).getJGeometry());
		} else {
			return null;
		}
	}
 
	public boolean isMutable() {
		return false;
	}
 
	public Serializable disassemble( Object o) throws HibernateException {
		return (Serializable) deepCopy( o);
	}
 
	public Object assemble( Serializable serializable, Object o) throws HibernateException {
		return deepCopy( serializable);
	}
 
	public Object replace( Object o, Object o1, Object o2) throws HibernateException {
		return (JGeometryType) o;
	}
 
	/* accessor */
	public JGeometry getJGeometry() {
		return geometryInstance;
	}
}

17 comments

  1. Philip May wrote:

    Hallo!

    Kannst Du mir vielleicht verraten welches Plugin Du nutzt, um Java Code zu posten? Sieht sehr nett aus mit dem Syntax Hilighting.

    Danke!

    Posted on February 19, 2007 at 8:44 PM | Permalink
  2. Michael wrote:

    Hi Philip,

    Das Plugin heisst “Code Snippet”

    http://blog.enargi.com/codesnippet/

    Posted on February 19, 2007 at 8:47 PM | Permalink
  3. Philip May wrote:

    Der Link ist leider tot. 🙁

    Posted on February 19, 2007 at 10:57 PM | Permalink
  4. Philip May wrote:

    Vielleicht kannst Du ja mal das folgende Plugin einbauen:
    http://txfx.net/code/wordpress.....-comments/
    Dann dann man per Mail benachrichtigt werden wenn Du oder jemand anders antwortet.

    Danke!

    Posted on February 20, 2007 at 12:47 PM | Permalink
  5. J-P wrote:

    Also would like to add that the line:

    OracleConnection oc = (OracleConnection) preparedStatement.getConnection();

    fails if using container managed datasources.

    The solutions is to use:

    …preparedStatement.getConnection().getMetaData().getConnection();

    Posted on September 11, 2007 at 6:14 PM | Permalink
  6. Michael wrote:

    Thank you J-P, you’re right!

    Posted on September 20, 2007 at 10:13 AM | Permalink
  7. Thomas wrote:

    Ich verwende Hibernate JPA innerhalb eines JBoss Seam Projektes und stoße bei der Verwendung des JGeometryTypes immer wieder auf folgende Exception:

    org.hibernate.PropertyAccessException: could not set a field value by reflection setter of com.efkon.fleettracking.datamodel.TripPoints.point

    Mein TripPoints Entity sieht dabei folgendermaßen aus:

    @Entity
    @Name("trippoints")
    @Scope(ScopeType.EVENT)
    @Table(name = "TRIP_POINTS")
    public class TripPoints implements Serializable {
        @Column(name="ALARM_TYPE")
        private String alarmType;
        @Id
        @Column(name="ID_TRIP_POINT", nullable = false)
        private Long idTripPoint;
        @Type(type="com.navsys.spatial.JGeometryType")
        private JGeometry point;
    ...

    Natürlich habe ich überall Setter und Getter dazu generiert.
    Kann mir jemand von euch dabei helfen?

    Mit bestem Dank,
    thomas

    Posted on October 30, 2007 at 2:53 PM | Permalink
  8. Michael wrote:

    Hallo Thomas,
    könnte es daran liegen, das der Typ des Members nicht mit Typ der Spalte übereinstimmt?

    @Type(type=”com.navsys.spatial.JGeometryType”) ist korrekt, aber der Member ist vom Type oracle.spatial.geometry.JGeometry.

    Hibernate findet dann die Getter / Setter nicht.

    Wenn es etwas JPA spezifisches, kann ich Dir leider nicht helfen, da ich kein JPA nutze.

    Posted on October 30, 2007 at 3:01 PM | Permalink
  9. Thomas wrote:

    Hallo Michael,

    danke für die schnelle Antwort! Das könnte es glaube ich sein! Ich werde es gleich ausprobieren. Hab enämlich gerade bei genauerer Betrachtung des Codes festgestellt, dass die gesamte JGeometry-Funktionalität innerhalb de UserTypes nochmals abgebildet ist.

    Ich melde mich nach dem Test wieder bei dir.

    Vielen Dank,
    Thomas

    Posted on October 30, 2007 at 3:06 PM | Permalink
  10. Thomas wrote:

    Ja, ich sehe gerade, dass im Code von Herrn Schuster folgendes Konstrukt definiert ist:

    public Class returnedClass() {
    return JGeometryType.class;
    }

    Somit war meine Implementierung falsch! Ich hatte schon in anderen Projekten UserTypes definiert, die bisher aber nie sich selbst zurückgegeben haben, sondern immer einen entsprechenden Java-Typ (z.B.: java.util.Date bei der Implementierung von Zeitzonenlogik innerhalb eines Userdefined Types oder einen java.Math.BigDecimal bei der Implementierung eines MonetaryAmount User-Types). Dadurch fallen bei der Verwendung des zurückgegebenen Typs die für die Businesslogik nicht notwendigen Methodne weg (z.B.: nullSafeGet(), nullSafeSet usw.)

    Betsen Dank nochmals,
    Thomas

    Posted on October 30, 2007 at 3:17 PM | Permalink
  11. Thomas wrote:

    Hi Michael,

    ja, das war das Problem! Auch einfach an folgendem Code-Konstrukt zu erkennen:

    public Class returnedClass() {
    return JGeometryType.class;
    }

    Bisher hatte ich das nicht so genau beachtet und bei eigenen UserType-Implementierungen immer einen primitiven Typ zurückgegeben.

    Aber so funktioniert es auch! Obwohl ich Methodne wie nullSafeSet() und nullSafeSet() innerhalb meiner Businesslogik eigentlich verbergen möchte…

    Mit bestem Dank,
    Thomas

    Posted on October 30, 2007 at 3:32 PM | Permalink
  12. Michael wrote:

    Hey Thomas,
    freut mich, dass ich Dir helfen konnte.

    Viele Grüße aus Aachen,
    Michael

    Posted on October 30, 2007 at 6:47 PM | Permalink
  13. Thomas wrote:

    Hallo Michael,

    ich habe JGeometry auch shcon innerhalb eines Toplink JPA Projektes verwendet. Innerhalb dieses Projektes war es mir möglich über sog. SessionCustomizer NamedQueries zu definieren, die mir Datenbankseitig eine “FindNearestNeighbour”-Funktion auf meine Geodaten ausführen.

    Weißt du zufällig ob es innerhalb von Hibernate JPA auch möglich ist die Standardoperatoren um Spatial-Operatoren zu erweitern (WithinDistance, FindNN, NN, NN_Distance…) und dies edann innerhalb von NamedQueries zu verwenden?

    Mit bestem Dank,
    Thomas

    Posted on October 31, 2007 at 3:26 PM | Permalink
  14. Michael wrote:

    Ich hab Named Queries in den Hibernate hbm.xml hinterlegt und dort kannst Du arbiträre Datenbankfunktionen aufrufen, das wie gesagt habe ich aber nur ohne den JPA Layer gemacht.

    z.B. um Elemente innerhalb eines Planes abzurufen:

    <sql-query name="elementeImPlan">
    	<return alias="mfe" class="foo.bar.Baz" />	
          SELECT {mfe.*} FROM (
    	    SELECT mfem.*
    	    FROM   [..]
    	    WHERE  [..]
              AND  SDO_FILTER(m.standort, :planrahmen) = 'TRUE'
    	    UNION ALL
    	    SELECT mfeuw.*
    	    FROM   [..]
    	    WHERE  [..]
    	      AND  g.planposition IS NOT NULL
              AND  SDO_FILTER(g.planposition, :planrahmen) = 'TRUE'
          ) mfe
    </sql-query>

    Die kannst Du dann z.B. wie folgt abrufen:

    return session().getNamedQuery("elementeImPlan")
    	.setXxx()
    	.setParameter("planrahmen", plan.getRahmen())
    	.list();

    plan.getRahmen() ist vom Typ JGeometryType.

    Alternativ habe ich an einigen Stellen auch die Hibernate QL wie folgt genutzt:

    return session().createQuery(
    		"FROM  blub as p " + 
    		"WHERE sdo_geom.validate_geometry_with_context(p.rahmen, 0.005) = 'TRUE' " +
    		"  AND sdo_relate(p.rahmen, :relation, 'mask=ANYINTERACT') = 'TRUE'"
    ).setParameter("relation", new JGeometryType(relation))

    Ich weiß nicht, was Du für eine DB verwendest, aber in meinen Beispielen handelt es sich um Oracle Spatial Data Option Operatoren. Wenn Du Dir eigene Methoden baust, sollte das analog funktionieren.

    Darf ich fragen, wie Du eigentlich auf mein Blog gestoßen bist? Kannst mich auch gerne weiter empfehlen 😉

    Grüße und viel Erfolg,
    Michael.

    Posted on October 31, 2007 at 3:57 PM | Permalink
  15. Jeremy wrote:

    Hi Michael,

    Thanks for the blog,

    I have a problem and unfortunately I don’t speak german.
    It is maybe the same problem as above.

    In my xml mapping file, I have:

    and in the associated hibernate entity :
    private JGeometry geom;
    public JGeometry getGeom() {
    return this.geom;
    }
    public void setGeom(JGeometry geom) {
    this.geom = geom;
    }

    When I try to load this entity, I get the following exception:

    BasicPropertyAccessor: IllegalArgumentException in class: be.pulsar.xnotam.model.Navaid, setter method of property: geom
    BasicPropertyAccessor: expected type: oracle.spatial.geometry.JGeometry, actual value: com.navsys.spatial.JGeometryType

    Thanks in advance!

    Jim

    Posted on December 13, 2007 at 3:49 PM | Permalink
  16. Jeremy wrote:

    The xml was (there was a problem in my post above)
    <property name=”geom” type=”com.navsys.spatial.JGeometryType”>
    <column name=”GEOM” />
    </property>

    Posted on December 13, 2007 at 3:52 PM | Permalink
  17. Michael wrote:

    Hey Jim,
    assuming your member “geom” is a oracle.spatial.geometry.JGeometry, the xml declaration is wrong and the problem is the same as above.

    What you wanna do with the hibernate code i linked in my original post is:
    Define a custom type for mapping oracle spatial named “com.navsys.spatial.JGeometryType”.

    Then you wanna use this type in your own hibernate mappings.

    So i guess you wanted the member “geom” to be a “com.navsys.spatial.JGeometryType” and not a “oracle.spatial.geometry.JGeometry”. If you change its type and the accessor accordingly, the no setter / getter errors will disappear.

    Cheers,
    Michael.

    Posted on December 13, 2007 at 4:25 PM | Permalink
Post a Comment

Your email is never published. We need your name and email address only for verifying a legitimate comment. For more information, a copy of your saved data or a request to delete any data under this address, please send a short notice to michael@simons.ac from the address you used to comment on this entry.
By entering and submitting a comment, wether with or without name or email address, you'll agree that all data you have entered including your IP address will be checked and stored for a limited time by Automattic Inc., 60 29th Street #343, San Francisco, CA 94110-4929, USA. only for the purpose of avoiding spam. You can deny further storage of your data by sending an email to support@wordpress.com, with subject “Deletion of Data stored by Akismet”.
Required fields are marked *