1 Reply Latest reply: Apr 6, 2006 1:17 PM by Bill Burke RSS

@SqlResultSetMapping - column not found problem

Scott Tamosunas Newbie

I am trying to write a direct SQL query for effeciency and running into a problem with the SqlResultSetMapping annotation. Just to get started, I am trying a simple join on 2 tables: Here's what I have:

Relation:

Campaign has a 1 to many association with Account

table defs look like (abbr):

CREATE TABLE `campaign` (
 `id` int(10) unsigned NOT NULL auto_increment,
 `name` varchar(64) NOT NULL default 'unknown',
 `account_id` int(10) unsigned default NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `account` (
 `id` int(10) unsigned NOT NULL auto_increment,
 `name` varchar(64) NOT NULL default 'unknown',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



The SqlResultSetMapping on my Campaign POJO:

@Entity
@Table(name="campaign")
@SqlResultSetMapping(name="GetCampaignStats", entities = {
 @EntityResult(name="com.mycompany.par.Campaign", fields = {
 @FieldResult(name="name", column="name"),
 @FieldResult(name="accountId", column="account_id")
 }),
 @EntityResult(name="com.mycomapny.par.Account", fields = {
 @FieldResult(name="name", column="name"),
 @FieldResult(name="id", column="id")
 })
 }
)


The SQL I am trying to execute in my Stateless Session Bean:


 Query q = em.createNativeQuery("select campaign.name, account.name from campaign campaign, account account where campaign.account_id = account.id", "GetCampaignStats");
 List l = q.getResultList();



Each time I try to run this query, I get the following error. This query works fine from a sql window. Not sure what I am missing.


12:56:49,671 INFO [STDOUT] Hibernate: select campaign.name, account.name from campaign campaign, account account where campaign.account_id = account.id

12:56:49,718 WARN [JDBCExceptionReporter] SQL Error: 0, SQLState: S0022
12:56:49,718 ERROR [JDBCExceptionReporter] Column 'id' not found.

Caused by: javax.ejb.EJBException: 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:192)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:98)
at org.jboss.aspects.tx.TxPropagationInterceptor.invoke(TxPropagationInterceptor.java:76)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:98)
at org.jboss.ejb3.stateless.StatelessInstanceInterceptor.invoke(StatelessInstanceInterceptor.java:54)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:98)
at org.jboss.aspects.security.AuthenticationInterceptor.invoke(AuthenticationInterceptor.java:78)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:98)
at org.jboss.ejb3.ENCPropagationInterceptor.invoke(ENCPropagationInterceptor.java:47)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:98)
at org.jboss.ejb3.asynchronous.AsynchronousInterceptor.invoke(AsynchronousInterceptor.java:106)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:98)
at org.jboss.ejb3.stateless.StatelessContainer.localInvoke(StatelessContainer.java:178)
at org.jboss.ejb3.stateless.StatelessLocalProxy.invoke(StatelessLocalProxy.java:74)
at $Proxy83.test(Unknown Source)
at com.opus3media.web.CampaignBean.createNewCampaign(CampaignBean.java:129)
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:585)
at org.apache.myfaces.el.MethodBindingImpl.invoke(MethodBindingImpl.java:129)
... 33 more
Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:65)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2148)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029)
at org.hibernate.loader.Loader.list(Loader.java:2024)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:111)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1674)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:147)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:164)
at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:39)
at com.opus3media.ejb.AppManagerBean.test(AppManagerBean.java:229)
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:585)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:109)
at org.jboss.ejb3.AllowedOperationsInterceptor.invoke(AllowedOperationsInterceptor.java:47)
at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:98)
at org.jboss.aspects.tx.TxPolicy.invokeInOurTx(TxPolicy.java:79)
... 54 more
Caused by: java.sql.SQLException: Column 'id' not found.
at com.mysql.jdbc.ResultSet.findColumn(ResultSet.java:2317)
at com.mysql.jdbc.ResultSet.getInt(ResultSet.java:1287)
at org.jboss.resource.adapter.jdbc.WrappedResultSet.getInt(WrappedResultSet.java:690)
at org.hibernate.type.IntegerType.get(IntegerType.java:28)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:113)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:102)
at org.hibernate.loader.Loader.getKeyFromResultSet(Loader.java:1088)
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:554)
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:2145)
... 70 more