尝试保存到MySQL日期时出现JpaRepository SQL语法错误 [英] JpaRepository SQL syntax error when trying to save to MySQL Date

问题描述

我的MySQL表中有以下一行:

I have the following row in my MySQL Table:

https://i.stack.imgur.com/u0FC4.png

此列表示为:

    @Column(name="release")
    @DateTimeFormat(pattern = "yyyy-MM-dd")
    private LocalDate release;

它绑定到Thymeleaf形式，如下所示:

And it is bound to a Thymeleaf form like so:

    <label for="release">Release date</label>
    <input type="date" 
      th:field="*{movie.release}"
      th:value="*{movie.release}"
      id="release" 
      class="form-control mb-3">

现在，如果我直接将日期插入MySQL数据库，日期将被加载并在表格上设置为正确的日期: https://i.stack.imgur.com/e35lr.png 但是每次我尝试保存或编辑电影时，都会出现以下错误:

Now if I insert a date into the MySQL db directly, the date is loaded and set to the correct date on the form: https://i.stack.imgur.com/e35lr.png But every time I try to save or edit the movie I get the following error:

java.sql.SQLSyntaxErrorException:您的SQL语法有错误；检查与您的MySQL服务器版本相对应的手册，以在'release ='2016-05-19'，title ='The Shawshank Redemption'，user_name ='susan'其中在第1行的'附近使用正确的语法

java.sql.SQLSyntaxErrorException: 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 'release='2016-05-19', title='The Shawshank Redemption', user_name='susan' where ' at line 1

即使我将发行日期设置为null，Hibernate也不会保存电影. 如果我完全删除了日期的@Column并将其从Thymeleaf表单中删除，则该对象将正确保存，不会出现错误.因为JpaRepository正在生成所有查询，所以我看不到语法可能是错误的.我也尝试过使用标准java.util.Date作为具有相同问题的类型.

Even if I set the release date to null Hibernate won't save the movie. If I completely remove the @Column for the date and remove it from the Thymeleaf form, the object is saved properly without errors. Because JpaRepository is generating all the queries I can't see how the Syntax could be wrong. I've also tried with standard java.util.Date as the type with the same issue.

删除@DateTimeFormat会出现以下错误:

Removing the @DateTimeFormat gives the following error:

[无法将类型'java.lang.String'的属性值转换为属性'movie.release'的必需类型'java.time.LocalDate']

[Failed to convert property value of type 'java.lang.String' to required type 'java.time.LocalDate' for property 'movie.release']

使用Spring Boot 2.3.5.RELEASE

Using Spring Boot 2.3.5.RELEASE

推荐答案

原来问题出在MySQL中的列名上- release 是受保护的单词，使用时会引起查询问题.当我尝试删除该列时应该已经注意到，因为它不会像其他列那样自动填充名称...

Turns out the problem was with the column name in MySQL - release is a protected word and causes issues with the query when used. Should have noticed when I tried to drop the column, as it would not auto-fill the name like it does for other columns...

这篇关于尝试保存到MySQL日期时出现JpaRepository SQL语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！