Hibernate version:3.0.5
I'm attempting to use sess.createSQLQuery to return a list of SpectralFeatures that meet some spatial constraint (I'm using Oracle spatial). As indicated in the relevant mapping docs (below). SpectralFeature extends AbstractFeature (which in turn extends AbstractE3Element). AbstractFeature contains the spatial information (GEO_SHAPE) used in the where clause. When I attempt to execute the query JDBC throws an "invalid column name" exception. However, if I take the SQL produced by hibernate and execute it directly in SQL plus it works fine and returns the all the fields needed to build the SpectralFeature subclass. Is an SQLQuery the right hibernate query type to use given I'm using joined-subclasses and Oracle Spatial functions?? Alternatives?
Exception & stack trace
Code:
[20-Sep-2006 11:44:01] 101844 [AWT-EventQueue-0] WARN (JDBCExceptionReporter.java:71) - SQL Error: 17006, SQLState: null
[20-Sep-2006 11:44:01] 101844 [AWT-EventQueue-0] ERROR (JDBCExceptionReporter.java:72) - Invalid column name
Exception in thread "AWT-EventQueue-0" gov.llnl.e3.exception.E3DatabaseAccessException: SQL Error while attempting to find AbstractFeature
at gov.llnl.e3.manager.E3Manager.findAbstractFeaturesWithInMBR(E3Manager.java:2196)
at gov.llnl.e3.manager.E3Manager.queryAbstractFeatures(E3Manager.java:2089)
at gov.llnl.e3.ui.frame.QueryFrame$18.run(QueryFrame.java:749)
at java.awt.event.InvocationEvent.dispatch(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)
Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:70)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:1596)
at org.hibernate.loader.Loader.list(Loader.java:1577)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:112)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1414)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:153)
at gov.llnl.e3.manager.E3Manager.findAbstractFeaturesWithInMBR(E3Manager.java:2188)
... 9 more
Caused by: java.sql.SQLException: Invalid column name
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:162)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:227)
at oracle.jdbc.driver.OracleStatement.get_column_index(OracleStatement.java:3086)
at oracle.jdbc.driver.OracleResultSetImpl.findColumn(OracleResultSetImpl.java:1854)
at oracle.jdbc.driver.OracleResultSet.getLong(OracleResultSet.java:1617)
at org.hibernate.type.LongType.get(LongType.java:26)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:77)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:68)
at org.hibernate.loader.Loader.getKeyFromResultSet(Loader.java:759)
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:292)
at org.hibernate.loader.Loader.doQuery(Loader.java:412)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:218)
at org.hibernate.loader.Loader.doList(Loader.java:1593)
... 14 more
createSQLQuery code: Code:
queryString = "SELECT {sf.*}";
queryString += " from Abstract_Feature sf_1_, Spectral_Feature sf ";
queryString += " where sf_1_.id = sf.id AND ";
queryString += " SDO_RELATE(sf_1_.GEO_SHAPE,";
queryString += " SDO_GEOMETRY(2003, 8307, NULL,";
queryString += " SDO_ELEM_INFO_ARRAY(1, 1003, 3),";
queryString += " SDO_ORDINATE_ARRAY(" + minX + "," + minY + "," + maxX + "," + maxY + "))";
queryString += " , 'mask=anyinteract') = 'TRUE'";
retv = session.createSQLQuery(queryString)
.addEntity("sf_1_", gov.llnl.e3.model.AbstractFeature.class)
.addEntity("sf", gov.llnl.e3.model.SpectralFeature.class)
.list();
Relevant Mapping Documents: SPECTRAL_FEATURECode:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<joined-subclass name="gov.llnl.e3.model.SpectralFeature" table="SPECTRAL_FEATURE" extends="gov.llnl.e3.model.AbstractFeature">
<key column="ID"/>
<property name="chemFitPlotURL" column="CHEM_FIT_PLOT_URL"/>
<property name="fStat" column="FSTAT" />
<property name="amplitudeUnits" column="AMPLITUDE_UNITS" />
<property name="coreThreshold" column="CORE_THRESHOLD" />
<set name="amplitudeValues" lazy="true" cascade="all, delete-orphan">
<key column="PARENT_FEATURE_ID"/>
<one-to-many class="gov.llnl.e3.model.MultiPointValue"/>
</set>
</joined-subclass>
</hibernate-mapping>
Code:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<joined-subclass name="gov.llnl.e3.model.AbstractFeature" table="ABSTRACT_FEATURE" extends="gov.llnl.e3.model.AbstractE3Element">
<key column="ID"/>
<set name="MeasuredMaterials" lazy="true" cascade="all, delete-orphan" inverse="true">
<key column="PARENT_FEATURE_ID"/>
<one-to-many class="gov.llnl.e3.model.MeasuredMaterial"/>
</set>
<set name="AnalyticImages" table="FEATURE_IMAGES" lazy="true" cascade="save-update">
<key column="PARENT_FEATURE_ID"/>
<many-to-many class="gov.llnl.e3.model.AbstractImage" column="CHILD_ABSTRACT_IMAGE_ID"/>
</set>
<set name="analystAnnotations" lazy="true" cascade="all, delete-orphan" inverse="true">
<key column="PARENT_FEATURE_ID"/>
<one-to-many class="gov.llnl.e3.model.AnalystAnnotation"/>
</set>
<many-to-one name="e3Type" column="E3_TYPE_ID" not-null="true"/>
<property name="label" not-null="true" column="LABEL"/>
<property name="probababilityOfDetection" column="P_OF_DETECTION"/>
<property name="probababilityOfFalseAlarm" column="P_OF_FALSE_ALARM"/>
<property name="pixelCount" column="PIXEL_COUNT"/>
<property name="geoShape" type="gov.llnl.e3.model.GeometryUserType">
<column name="GEO_SHAPE" sql-type="sdo_geometry"/>
</property>
<property name="pixelShape" type="gov.llnl.e3.model.GeometryUserType">
<column name="PIXEL_SHAPE" sql-type="sdo_geometry"/>
</property>
<many-to-one name="parentAnalysis" column="ANALYSIS_ID"/>
<property name="vetted" column="VETTED"/>
<property name="vettedComment" column="VETTED_COMMENT" length="2048"/>
</joined-subclass>
</hibernate-mapping>
Code:
<?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.AbstractE3Element" table="ABSTRACT_E3_ELEMENT">
<id name="id" column="ID" type="long">
<generator class="native"/>
</id>
</class>
</hibernate-mapping>
Login
FAQ
Search