-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 1 post ] 
  Previous topic | Next topic 
Author Message
phenderson
 Post subject: Native Query GOTCHA
PostPosted: Fri Jan 28, 2011 5:03 am 
Newbie

Joined: Sat Aug 30, 2003 5:46 am
Posts: 5
I thought I would post here a problem I've just spent a while figuring out.

The setup is Hibernate 3.6, JPA, Postgresql 8.4

I have a native query which looks like

Code:
SELECT
  p.project_id,
  p.project_ref,
  p.project_name,
  p.description,
  (SELECT u.full_name FROM users AS u WHERE u.user_id=p.project_manager_user_id),
  p.status,
  p.customer_id,
  (SELECT c.customer_name FROM customer_details AS c WHERE c.customer_detail_id=p.customer_id),
  p.budget,
  p.credit_limit,
  (SELECT a.balance FROM accounts AS a WHERE a.account_id=p.finance_account_id),
---  BLAH BLAH REST OF QUERY



Notice I am using 3 sub selects to pull out human friendly strings and a number.

When running this Hibernate barfs big time (see stack trace below) on the getResultList() call.

Code:
    Query q = em.createNativeQuery(sql);
    // set parameters..
    List results = q.getResultList();


I did some digging around and found (to my horror) that Hibernate is requesting result set fields by name!
(Postgresql returns a column name of "?column?" for unnamed columns)

So when it was time to read the big decimal account balance sub select value Hibernate was actually reading
the 1st sub select value as all subselect column names are the same.

The really worrying thing is that if all the sub selects had the same type Hibernate would silently fail and read the wrong
field.

The solution, add an alias to the SQL so the result set column names are unique. Which still leaves me worrying that it would be all too easy to miss one.
Of course the real solution would be to read the result set columns by column number, but I guess that would be a big change to the code base.


All the best

Peter Henderson.




Reference Stack trace.

Code:
javax.persistence.PersistenceException: org.hibernate.exception.DataException: could not execute query
   at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1214)
   at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1147)
   at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:255)
   at com.starjar.starjarenterprise5.domain.dao2.DaoBaseV2.getRecords(DaoBaseV2.java:303)
   at com.starjar.engine.ProjectApi.findProjects(ProjectApi.java:61)
   at com.starjar.engine.ProjectApi$$EnhancerByCGLIB$$222d0696.CGLIB$findProjects$3(<generated>)
   at com.starjar.engine.ProjectApi$$EnhancerByCGLIB$$222d0696$$FastClassByCGLIB$$52689b00.invoke(<generated>)
   at net.sf.cglib.proxy.MethodProxy.invokeSuper(MethodProxy.java:167)
   at com.starjar.server.remote_impl.MyMethodInterceptor.intercept(MyMethodInterceptor.java:25)
   at com.starjar.engine.ProjectApi$$EnhancerByCGLIB$$222d0696.findProjects(<generated>)
   at com.starjar.server.remote_impl.ProjectService.findProjects(ProjectService.java:106)
   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 com.starjar.server.rpc.StarjarInvocationServlet.invokeImpl(StarjarInvocationServlet.java:282)
   at com.starjar.server.rpc.StarjarInvocationServlet.invoke(StarjarInvocationServlet.java:180)
   at com.starjar.server.rpc.StarjarInvocationServlet.processRequest(StarjarInvocationServlet.java:97)
   at com.starjar.server.rpc.StarjarInvocationServlet.doPost(StarjarInvocationServlet.java:128)
   at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
   at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
   at com.starjar.server.StarjarGwtFilter.doFilter(StarjarGwtFilter.java:63)
   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
   at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
   at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
   at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:558)
   at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
   at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
   at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
   at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
   at org.apache.coyote.http11.Http11NioProcessor.process(Http11NioProcessor.java:883)
   at org.apache.coyote.http11.Http11NioProtocol$Http11ConnectionHandler.process(Http11NioProtocol.java:721)
   at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:2258)
   at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
   at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
   at java.lang.Thread.run(Thread.java:662)
Caused by: org.hibernate.exception.DataException: could not execute query
   at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:102)
   at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
   at org.hibernate.loader.Loader.doList(Loader.java:2536)
   at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276)
   at org.hibernate.loader.Loader.list(Loader.java:2271)
   at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:316)
   at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1842)
   at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:165)
   at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:157)
   at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:246)
   ... 36 more
Caused by: org.postgresql.util.PSQLException: Bad value for type BigDecimal : Jeremy Edwards
   at org.postgresql.jdbc2.AbstractJdbc2ResultSet.toBigDecimal(AbstractJdbc2ResultSet.java:2756)
   at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getBigDecimal(AbstractJdbc2ResultSet.java:2177)
   at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getBigDecimal(AbstractJdbc2ResultSet.java:325)
   at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getBigDecimal(AbstractJdbc2ResultSet.java:331)
   at org.apache.tomcat.dbcp.dbcp.DelegatingResultSet.getBigDecimal(DelegatingResultSet.java:305)
   at org.hibernate.type.descriptor.sql.DecimalTypeDescriptor$2.doExtract(DecimalTypeDescriptor.java:62)
   at org.hibernate.type.descriptor.sql.BasicExtractor.extract(BasicExtractor.java:64)
   at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:253)
   at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:249)
   at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:234)
   at org.hibernate.loader.custom.CustomLoader$ScalarResultColumnProcessor.extract(CustomLoader.java:505)
   at org.hibernate.loader.custom.CustomLoader$ResultRowProcessor.buildResultRow(CustomLoader.java:451)
   at org.hibernate.loader.custom.CustomLoader.getResultColumnOrRow(CustomLoader.java:348)
   at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:639)
   at org.hibernate.loader.Loader.doQuery(Loader.java:829)
   at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274)
   at org.hibernate.loader.Loader.doList(Loader.java:2533)
   ... 43 more


java.lang.RuntimeException: java.lang.reflect.InvocationTargetException
   at com.starjar.server.rpc.StarjarInvocationServlet.invokeImpl(StarjarInvocationServlet.java:302)
   at com.starjar.server.rpc.StarjarInvocationServlet.invoke(StarjarInvocationServlet.java:180)
   at com.starjar.server.rpc.StarjarInvocationServlet.processRequest(StarjarInvocationServlet.java:97)
   at com.starjar.server.rpc.StarjarInvocationServlet.doPost(StarjarInvocationServlet.java:128)
   at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
   at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
   at com.starjar.server.StarjarGwtFilter.doFilter(StarjarGwtFilter.java:63)
   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
   at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
   at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
   at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:558)
   at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
   at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
   at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
   at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
   at org.apache.coyote.http11.Http11NioProcessor.process(Http11NioProcessor.java:883)
   at org.apache.coyote.http11.Http11NioProtocol$Http11ConnectionHandler.process(Http11NioProtocol.java:721)
   at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:2258)
   at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
   at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
   at java.lang.Thread.run(Thread.java:662)
Caused by: java.lang.reflect.InvocationTargetException
   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 com.starjar.server.rpc.StarjarInvocationServlet.invokeImpl(StarjarInvocationServlet.java:282)
   ... 23 more
Caused by: javax.persistence.PersistenceException: org.hibernate.exception.DataException: could not execute query
   at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1214)
   at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1147)
   at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:255)
   at com.starjar.starjarenterprise5.domain.dao2.DaoBaseV2.getRecords(DaoBaseV2.java:303)
   at com.starjar.engine.ProjectApi.findProjects(ProjectApi.java:61)
   at com.starjar.engine.ProjectApi$$EnhancerByCGLIB$$222d0696.CGLIB$findProjects$3(<generated>)
   at com.starjar.engine.ProjectApi$$EnhancerByCGLIB$$222d0696$$FastClassByCGLIB$$52689b00.invoke(<generated>)
   at net.sf.cglib.proxy.MethodProxy.invokeSuper(MethodProxy.java:167)
   at com.starjar.server.remote_impl.MyMethodInterceptor.intercept(MyMethodInterceptor.java:25)
   at com.starjar.engine.ProjectApi$$EnhancerByCGLIB$$222d0696.findProjects(<generated>)
   at com.starjar.server.remote_impl.ProjectService.findProjects(ProjectService.java:106)
   ... 28 more
Caused by: org.hibernate.exception.DataException: could not execute query
   at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:102)
   at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
   at org.hibernate.loader.Loader.doList(Loader.java:2536)
   at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276)
   at org.hibernate.loader.Loader.list(Loader.java:2271)
   at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:316)
   at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1842)
   at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:165)
   at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:157)
   at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:246)
   ... 36 more
Caused by: org.postgresql.util.PSQLException: Bad value for type BigDecimal : Some Persons Name Here
   at org.postgresql.jdbc2.AbstractJdbc2ResultSet.toBigDecimal(AbstractJdbc2ResultSet.java:2756)
   at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getBigDecimal(AbstractJdbc2ResultSet.java:2177)
   at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getBigDecimal(AbstractJdbc2ResultSet.java:325)
   at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getBigDecimal(AbstractJdbc2ResultSet.java:331)
   at org.apache.tomcat.dbcp.dbcp.DelegatingResultSet.getBigDecimal(DelegatingResultSet.java:305)
   at org.hibernate.type.descriptor.sql.DecimalTypeDescriptor$2.doExtract(DecimalTypeDescriptor.java:62)
   at org.hibernate.type.descriptor.sql.BasicExtractor.extract(BasicExtractor.java:64)
   at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:253)
   at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:249)
   at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:234)
   at org.hibernate.loader.custom.CustomLoader$ScalarResultColumnProcessor.extract(CustomLoader.java:505)
   at org.hibernate.loader.custom.CustomLoader$ResultRowProcessor.buildResultRow(CustomLoader.java:451)
   at org.hibernate.loader.custom.CustomLoader.getResultColumnOrRow(CustomLoader.java:348)
   at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:639)
   at org.hibernate.loader.Loader.doQuery(Loader.java:829)
   at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274)
   at org.hibernate.loader.Loader.doList(Loader.java:2533)
   ... 43 more
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 1 post ] 

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.