Why I have problem in Spring data JPA when I name my table "order" in MySQL database?

[Stefan Jankovic]

I have a table called "order" in MySQL that is populated with columns and rows. Every query works fine when I run native SQL queries in Workbench.

Problem arrises when I use Spring data JPA and call method findAll(). What I get is:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'order order0_' at line 1

I renamed table to "order_test" with same columns and rows and it worked fine. Can someone tell me why it didn't work? What went wrong?

[Tim Holloway]

ORDER is a reserved word in SQL (as in ORDER BY). JPA generates and uses SQL internally. It's also a reserved work in JPQL, I think, which may be why the query compiler didn't know to use an escape mechanism that allows getting around that.

I'd recommend that you name your table ORDERS. I think in fact, that using the plural form was a shop standard for one place I worked.

[Stefan Jankovic]

Yes, I had the same thought - "order" being a reserved word. But as I mentioned, queries worked fine in Workbench which makes this confusing for me.

[Tim Holloway]

What was the error? And for that matter, what queries worked and on what brand of database?

[Stefan Jankovic]

I described it all in the first message

[Tim Holloway]

No, I'd like to see the SQL you were able to run from the console.

Note that the error message didn't come from JPA, though. It came from the MYSQL JDBC driver.

An SQL trace log from JPA would have been useful, but I'd bet that the generated SQL looks something like this:

SELECT order0_.a, order0_.b, ... FROM order order0_

[Stefan Jankovic]

I used queries like:

SELECT * FROM demo.order;
insert into demo.order(id, name) values (3, 'dog');

, which worked fine.

Yep, I noticed that - it indeed came from JDBC. Is there any chance I can see the full generated query command as this is the only thing I am shown (except long stack trace that doesn't help)?

[Tim Holloway]

You seem to be providing explicit schema names and I don't think JPA is doing that unless more than one schema is in use.

Yes, you can see the exact schema when using Hibernate JPA by activating the proper logger. I think it's something like org.hibernate.sql, but I don't remember.

[Tim Holloway]

I meant "you can see the exact SQL". My fingers are only loosely attached to my brain, it seems.

[Stefan Jankovic]

Hahaha yeah I would like to see generated SQL query that Hibernate does for us It would solve our problems I think hehe. Any chance it exists, I mean have you even encountered someone doing it?

[Tim Holloway]

https://www.baeldung.com/hibernate-logging-levels

[Stefan Jankovic]

Awesome, thanks, man! I owe you a beer.