I'm seeing an "Invalid column name" error when mapping the results of a named native query to an entity with a composite key. The query is retrieving the minimum bounding rectange of an Oracle SDO_GEOMETRY object via an Oracle Spatial function calls. Essentially I get a list of lat/long coordinates ordered by id and using those to construct an internal representation that is used throughout our app.
After some debugging I discovered this "Invalid column name" error is occurring because Hiberate is looking for column "coordina1_3_0_" which isn't returned by the query. I'm using Hibernate 3.6.2, with hibernate-jpa-2.0-api-1.0.0.Final.jar, and Oracle's JDBC driver ojdbc14.jar. I have tried switching to an embeddable composite key but the result was the same. I've got a work around for now (the result set mapping works with a single id, and I just make sure everything is detached), but I'd really like to solve this, so any help is appreciated.
Here is the query (from my orm.xml)
Code:
<named-native-query name="GetOverlayMBRQuery" result-set-mapping="OverlayPointRecord">
<query>
<![CDATA[SELECT o.name name, o.user_stamp user_stamp, o.author author, o.remarks remarks,
t.id id, t.x x, t.y y FROM overlays o,
TABLE(SELECT SDO_UTIL.GETVERTICES(mbr)
FROM (SELECT SDO_AGGR_MBR(shape) mbr
FROM overlays o where o.name = :overlayName)) t where o.name = :overlayName]]>
</query>
<hint name="org.hibernate.readOnly" value="true"/>
</named-native-query>
And the related result set mapping:
Code:
<sql-result-set-mapping name="OverlayPointRecord">
<entity-result entity-class="OverlayPointRecord">
<field-result name="name" column="name"/>
<field-result name="author" column="author"/>
<field-result name="user" column="user_stamp"/>
<field-result name="coordinateId" column="id"/>
<field-result name="longitude" column="X"/>
<field-result name="latitude" column="Y"/>
</entity-result>
</sql-result-set-mapping>
The returned rows look like this:
Code:
NAME USER_STAMP AUTHOR REMARKS ID X Y
blah blah blah some boundary 1 50.0 50.0
blah blah blah some boundary 2 40.0 40.0
The entity class and primary key class:
Code:
@Entity
@IdClass(OverlayPointRecordPK.class)
public class OverlayPointRecord implements Serializable {
private static final long serialVersionUID = -5081444949787232452L;
@Id
private String name;
private String description;
private String author;
private String user;
@Id
private int coordinateId;
private double longitude;
private double latitude;
public OverlayPointRecord() {
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getDescription() {
return description;
}
public void setDescription(String description) {
this.description = description;
}
public String getAuthor() {
return author;
}
public void setAuthor(String author) {
this.author = author;
}
public String getUser() {
return user;
}
public void setUser(String user) {
this.user = user;
}
public int getCoordinateId() {
return coordinateId;
}
public void setCoordinateId(int coordinateId) {
this.coordinateId = coordinateId;
}
public double getLongitude() {
return longitude;
}
public void setLongitude(double longitude) {
this.longitude = longitude;
}
public double getLatitude() {
return latitude;
}
public void setLatitude(double latitude) {
this.latitude = latitude;
}
public static long getSerialversionuid() {
return serialVersionUID;
}
}
Code:
public class OverlayPointRecordPK implements Serializable {
private static final long serialVersionUID = 7857149467579511726L;
protected String name;
protected int coordinateId;
public OverlayPointRecordPK() {
}
public OverlayPointRecordPK(String name, int coordinateId) {
this.name = name;
this.coordinateId = coordinateId;
}
public String getName() {
return name;
}
public int getCoordinateId() {
return coordinateId;
}
// hashCode() and equals()
}
I'm calling the query as follows:
Code:
Query mbrQuery = em.createNamedQuery("GetOverlayMBRQuery");
mbrQuery.setParameter("overlayName", overlayName);
List<OverlayPointRecord> overlayPointRecords = (List<OverlayPointRecord>)mbrQuery.getResultList();
Here is the relevant stack trace:
Code:
javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not execute query
at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1214)
at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1147)
at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:255)
Caused by: org.hibernate.exception.GenericJDBCException: could not execute query
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:140)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:128)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.loader.Loader.doList(Loader.java:2536)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276)
at org.hibernate.loader.Loader.list(Loader.java:2271)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:316)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1842)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:165)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:157)
at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:246)
... 28 more
Caused by: java.sql.SQLException: Invalid column name
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
at oracle.jdbc.driver.OracleStatement.getColumnIndex(OracleStatement.java:3291)
at oracle.jdbc.driver.OracleResultSetImpl.findColumn(OracleResultSetImpl.java:1914)
at oracle.jdbc.driver.OracleResultSet.getInt(OracleResultSet.java:1563)
at com.mchange.v2.c3p0.impl.NewProxyResultSet.getInt(NewProxyResultSet.java:2573)
at org.hibernate.type.descriptor.sql.IntegerTypeDescriptor$2.doExtract(IntegerTypeDescriptor.java:61)
at org.hibernate.type.descriptor.sql.BasicExtractor.extract(BasicExtractor.java:64)
at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:253)
at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:249)
at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:229)
at org.hibernate.type.AbstractStandardBasicType.hydrate(AbstractStandardBasicType.java:330)
at org.hibernate.type.ComponentType.hydrate(ComponentType.java:593)
at org.hibernate.loader.Loader.extractKeysFromResultSet(Loader.java:668)
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:606)
at org.hibernate.loader.Loader.doQuery(Loader.java:829)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274)
at org.hibernate.loader.Loader.doList(Loader.java:2533)
... 35 more
Login
FAQ
Search