Spring Boot Data JPA的NativeQuery在MySQL Workbench上正常工作的查询中抛出语法错误。

huangapple go评论79阅读模式
英文:

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"&quot; 更改为 &#39;,但结果没有改变(将 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=&quot;fixed&quot;;

This is the line in my Repository:

@Repository
public interface ScheduleRepository extends CrudRepository&lt;Schedule, Integer&gt; {

    @Query(value=&quot;SELECT s.* FROM fips.schedule s &quot; +
            &quot;inner join lecture_object lo on s.id_lecture_object = lo.id_lecture_object &quot; +
            &quot;inner join lecture_semester ls on lo.id_lecture_semester = ls.id_lecture_semester &quot; +
            &quot;inner join lecture_semester_has_possible_lecturers ll on ls.id_lecture_semester = ll.id_lecture_semester&quot; +
            &quot;where s.id_scenario = :scenarioId &quot; +
            &quot;and ll.id_lecturer = :lecturerId &quot; +
            &quot;and ll.status = \&quot;fixed\&quot;&quot;, nativeQuery = true)
    List&lt;Schedule&gt; getAllByFixedLecturerAndScenario(@Param(&quot;lecturerId&quot;) int lecturerId, @Param(&quot;scenarioId&quot;) 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 = &quot;fixed&quot;
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 &#39;s.id_scenario = 1 and ll.id_lecturer = 103 and ll.status = &quot;fixed&quot;&#39; 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 &quot; around fixed to &#39;, which did not change the outcome (the same goes for replacing the status = &quot;fixed&quot; by status LIKE &quot;fixed&quot;.

答案1

得分: 2

在“where”之前的字符串末尾需要一个空格。将

on ls.id_lecture_semester = ll.id_lecture_semester&quot;

更改为

on ls.id_lecture_semester = ll.id_lecture_semester &quot;
英文:

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

on ls.id_lecture_semester = ll.id_lecture_semester&quot;

to

on ls.id_lecture_semester = ll.id_lecture_semester &quot;

huangapple
  • 本文由 发表于 2020年9月23日 00:14:34
  • 转载请务必保留本文链接:https://go.coder-hub.com/64013703.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定