1 Reply Latest reply on Mar 20, 2009 6:24 PM by elvisd

Column not found problem

elvisd Mar 20, 2009 1:39 PM

Hi all,

I have a problem using native queries.
I should query a view on my database. The resultset is then manually mapped to a non-@entity tagged pojo.

The view has the following columns:

+--------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| artStoreid | bigint(20) | NO | | 0 | |
| articleId | bigint(20) | NO | | 0 | |
| articleNbr | int(11) | NO | | NULL | |
| articleDescription | varchar(126) | NO | | NULL | |
| storeId | bigint(20) | NO | | 0 | |
| storeCode | varchar(10) | NO | | NULL | |
| storeDescription | varchar(62) | NO | | NULL | |
| siteId | bigint(20) | NO | | 0 | |
+--------------------+--------------+------+-----+---------+-------+

The log message is the following:

18:32:51,212 INFO [OASI] Native query: SELECT artStoreId, articleId, articleNbr, articleDescription, storeId, storeCode, storeDescription, siteId FROM artstoresearch WHERE siteId=3 AND (articleNbr LIKE'%' OR articleDescription LIKE '') order by articleNbr
18:32:51,214 INFO [STDOUT] Hibernate:
 SELECT
 artStoreId,
 articleId,
 articleNbr,
 articleDescription,
 storeId,
 storeCode,
 storeDescription,
 siteId
 FROM
 artstoresearch
 WHERE
 siteId=3
 AND (
 articleNbr LIKE'%'
 OR articleDescription LIKE ''
 )
 order by
 articleNbr
18:32:51,214 INFO [BigIntegerType] could not read column value from result set: id; Column 'id' not found.
18:32:51,215 WARN [JDBCExceptionReporter] SQL Error: 0, SQLState: S0022
18:32:51,215 ERROR [JDBCExceptionReporter] Column 'id' not found.
18:32:51,216 ERROR [OASI] Loading ArticleStoreView records via NativeQuery.
javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
 at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:614)
 at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:76)
 at ch.oasi.xlnet.dao.ArticleStoreViewDao.findByArticleAndSite(ArticleStoreViewDao.java:38)
 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.invokeTarget(MethodInvocation.java:122)
 at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:111)
 at org.jboss.ejb3.EJBContainerInvocationWrapper.invokeNext(EJBContainerInvocationWrapper.java:69)
 at org.jboss.ejb3.interceptors.aop.InterceptorSequencer.invoke(InterceptorSequencer.java:73)
 at org.jboss.ejb3.interceptors.aop.InterceptorSequencer.aroundInvoke(InterceptorSequencer.java:59)
 at sun.reflect.GeneratedMethodAccessor330.invoke(Unknown Source)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
 at java.lang.reflect.Method.invoke(Method.java:597)
 at org.jboss.aop.advice.PerJoinpointAdvice.invoke(PerJoinpointAdvice.java:174)
 at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
 at org.jboss.ejb3.interceptors.aop.InvocationContextInterceptor.fillMethod(InvocationContextInterceptor.java:72)
 at org.jboss.aop.advice.org.jboss.ejb3.interceptors.aop.InvocationContextInterceptor_z_fillMethod_7988008.invoke(InvocationContextInterceptor_z_fillMethod_7988008.java)
 at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
 at org.jboss.ejb3.interceptors.aop.InvocationContextInterceptor.setup(InvocationContextInterceptor.java:88)
 at org.jboss.aop.advice.org.jboss.ejb3.interceptors.aop.InvocationContextInterceptor_z_setup_7988008.invoke(InvocationContextInterceptor_z_setup_7988008.java)
 at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
 at org.jboss.ejb3.connectionmanager.CachedConnectionInterceptor.invoke(CachedConnectionInterceptor.java:62)
 at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
 at org.jboss.ejb3.entity.TransactionScopedEntityManagerInterceptor.invoke(TransactionScopedEntityManagerInterceptor.java:56)
 at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
 at org.jboss.ejb3.AllowedOperationsInterceptor.invoke(AllowedOperationsInterceptor.java:47)
 at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
 at org.jboss.ejb3.tx.NullInterceptor.invoke(NullInterceptor.java:42)
 at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
 at org.jboss.ejb3.stateless.StatelessInstanceInterceptor.invoke(StatelessInstanceInterceptor.java:68)
 at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
 at org.jboss.aspects.tx.TxPolicy.invokeInOurTx(TxPolicy.java:79)
 at org.jboss.aspects.tx.TxInterceptor$Required.invoke(TxInterceptor.java:190)
 at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
 at org.jboss.aspects.tx.TxPropagationInterceptor.invoke(TxPropagationInterceptor.java:76)
 at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
 at org.jboss.ejb3.tx.NullInterceptor.invoke(NullInterceptor.java:42)
 at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
 at org.jboss.ejb3.security.RoleBasedAuthorizationInterceptorv2.invoke(RoleBasedAuthorizationInterceptorv2.java:201)
 at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
 at org.jboss.ejb3.security.Ejb3AuthenticationInterceptorv2.invoke(Ejb3AuthenticationInterceptorv2.java:159)
 at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
 at org.jboss.ejb3.ENCPropagationInterceptor.invoke(ENCPropagationInterceptor.java:41)
 at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
 at org.jboss.ejb3.asynchronous.AsynchronousInterceptor.invoke(AsynchronousInterceptor.java:106)
 at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
 at org.jboss.ejb3.BlockContainerShutdownInterceptor.invoke(BlockContainerShutdownInterceptor.java:65)
 at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
 at org.jboss.aspects.currentinvocation.CurrentInvocationInterceptor.invoke(CurrentInvocationInterceptor.java:67)
 at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:102)
 at org.jboss.ejb3.stateless.StatelessContainer.dynamicInvoke(StatelessContainer.java:486)
 at org.jboss.ejb3.session.InvokableContextClassProxyHack._dynamicInvoke(InvokableContextClassProxyHack.java:56)
 at org.jboss.aop.Dispatcher.invoke(Dispatcher.java:91)
 at org.jboss.aspects.remoting.AOPRemotingInvocationHandler.invoke(AOPRemotingInvocationHandler.java:82)
 at org.jboss.remoting.ServerInvoker.invoke(ServerInvoker.java:908)
 at org.jboss.remoting.transport.socket.ServerThread.completeInvocation(ServerThread.java:742)
 at org.jboss.remoting.transport.socket.ServerThread.processInvocation(ServerThread.java:695)
 at org.jboss.remoting.transport.socket.ServerThread.dorun(ServerThread.java:522)
 at org.jboss.remoting.transport.socket.ServerThread.run(ServerThread.java:230)
Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
 at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:90)
 at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
 at org.hibernate.loader.Loader.doList(Loader.java:2231)
 at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2125)
 at org.hibernate.loader.Loader.list(Loader.java:2120)
 at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:312)
 at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1722)
 at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:165)
 at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:175)
 at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:67)
 ... 59 more
Caused by: java.sql.SQLException: Column 'id' not found.
 at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
 at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
 at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)
 at com.mysql.jdbc.ResultSetImpl.findColumn(ResultSetImpl.java:1136)
 at com.mysql.jdbc.ResultSetImpl.getBigDecimal(ResultSetImpl.java:1406)
 at org.jboss.resource.adapter.jdbc.WrappedResultSet.getBigDecimal(WrappedResultSet.java:320)
 at org.hibernate.type.BigIntegerType.get(BigIntegerType.java:57)
 at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:184)
 at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:210)
 at org.hibernate.loader.custom.CustomLoader$ScalarResultColumnProcessor.extract(CustomLoader.java:497)
 at org.hibernate.loader.custom.CustomLoader$ResultRowProcessor.buildResultRow(CustomLoader.java:443)
 at org.hibernate.loader.custom.CustomLoader.getResultColumnOrRow(CustomLoader.java:340)
 at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:629)
 at org.hibernate.loader.Loader.doQuery(Loader.java:724)
 at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
 at org.hibernate.loader.Loader.doList(Loader.java:2228)
 ... 66 more

1. Re: Column not found problem

elvisd Mar 20, 2009 6:24 PM (in response to elvisd)

Hi have tried out a bit... if I name the first column (artStoreId) as Id than everything works...
Is this linked with the way hibernate builds the collection?
Any workaround? Any hint?
Actions