Hibernate, Hibernate Spatial, Oracle, existing table(s)

[Pedro Mendes]

Hi people,

I don't know if the problem I'm facing has anything to do with Play! itself, my feeling is that it is not, but here's the description:
I'm trying to built some models where some of the properties are JTS Geometries. This is achievable using the type provided "org.hibernatespatial.GeometryUserType" by hibernate-spatial in conjunction with the default hibernate dependencies packed with Play! and the Java Topology Suite (JTS). The specific spatial extension I'm using is for Oracle databases (hibernate-spatial-oracle).

The model works was expected if my model is defined like this:

package models;

import java.util.Date;

import javax.persistence.Entity;

import org.hibernate.annotations.Type;

import play.data.validation.Required;

import play.db.jpa.Model;

import com.vividsolutions.jts.geom.Geometry;

@Entity

public class Feature extends Model {

@Required

public String name;

@Required

public String description;

@Required

public Date creationDate = new Date();

public char visible = 'S';

@Type(type = "org.hibernatespatial.GeometryUserType") 

public Geometry point;

@Type(type = "org.hibernatespatial.GeometryUserType") 

public Geometry linestring;

@Type(type = "org.hibernatespatial.GeometryUserType") 

public Geometry polygon;

public Feature(String name, String description, Date creationDate, char visible, Geometry point, Geometry linestring, Geometry polygon) {

this.name = name;

this.description = description;

this.creationDate = creationDate;;

this.visible = visible;

this.point = point;

this.linestring = linestring;

this.polygon = polygon;

}

public static Feature connect(String name, String description){

return find("byNameAndDescription", name, description).first();

}

    

}

In this case the table FEATURE doesn't not exists in the database and when I run the test the first time the table is created as expected with %test.jpa.dll=create.

But my end goal is to create a model using existing database domain objects. I've a rather complex database model in which I have already identified the domain objects that must be modeled. The problem arises when I try to create a model for an existing table:

import java.util.Date;

import javax.persistence.Column;

import javax.persistence.Entity;

import javax.persistence.GeneratedValue;

import javax.persistence.GenerationType;

import javax.persistence.Id;

import javax.persistence.SequenceGenerator;

import javax.persistence.Table;

import org.hibernate.annotations.Type;

import play.db.jpa.GenericModel;

import com.vividsolutions.jts.geom.Geometry;

@Entity

@Table(name="gir_annotation")

public class Annotation extends GenericModel {

@Id

@GeneratedValue(strategy=GenerationType.SEQUENCE, generator="annotationSequence")

@SequenceGenerator(name="annotationSequence", sequenceName="gir_annotation_seq")

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

public Integer id;

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

public String caption;

@Type(type="org.hibernatespatial.GeometryUserType")

@Column(name="location")

public Geometry location;

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

public String color = "#0000FF";

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

public Double scaleFactor = 1.0;

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

public Integer rotation = 0;

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

public Integer userCreate;

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

public Date creationDate = new Date();

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

public char isPrivate = 'N';

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

public char isTemporary = 'S';

@Column(name="id_user_altera")

public Integer userUpdate;

@Column(name="data_altera")

public Date updateDate;

public Annotation(String caption, Integer userCreate){

this.caption = caption;

this.userCreate = userCreate;

}

public Annotation(String caption, Geometry location, Integer userCreate){

this.caption = caption;

this.location = location;

this.userCreate = userCreate;

}

}

In this case, I had to extend from GenericModel because of the custom id field in the table (girid). When testing inserting a new Annotation like:

Geometry location = new WKTReader().read("POINT(7 7)");

location.setSRID(27492);

new Annotation( "ImAnAnnotation", location, new Integer(151) ).save();

I'm getting the following exception:

javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: Could not execute JDBC batch update
	at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1214)
	at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1147)
	at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1153)
	at org.hibernate.ejb.AbstractEntityManagerImpl.flush(AbstractEntityManagerImpl.java:798)
	at play.db.jpa.JPABase._save(JPABase.java:47)
	at play.db.jpa.GenericModel.save(GenericModel.java:184)
	at AnnotationTest.createAndRetrieveAnnotation(AnnotationTest.java:27)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:44)
	at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15)
	at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:41)
	at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20)
	at play.test.PlayJUnitRunner$StartPlay$2$1.evaluate(PlayJUnitRunner.java:98)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:76)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:50)
	at org.junit.runners.ParentRunner$3.run(ParentRunner.java:193)
	at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:52)
	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:191)
	at org.junit.runners.ParentRunner.access$000(ParentRunner.java:42)
	at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:184)
	at org.junit.runners.ParentRunner.run(ParentRunner.java:236)
	at play.test.PlayJUnitRunner.run(PlayJUnitRunner.java:48)
	at org.junit.runners.Suite.runChild(Suite.java:128)
	at org.junit.runners.Suite.runChild(Suite.java:24)
	at org.junit.runners.ParentRunner$3.run(ParentRunner.java:193)
	at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:52)
	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:191)
	at org.junit.runners.ParentRunner.access$000(ParentRunner.java:42)
	at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:184)
	at org.junit.runners.ParentRunner.run(ParentRunner.java:236)
	at org.junit.runner.JUnitCore.run(JUnitCore.java:157)
	at org.junit.runner.JUnitCore.run(JUnitCore.java:136)
	at org.junit.runner.JUnitCore.run(JUnitCore.java:117)
	at play.test.TestEngine.run(TestEngine.java:101)
	at controllers.TestRunner.run(TestRunner.java:67)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at play.mvc.ActionInvoker.invokeWithContinuation(ActionInvoker.java:546)
	at play.mvc.ActionInvoker.invoke(ActionInvoker.java:500)
	at play.mvc.ActionInvoker.invokeControllerMethod(ActionInvoker.java:476)
	at play.mvc.ActionInvoker.invokeControllerMethod(ActionInvoker.java:471)
	at play.mvc.ActionInvoker.invoke(ActionInvoker.java:159)
	at play.server.PlayHandler$NettyInvocation.execute(PlayHandler.java:220)
	at play.Invoker$Invocation.run(Invoker.java:265)
	at play.server.PlayHandler$NettyInvocation.run(PlayHandler.java:200)
	at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:441)
	at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
	at java.util.concurrent.FutureTask.run(FutureTask.java:138)
	at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:98)
	at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:207)
	at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
	at java.lang.Thread.run(Thread.java:619)
Caused by: org.hibernate.exception.SQLGrammarException: Could not execute JDBC batch update
	at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92)
	at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
	at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:275)
	at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:268)
	at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:184)
	at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:345)
	at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:51)
	at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1216)
	at org.hibernate.ejb.AbstractEntityManagerImpl.flush(AbstractEntityManagerImpl.java:795)
	... 54 more
Caused by: java.sql.BatchUpdateException: ORA-00932: inconsistent datatypes: expected NUMBER got MDSYS.SDO_GEOMETRY

	at oracle.jdbc.driver.DatabaseError.throwBatchUpdateException(DatabaseError.java:566)
	at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:9365)
	at oracle.jdbc.driver.OracleStatementWrapper.executeBatch(OracleStatementWrapper.java:210)
	at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeBatch(NewProxyPreparedStatement.java:1723)
	at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:70)
	at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:268)
	... 60 more

I can infer that object holding the STRUCT representing the SDO_GEOMETRY type must be misplaced when passed by to the PreparedStatement with the INSERT, but I would really like to know if this has anything to do with the way properties are declared or anything else..

Here's the generated SQL statements logged to the console. They seem OK:

18:23:42,873 DEBUG ~

    insert

    into

        gir_annotation

        (caption, color, data_create, private, temp, location, rotation, scale_factor, data_altera, id_user_create, id_user_altera, girid)

    values

        (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

Hibernate:

    insert

    into

        gir_annotation

        (caption, color, data_create, private, temp, location, rotation, scale_factor, data_altera, id_user_create, id_user_altera, girid)

    values

        (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

One more note on this, maybe that's important too, I can successfully manage a model from a simple domain object without any Geometry attributes.

Thanks in advance!

Mendes.

[Pedro Mendes]

Hi again,

I got it to work. I had to modified some of the JPA Column annotations. The "Annotation" model follows below.

Thanks.

Mendes.

import java.util.Date;

import javax.persistence.Column;

import javax.persistence.Entity;

import javax.persistence.GeneratedValue;

import javax.persistence.GenerationType;

import javax.persistence.Id;

import javax.persistence.SequenceGenerator;

import javax.persistence.Table;

import org.hibernate.annotations.Type;

import play.db.jpa.GenericModel;

import com.vividsolutions.jts.geom.Geometry;

@Entity

@Table(name="gir_annotation")

public class Annotation extends GenericModel {

@Id

@GeneratedValue(strategy=GenerationType.SEQUENCE, generator="annotationSequence")

@SequenceGenerator(name="annotationSequence", sequenceName="gir_annotation_seq")

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

public Integer id;

@Column(name="caption", nullable=false, length=512)

public String caption;

@Type(type="org.hibernatespatial.GeometryUserType")

@Column(name="location")

public Geometry location;

@Column(name="color", nullable=false, length=7)

public String color = "#0000FF";

@Column(name="scale_factor", nullable=false, length=4, scale=2)

public Double scaleFactor = 1.0;

@Column(name="rotation", nullable=false, length=9, scale=5)

public Double rotation = 0.0;

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

public Integer userCreate;

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

public Date creationDate = new Date();

@Column(name="private", nullable=false, length=1)

public char isPrivate = 'N';

@Column(name="temp", nullable=false, length=1)