• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Tim Cooke
  • Liutauras Vilda
  • Jeanne Boyarsky
Sheriffs:
  • Paul Clapham
  • Rob Spoor
  • Junilu Lacar
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Piet Souris
  • Carey Brown
Bartenders:

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

 
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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:


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?
 
Saloon Keeper
Posts: 26526
187
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Saloon Keeper
Posts: 26526
187
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
What was the error? And for that matter, what queries worked and on what brand of database?
 
Stefan Jankovic
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I described it all in the first message
 
Tim Holloway
Saloon Keeper
Posts: 26526
187
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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:


 
Stefan Jankovic
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I used queries like:

, 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
Saloon Keeper
Posts: 26526
187
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Saloon Keeper
Posts: 26526
187
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I meant "you can see the exact SQL". My fingers are only loosely attached to my brain, it seems.
 
Stefan Jankovic
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Saloon Keeper
Posts: 26526
187
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
https://www.baeldung.com/hibernate-logging-levels
 
Stefan Jankovic
Ranch Hand
Posts: 57
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Awesome, thanks, man! I owe you a beer.
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic