DEV Community

Salad Lam
Salad Lam

Posted on

2

Capitalisation of table name generated by Hibernate when using MySQL server

Notice

I wrote this article and was originally published on Qiita on 17 September 2022.


My problem

I am working on a Spring Boot project with JPA (Hibernate implementation), and the database underlying is MySQL running on Windows. The table names are in lower case. One day I moved the database from Windows to Linux, imported all data, and then restarted the application. Hibernate created some of the tables for me, with names same as entity class, including capitalisation. And it reads the data from the new table instead of the old table with lower case name. I tried to find out why and finally got the reason.

Database and table name of MySQL

First I need to talk about history. In the time of MyISAM storage engine, a database is just a folder and a table is a group of files in that folder. The name of the database is the name of the folder. In Windows, when accessing a folder, the case of name is not considered, so "Student" and "student" means the same folder. But in Linux, the folder name is case sensitive. So when running two SQL query below, different behavior can be seen when running on Windows and Linux.

SELECT `name`, `class` from `student`;
SELECT `name`, `class` from `Student`;
Enter fullscreen mode Exit fullscreen mode

And now lower_case_table_names system variable determines the behavior of case of table name. For details please read this.

When Hibernate will help you generate table name

When an entity class is not specified a table name by @Table annotation, Hibernate will determine the table name. Hibernate first read the system variable lower_case_table_names, and then decided to use the name including capitalisation or not by this value.

Back to my problem

In the newly setup Linux MySQL database, add the following line into my.cnf. Restart both MySQL server and my application, and the problem is solved.

[mysqld]
lower_case_table_names = 1
Enter fullscreen mode Exit fullscreen mode
👋 One new thing before you go

If you don't invest in your dev career who will?

But how do you do it without breaking the bank?

We have created a membership program that helps cap your costs so you can build and experiment for less. And we currently have early-bird pricing which makes it an even better value! 🐥

Just one of many great perks of being part of the network ❤️

Top comments (2)

Collapse
 
tleipzig profile image
Thomas

Well analyzed, it's a MySQL "feature" first of all. Hibernate may also contribute some magic - check out dev.to/tleipzig/uppercase-table-na... for some more details.

Collapse
 
saladlam profile image
Salad Lam

Thank you for your information.

A Workflow Copilot. Tailored to You.

Pieces.app image

Our desktop app, with its intelligent copilot, streamlines coding by generating snippets, extracting code from screenshots, and accelerating problem-solving.

Read the docs

👋 Kindness is contagious

Dive into an ocean of knowledge with this thought-provoking post, revered deeply within the supportive DEV Community. Developers of all levels are welcome to join and enhance our collective intelligence.

Saying a simple "thank you" can brighten someone's day. Share your gratitude in the comments below!

On DEV, sharing ideas eases our path and fortifies our community connections. Found this helpful? Sending a quick thanks to the author can be profoundly valued.

Okay