I'm simply trying to map a native query to a single entity but am having no luck. I'm using JBoss 4.0.5.GA w/ Hibernate 3.2.2, Anno. 3.2.2 (svn), and EM 3.2.1. I've tried reverting to stock JBoss 4.0.5 but the issue remains.
The entity w/ native query:
Code:
@Entity
@Table(name="pub.\"ord-traf\"")
@NamedNativeQuery(
name="orderTrafficQuery",
query="select max(ot.\"load-no\") as loadNo, max(ot.\"truck-no\") as truckNo, " +
"max(ot.\"trailer-no\") as trailerNo, max(ot.\"carrier-no\") as carrierNo, " +
"max(ot.\"ship-date\") as shipDate, max(ot.\"spec-instr\") as specInstr, " +
"max(ot.\"est-freight\") as estFreight, max(ot.\"act-freight\") as actFreight, " +
"max(ot.\"lock-load\") as lockLoad, max(v.\"Name\") as vendorName, " +
"count(otd.\"order-no\") as customerCount " +
"from pub.\"ord-traf\" ot " +
"left join pub.\"vendor\" v on v.\"Vendor-code\" = ot.\"carrier-no\" " +
"left join pub.\"ord-traf-d\" otd on otd.\"load-no\" = ot.\"load-no\" " +
"where ot.\"ship-date\" = ? " +
"group by ot.\"carrier-no\"",
resultSetMapping="orderTrafficResult")
@SqlResultSetMapping(name="orderTrafficResult", entities={
@EntityResult(entityClass=OrderTraffic.class, fields={
@FieldResult(name="loadNo", column="loadNo"),
@FieldResult(name="truckNo", column="truckNo"),
@FieldResult(name="trailerNo", column="trailerNo"),
@FieldResult(name="carrierNo", column="carrierNo"),
@FieldResult(name="shipDate", column="shipDate"),
@FieldResult(name="specInstr", column="specInstr"),
@FieldResult(name="estFreight", column="estFreight"),
@FieldResult(name="actFreight", column="actFreight"),
@FieldResult(name="lockLoad", column="lockLoad"),
@FieldResult(name="vendorName", column="vendorName"),
@FieldResult(name="customerCount", column="customerCount")})})
public class OrderTraffic implements Serializable
{
@Id
@Column(name="`load-no`", nullable=false)
private String loadNo;
@Column(name="`truck-no`")
private String truckNo;
@Column(name="`trailer-no`")
private String trailerNo;
@Column(name="`carrier-no`")
private String carrierNo;
@Column(name="`ship-date`")
@Temporal(TemporalType.DATE)
private Date shipDate;
@Column(name="`spec-instr`")
private String specInstr;
@Column(name="`est-freight`")
private BigDecimal estFreight;
@Column(name="`act-freight`")
private BigDecimal actFreight;
@Column(name="`lock-load`")
private Boolean lockLoad;
@Transient
private String vendorName;
@Transient
private Integer customerCount;
.......................................
}
...queried like so:
Code:
public List<OrderTraffic> getOrderTraffic(Date shipDate)
{
Query q = this.em.createNamedQuery("orderTrafficQuery");
q.setParameter(1, shipDate);
return q.getResultList();
}
...throws an exception:
Code:
14:42:42,342 INFO [STDOUT] Hibernate:
/* named native SQL query orderTrafficQuery */ select
max(ot."load-no") as loadNo,
max(ot."truck-no") as truckNo,
max(ot."trailer-no") as trailerNo,
max(ot."carrier-no") as carrierNo,
max(ot."ship-date") as shipDate,
max(ot."spec-instr") as specInstr,
max(ot."est-freight") as estFreight,
max(ot."act-freight") as actFreight,
max(ot."lock-load") as lockLoad,
max(v."Name") as vendorName,
count(otd."order-no") as customerCount
from
pub."ord-traf" ot
left join
pub."vendor" v
on v."Vendor-code" = ot."carrier-no"
left join
pub."ord-traf-d" otd
on otd."load-no" = ot."load-no"
where
ot."ship-date" = ?
group by
ot."carrier-no"
14:42:42,633 INFO [StringType] could not read column value from result set: carrier4_562_0_; [DataDirect][OpenEdge JDBC Driver]Invalid column name: carrier4_562_0_
14:42:42,733 WARN [JDBCExceptionReporter] SQL Error: 0, SQLState: 42S22
14:42:42,733 ERROR [JDBCExceptionReporter] [DataDirect][OpenEdge JDBC Driver]Invalid column name: carrier4_562_0_
14:42:42,813 ERROR [[Test]] Servlet.service() for servlet Test threw exception
javax.ejb.EJBException: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
at org.jboss.ejb3.tx.Ejb3TxPolicy.handleExceptionInOurTx(Ejb3TxPolicy.java:69)
at org.jboss.aspects.tx.TxPolicy.invokeInOurTx(TxPolicy.java:83)
at org.jboss.aspects.tx.TxInterceptor$Required.invoke(TxInterceptor.java:191)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.aspects.tx.TxPropagationInterceptor.invoke(TxPropagationInterceptor.java:76)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.ejb3.stateless.StatelessInstanceInterceptor.invoke(StatelessInstanceInterceptor.java:62)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.aspects.security.AuthenticationInterceptor.invoke(AuthenticationInterceptor.java:77)
at org.jboss.ejb3.security.Ejb3AuthenticationInterceptor.invoke(Ejb3AuthenticationInterceptor.java:102)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.ejb3.ENCPropagationInterceptor.invoke(ENCPropagationInterceptor.java:47)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.ejb3.asynchronous.AsynchronousInterceptor.invoke(AsynchronousInterceptor.java:106)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.ejb3.stateless.StatelessContainer.localInvoke(StatelessContainer.java:211)
at org.jboss.ejb3.stateless.StatelessLocalProxy.invoke(StatelessLocalProxy.java:79)
at $Proxy1027.getOrderTraffic(Unknown Source)
at com.myapp.portlets.ui.servlet.Test.doGet(Test.java:48)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:697)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:96)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:175)
at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:74)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
at org.jboss.web.tomcat.tc5.jca.CachedConnectionValve.invoke(CachedConnectionValve.java:156)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:664)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
at org.apache.tomcat.util.net.MasterSlaveWorkerThread.run(MasterSlaveWorkerThread.java:112)
at java.lang.Thread.run(Thread.java:619)
Caused by: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:647)
at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:73)
at com.myapp.session.OrderBean.getOrderTraffic(OrderBean.java:56)
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.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:112)
at org.jboss.ejb3.interceptor.InvocationContextImpl.proceed(InvocationContextImpl.java:166)
at org.jboss.ejb3.interceptor.EJB3InterceptorsInterceptor.invoke(EJB3InterceptorsInterceptor.java:63)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.ejb3.entity.TransactionScopedEntityManagerInterceptor.invoke(TransactionScopedEntityManagerInterceptor.java:54)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.ejb3.AllowedOperationsInterceptor.invoke(AllowedOperationsInterceptor.java:46)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
at org.jboss.aspects.tx.TxPolicy.invokeInOurTx(TxPolicy.java:79)
... 38 more
Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2214)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2095)
at org.hibernate.loader.Loader.list(Loader.java:2090)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:289)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1695)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:152)
at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:64)
... 52 more
Caused by: java.sql.SQLException: [DataDirect][OpenEdge JDBC Driver]Invalid column name: carrier4_562_0_
at com.ddtek.jdbc.base.BaseExceptions.createException(Unknown Source)
at com.ddtek.jdbc.base.BaseExceptions.getException(Unknown Source)
at com.ddtek.jdbc.base.BaseResultSet.getColumnOrdinal(Unknown Source)
at com.ddtek.jdbc.base.BaseResultSet.getString(Unknown Source)
at org.jboss.resource.adapter.jdbc.WrappedResultSet.getString(WrappedResultSet.java:880)
at org.hibernate.type.StringType.get(StringType.java:18)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:163)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:154)
at org.hibernate.type.ManyToOneType.hydrate(ManyToOneType.java:103)
at org.hibernate.persister.entity.AbstractEntityPersister.hydrate(AbstractEntityPersister.java:2092)
at org.hibernate.loader.Loader.loadFromResultSet(Loader.java:1371)
at org.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:1299)
at org.hibernate.loader.Loader.getRow(Loader.java:1197)
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:568)
at org.hibernate.loader.Loader.doQuery(Loader.java:689)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
at org.hibernate.loader.Loader.doList(Loader.java:2211)
... 59 more
However if I take the raw query generated below and run it against the db, it works just fine:
Code:
select
max(ot."load-no") as loadNo,
max(ot."truck-no") as truckNo,
max(ot."trailer-no") as trailerNo,
max(ot."carrier-no") as carrierNo,
max(ot."ship-date") as shipDate,
max(ot."spec-instr") as specInstr,
max(ot."est-freight") as estFreight,
max(ot."act-freight") as actFreight,
max(ot."lock-load") as lockLoad,
max(v."Name") as vendorName,
count(otd."order-no") as customerCount
from
pub."ord-traf" ot
left join
pub."vendor" v
on v."Vendor-code" = ot."carrier-no"
left join
pub."ord-traf-d" otd
on otd."load-no" = ot."load-no"
where
ot."ship-date" = '1/10/2007'
group by
ot."carrier-no"
I've tried leaving out the last two columns (which are transient in the entity) and I've also tried the query w/o aggregation...it fails w/ the same exception regardless.
I *also* tried removing the carrier-no field from the query and the entity since that's the one that's having an issue in the case above. However, it just picks another field to complain about.
Any ideas?
Thanks!
EDIT
I was able to reproduce this on a small sample project that runs against MSSQL 2000 - with or without quotes this does not work...I get the same exception w/ the strange table alias name.
Login
FAQ
Search
