Query not returning a result though data is in database

Question

In a Spring MVC application using Hibernate and jpa, the following jpa query is throwing a no result error, even when I have confirmed that there is a result in the DrugWords table and a set of rxcui values for the chosen DrugWord in the rxnconsojunction table:

@SuppressWarnings("unchecked")
public DrugWord findDrugWord(String wrd) {
    System.out.println("..... wrd is: "+wrd);//prints a value with valid records in database
    Query query = this.em.createQuery("SELECT word FROM DrugWord word left join fetch word.concepts WHERE word.name =:wrd");
    query.setParameter("wrd", wrd);
//        try{
        return (DrugWord) query.getSingleResult();
//          }
//        catch(Exception e){}
//        return new DrugWord();
}

Here is the complete stack trace:

Hibernate: select drugword0_.name as name1_12_0_, drugconcep2_.rxcui as rxcui1_10_1_, concepts1_.word as word1_12_0__, concepts1_.rxcui as rxcui2_13_0__ from drugwords drugword0_ left outer join drugwordsconsoJunction concepts1_ on drugword0_.name=concepts1_.word left outer join drugconcepts drugconcep2_ on concepts1_.rxcui=drugconcep2_.rxcui where drugword0_.name=?
WARN  warn - Handler execution resulted in exception
org.springframework.dao.EmptyResultDataAccessException: No entity found for query; nested exception is javax.persistence.NoResultException: No entity found for query
    at org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:304) ~[spring-orm-3.2.5.RELEASE.jar:3.2.5.RELEASE]
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:108) ~[spring-orm-3.2.5.RELEASE.jar:3.2.5.RELEASE]
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:403) ~[spring-orm-3.2.5.RELEASE.jar:3.2.5.RELEASE]
    at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:58) ~[spring-tx-3.2.5.RELEASE.jar:3.2.5.RELEASE]
    at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213) ~[spring-tx-3.2.5.RELEASE.jar:3.2.5.RELEASE]
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:163) ~[spring-tx-3.2.5.RELEASE.jar:3.2.5.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) ~[spring-aop-3.2.5.RELEASE.jar:3.2.5.RELEASE]
    at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:80) ~[spring-aop-3.2.5.RELEASE.jar:3.2.5.RELEASE]
    at org.springframework.samples.knowledgemanager.util.CallMonitoringAspect.invoke(CallMonitoringAspect.java:80) ~[CallMonitoringAspect.class:na]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.6.0_29]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) ~[na:1.6.0_29]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) ~[na:1.6.0_29]
    at java.lang.reflect.Method.invoke(Method.java:597) ~[na:1.6.0_29]
    at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:621) ~[spring-aop-3.2.5.RELEASE.jar:3.2.5.RELEASE]
    at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:610) ~[spring-aop-3.2.5.RELEASE.jar:3.2.5.RELEASE]
    at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:65) ~[spring-aop-3.2.5.RELEASE.jar:3.2.5.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) ~[spring-aop-3.2.5.RELEASE.jar:3.2.5.RELEASE]
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:91) ~[spring-aop-3.2.5.RELEASE.jar:3.2.5.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) ~[spring-aop-3.2.5.RELEASE.jar:3.2.5.RELEASE]
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204) ~[spring-aop-3.2.5.RELEASE.jar:3.2.5.RELEASE]
    at $Proxy48.findDrugWord(Unknown Source) ~[na:na]
    at org.springframework.samples.knowledgemanager.service.ClinicServiceImpl.findDrugWord(ClinicServiceImpl.java:284) ~[ClinicServiceImpl.class:na]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.6.0_29]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) ~[na:1.6.0_29]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) ~[na:1.6.0_29]
    at java.lang.reflect.Method.invoke(Method.java:597) ~[na:1.6.0_29]
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317) ~[spring-aop-3.2.5.RELEASE.jar:3.2.5.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183) ~[spring-aop-3.2.5.RELEASE.jar:3.2.5.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150) ~[spring-aop-3.2.5.RELEASE.jar:3.2.5.RELEASE]
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:96) ~[spring-tx-3.2.5.RELEASE.jar:3.2.5.RELEASE]
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:260) ~[spring-tx-3.2.5.RELEASE.jar:3.2.5.RELEASE]
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:94) ~[spring-tx-3.2.5.RELEASE.jar:3.2.5.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) ~[spring-aop-3.2.5.RELEASE.jar:3.2.5.RELEASE]
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204) ~[spring-aop-3.2.5.RELEASE.jar:3.2.5.RELEASE]
    at $Proxy50.findDrugWord(Unknown Source) ~[na:na]
    at org.springframework.samples.knowledgemanager.web.MedicationController.processFindForm(MedicationController.java:84) ~[MedicationController.class:na]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.6.0_29]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) ~[na:1.6.0_29]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) ~[na:1.6.0_29]
    at java.lang.reflect.Method.invoke(Method.java:597) ~[na:1.6.0_29]
    at org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:219) ~[spring-web-3.2.5.RELEASE.jar:3.2.5.RELEASE]
    at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132) ~[spring-web-3.2.5.RELEASE.jar:3.2.5.RELEASE]
    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104) ~[spring-webmvc-3.2.5.RELEASE.jar:3.2.5.RELEASE]
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:745) ~[spring-webmvc-3.2.5.RELEASE.jar:3.2.5.RELEASE]
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:686) ~[spring-webmvc-3.2.5.RELEASE.jar:3.2.5.RELEASE]
    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80) ~[spring-webmvc-3.2.5.RELEASE.jar:3.2.5.RELEASE]
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:925) [spring-webmvc-3.2.5.RELEASE.jar:3.2.5.RELEASE]
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:856) [spring-webmvc-3.2.5.RELEASE.jar:3.2.5.RELEASE]
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:936) [spring-webmvc-3.2.5.RELEASE.jar:3.2.5.RELEASE]
    at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:827) [spring-webmvc-3.2.5.RELEASE.jar:3.2.5.RELEASE]
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:621) [servlet-api.jar:na]
    at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:812) [spring-webmvc-3.2.5.RELEASE.jar:3.2.5.RELEASE]
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:728) [servlet-api.jar:na]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305) [catalina.jar:7.0.42]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) [catalina.jar:7.0.42]
    at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:77) [spring-web-3.2.5.RELEASE.jar:3.2.5.RELEASE]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-3.2.5.RELEASE.jar:3.2.5.RELEASE]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243) [catalina.jar:7.0.42]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) [catalina.jar:7.0.42]
    at com.github.dandelion.datatables.core.web.filter.DatatablesFilter.doFilter(DatatablesFilter.java:73) [datatables-core-0.9.2.jar:na]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243) [catalina.jar:7.0.42]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) [catalina.jar:7.0.42]
    at com.github.dandelion.datatables.extras.servlet2.filter.DatatablesFilter.doFilter(DatatablesFilter.java:71) [datatables-servlet2-0.9.2.jar:na]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243) [catalina.jar:7.0.42]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) [catalina.jar:7.0.42]
    at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88) [spring-web-3.2.5.RELEASE.jar:3.2.5.RELEASE]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-3.2.5.RELEASE.jar:3.2.5.RELEASE]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243) [catalina.jar:7.0.42]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) [catalina.jar:7.0.42]
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222) [catalina.jar:7.0.42]
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123) [catalina.jar:7.0.42]
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502) [catalina.jar:7.0.42]
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171) [catalina.jar:7.0.42]
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99) [catalina.jar:7.0.42]
    at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:953) [catalina.jar:7.0.42]
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118) [catalina.jar:7.0.42]
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408) [catalina.jar:7.0.42]
    at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1023) [tomcat-coyote.jar:7.0.42]
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:589) [tomcat-coyote.jar:7.0.42]
    at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:310) [tomcat-coyote.jar:7.0.42]
    at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886) [na:1.6.0_29]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908) [na:1.6.0_29]
    at java.lang.Thread.run(Thread.java:662) [na:1.6.0_29]
Caused by: javax.persistence.NoResultException: No entity found for query
    at org.hibernate.ejb.QueryImpl.getSingleResult(QueryImpl.java:286) ~[hibernate-entitymanager-4.2.1.Final.jar:4.2.1.Final]
    at org.springframework.samples.knowledgemanager.repository.jpa.JpaMedicationRepositoryImpl.findDrugWord(JpaMedicationRepositoryImpl.java:75) ~[JpaMedicationRepositoryImpl.class:na]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.6.0_29]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) ~[na:1.6.0_29]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) ~[na:1.6.0_29]
    at java.lang.reflect.Method.invoke(Method.java:597) ~[na:1.6.0_29]
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317) ~[spring-aop-3.2.5.RELEASE.jar:3.2.5.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183) ~[spring-aop-3.2.5.RELEASE.jar:3.2.5.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150) ~[spring-aop-3.2.5.RELEASE.jar:3.2.5.RELEASE]
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:155) ~[spring-tx-3.2.5.RELEASE.jar:3.2.5.RELEASE]
    ... 77 common frames omitted

Note

Changing the query to be em.find(DrugWord.class, wrd) identified a different, unrelated error, which I then resolved. Now it is showing that the concepts collection for the selected DrugWord, even though there is data in the underlying table.

I assume you are using wrd + "%", for like operation and not =. Also why is left join needed in the query, I don't see that being retrieved or used in where clause. — Gayathri, Mar 24 '14 at 20:56
@Gayathri Thank you for looking into this. I just removed `+ "%"`. However, the `concepts` List will be called from a jsp as `chosen_word.concepts`, which will output a list of `rxcui` values associated with the chosen `word` in the junction table in my SQL DDL code above. — CodeMed, Mar 24 '14 at 21:02
check out these links http://stackoverflow.com/questions/8138458/javax-persistence-noresultexception-no-entity-found-for-query , http://lists.jboss.org/pipermail/jboss-user/2007-October.txt , http://stackoverflow.com/questions/2002993/jpa-getsingleresult-or-null , — z atef, Mar 24 '14 at 21:03
I saw the fetch type for concept as EAGER. So, i think it should be available with word.concepts No need of explicitly fetching that — Gayathri, Mar 24 '14 at 21:04
@NullSoulException I read those links before posting. Those links address situations when there is no data in the tables that should match the query. My posting is about a situation where there is data in the database, and in my case the query is not pulling the data that does exist. — CodeMed, Mar 24 '14 at 21:07
@Gayathri Thank you again for looking into this. I just deleted the left join from the query and relaunched the app, but the same error gets thrown, so the left join issue seems to not be related to the problem. Here is what it looks like now: `Query query = this.em.createQuery("SELECT word FROM DrugWord word WHERE word.name =:wrd");` Do you have any other suggestions? — CodeMed, Mar 24 '14 at 21:10
No No, left join is definitely not causing the issue, I meant it wasn't necessary in the query. Removing % should have solved the problem. If not, Can you please post data and the value of word being passed. Also can you verify they are exactly same? Can you enable showSql and see what result you get when you run that on your DB? — Gayathri, Mar 24 '14 at 21:14
You can also use, em.find(DrugWord.class,wrd), since you are looking by primary key. — Gayathri, Mar 24 '14 at 21:21
@Gayathri I just added the info you requested to my original posting above. Data and code for populating database with data. One word that fails is abacavir, but I cant get the module to load in general because of these errors, so I think that every word is failing. I don't think the problem is just that one word. — CodeMed, Mar 24 '14 at 21:26
@Gayathri I tried `em.find(DrugWord.class, wrd)` and got a different error message. I posted a link to the complete stack trace of the new error at the bottom of my original posting above. — CodeMed, Mar 24 '14 at 21:33
@Gayathri Did I provide all the information that you need to find the problem? — CodeMed, Mar 24 '14 at 22:08
Yes! I think with find the exception is in your JSP not the service. looks like you are trying to get Concepts as String where as Concepts is a collection. Can you try to print the result before using in service? — Gayathri, Mar 24 '14 at 22:11
@Gayathri Here is the line in the jsp that calls concepts: ` — CodeMed, Mar 24 '14 at 22:14
@Gayathri I resolved the string error by fixing a line in the controller that populates `sel_word` with a `DrugWord` object. But then when I do a `system.out.println()` for the size of `sel_word.getConcepts().getSize()`, it prints out size 0, which means that the `concepts` collection has not been populated by the query. Any ideas? — CodeMed, Mar 24 '14 at 22:23
There was a hibernate generated sql query in log you removed, Can you execute that directly on your DB and see if you get results. That is the final query executed. If that doesn't return results, means there is issue in the data you have in your tables. — Gayathri, Mar 24 '14 at 22:41
@Gayathri I just added the error log back in to the end of my original posting. I have executed the queries in my database and they do return results. I also posted the data and database population code in my posting above so that you can easily recreate this if you have MySQL on your computer. Any other ideas? — CodeMed, Mar 24 '14 at 22:50
select drugword0_.name as name1_12_1_, concepts1_.word as word1_12_3_, drugconcep2_.rxcui as rxcui2_13_3_, drugconcep2_.rxcui as rxcui1_10_0_ from drugwords drugword0_ left outer join drugwordsconsoJunction concepts1_ on drugword0_.name=concepts1_.word left outer join drugconcepts drugconcep2_ on concepts1_.rxcui=drugconcep2_.rxcui where drugword0_.name='abacavir' what is the result of this query? — Gayathri, Mar 25 '14 at 01:26
@Gayathri Now it is returning `null`. I am not sure why. You asked for all of the code and data to be able to recreate this on your machine. I posted all of that above. Are you willing to try this on your machine? That would be a lot faster than going back and forth, especially with the time zone difference. — CodeMed, Mar 25 '14 at 02:47

score 1 · Answer 1 · edited Feb 09 '23 at 21:43

1

The problem might be you forgot to declare @entity on top of entity class or otherwise in configuration hbm2ddl might be create instead of update.

edited Feb 09 '23 at 21:43

halfer

19,824
17
99
186

answered Mar 24 '14 at 22:46

user2710786

41
5

Which entity are you saying does not have the `at_Entity` annotation? When I look at the links in the code in my original posting above, I see `at_Entity` at the top of each entity's code. – CodeMed Mar 24 '14 at 22:54

Query not returning a result though data is in database

Note

1 Answers1