Spring Boot中MySQL语法错误如何修复？附报错与代码示例

错误原因 #

你开启了nativeQuery = true执行原生MySQL SQL，但同时在SQL中使用了HQL特有的NEW com.emp.employeeMangement.api.DTO.ResponseDTO(...)语法——MySQL原生SQL无法识别Java类的实例化逻辑，因此在该位置抛出SQL语法错误。

修复方案 #

方案一：改用HQL语法（关闭原生查询） #

将nativeQuery = true改为nativeQuery = false，使用HQL查询以支持NEW DTO实例化语法，但需注意HQL不支持MySQL用户变量（如@per_day），需调整计算逻辑：

@Query(value = "select NEW com.emp.employeeMangement.api.DTO.ResponseDTO(" +
        "emp.empName, " +
        "emp.gender, " +
        "emp.email, " +
        "emp.empCode, " +
        "att.noOfAbsent, " +
        "att.noOfPresent, " +
        "att.month, " +
        "att.noOfDaysInMonth, " +
        "sal.salAmount, " +
        "round(sal.salAmount / att.noOfDaysInMonth), " +
        "round(att.noOfPresent * (sal.salAmount / att.noOfDaysInMonth)) " +
        ") " +
        "from Employee emp " +
        "join Attendence att on emp.emp_id = att.cp_fk " +
        "left join Salary sal on sal.emp_id_fk = emp.emp_id")
List<ResponseDTO> getInfoInExcel();

若不想重复计算sal.salAmount/att.noOfDaysInMonth，可在ResponseDTO中新增构造函数，传入基础字段后在DTO内部计算派生值：

// ResponseDTO新增构造函数
public ResponseDTO(String empName, String gender, String email, String empCode,
                   Integer noOfAbsent, Integer noOfPresent, String month, Integer noOfDaysInMonth,
                   BigDecimal salAmount) {
    this.empName = empName;
    this.gender = gender;
    this.email = email;
    this.empCode = empCode;
    this.noOfAbsent = noOfAbsent;
    this.noOfPresent = noOfPresent;
    this.month = month;
    this.noOfDaysInMonth = noOfDaysInMonth;
    this.salAmount = salAmount;
    this.salperday = salAmount.divide(new BigDecimal(noOfDaysInMonth), RoundingMode.HALF_UP);
    this.Salarycurrentmonth = salperday.multiply(new BigDecimal(noOfPresent));
}

对应简化后的HQL：

@Query(value = "select NEW com.emp.employeeMangement.api.DTO.ResponseDTO(" +
        "emp.empName, " +
        "emp.gender, " +
        "emp.email, " +
        "emp.empCode, " +
        "att.noOfAbsent, " +
        "att.noOfPresent, " +
        "att.month, " +
        "att.noOfDaysInMonth, " +
        "sal.salAmount " +
        ") " +
        "from Employee emp " +
        "join Attendence att on emp.emp_id = att.cp_fk " +
        "left join Salary sal on sal.emp_id_fk = emp.emp_id")
List<ResponseDTO> getInfoInExcel();

方案二：保留原生SQL，手动转换DTO #

维持nativeQuery = true，仅查询原始字段，在Service层将Object[]类型的查询结果手动转换为ResponseDTO对象：

Repository代码修改：

@Query(value = "select " +
        "emp.empName, " +
        "emp.gender, " +
        "emp.email, " +
        "emp.empCode, " +
        "att.noOfAbsent, " +
        "att.noOfPresent, " +
        "att.month, " +
        "att.noOfDaysInMonth, " +
        "sal.salAmount, " +
        "round(sal.salAmount/att.noOfDaysInMonth) as salperday, " +
        "round(att.noOfPresent*(sal.salAmount/att.noOfDaysInMonth)) as Salarycurrentmonth " +
        "from Employee as emp " +
        "join Attendence as att " +
        "on emp.emp_id=att.cp_fk " +
        "left join Salary as sal " +
        "on sal.emp_id_fk=emp.emp_id", nativeQuery = true)
List<Object[]> getInfoInExcel();

Service层转换逻辑：

public List<ResponseDTO> getExcelInfo() {
    List<Object[]> rawData = employeeRepository.getInfoInExcel();
    List<ResponseDTO> dtoList = new ArrayList<>(rawData.size());
    for (Object[] row : rawData) {
        ResponseDTO dto = new ResponseDTO(
                (String) row[0],
                (String) row[1],
                (String) row[2],
                (String) row[3],
                (Integer) row[4],
                (Integer) row[5],
                (String) row[6],
                (Integer) row[7],
                (BigDecimal) row[8],
                (BigDecimal) row[9],
                (BigDecimal) row[10]
        );
        dtoList.add(dto);
    }
    return dtoList;
}

注意：需保证ResponseDTO存在与参数数量、类型匹配的构造函数，否则转换时会抛出异常。

内容的提问来源于stack exchange，提问作者kavitha G