英文:
Spring Boot Data JPA NativeQuery throws Syntax-Error on Query working in MySQL Workbench
问题
我正在使用 Spring Boot Data JPA 并且希望从我的 MySQL 数据库中查询数据。我在 MySQL Workbench 中编写了一个查询，在其中可以正常运行，现在我想在我的 Repository 中使用完全相同的查询作为本地查询。
这是在 MySQL Workbench 中工作的查询：
SELECT s.* FROM fips.schedule sinner join lecture_object lo on s.id_lecture_object = lo.id_lecture_objectinner join lecture_semester ls on lo.id_lecture_semester = ls.id_lecture_semesterinner join lecture_semester_has_possible_lecturers ll on ls.id_lecture_semester = ll.id_lecture_semesterwhere s.id_scenario = 1 and ll.id_lecturer=103 and ll.status="fixed";
这是我在 Repository 中的行：
@Repositorypublic 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);}
现在在执行时我得到以下错误：
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: 420002020-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 12020-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
似乎 WHERE 子句有些问题，但它与在 MySQL WB 中运行的语句完全相同。我在本地查询中漏掉了什么吗？我还尝试过将围绕 "fixed" 的 " 更改为 '，但结果没有改变（将 status = "fixed" 替换为 status LIKE "fixed" 也是一样的情况）。
英文:
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 sinner join lecture_object lo on s.id_lecture_object = lo.id_lecture_objectinner join lecture_semester ls on lo.id_lecture_semester = ls.id_lecture_semesterinner join lecture_semester_has_possible_lecturers ll on ls.id_lecture_semester = ll.id_lecture_semesterwhere s.id_scenario = 1 and ll.id_lecturer=103 and ll.status="fixed";
This is the line in my Repository:
@Repositorypublic 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: 420002020-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 12020-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".
答案1
得分: 2
在“where”之前的字符串末尾需要一个空格。将
on ls.id_lecture_semester = ll.id_lecture_semester"
更改为
on ls.id_lecture_semester = ll.id_lecture_semester "
英文:
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 "
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论