JPA/Hibernate Native Queries not working as per requirement

Question

i am not understanding where the problem lies as the following query is executing as per requirement in MYSQL Workbench.

Below is my NativeQuery function in JPA

public ConsumerAggregatedDTO getCustomerEnrolled(Long businessAccountId, String paramName, String screenName,String status) {
    ConsumerAggregatedDTO consumersEnrolled = null;
    try {
        String query = "select count(distinct consumer_id) from Consumer_Business_Acct_Mapping t1 join "
                + "Dim_Date t2 on date(t1.created_date)=t2.full_date where t1.business_acct_id= :business_account_id and t1.status= :status and t2.full_date between"
                + "(select str_to_date(lower_bound,'%Y-%m-%d') from Param_App where business_account_id= :business_account_id and screen_name= :screen_name and param_name= :param_name and status= :status) "
                + "and (select str_to_date(upper_bound,'%Y-%m-%d') from Param_App where business_account_id= :business_account_id and screen_name= :screen_name and param_name= :param_name and status= :status)";

        consumersEnrolled = (ConsumerAggregatedDTO) entityManager.createNativeQuery(query, ConsumerBusinessAccount.class)
                .setParameter("business_account_id", businessAccountId)
                .setParameter("param_name", paramName)
                .setParameter("screen_name",screenName)
                .setParameter("status", status)
                .getSingleResult();
    }
    // catch(EmptyResultDataAccessException ex)
    catch (javax.persistence.NoResultException ex) {
        // No entity found for query
        return null;
    }

    return consumersEnrolled;
}

and when in run the code it get the error column 'id' is not found and throws a Java SQLException. Below is the stackTrace.

java.sql.SQLException: Column 'id' not found.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:963) ~[mysql-connector-java-5.1.39.jar:5.1.39]
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:896) ~[mysql-connector-java-5.1.39.jar:5.1.39]
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:885) ~[mysql-connector-java-5.1.39.jar:5.1.39]
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860) ~[mysql-connector-java-5.1.39.jar:5.1.39]
at com.mysql.jdbc.ResultSetImpl.findColumn(ResultSetImpl.java:1077) ~[mysql-connector-java-5.1.39.jar:5.1.39]
at com.mysql.jdbc.ResultSetImpl.getLong(ResultSetImpl.java:2759) ~[mysql-connector-java-5.1.39.jar:5.1.39]
at org.hibernate.type.descriptor.sql.BigIntTypeDescriptor$2.doExtract(BigIntTypeDescriptor.java:63) ~[hibernate-core-5.0.9.Final.jar:5.0.9.Final]
at org.hibernate.type.descriptor.sql.BasicExtractor.extract(BasicExtractor.java:47) ~[hibernate-core-5.0.9.Final.jar:5.0.9.Final]
at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:238) ~[hibernate-core-5.0.9.Final.jar:5.0.9.Final]
at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:234) ~[hibernate-core-5.0.9.Final.jar:5.0.9.Final]
at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:224) ~[hibernate-core-5.0.9.Final.jar:5.0.9.Final]
at org.hibernate.type.AbstractStandardBasicType.hydrate(AbstractStandardBasicType.java:300) ~[hibernate-core-5.0.9.Final.jar:5.0.9.Final]
at org.hibernate.loader.Loader.extractKeysFromResultSet(Loader.java:789) ~[hibernate-core-5.0.9.Final.jar:5.0.9.Final]
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:714) ~[hibernate-core-5.0.9.Final.jar:5.0.9.Final]
at org.hibernate.loader.Loader.processResultSet(Loader.java:972) ~[hibernate-core-5.0.9.Final.jar:5.0.9.Final]
at org.hibernate.loader.Loader.doQuery(Loader.java:930) ~[hibernate-core-5.0.9.Final.jar:5.0.9.Final]
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:336) ~[hibernate-core-5.0.9.Final.jar:5.0.9.Final]
at org.hibernate.loader.Loader.doList(Loader.java:2617) ~[hibernate-core-5.0.9.Final.jar:5.0.9.Final]
at org.hibernate.loader.Loader.doList(Loader.java:2600) ~[hibernate-core-5.0.9.Final.jar:5.0.9.Final]
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2429) ~[hibernate-core-5.0.9.Final.jar:5.0.9.Final]
at org.hibernate.loader.Loader.list(Loader.java:2424) ~[hibernate-core-5.0.9.Final.jar:5.0.9.Final]
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:336) ~[hibernate-core-5.0.9.Final.jar:5.0.9.Final]
at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:1967) ~[hibernate-core-5.0.9.Final.jar:5.0.9.Final]
at org.hibernate.internal.AbstractSessionImpl.list(AbstractSessionImpl.java:322) ~[hibernate-core-5.0.9.Final.jar:5.0.9.Final]
at org.hibernate.internal.SQLQueryImpl.list(SQLQueryImpl.java:125) ~[hibernate-core-5.0.9.Final.jar:5.0.9.Final]
at org.hibernate.jpa.internal.QueryImpl.list(QueryImpl.java:606) ~[hibernate-entitymanager-5.0.9.Final.jar:5.0.9.Final]
at org.hibernate.jpa.internal.QueryImpl.getSingleResult(QueryImpl.java:529) ~[hibernate-entitymanager-5.0.9.Final.jar:5.0.9.Final]
at io.pika.data.ConsumerDao.getCustomerEnrolled(ConsumerDao.java:195) ~[classes/:na]
at io.pika.data.ConsumerDao$$FastClassBySpringCGLIB$$9be9f38f.invoke(<generated>) ~[classes/:na]
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) ~[spring-core-4.3.2.RELEASE.jar:4.3.2.RELEASE]
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:720) ~[spring-aop-4.3.2.RELEASE.jar:4.3.2.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) ~[spring-aop-4.3.2.RELEASE.jar:4.3.2.RELEASE]
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136) ~[spring-tx-4.3.2.RELEASE.jar:4.3.2.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.2.RELEASE.jar:4.3.2.RELEASE]
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99) ~[spring-tx-4.3.2.RELEASE.jar:4.3.2.RELEASE]
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:280) ~[spring-tx-4.3.2.RELEASE.jar:4.3.2.RELEASE]
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96) ~[spring-tx-4.3.2.RELEASE.jar:4.3.2.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.2.RELEASE.jar:4.3.2.RELEASE]
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:655) ~[spring-aop-4.3.2.RELEASE.jar:4.3.2.RELEASE]
at io.pika.data.ConsumerDao$$EnhancerBySpringCGLIB$$bcfbceca.getCustomerEnrolled(<generated>) ~[classes/:na]
at io.pika.service.webapi.OverviewApiService.getOverviewDetails(OverviewApiService.java:57) ~[classes/:na]
at io.pika.web.Controllers.webapi.OverviewController.getOverviewDetails(OverviewController.java:34) ~[classes/:na]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_101]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_101]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_101]
at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_101]
at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:221) ~[spring-web-4.3.2.RELEASE.jar:4.3.2.RELEASE]
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:136) ~[spring-web-4.3.2.RELEASE.jar:4.3.2.RELEASE]
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:114) ~[spring-webmvc-4.3.2.RELEASE.jar:4.3.2.RELEASE]
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827) ~[spring-webmvc-4.3.2.RELEASE.jar:4.3.2.RELEASE]
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738) ~[spring-webmvc-4.3.2.RELEASE.jar:4.3.2.RELEASE]
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85) ~[spring-webmvc-4.3.2.RELEASE.jar:4.3.2.RELEASE]
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:963) ~[spring-webmvc-4.3.2.RELEASE.jar:4.3.2.RELEASE]
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:897) ~[spring-webmvc-4.3.2.RELEASE.jar:4.3.2.RELEASE]
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970) ~[spring-webmvc-4.3.2.RELEASE.jar:4.3.2.RELEASE]
at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:872) ~[spring-webmvc-4.3.2.RELEASE.jar:4.3.2.RELEASE]
at javax.servlet.http.HttpServlet.service(HttpServlet.java:648) ~[tomcat-embed-core-8.5.4.jar:8.5.4]
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846) ~[spring-webmvc-4.3.2.RELEASE.jar:4.3.2.RELEASE]
at javax.servlet.http.HttpServlet.service(HttpServlet.java:729) ~[tomcat-embed-core-8.5.4.jar:8.5.4]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:230) ~[tomcat-embed-core-8.5.4.jar:8.5.4]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165) ~[tomcat-embed-core-8.5.4.jar:8.5.4]
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) ~[tomcat-embed-websocket-8.5.4.jar:8.5.4]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:192) ~[tomcat-embed-core-8.5.4.jar:8.5.4]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165) ~[tomcat-embed-core-8.5.4.jar:8.5.4]
at io.pika.web.Filters.CorsFilter.doFilter(CorsFilter.java:22) ~[classes/:na]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:192) ~[tomcat-embed-core-8.5.4.jar:8.5.4]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165) ~[tomcat-embed-core-8.5.4.jar:8.5.4]
at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:208) ~[spring-security-web-4.1.1.RELEASE.jar:4.1.1.RELEASE]
at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:177) ~[spring-security-web-4.1.1.RELEASE.jar:4.1.1.RELEASE]
at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:346) ~[spring-web-4.3.2.RELEASE.jar:4.3.2.RELEASE]
at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:262) ~[spring-web-4.3.2.RELEASE.jar:4.3.2.RELEASE]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:192) ~[tomcat-embed-core-8.5.4.jar:8.5.4]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165) ~[tomcat-embed-core-8.5.4.jar:8.5.4]
at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:99) ~[spring-web-4.3.2.RELEASE.jar:4.3.2.RELEASE]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-4.3.2.RELEASE.jar:4.3.2.RELEASE]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:192) ~[tomcat-embed-core-8.5.4.jar:8.5.4]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165) ~[tomcat-embed-core-8.5.4.jar:8.5.4]
at org.springframework.web.filter.HttpPutFormContentFilter.doFilterInternal(HttpPutFormContentFilter.java:87) ~[spring-web-4.3.2.RELEASE.jar:4.3.2.RELEASE]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-4.3.2.RELEASE.jar:4.3.2.RELEASE]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:192) ~[tomcat-embed-core-8.5.4.jar:8.5.4]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165) ~[tomcat-embed-core-8.5.4.jar:8.5.4]
at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:77) ~[spring-web-4.3.2.RELEASE.jar:4.3.2.RELEASE]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-4.3.2.RELEASE.jar:4.3.2.RELEASE]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:192) ~[tomcat-embed-core-8.5.4.jar:8.5.4]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165) ~[tomcat-embed-core-8.5.4.jar:8.5.4]
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:197) ~[spring-web-4.3.2.RELEASE.jar:4.3.2.RELEASE]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-4.3.2.RELEASE.jar:4.3.2.RELEASE]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:192) ~[tomcat-embed-core-8.5.4.jar:8.5.4]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165) ~[tomcat-embed-core-8.5.4.jar:8.5.4]
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:198) ~[tomcat-embed-core-8.5.4.jar:8.5.4]
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:108) [tomcat-embed-core-8.5.4.jar:8.5.4]
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:522) [tomcat-embed-core-8.5.4.jar:8.5.4]
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140) [tomcat-embed-core-8.5.4.jar:8.5.4]
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79) [tomcat-embed-core-8.5.4.jar:8.5.4]
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87) [tomcat-embed-core-8.5.4.jar:8.5.4]
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:349) [tomcat-embed-core-8.5.4.jar:8.5.4]
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:1110) [tomcat-embed-core-8.5.4.jar:8.5.4]
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66) [tomcat-embed-core-8.5.4.jar:8.5.4]
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:785) [tomcat-embed-core-8.5.4.jar:8.5.4]
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1425) [tomcat-embed-core-8.5.4.jar:8.5.4]
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) [tomcat-embed-core-8.5.4.jar:8.5.4]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [na:1.8.0_101]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [na:1.8.0_101]
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) [tomcat-embed-core-8.5.4.jar:8.5.4]
at java.lang.Thread.run(Thread.java:745) [na:1.8.0_101]

I am not able to comprehend where the problem exactly lies.If someone can show the error or the method in which i have made a mistake.

post what SQL actually is invoked by your JPA provider (in its log) — Neil Stockton, Jan 19 '17 at 15:04

score 2 · Accepted Answer · answered Jan 19 '17 at 14:07

2

Your query returns a COUNT(*), but you're telling JPA to map it to a ConsumerAggregatedDTO object.

Instead of

consumersEnrolled = (ConsumerAggregatedDTO) entityManager.createNativeQuery(query, ConsumerBusinessAccount.class)

use

BigInteger count = (BigInteger)entityManager.createNativeQuery(query)

and manually create the ConsumerAggregatedDTO object with the resulting value.

answered Jan 19 '17 at 14:07

Kayaman

72,141
5
83
121

sure, but that doesn't explain why MySQL throws an exception of course – Neil Stockton Jan 19 '17 at 18:22
@NeilStockton Actually it does. You can see that Hibernate is trying `Loader.extractKeysFromResultSet`. It's desperately trying to get the key for an entity, but the query doesn't return an entity. It's not MySQL's fault that Hibernate is asking for the value of a non existing column in the result. – Kayaman Jan 19 '17 at 19:24

score 0 · Answer 2 · answered Jan 19 '17 at 14:03

0

Your query seems to be wrong on SQL side. Just set up your JPA to spit out SQL before it is sent to dababase, and debug it with a tool of your choice

answered Jan 19 '17 at 14:03

Konstantin Pribluda

12,329
1
30
35

There's nothing wrong with the query. Hibernate is just asking for a column that wasn't returned (`id`) and that's when MySQL throws an exception. – Kayaman Jan 19 '17 at 19:35
This is native query, and exception comes from database. HIbernate does not even kick in to asking about returned columns - query is wrong on SQL side – Konstantin Pribluda Jan 20 '17 at 08:24
It's clearly visible from the stacktrace. `BigIntTypeDescriptor$2.doExtract` (Hibernate) calls `ResultSet.getLong("id");` at which point the exception is thrown (by the MySQL driver). The query is executed correctly, but the (attempted) mapping of the query results to a `ConsumerAggregatedDTO` fails. – Kayaman Jan 20 '17 at 08:26
I guess reading stack traces is a lost art these days, considering you claim that Hibernate isn't asking for the columns. – Kayaman Jan 20 '17 at 08:34

JPA/Hibernate Native Queries not working as per requirement

2 Answers2