Using Hibernate with Oracle Spatial

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;
	}
}

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

Post a comment | Trackback URI | RSS 2.0 feed for those comments

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
Michael wrote:

Hi Philip,

Das Plugin heisst “Code Snippet”

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

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

Der Link ist leider tot. 🙁

Posted on February 19, 2007 at 10:57 PM | Permalink
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
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
Michael wrote:

Thank you J-P, you’re right!

Posted on September 20, 2007 at 10:13 AM | Permalink

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

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
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
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
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
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
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

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

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
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
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

info.michael-simons.eu

Using Hibernate with Oracle Spatial

17 comments

Post a Comment

Meta

Related posts

More from my site