英文:
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 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";
这是我在 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);
}
现在在执行时我得到以下错误:
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
似乎 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 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"
.
答案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 "
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论