java - SQLGrammarexception，执行 native 查询时未找到列 'Id'

Question

我有以下表格:

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

考官:

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

这是我的 PlacementDaoRdbms 类，它为上面的 2 个表执行 native SQL 查询:

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;
}

}

好吧，基本上 SQL 查询会搜索符合两个表中最低标准的 studentRegNo(具有最低资格和测试百分比)。查询执行后，将抛出以下异常。

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)

现在，我不确定为什么它首先要查找 QualificationId 列，即使找到了，为什么找不到它？

Answer 1

这是因为您的实体 StudentEduPojo 已将主键 (@Id) 定义为 QualificationId。所以它在搜索结果中搜索它但没有找到它。

最好将 studentRegNo 设置为实体中的 Id，或者更改查询以返回 QualificationId。

注意:您还必须按照@Joop Eggen(字段名称的别名问题)在另一个答案中所说的进行更改