CODEWITHSUNDEEP
javamysqlsqlhibernate
Singhal2
Singhal2

Reputation: 450

SQLGrammar exception, coulmn 'Id' not found when executing native query

I have the following tables:

stdqualificationmaster

QualificationId studentRegNo CourseName Percentage

1               1            A           91 
2               1            B           81
3               1            C           71
4               1            D           61
5               2            A           91
6               2            B           81
7               2            C           71
8               2            D           50

testmaster:

TestId  studentRegNo testLevel percentage

1       1            1         91 
2       1            2         81
3       1            3         71
4       2            1         80
5       2            2         99
6       2            3         87

This is my PlacementDaoRdbms class which executes the native SQL query for the 2 tables above:

PlacementDaoRdbms

package com.ms.avalon.master.dao.rdbms;

import java.util.ArrayList;
import java.util.List;
import org.hibernate.SQLQuery;
import org.hibernate.Transaction;
import org.hibernate.criterion.DetachedCriteria;
import com.ms.avalon.master.beans.PlacementCaseBean;
import com.ms.avalon.master.dao.PlacementDao;
import com.ms.avalon.master.formbeans.PlacementFormBean;
import com.ms.avalon.master.pojos.studentpojos.StudentEduPojo;
import com.ms.avalon.master.pojos.studentpojos.StudentTestPojo;

public class PlacementDaoRdbms extends DBConnectionDao implements PlacementDao {

@Override
public List<StudentEduPojo> searchWithCases(PlacementCaseBean caseBean, PlacementFormBean placementBean) {

    System.out.println(session);
    System.out.println(hibernateTemplate);

    DetachedCriteria criteriaE = DetachedCriteria.forClass(StudentEduPojo.class, "qualTable");
    DetachedCriteria criteriaT = DetachedCriteria.forClass(StudentTestPojo.class, "testTable");

    String query = 
            "SELECT stdqualificationmaster.studentRegNo "
            + "FROM "
            + "(SELECT studentRegNo FROM stdqualificationmaster "
            + "WHERE (CourseName = 'High School' AND Percentage >= 90) "
            + "OR (CourseName = 'Intermediate' AND Percentage >= 80) "
            + "OR (CourseName = 'BCA' AND Percentage >= 70) "
            + "OR (CourseName = 'MCA' AND Percentage >= 60) "
            + "GROUP BY studentRegNo "
            + "HAVING COUNT(1) = 4 "
            + ") stdqualificationmaster JOIN "
            + "(SELECT studentRegNo FROM testmaster "
            + "WHERE (testLevel = '1' AND percentage >= 90) "
            + "OR (testLevel = '2' AND percentage >= 80) "
            + "OR (testLevel = '3' AND percentage >= 70) "
            + "GROUP BY studentRegNo "
            + "HAVING COUNT(1) = 3 "
            + ") testmaster ON stdqualificationmaster.studentRegNo = testmaster.studentRegNo;";

    Transaction tx = session.beginTransaction();
    SQLQuery q = session.createSQLQuery(query)
    .addEntity(StudentEduPojo.class);

    List<StudentEduPojo> list = q.list();
    System.out.println(list.size());

    return null;
}

}

Alright, so basically the SQL query searches for studentRegNo which matches the minimum criteria in both the tables (has minimum qualification and test percentages). Once the query executes, following exception is thrown.

Hibernate: SELECT * FROM (SELECT studentRegNo FROM stdqualificationmaster WHERE (CourseName = 'High School' AND Percentage >= 90) OR (CourseName = 'Intermediate' AND Percentage >= 80) OR (CourseName = 'BCA' AND Percentage >= 70) OR (CourseName = 'MCA' AND Percentage >= 60) GROUP BY studentRegNo HAVING COUNT(1) = 4 ) stdqualificationmaster JOIN (SELECT studentRegNo FROM testmaster WHERE (testLevel = '1' AND percentage >= 90) OR (testLevel = '2' AND percentage >= 80) OR (testLevel = '3' AND percentage >= 70) GROUP BY studentRegNo HAVING COUNT(1) = 3 ) testmaster ON stdqualificationmaster.studentRegNo = testmaster.studentRegNo;

May 12, 2016 3:37:50 PM org.apache.catalina.core.StandardWrapperValve invoke SEVERE: Servlet.service() for servlet [dispatcher] in context with path [/Avalon-1.0] threw exception [Request processing failed; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query] with root cause java.sql.SQLException: Column 'QualificationId' 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.getInt(ResultSetImpl.java:2777) at org.apache.commons.dbcp.DelegatingResultSet.getInt(DelegatingResultSet.java:236) at org.hibernate.type.IntegerType.get(IntegerType.java:28) at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:113) at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:102) at org.hibernate.loader.Loader.getKeyFromResultSet(Loader.java:1088) at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:553) at org.hibernate.loader.Loader.doQuery(Loader.java:689) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224) at org.hibernate.loader.Loader.doList(Loader.java:2144) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2028) at org.hibernate.loader.Loader.list(Loader.java:2023) 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:150) at com.ms.avalon.master.dao.rdbms.PlacementDaoRdbms.searchWithCases(PlacementDaoRdbms.java:107) at com.ms.avalon.master.business.TalentCaseHandler.searchForCases(TalentCaseHandler.java:63) at com.ms.avalon.master.service.impl.PlacementServiceImpl.searchForTalent(PlacementServiceImpl.java:31) at com.ms.avalon.master.controllers.PlacementController.searchForTalent(PlacementController.java:29) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) at org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:176) at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:427) at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:415) at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:788) at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:717) at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:644) at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:560) at javax.servlet.http.HttpServlet.service(HttpServlet.java:646) at javax.servlet.http.HttpServlet.service(HttpServlet.java:727) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at com.opensymphony.module.sitemesh.filter.PageFilter.parsePage(PageFilter.java:119) at com.opensymphony.module.sitemesh.filter.PageFilter.doFilter(PageFilter.java:55) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:504) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103) at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:421) at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1074) at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:611) at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:316) at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) at java.lang.Thread.run(Unknown Source)

Now, I am not sure why was it looking for the QualificationId column in the first place and even when it is, why is it not finding it?

Upvotes: 1

Views: 1046

Answers (2)

Thanga
Thanga

Reputation: 8161

This is because your Entity StudentEduPojo has defined the primary key (@Id) as QualificationId. So It searches for that in the search result and didn't find it.

Better you set studentRegNo as Id in the Entity or change your query to return QualificationId.

Note: You have also to do the change said in the other answer by @Joop Eggen (Alias issue with field names)

Upvotes: 1

Joop Eggen
Joop Eggen

Reputation: 109613

My guess is that the aliases play havoc.

Not sure whether 2 GROUP BYs work, but the following seem feasible, maybe even an AND EXISTS( .... s.studentRegNo = studentRegno).

String query = 
        "SELECT studentRegNo FROM stdqualificationmaster s "
        + "WHERE (CourseName = 'High School' AND Percentage >= 90) "
        + "OR (CourseName = 'Intermediate' AND Percentage >= 80) "
        + "OR (CourseName = 'BCA' AND Percentage >= 70) "
        + "OR (CourseName = 'MCA' AND Percentage >= 60) "
        + "GROUP BY studentRegNo "
        + "HAVING COUNT(1) = 4 "
        + "AND "
        + "studentRegNo IN (SELECT studentRegNo FROM testmaster "
        + "WHERE (testLevel = '1' AND percentage >= 90) "
        + "OR (testLevel = '2' AND percentage >= 80) "
        + "OR (testLevel = '3' AND percentage >= 70) "
        + "GROUP BY studentRegNo "
        + "HAVING COUNT(1) = 3 "
        + ")";

Upvotes: 0

Related Questions