hibernate-spatial on oracle12c - sdo_geometry objects become empty on em.merge

Question

there is a table containing a column of type SDO_GEOMETRY representing some points. Later a spatial query like 'show all other points within distance x' shall be executed. After pumping some data via sqlloader into a import-table I am going to bring these datasets into a base-table while merging if business-key still exists.

My application is a executable jar using hibernate 5.0.9, hibernate-spatial 5.0.9 final, Oracle12c database and the hibernate.dialect has been

org.hibernate.dialect.Oracle12cDialect

before but now changed to

org.hibernate.spatial.dialect.oracle.OracleSpatial10gDialect

I know that there is only a spatial-dialect oficially for Oracle10 and also testet with oracle11 but with oracle12.

The problem now is, that the objects are stored via hibernate into base-table but inside the SDO-Geometry all coordinates are null (while the sdo_geometry object isnt).

select
 businessKey,
 my_sdo_geometry.sdo_point.x longitude,
 my_sdo_geometry.sdo_point.y latitude
from my_import_table;

After sqlloader, all coordinates in import-table are shown well.

But after processing each import-dataset its column 'imported' {Y, N} is set to 'Y'. And doing so I am using myEntityManager.merge(importDataset). Due to this (although alle coordinates had been fine after sqlloader) the coordinates of the import-dataset are cleared to null.

//coordinates still viewable in database    
importObject.setImported(Boolean.TRUE);
em.merge(importObject);
//coordinates null now

I guess that the spatial dialect is not working properly. I am afraid that this happens due to some incompatibility between last version of hibernate-spatial and oracle12c.

I am writing here with hope that there is anybody using hibernate-spatial successfully with oracle12 (to clarify that its possible at all) and to may get some help with any bad configuration on my side.

The persistance.xml of my application:

<persistence xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://java.sun.com/xml/ns/persistence
    http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd" version="1.0">

    <persistence-unit name="myUnit" transaction-type="RESOURCE_LOCAL">
        <provider>org.hibernate.ejb.HibernatePersistence</provider>

        <properties>
            <property name="hibernate.ejb.cfgfile" value="/hibernate.cfg.xml" />
            <property name="use_sql_comments" value="true" />
            <property name="hibernate.show_sql" value="false" />
            <property name="hibernate.format_sql" value="false" />
            <property name="hibernate.connection.driver_class" value="oracle.jdbc.driver.OracleDriver" />
            <property name="hibernate.connection.url" value="jdbc:oracle:thin:@123.456.789.1:1234/cm" />
            <property name="hibernate.connection.username" value="testMe" />
            <property name="hibernate.connection.password" value="********" />
            <property name="hibernate.dialect" value="org.hibernate.dialect.Oracle12cDialect" />
        </properties>
    </persistence-unit> </persistence>

Thanks in advance.

edit: in the entity class the sdo_geometry column (here called 'Position') getter is annotated as follows:

@Column(name = "POSITION", nullable = false)

public Point getPosition() {
    return this.position;
}

edit 2: after enabling hibernate sql trace, I found that hibernate is trying this update:

Hibernate: update IMPORT_TABLE set HB_VERSION=?, BUSINESS_KEY=?, IMPORTED=?, [...], POSITION=? where IMPORT_TABLE_ID=? and HB_VERSION=?

I am pointing to POSITION=? which cannot be right I believe. At this position I would expect the hibernate-dialect to take place. May another hint to an imcompatibility with hibernate-dialect spatial10 and oracle12.

Answer 1

Hibernate Spatial by default stores the coordinates in the SDO_Ordinates array, even for Points. The SDO_Geometry.SDO_Point field is not used, so will always be null. In SQL you can use the SDO_UTIL.GetVertices() function to access the coordinate data.

In geolatte-geom version 1.1 or higher you should be able to set the GEOLATTE_USE_SDO_POINT_TYPE Java system property. With that property set, Point geometries will be stored in the SDO_Geometry.SDO_Point field. If your Hibernate version uses an older Geolatte-geom version, you may want to add a more recent version to your classpath so you can use this feature.

Btw. Hibernate Spatial works by encoding the Java Geometries to values that can be set in a prepared statement, so the "... Position=?" part in the generated SQL is fine.

Answer 2

I found a workaround which is not answering my question as I intend but its a workaround so far.

Instead of using hibernate objects and the merge-operator It works when instead using a custom query like this:

Query q = em.createQuery("INSERT INTO base_table"
            + "(techId, myBuildingFK, [...], my_sdo_geometry) "
            + "SELECT l.impTechId, b, [...], l.my_sdo_geometry"
            + "FROM import_table l, Buildiung b "
            + "WHERE l.businessKey = g.businessKey ");
q.executeUpdate();

With this HQL-Query I am getting the coordinates not be emptied. Due to its still a HQL Query may something of the spatial-dialect is still used but it seems it is not working properly with merge-command of entity manager. May I am missing still some annotation at the entity to get this working may its incompatible with Oracle12.

I hope still for other and may better answers than mine.

edit: because I need merge-behaviour I thought of just merging the object via entity manager and then do an sql or hql updatequery to store the sdo_geometry but since the transaction did not finish there is no datset to be updated.

//        //not working
//        em.merge(mergeObject);

//        String updateQueryString =
//                "update BaseTable set sdo_geometry = :sdoGeometry" + " where baseTableId = :baseTableId";
//        Query updateQuery = em.createQuery(updateQueryString);
//        updateQuery.setParameter("sdoGeometry", mergeObject.getSdoGeometry());
//        updateQuery.setParameter("baseTableId", mergeObject.getBaseTableId());
//        int cnt = updateQuery.executeUpdate();

Due to this I am now doing a native oracle merge statement which does work as expected but escapes every hibernate behaviour and is of bad architecture I think.

String mergeQueryString = "MERGE INTO Base_Table gl "
    + "USING (SELECT * FROM Import_Table WHERE import_table_id = :importTableId) igl "
    + "ON (gl.base_table_id = :baseTableId) "
    + "WHEN MATCHED THEN UPDATE SET gl.my_sdo_geometry = igl.my_sdo_geometry "
    + "WHEN NOT MATCHED THEN INSERT (gl.baseTableId, gl.anyFkId, gl.my_sdo_geometry) "
    + "VALUES (id_gen_function, :anyFkId, igl.my_sdo_geometry)";
Query mergeQuery = em.createNativeQuery(mergeQueryString);
mergeQuery.setParameter("importTableId", importObject.getImportTableId());
mergeQuery.setParameter("baseTableId", refModelObject.getBaseTableId());
mergeQuery.setParameter("anyFkId", refModelObject.getAnyFkObject().getAnyFkId());
int cnt = mergeQuery.executeUpdate();