Mam interfejs:
@Query(value = "SELECT new com.example.order.PersonOrderDto (TOP 5 FK_PERSON AS idCustomer, COUNT(*)) AS totalOrders FROM ORDERS WHERE price>50 GROUP BY"
+ " FK_PERSON Order by count(*) DESC",
nativeQuery = true)
List<PersonOrderDto> fetchCustomers();
dostaję błąd:org.h2.jdbc.JdbcSQLSyntaxErrorException: Błąd składniowy w wyrażeniu SQL "SELECT NEW COM.[]EXAMPLE.ORDER.PERSONORDERDTO(FK_PERSON AS IDCUSTOMER, COUNT()) AS TOTALORDERS FROM ORDERS WHERE PRICE>50 GROUP BY FK_PERSON ORDER BY COUNT() DESC"
Syntax error in SQL statement "SELECT NEW COM.[]EXAMPLE.ORDER.PERSONORDERDTO(FK_PERSON AS IDCUSTOMER, COUNT()) AS TOTALORDERS FROM ORDERS WHERE PRICE>50 GROUP BY FK_PERSON ORDER BY COUNT() DESC
Chociaż samo wyrażenie SQL jest poprawne na 99%. Chciałem zmapować sobie to na obiekt DTO. Co jest nie tak?
Klasa PersonOrderDto:
@Builder
@Data
@Getter
@Setter
@AllArgsConstructor
public class PersonOrderDto {
private Integer idCustomer;
private Integer totalOrders;
}
Person:
@Entity
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Person {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;
private String name;
public Person(String name) {
this.name = name;
}
}
oraz
`@Entity(name = "orders")
public class Order {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;
@Column(name = "fk_person")
private Integer person;
@Column
private Integer price;
@Column(name = "issued")
private LocalDate date;
public Order() {
}
public Order(Integer person, Integer price, LocalDate date) {
this.person = person;
this.price = price;
this.date = date;
}
public Integer getId() {
return id;
}
public Integer getPerson() {
return person;
}
public Integer getPrice() {
return price;
}
public LocalDate getDate() {
return date;
}
}`