1 Reply Latest reply on Apr 6, 2006 1:17 PM by bill.burke

    @SqlResultSetMapping - column not found problem

    scotttam

      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