When working with JPA, one problem that you might encounter is that the table name in the database is defined in lowercase, but the entity that we declare to define the table name is uppercase or vice versa.
For example, I have a table with a table name defined as lowercase:
|
1 2 3 4 5 |
CREATE TABLE `clazz` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(45) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
But the entity of this table defines table name as uppercase:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
package com.huongdanjava.jpa; import javax.persistence.Column; import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.GenerationType; import javax.persistence.Id; import javax.persistence.Table; import lombok.Getter; import lombok.Setter; @Entity @Table(name = "CLAZZ") @Getter @Setter public class Clazz { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Long id; @Column private String name; } |
Some database systems do not have any problem with that kind of declaration, but some databases do not. As I am using MySQL database version 5.7.22 with the value of lower_case_table_names set to 0 by default, defining the table and entity as above will cause me to have problems when using. Because, with the value 0 of the lower_case_table_names, the tables in MySQL must be case sensitive, see here for more.
To see what the problem is, I will create a simple Maven project as follows:
Maven dependencies:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
<dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate-core</artifactId> <version>5.2.17.Final</version> </dependency> <dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate-entitymanager</artifactId> <version>5.2.17.Final</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>6.0.6</version> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.16.20</version> <scope>provided</scope> </dependency> |
The definition of the table and entity I mentioned above.
JPA configuration file has content as follows:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
<persistence version="2.1" xmlns="http://xmlns.jcp.org/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence_2_1.xsd"> <persistence-unit name="jpaexample" transaction-type="RESOURCE_LOCAL"> <class>com.huongdanjava.jpa.Clazz</class> <exclude-unlisted-classes>true</exclude-unlisted-classes> <properties> <property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver" /> <property name="javax.persistence.jdbc.url" value="jdbc:mysql://localhost:3306/jpa_example" /> <property name="javax.persistence.jdbc.user" value="root" /> <property name="javax.persistence.jdbc.password" value="123456" /> <property name="hibernate.format_sql" value="true" /> <property name="hibernate.show_sql" value="true" /> <property name="hibernate.use_sql_comments" value="true" /> <property name="hibernate.id.new_generator_mappings" value="false" /> </properties> </persistence-unit> </persistence> |
For simplicity, I will write a small code to insert a record into the clazz table in the Application class as follows:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
package com.huongdanjava.jpa; import javax.persistence.EntityManager; import javax.persistence.EntityManagerFactory; import javax.persistence.EntityTransaction; import javax.persistence.Persistence; public class Application { public static void main(String[] args) { EntityManagerFactory emf = Persistence.createEntityManagerFactory("jpaexample"); EntityManager em = emf.createEntityManager(); EntityTransaction transaction = em.getTransaction(); transaction.begin(); Clazz clazz = new Clazz(); clazz.setName("Class A"); em.persist(clazz); transaction.commit(); } } |
Then, if you run the application, the following error will appear:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
Exception in thread "main" javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute statement at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:149) at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:157) at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:164) at org.hibernate.internal.SessionImpl.firePersist(SessionImpl.java:790) at org.hibernate.internal.SessionImpl.persist(SessionImpl.java:768) at com.huongdanjava.jpa.Application.main(Application.java:19) Caused by: org.hibernate.exception.SQLGrammarException: could not execute statement at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:63) at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:97) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:178) at org.hibernate.dialect.identity.GetGeneratedKeysDelegate.executeAndExtract(GetGeneratedKeysDelegate.java:57) at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:42) at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2933) at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3524) at org.hibernate.action.internal.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:81) at org.hibernate.engine.spi.ActionQueue.execute(ActionQueue.java:637) at org.hibernate.engine.spi.ActionQueue.addResolvedEntityInsertAction(ActionQueue.java:282) at org.hibernate.engine.spi.ActionQueue.addInsertAction(ActionQueue.java:263) at org.hibernate.engine.spi.ActionQueue.addAction(ActionQueue.java:317) at org.hibernate.event.internal.AbstractSaveEventListener.addInsertAction(AbstractSaveEventListener.java:318) at org.hibernate.event.internal.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:275) at org.hibernate.event.internal.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:182) at org.hibernate.event.internal.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:113) at org.hibernate.jpa.event.internal.core.JpaPersistEventListener.saveWithGeneratedId(JpaPersistEventListener.java:67) at org.hibernate.event.internal.DefaultPersistEventListener.entityIsTransient(DefaultPersistEventListener.java:189) at org.hibernate.event.internal.DefaultPersistEventListener.onPersist(DefaultPersistEventListener.java:132) at org.hibernate.event.internal.DefaultPersistEventListener.onPersist(DefaultPersistEventListener.java:58) at org.hibernate.internal.SessionImpl.firePersist(SessionImpl.java:783) ... 2 more Caused by: java.sql.SQLSyntaxErrorException: Table 'jpa_example.CLAZZ' doesn't exist at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:536) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:513) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:115) at com.mysql.cj.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:1983) at com.mysql.cj.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1826) at com.mysql.cj.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2034) at com.mysql.cj.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:1970) at com.mysql.cj.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5001) at com.mysql.cj.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1955) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:175) ... 20 more |
We can modify the configuration for MySQL to set the value of the lower_case_table_names to 2, but imagine if your application must run in different environments, or you may not be able to change the configuration. So, what should we do?
To solve this problem, let edit the JPA configuration file to add a Hibernate property that allows us to ignore the case sensitive of the database system that we are running.
If you are using Hibernate version 4.x then the property is “hibernate.ejb.naming_strategy” and its value is “org.hibernate.cfg.ImprovedNamingStrategy”. Then the content of the persistence.xml file would be:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
<persistence version="2.1" xmlns="http://xmlns.jcp.org/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence_2_1.xsd"> <persistence-unit name="jpaexample" transaction-type="RESOURCE_LOCAL"> <class>com.huongdanjava.jpa.Clazz</class> <exclude-unlisted-classes>true</exclude-unlisted-classes> <properties> <property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver" /> <property name="javax.persistence.jdbc.url" value="jdbc:mysql://localhost:3306/jpa_example" /> <property name="javax.persistence.jdbc.user" value="root" /> <property name="javax.persistence.jdbc.password" value="123456" /> <property name="hibernate.format_sql" value="true" /> <property name="hibernate.show_sql" value="true" /> <property name="hibernate.use_sql_comments" value="true" /> <property name="hibernate.id.new_generator_mappings" value="false" /> <property name="hibernate.ejb.naming_strategy" value="org.hibernate.cfg.ImprovedNamingStrategy" /> </properties> </persistence-unit> </persistence> |
If you are using Hibernate 5.x then the property is “hibernate.physical_naming_strategy” with the value “com.huongdanjava.jpa.PhysicalNamingStrategyImpl”. The contents of the PhysicalNamingStrategyImpl class will look like this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
package com.huongdanjava.jpa; import java.io.Serializable; import java.util.Locale; import org.hibernate.boot.model.naming.Identifier; import org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl; import org.hibernate.engine.jdbc.env.spi.JdbcEnvironment; public class PhysicalNamingStrategyImpl extends PhysicalNamingStrategyStandardImpl implements Serializable { public static final PhysicalNamingStrategyImpl INSTANCE = new PhysicalNamingStrategyImpl(); @Override public Identifier toPhysicalTableName(Identifier name, JdbcEnvironment context) { return new Identifier(addUnderscores(name.getText()), name.isQuoted()); } @Override public Identifier toPhysicalColumnName(Identifier name, JdbcEnvironment context) { return new Identifier(addUnderscores(name.getText()), name.isQuoted()); } protected static String addUnderscores(String name) { final StringBuilder buf = new StringBuilder(name.replace('.', '_')); for (int i = 1; i < buf.length() - 1; i++) { if (Character.isLowerCase(buf.charAt(i - 1)) && Character.isUpperCase(buf.charAt(i)) && Character.isLowerCase(buf.charAt(i + 1))) { buf.insert(i++, '_'); } } return buf.toString().toLowerCase(Locale.ROOT); } } |
The contents of my persistence.xml file are as follows:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
<persistence version="2.1" xmlns="http://xmlns.jcp.org/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence_2_1.xsd"> <persistence-unit name="jpaexample" transaction-type="RESOURCE_LOCAL"> <class>com.huongdanjava.jpa.Clazz</class> <exclude-unlisted-classes>true</exclude-unlisted-classes> <properties> <property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver" /> <property name="javax.persistence.jdbc.url" value="jdbc:mysql://localhost:3306/jpa_example" /> <property name="javax.persistence.jdbc.user" value="root" /> <property name="javax.persistence.jdbc.password" value="123456" /> <property name="hibernate.format_sql" value="true" /> <property name="hibernate.show_sql" value="true" /> <property name="hibernate.use_sql_comments" value="true" /> <property name="hibernate.id.new_generator_mappings" value="false" /> <property name="hibernate.physical_naming_strategy" value="com.huongdanjava.jpa.PhysicalNamingStrategyImpl" /> </properties> </persistence-unit> </persistence> |
In this example, I’m using Hibernate 5. Running the application again, you will not see any errors.
