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
View the original post :
http://www.jboss.com/index.html?module=bb&op=viewtopic&p=3935556#3935556
Reply to the post :
http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=3935556
-------------------------------------------------------
This SF.Net email is sponsored by xPML, a groundbreaking scripting language
that extends applications into web and mobile media. Attend the live webcast
and join the prime developer group breaking into this new coding territory!
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=110944&bid=241720&dat=121642
_______________________________________________
JBoss-user mailing list
JBoss-user@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/jboss-user
