Native SQLQuey combined with addEntity forces to retrieve all columns

Description

Hello,
I think it is rather a feature after looking inside Hibernate Code but since I didn't find anything about it in the Docs Chapter 16 (http://www.hibernate.org/hib_docs/reference/en/html/querysql.html)

The problem is that If you do not specify <alias>.* in a Native SQL Query it will fail.
To be clear, it seems that when using addEntity or <return alias="..." /> you cannot retrieve only the column you are interested in. Instead addScalar or <return-scalar must be used.
I think the documentation shoud explicitely indicate it or the feature shoud be developped since it would be rather a great feature when native SQL has to be used.

Hibernate Mapping:
Attached file

<sql-query name="getInfoComplEcom">
<return alias="acv" class="com.kingfisher.chrgtecom.dataobject.ecom.DoArtCondVente" />
<![CDATA[
SELECT
theAcv.CARTICLE as {acv.carticle},
theAcv.CESCLAVE as {acv.cesclave},
theAcv.LARTICLE as {acv.larticle},
theAcv.LARTICLE_RED as {acv.larticleRed}
FROM ART_COND_VENTE theAcv
WHERE theAcv.CARTICLE in (:codeArticles)
]]>
</sql-query>

Java code:
// codeArticles is a List of Integer Ids
Session session = getCurrentSession();
Query query = session.getNamedQuery("getInfoComplEcom");
query.setParameterList("codeArticles",codeArticles);
List result = query.list();
return result;

Missing column is :TMODIF6_0_ , alias column of tmodif property, not in Native Query
Stack:
ERROR:http-8080-Processor4-org.hibernate.util.JDBCExceptionReporter-Nom de colonne non valide
ERROR:http-8080-Processor4-com.foo.action.ChargerProduitAction-Development error for URI :'/chrgtecom/main/chargerProduit.do'
org.hibernate.exception.GenericJDBCException: could not execute query
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
at org.hibernate.exception.JDBCExceptionHelper.convert (JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2223)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
at org.hibernate.loader.Loader.list(Loader.java :2099)
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 com.foo.dao.EcomDao.getInfoComplEcom(EcomDao.java:112)
at com.foo.services.EcomServices.getInfoComplEcom (EcomServices.java:20)
at com.foo.services.EcomServices$$FastClassByCGLIB$$5dc5078d.invoke(<generated>)
at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:149)
at org.springframework.aop.framework.Cglib2AopProxy$CglibMethodInvocation.invokeJoinpoint (Cglib2AopProxy.java:695)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:144)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke (TransactionInterceptor.java:107)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:166)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke (TransactionInterceptor.java:107)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:166)
at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept (Cglib2AopProxy.java:630)
at com.foo.services.EcomServices$$EnhancerByCGLIB$$4ffbebb6.getInfoComplEcom(<generated>)
at com.foo.services.AgileServices.getInfosCompl(AgileServices.java :131)
at com.foo.services.AgileServices.prepareInfosCompl(AgileServices.java:152)
at com.foo.services.AgileServices$$FastClassByCGLIB$$b720e887.invoke(<generated>)
at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:149)
at org.springframework.aop.framework.Cglib2AopProxy$CglibMethodInvocation.invokeJoinpoint(Cglib2AopProxy.java:695)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed (ReflectiveMethodInvocation.java:144)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:107)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed (ReflectiveMethodInvocation.java:166)
at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:630)
at com.foo.services.AgileServices$$EnhancerByCGLIB$$61330582.prepareInfosCompl (<generated>)
at com.foo.action.ChargerProduitAction.process(ChargerProduitAction.java:122)
at com.ubikingenierie.fwk.action.GenericAction.execute(GenericAction.java:113)
at org.springframework.web.struts.DelegatingActionProxy.execute (DelegatingActionProxy.java:110)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:431)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:236)
at com.ubikingenierie.fwk.struts.processor.FwkRequestProcessor.process(FwkRequestProcessor.java:142)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1196)
at org.apache.struts.action.ActionServlet.doPost (ActionServlet.java:432)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter (ApplicationFilterChain.java:199)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:145)
at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java :63)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:75)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:165)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:145)
at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:265)
at org.acegisecurity.intercept.web.FilterSecurityInterceptor.invoke (FilterSecurityInterceptor.java:107)
at org.acegisecurity.intercept.web.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:72)
at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter (FilterChainProxy.java:275)
at org.acegisecurity.ui.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:110)
at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java :275)
at com.ubikingenierie.fwk.timeout.LoggedOnApplicationFilter.doFilter(LoggedOnApplicationFilter.java:101)
at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
at org.acegisecurity.providers.anonymous.AnonymousProcessingFilter.doFilter(AnonymousProcessingFilter.java:125)
at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
at org.acegisecurity.ui.AbstractProcessingFilter.doFilter(AbstractProcessingFilter.java:229)
at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
at org.acegisecurity.ui.logout.LogoutFilter.doFilter (LogoutFilter.java:106)
at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
at org.acegisecurity.context.HttpSessionContextIntegrationFilter.doFilter(HttpSessionContextIntegrationFilter.java :286)
at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
at org.acegisecurity.securechannel.ChannelProcessingFilter.doFilter(ChannelProcessingFilter.java:138)
at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
at org.acegisecurity.util.FilterChainProxy.doFilter(FilterChainProxy.java:149)
at org.acegisecurity.util.FilterToBeanProxy.doFilter (FilterToBeanProxy.java:98)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:165)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java :145)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:210)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:596)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:433)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:955)
at org.apache.catalina.core.StandardContextValve.invoke (StandardContextValve.java:139)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:596)
at org.apache.catalina.valves.CertificatesValve.invoke(CertificatesValve.java :198)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:594)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:433)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:955)
at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2460)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java :133)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:596)
at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:119)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:594)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext (StandardPipeline.java:594)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:545)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java :594)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:433)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:955)
at org.apache.catalina.core.StandardEngineValve.invoke (StandardEngineValve.java:127)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:596)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java :433)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:955)
at org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:157)
at org.apache.coyote.http11.Http11Processor.process (Http11Processor.java:874)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:665)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java :528)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:81)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:685)
at java.lang.Thread.run (Thread.java:534)
Caused by: java.sql.SQLException: Nom de colonne non valide
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
at oracle.jdbc.driver.DatabaseError.throwSqlException (DatabaseError.java:162)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:227)
at oracle.jdbc.driver.OracleStatement.get_column_index(OracleStatement.java:3060)
at oracle.jdbc.driver.OracleResultSetImpl.findColumn (OracleResultSetImpl.java:1861)
at org.apache.commons.dbcp.DelegatingResultSet.findColumn(DelegatingResultSet.java:292)
at org.hibernate.jdbc.ColumnNameCache.getIndexForColumnName(ColumnNameCache.java:28)
at org.hibernate.jdbc.ResultSetWrapper.findColumn(ResultSetWrapper.java:61)
at org.hibernate.jdbc.ResultSetWrapper.getString(ResultSetWrapper.java:225)
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.AbstractType.hydrate(AbstractType.java :81)
at org.hibernate.persister.entity.AbstractEntityPersister.hydrate(AbstractEntityPersister.java:2096)
at org.hibernate.loader.Loader.loadFromResultSet(Loader.java:1380)
at org.hibernate.loader.Loader.instanceNotYetLoaded (Loader.java:1308)
at org.hibernate.loader.Loader.getRow(Loader.java:1206)
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:580)
at org.hibernate.loader.Loader.doQuery(Loader.java:701)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
at org.hibernate.loader.Loader.doList(Loader.java:2220)
... 98 more

Hope this is not a duplicate, I searched for a bug or in the forum.
Philippe
http://www.ubik-ingenierie.com

Environment

Hibernate 3.2.5, All Databases

Activity

Show:
Max Rydahl Andersen
November 29, 2007, 10:21 AM

From the docs:
"If the entity is mapped with a many-to-one to another entity it is required to also return this when performing the native query, otherwise a database specific "column not found" error will occur. The additional columns will automatically be returned when using the * notation, but we prefer to be explicit as in the following example for a many-to-one to a Dog:"

"Native sql queries which query for entities that is mapped as part of an inheritance must include all properties for the baseclass and all it subclasses."

I guess the last one there could be rephrased to remove the inheritance part since it is also applicable for base classes.

And yes that code works. Why would you ever want an entity to be partial ? then it is not an entity!

Philippe Mouawad
November 29, 2007, 8:04 PM

M Rydhal Andersen,
I try as a user of Hibernate to contribute useful proposals, I (and 4 other colleagues of mine) met this problem after carefully reading the documentation,.
We all understood it this way, so In My Humble Opinion IT IS NOT CLEAR.

Concerning your question:
Why would you ever want an entity to be partial ? then it is not an entity!

Well, since I use a Native Query, notably for performance reasons in this case, I kind of short circuit Beautiful Object Mapping.
And I only want to get the fields I will use, consider a Batch that uses Native Query API to get 5 Million lines from Database.
What in your mind is the Impact of getting useless data from Database ? considering IO/ Network and Memory usage ?

I agree there is a way with scalar or Classic Java Oject (Not ENtity), but getting it this way is far better considering usability.
BUT IT IS ONLY MY HUMBLE OPINION.

Philippe.

Emmanuel Bernard
November 29, 2007, 8:37 PM

you consider your solution far better, we consider it far too wrong And Hibernate has nice solutions for your problem through the use of scalar and result transformers.
And please don't yell / use caps, it hurts in the morning, especially when they are supposed to be humbled opinions.

Philippe Mouawad
November 30, 2007, 3:01 PM

I see we are on serious argument, so my answer will be of this kind:

Our Father (Hibernate Master), who art in heaven (of Object Mapping),
hallowed be thy name (You're simply the best, better than all the rest
Better than anyone, anyone I've ever met ...);
thy kingdom come;
thy will be done on earth as it is in heaven.
Give us this day our daily bread;
and forgive us our trespasses (I dared to emit a little criticism, SHALL I BE DAMNED forever ? )
as we forgive those who trespass against us;
and lead us not into temptation (of using poor SQL),
but deliver us from evil (which leads us to emit a poor little complaint about API).

For the kingdom, the power, and the glory are yours
now and for ever.

Amen.

And to be serious, thank you for your helpful help ,
as you didn't read, I already know about scalar, and since I read
Chapter 16 as my Bible, I also know about Transformers ,<resultset>...

ADIOS AMIGOs

Max Rydahl Andersen
November 30, 2007, 3:23 PM

Philippe - if you think something is unclear in the docs and you already have learned the answer then please contribute the patch to the docs that will make it clear that an entity cannot be loaded from a sqlquery if you don't return everything since then it is:

a) not an entity
b) not possible to use safely in all the operations on the session
c) not an entity

...

Assignee

Unassigned

Reporter

Philippe Mouawad

Fix versions

None

Labels

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Priority

Major
Configure