java - 我有错误检查手册对应的mysql服务器版本

Question

存储库:

包 com.apsilabs.meterwebapi.repository;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import com.apsilabs.meterwebapi.model.ActualsBlockLoad;
import com.apsilabs.meterwebapi.viewmodel.ActualsBlock;

@Repository
public interface ActualsBlockLoadRepository extends JpaRepository<ActualsBlockLoad, Long> {
    @Query(nativeQuery=true,value= "SELECT NEW com.apsilabs.meterwebapi.viewmodel.ActualsBlock(CONVERT(hour(rtc), CHAR(50)) as hours,CONVERT(date(rtc), CHAR(50)) as dates,CONVERT(IFNULL(avg(JSON_EXTRACT(JSON_UNQUOTE(data),'$.\\\"1012270255\\\"')),0),CHAR(50)) as voltage) from actuals_block_load  group by hours,dates order by dates,hours")
    public List<ActualsBlock> getActualsBlockLoad();

}

我有一个错误:您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在第 1 行 '.apsilabs.meterwebapi.viewmodel.ActualsBlock(CONVERT(hour(rtc), CHAR(50)) as hou' 附近使用的正确语法

新类:

package com.apsilabs.meterwebapi.viewmodel;

public class ActualsBlock {
    private String hours;
    private String dates;
    private String voltage;

    public String getHours() {
        return hours;
    }

    public void setHours(String hours) {
        this.hours = hours;
    }

    public String getDates() {
        return dates;
    }

    public void setDates(String dates) {
        this.dates = dates;
    }

    public String getVoltage() {
        return voltage;
    }

    public void setVoltage(String voltage) {
        this.voltage = voltage;
    }

    public ActualsBlock(String hours, String dates, String voltage) {
        super();
        this.hours = hours;
        this.dates = dates;
        this.voltage = voltage;
    }

    public ActualsBlock() {
        super();
    }

}

Answer 1

您已使用nativeQuery=true，并且此查询不是 native 的。

SELECT NEW com.apsilabs.meterwebapi.viewmodel.ActualsBlock(CONVERT(hour(rtc), CHAR(50)) as hours,CONVERT(date(rtc), CHAR(50)) as dates,CONVERT(IFNULL(avg(JSON_EXTRACT(JSON_UNQUOTE(data),'$.\\\"1012270255\\\"')),0),CHAR(50)) as voltage) from actuals_block_load  group by hours,dates order by dates,hours.

为什么您使用 NEW com.apsilabs.meterwebapi.viewmodel.ActualsBlock(CONVERT.... 如果是 native 查询