java - 当我从数据库表中获取特定列时，在代码中出现 java.sql.SQLException

Question

我正在尝试使用createSQLQuery从数据库中获取特定列。此查询还使用外连接和内连接。问题是当我尝试将该查询分配到列表中时，它显示 SQLException。下面是代码:

Session session = sessionFactory.openSession();
    List<EmployeeLeaveRecord> empList=new ArrayList<EmployeeLeaveRecord>();
    SQLQuery query=session.createSQLQuery("SELECT a.id,a.applied_date,a.employee_id,d.name as EmpName,d.middlename as EmpMiddleName,d.lastname as EmpLastName,"
            + "c.leaveCategory,a.from_date,a.to_date,a.manager_status,a.approver_Id,b.name as ApproverFirstName,b.middlename as ApproverMidName,"
            + "b.lastname as ApproverLastName,a.num_of_leaves FROM lms_employee_leave_records a left outer join lms_employee b "
            + "on a.approver_Id=b.employee_id inner join lms_leave_category c on a.leave_category_id=c.id inner join lms_employee d "
            + "on a.employee_id=d.employee_id where a.employee_status !='CANCELLED' order by a.applied_date desc;");

    query.addEntity(EmployeeLeaveRecord.class);
    empList = query.list();
    System.out.println("AFTR------------emplist size = "+empList.size());
    for (Iterator iterator = empList.iterator(); iterator.hasNext();) {
            EmployeeLeaveRecord employeeLeaveRecord = (EmployeeLeaveRecord) iterator.next();

            System.out.println("No. of leaves are:"+employeeLeaveRecord.getNumOfLeaves());

    }

    session.close();
    return empList ;

这是一个异常(exception):

Oct 07, 2016 7:11:43 PM org.hibernate.util.JDBCExceptionReporter logExceptions
WARNING: SQL Error: 0, SQLState: S0022
Oct 07, 2016 7:11:43 PM org.hibernate.util.JDBCExceptionReporter logEx``ceptions
SEVERE: Column 'emp_reason' not found.
Oct 07, 2016 7:11:43 PM org.apache.catalina.core.StandardWrapperValve invoke
SEVERE: Servlet.service() for servlet [DigiLMS] in context with path [/DigiLMS] threw exception [Request processing failed; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query] with root cause
java.sql.SQLException: Column 'emp_reason' not found.
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1078)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:920)
    at com.mysql.jdbc.ResultSetImpl.findColumn(ResultSetImpl.java:1163)
    at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5729)

这是我的 EmployeeLeaveRecord 类文件:

        public EmployeeLeaveRecord(EmployeeStatus empStatus, Date fromDate, Date toDate,
            LeaveCategory leaveCategory, ManagerStatus managerStatus, Date appliedDate, Employee employee,Employee approver,/* String name,*/
            String source, Date managerApprovalDate, String empReason, float numOfLeaves) {
        super();
        this.empStatus = empStatus;
        this.fromDate = fromDate;
        this.toDate = toDate;
        this.leaveCategory = leaveCategory;
        this.managerStatus = managerStatus;
        this.appliedDate = appliedDate;
        this.employee = employee;
        //this.name = name;
        this.source = source;
        this.managerApprovalDate = managerApprovalDate;
        this.empReason = empReason;
        this.numOfLeaves = numOfLeaves;
        this.approver = approver;
    }

Answer 1

您的查询在其投影中不包含 emp_reason 列。由于您的对象构造函数期望如此，因此 Hibernate 在调用构造函数时会尝试在结果集中找到该列(因此您会收到该异常)。将 emp_reason 添加到查询中的 select 语句应该可以修复它。