I have the following row in my MySQL Table: https://i.stack.imgur.com/u0FC4.png This column is represented as: And it is bound to a Thymeleaf form like so: 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

JpaRepository SQL syntax error when trying to save to MySQL Date

I have the following row in my MySQL Table:

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

This column is represented as:

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

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">

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: 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

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.

EDIT: Removing the @DateTimeFormat gives the following error:

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

Using Spring Boot 2.3.5.RELEASE

Answer

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…

Advertisement

Answer