I am using Spring Data JPA to get data from a MySQL database. I have this function adnotated with @Query in a repository

@Query(value = "SELECT * FROM  Treatments INNER JOIN Animals ON Treatments.animal_id = Animals.animal_id WHERE Animals.owner_id = ?1 AND Treatments.enddate > curdate()", nativeQuery = true)
        Page<Treatments> findAll(@Param("ownerId") Optional<Owner> owner, Pageable pageable);

Spring generates the SQL as it is, then adds order by INNER.startdate asc limit ? And then it throws this error Unknown column 'INNER.startdate' in 'order clause' Why is this happening? I tried adding an orderby clause myself but it will just add its own orderby clause anyway and throw the error. The query works inside MySql.

CodePudding user response：

I suppose Spring modifies your query because you use Page as the return type. When you use a native query together with Page, you should add a countQuery, see Spring Documentation.