CODEWITHSUNDEEP
javamysqlspringspring-bootjpa
Rüdiger
Rüdiger

Reputation: 943

Spring Boot Data JPA NativeQuery throws Syntax-Error on Query working in MySQL Workbench

I am using Spring Boot Data JPA and want to query data from my MySQL database. I wrote a query in MySQL workbench that works out perfectly fine in it and now I'd like to use exactly that query in my Repository as a native query.

This is the query working my MySQL Workbench:

SELECT s.* FROM fips.schedule s 
inner join lecture_object lo on s.id_lecture_object = lo.id_lecture_object 
inner join lecture_semester ls on lo.id_lecture_semester = ls.id_lecture_semester 
inner join lecture_semester_has_possible_lecturers ll on ls.id_lecture_semester = ll.id_lecture_semester
where s.id_scenario = 1 and ll.id_lecturer=103 and ll.status="fixed";

This is the line in my Repository:

@Repository
public interface ScheduleRepository extends CrudRepository<Schedule, Integer> {

    @Query(value="SELECT s.* FROM fips.schedule s " +
            "inner join lecture_object lo on s.id_lecture_object = lo.id_lecture_object " +
            "inner join lecture_semester ls on lo.id_lecture_semester = ls.id_lecture_semester " +
            "inner join lecture_semester_has_possible_lecturers ll on ls.id_lecture_semester = ll.id_lecture_semester" +
            "where s.id_scenario = :scenarioId " +
            "and ll.id_lecturer = :lecturerId " +
            "and ll.status = \"fixed\"", nativeQuery = true)
    List<Schedule> getAllByFixedLecturerAndScenario(@Param("lecturerId") int lecturerId, @Param("scenarioId") int scenarioId);
}

Now upon execution I get the following error:

2020-09-22 18:02:15.607 DEBUG 7156 --- [nio-8081-exec-4] org.hibernate.SQL                        : SELECT s.* FROM fips.schedule s inner join lecture_object lo on s.id_lecture_object = lo.id_lecture_object inner join lecture_semester ls on lo.id_lecture_semester = ls.id_lecture_semester inner join lecture_semester_has_possible_lecturers ll on ls.id_lecture_semester = ll.id_lecture_semesterwhere s.id_scenario = ? and ll.id_lecturer = ? and ll.status = "fixed"
2020-09-22 18:02:15.608  WARN 7156 --- [nio-8081-exec-4] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 1064, SQLState: 42000
2020-09-22 18:02:15.608 ERROR 7156 --- [nio-8081-exec-4] o.h.engine.jdbc.spi.SqlExceptionHelper   : 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 's.id_scenario = 1 and ll.id_lecturer = 103 and ll.status = "fixed"' at line 1
2020-09-22 18:02:15.610 ERROR 7156 --- [nio-8081-exec-4] c.v.flow.server.DefaultErrorHandler      : 
could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet

It seems like there is something wrong with the WHERE-clause, but it is exactly the same statement that works in MySQL WB. Am I missing something on the native queries? I also tried changing the " around fixed to ', which did not change the outcome (the same goes for replacing the status = "fixed" by status LIKE "fixed".

Upvotes: 1

Views: 1917

Answers (1)

PeterHe
PeterHe

Reputation: 2766

You need a space in the end of the string before "where". Change

on ls.id_lecture_semester = ll.id_lecture_semester"

to

on ls.id_lecture_semester = ll.id_lecture_semester "

Upvotes: 2

Related Questions