I've created a custom GeometryUserType that allows me to map an Oracle SDO_Geometry to a JGeometry object. I have a "Collect" class that contains a local variable "mbr" that is of type JGeometry. Collect objects are mapped to the DB via the mapping file below.

Problem: session.save(myCollect) works fine as long as myCollect.mbr is not null. However, if mbr is null an exception is thrown see below, despite the fact the mapping property for mbr allows nulls. GeometryUserType's nullSafeSet seems to do the right thing. Any guidance?

Hibernate version: 3.05

DB Version: Oracle 10G spatial

Collect Mapping document:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
<class name="gov.llnl.e3.model.Collect" lazy="true" table="COLLECT" >
<id name="id" column="ID" type="long">
<generator class="native"/>
</id>
<set name="Datasets" lazy="true" cascade="save-update" inverse="true">
<key column="COLLECT_ID"/>
<one-to-many class="gov.llnl.e3.model.Dataset"/>
</set>
<property name="collectName" unique="true" column="COLLECT_NAME" not-null="true"/>
<property name="collectBeginTime" type="timestamp" column="COLLECT_BEGIN_TIME"/>
<property name="collectEndTime" type="timestamp" column="COLLECT_END_TIME"/>
<property name="mbr" type="gov.llnl.e3.model.GeometryUserType">
<column name="MBR" sql-type="sdo_geometry"/>
</property>

<many-to-one name="countryCode" column="COUNTRY_CODE_ID" not-null="true"/>

</class>
</hibernate-mapping>


GeometryUserType source
package gov.llnl.e3.model;

import java.io.Serializable;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

import oracle.jdbc.driver.OracleConnection;
import oracle.spatial.geometry.JGeometry;
import oracle.sql.STRUCT;

import org.hibernate.HibernateException;
import org.hibernate.usertype.UserType;

public class GeometryUserType implements UserType {

private static final int[] SQL_TYPES = {Types.STRUCT};

public int[] sqlTypes() {
return SQL_TYPES;
}

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

public boolean equals(Object obj1, Object obj2) throws HibernateException {
if (obj1 == obj2) return true;
if (obj1 == null || obj2 == null) return false;
return (obj1).equals(obj2);
}

public int hashCode(Object o) throws HibernateException {
return ((STRUCT)o).hashCode();
}

public Object nullSafeGet(ResultSet resultSet, String[] strings, Object o) throws HibernateException, SQLException {
STRUCT geometry = (STRUCT) resultSet.getObject(strings[0]);
JGeometry jg = JGeometry.load(geometry);
return resultSet.wasNull() ? null : jg;
}

public void nullSafeSet(PreparedStatement preparedStatement, Object o, int i) throws HibernateException, SQLException {
if (o == null) {
preparedStatement.setNull(i, Types.STRUCT);
} else {
OracleConnection oc = (OracleConnection)preparedStatement.getConnection();
STRUCT struct = JGeometry.store((JGeometry)o,oc);
preparedStatement.setObject(i, struct);
}
}

public Object deepCopy(Object o) throws HibernateException {
if (o == null)
return null;

return ((JGeometry) o).clone();
}

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 (STRUCT) o;
}
}

SQL Create Table:
create table COLLECT (
ID number(19,0) not null,
COLLECT_NAME varchar2(255) not null unique,
COLLECT_BEGIN_TIME timestamp,
COLLECT_END_TIME timestamp,
MBR sdo_geometry,
COUNTRY_CODE_ID number(19,0) not null,
primary key (ID)
)
Full stack trace of any exception that occurs (only when mrb is null)
Hibernate: insert into COLLECT (COLLECT_NAME, COLLECT_BEGIN_TIME, COLLECT_END_TIME, MBR, COUNTRY_CODE_ID, ID) values (?, ?, ?, ?, ?, ?)
0 [main] ERROR (JDBCExceptionReporter.java:72) - Invalid column type: sqlType=2002
11813 [main] ERROR (AbstractFlushingEventListener.java:277) - Could not synchronize database state with session
org.hibernate.exception.GenericJDBCException: could not insert: [gov.llnl.e3.model.Collect]
at org.hibernate.exception.ErrorCodeConverter.handledNonSpecificException(ErrorCodeConverter.java:92)
at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:80)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.persister.entity.BasicEntityPersister.insert(BasicEntityPersister.java:1869)
at org.hibernate.persister.entity.BasicEntityPersister.insert(BasicEntityPersister.java:2200)
at org.hibernate.action.EntityInsertAction.execute(EntityInsertAction.java:46)
at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:239)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:223)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:136)
at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:274)
at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:730)
at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:324)
at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:86)
at gov.llnl.e3.manager.E3Manager.endTransaction(E3Manager.java:201)
at gov.llnl.e3.manager.E3Manager.endTransaction(E3Manager.java:173)
at gov.llnl.e3.manager.E3Manager.storeOrUpdate(E3Manager.java:537)
at gov.llnl.e3.loaders.Examples.ingestExample(Examples.java:103)
at gov.llnl.e3.loaders.E3Loader.main(E3Loader.java:39)

This is because type 2002 (STRUCT) is not what's reported actually.

It's reported as a 1111(OTHER) instead.

Here's how to have a UserType for JGeometry <-> SDO_GEOMETRY mapping:

http://www.hibernate.org/Documentation/ ... oJGeometry