MySQLSyntaxErrorException in Repository interface

Question

I'm using code below:

@RestResource(exported = false)
public interface TransactionRepository extends CrudRepository<Transaction, Long> {

@Query(value = "SELECT " +
        "    new com.test.technical.dto.TopMemberDTO(m.id, m.name, m.email, COUNT(t.book_id)) " +
        "FROM " +
        "    member m JOIN transaction t where m.id = t.member_id and t.date_of_issue >= :dateOfIssue and t.date_of_return <= :dateOfReturn " +
        "GROUP BY t.member_id ORDER BY COUNT(t.book_id) DESC limit 5", nativeQuery = true)


List<TopMemberDTO> getTopMembers(@Param("dateOfIssue") LocalDateTime dateOfIssue, @Param("dateOfReturn") LocalDateTime dateOfReturn);

Error is:

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 '.test.technical.dto.TopMemberDTO(m.id, m.name, m.email, COUNT(t.book_id)) F' at line 1

I never saw `SELECT new ....` in MySQL can you give me any link to what does it mean? — Alex, Dec 24 '18 at 14:32
When we want the result from DB and want that result to Map to our class[TopMemberDTO] automatically, then we use it. — , Dec 24 '18 at 14:33
https://stackoverflow.com/questions/36328063/how-to-return-a-custom-object-from-a-spring-data-jpa-group-by-query — , Dec 24 '18 at 14:40
You cannot use the `new` syntax in a SQL query only in a JPQL query you can use that. — M. Deinum, Dec 24 '18 at 15:51

score 1 · Answer 1 · answered Dec 24 '18 at 16:53

You are using jpql and you say it is nativeQuery = true, you need to change it. This kind of queries i use them of this way.

@Entity
@Table(name = "TABLE")
@NamedQueries({
    @NamedQuery(name = "ANYIDENTIFIER", query = "select new example.DTO(rp.example) from 
RemesaProceso rp where rp.vigente = true and rp.proceso.idProceso = :idProceso order by rp.remesaId asc")
})

And in another class I return a list of DTO

return entityManager.createNamedQuery("ANYIDENTIFIER").setParameter("idProceso ", 2).getSingleResult();

MySQLSyntaxErrorException in Repository interface

1 Answers1