Cannot find syntax error in spring-data-jpa query-CodePudding

I'm having a problem with my code, jpa tells me:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.example.demo.dto.TimbratureOfDay(DATE(data_timbratura), min(time(data_timbratur' at line 1

I'm trying to map the result set on a class of mine which isn't annotated as an entity, I know it can be done because I have before, I've checked several times because usually when this error occurs it just means that I forgot to add a space in the query when pasting it from mySQl workbench to the DAO but this time it doesn't seem to be the case, here's the method:

@Query(value = "SELECT new com.example.demo.dto.TimbratureOfDay(DATE(data_timbratura), min(time(data_timbratura)), max(time(data_timbratura))) "
              "FROM timbrature "
              "WHERE year(data_timbratura) = ?1 and "
              "fk_employee = ?3 "
              "and month(data_timbratura) = ?2 "
              "GROUP BY DATE(data_timbratura)", nativeQuery = true)
    public List<TimbratureOfDay> getTimbratureOfMonth(int year,String month,Long idEmployee);

and here's the class:

public class TimbratureOfDay {
    private Date giorno;
    private Time entrata;
    private Time uscita;
    
    public TimbratureOfDay() {}
    
    public TimbratureOfDay(Date giorno, Time entrata, Time uscita) {
        super();
        this.giorno = giorno;
        this.entrata = entrata;
        this.uscita = uscita;
    }

    public Date getGiorno() {
        return giorno;
    }

    public void setGiorno(Date giorno) {
        this.giorno = giorno;
    }

    public Time getEntrata() {
        return entrata;
    }

    public void setEntrata(Time entrata) {
        this.entrata = entrata;
    }

    public Time getUscita() {
        return uscita;
    }

    public void setUscita(Time uscita) {
        this.uscita = uscita;
    }
}

CodePudding user response：

You're using nativeQuery=true and using syntax of JPQL. You can either set nativeQuery=false and use JPQL or set nativeQuery=true and use SQL in MySQL dialect.

CodePudding user response：

You can try with nativeQuery=false, and at any time, you can set detail log level to dump the real SQL which is executed.