Hibernate persist failure with PostGIS Geometry

Question

Related to previous question. I have a Spring Roo application using Hibernate to write a Geometry object to a PostGIS database using JTS. I believe I've fixed the problems I had in defining my Geometry object, and now Hibernate is executing its persist() method, but something is going wrong just before it hits the DB and I'm getting the exception below.

Here are some interesting lines. First from the Hibernate logs, the object to be persisted, and then an SQL query (presumably the ? are substituted):

...
DEBUG org.hibernate.pretty.Printer - com.test.LandUse{id=1, centerPoint=POINT (5 6), version=0}
...
DEBUG org.hibernate.SQL - insert into land_use (center_point, version, id) values (?, ?, ?)
...

Then some more things happen, though nothing obviously bad. However I don't see any 'final' SQL, and there is an attempt to roll back the transaction. Then:

org.springframework.transaction.TransactionSystemException: Could not commit JPA transaction; nested exception is javax.persistence.RollbackException: Error while committing the transaction
    at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:521)
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:754)
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:723)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:393)
    at org.springframework.transaction.aspectj.AbstractTransactionAspect.ajc$afterReturning$org_springframework_transaction_aspectj_AbstractTransactionAspect$3$2a73e96c(AbstractTransactionAspect.aj:78)
    at com.test.LandUse_Roo_Jpa_ActiveRecord.ajc$interMethod$com_test_LandUse_Roo_Jpa_ActiveRecord$com_test_LandUse$persist(LandUse_Roo_Jpa_ActiveRecord.aj:44)
    at com.test.LandUse.persist(LandUse.java:1)
    at com.test.LandUse_Roo_Jpa_ActiveRecord.ajc$interMethodDispatch1$com_test_LandUse_Roo_Jpa_ActiveRecord$com_test_LandUse$persist(LandUse_Roo_Jpa_ActiveRecord.aj)
    at com.test.LandUseController_Roo_Controller.ajc$interMethod$com_test_LandUseController_Roo_Controller$com_test_LandUseController$create(LandUseController_Roo_Controller.aj:29)
    at com.test.LandUseController.create(LandUseController.java:1)
...
Caused by: javax.persistence.RollbackException: Error while committing the transaction
at org.hibernate.ejb.TransactionImpl.commit(TransactionImpl.java:93)
    at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:512)
    ... 54 more
Caused by: java.lang.UnsupportedOperationException
    at org.hibernate.spatial.GeometrySqlTypeDescriptor.getBinder(GeometrySqlTypeDescriptor.java:52)
    at org.hibernate.type.AbstractStandardBasicType.nullSafeSet(AbstractStandardBasicType.java:283)
    at org.hibernate.type.AbstractStandardBasicType.nullSafeSet(AbstractStandardBasicType.java:278)
    at org.hibernate.type.AbstractSingleColumnStandardBasicType.nullSafeSet(AbstractSingleColumnStandardBasicType.java:89)
    at org.hibernate.persister.entity.AbstractEntityPersister.dehydrate(AbstractEntityPersister.java:2184)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2430)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2874)
    at org.hibernate.action.EntityInsertAction.execute(EntityInsertAction.java:79)
    at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:273)
    at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:265)
    at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:184)
    at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321)
    at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:51)
    at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1216)
    at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:383)
    at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:133)
    at org.hibernate.ejb.TransactionImpl.commit(TransactionImpl.java:76)
    ... 55 more

I've been trying to get this simple use case (an object with just a single Geometry property) working for over a week now, and am about at my wits' end. If I replace the Geometry object with a String it works just fine. Does anyone know what might be causing such an error?

EDIT: Thierry's answer below got me poking through the source, and I noticed the exception is thrown in GeometrySqlTypeDescriptor, which has some interesting contents:

/**
 * A generic <code>SqlTypeDescriptor</code>, intended to be remapped
 * by the spatial dialect.
 *
 * @author Karel Maesen, Geovise BVBA
 *         creation-date: 7/27/11
 */
public class GeometrySqlTypeDescriptor implements SqlTypeDescriptor {

    public static final GeometrySqlTypeDescriptor INSTANCE = new GeometrySqlTypeDescriptor();

    @Override
    public int getSqlType() {
        return 3000; //this value doesn't conflict with presently defined java.sql.Types values.
    }

    @Override
    public boolean canBeRemapped() {
        return true;
    }

    @Override
    public <X> ValueBinder<X> getBinder(JavaTypeDescriptor<X> javaTypeDescriptor) {
        throw new UnsupportedOperationException();
    }

    @Override
    public <X> ValueExtractor<X> getExtractor(JavaTypeDescriptor<X> javaTypeDescriptor) {
        throw new UnsupportedOperationException();
    }
}

In particular, note the class comment suggesting something is clearly wrong with the Hibernate dialect mapping. Unfortunately I have no idea what that means, but I'm guessing due to some kind of version mismatch. (Note also the declaration of SQL type 3000, as per my previous error!)

My current dialect is org.hibernate.spatial.dialect.postgis.PostgisDialect, as per the Hibernate Spatial usage guide. I'm using Hibernate Spatial 4.0-M1, JTS 1.12, and PostGIS 2.0.1. I'll try with a couple of different versions of PostGIS perhaps, particularly since that's the one dependency that Hibernate Spatial is supposed to provide but doesn't seem to.

Answer 1

It seems the problem was that the PostgisDialect was not been picked up and integrated correctly, and hence the required operations were not supported. The solution was as simple as upgrading from Hibernate 3.6.9.Final to 4.1.6.Final!

See my thread on the mailing list for more information.

As per that thread, you should also be aware that as of Hibernate Spatial 4.0-M1, only the Geometry type is specified to Hibernate, and hence the @Column annotation must set columnDefinition="Geometry", and not Point or anything else. This may be fixed in the future.

With this anthology of modifications, I can finally write a Point to a database! The correct property specification is:

@Column(columnDefinition="Geometry")
@Type(type = "org.hibernate.spatial.GeometryType")
private Point centerPoint;

Answer 2

I got this exception when I forgot to add the Postgis Dialect in hibernate configuration file.

Add following line to hibernate.cfg.xml

<property name="dialect">org.hibernate.spatial.dialect.postgis.PostgisDialect</property>

Answer 3

Yes, the ? are substituted by the values you need to store.

Did you try to use the following type: GeometryUserType and not the GeometryType? I suspect GeometryType is not directly supported by the API of Hibernate Spatial Project. It is maybe an abstract class which you could not instantiate directly to map your datas with annotations - it acts beyond the scene as we have experimented.

Caused by: java.lang.UnsupportedOperationException which has make me tell that.

And the last XML stuff inside the tutorial you have followed is clear:

...
<property name="geometry" type="org.hibernatespatial.GeometryUserType">
    <column name="geom" />
</property>
...

Looking at the code inside the GeometryUserType I see only one place where these exception could be thrown.

public Object conv2DBGeometry(Geometry jtsGeom, Connection connection) {
        org.postgis.Geometry geom = null;
        jtsGeom = forceEmptyToGeometryCollection(jtsGeom);
        if (jtsGeom instanceof com.vividsolutions.jts.geom.Point) {
            geom = convertJTSPoint((com.vividsolutions.jts.geom.Point) jtsGeom);
        } else if (jtsGeom instanceof com.vividsolutions.jts.geom.LineString) {
            geom = convertJTSLineString((com.vividsolutions.jts.geom.LineString) jtsGeom);
        } else if (jtsGeom instanceof com.vividsolutions.jts.geom.MultiLineString) {
            geom = convertJTSMultiLineString((com.vividsolutions.jts.geom.MultiLineString) jtsGeom);
        } else if (jtsGeom instanceof com.vividsolutions.jts.geom.Polygon) {
            geom = convertJTSPolygon((com.vividsolutions.jts.geom.Polygon) jtsGeom);
        } else if (jtsGeom instanceof com.vividsolutions.jts.geom.MultiPoint) {
            geom = convertJTSMultiPoint((com.vividsolutions.jts.geom.MultiPoint) jtsGeom);
        } else if (jtsGeom instanceof com.vividsolutions.jts.geom.MultiPolygon) {
            geom = convertJTSMultiPolygon((com.vividsolutions.jts.geom.MultiPolygon) jtsGeom);
        } else if (jtsGeom instanceof com.vividsolutions.jts.geom.GeometryCollection) {
            geom = convertJTSGeometryCollection((com.vividsolutions.jts.geom.GeometryCollection) jtsGeom);
        }

        if (geom != null)
            return new PGgeometry(geom);
        else
            throw new UnsupportedOperationException("Conversion of "
                    + jtsGeom.getClass().getSimpleName()
                    + " to PGgeometry not supported");
    }

Where PGgeometry stands for PostGis Geometry I think (or maybe PostgreSQL).

I have found some topics where Karel Maesen and others speak about the InnoDB support is not very well, but they are maybe outdated (05-2011).

Good luck!