Spring JPA trap for camel case table field name.

copy from: https://www.mathworks.com/matlabcentral/fileexchange/101118-convert-naming-convention?s_tid=prof_contriblnk

Although the “camel case” is not a good practice for DB table field naming, we will sometimes encounter situations with a such field name, e.g. accessing an old existing DB. And there is a trap if we use the “Spring JPA” to generate an SQL statement. In this article, I’ll show the problem I meet and how to solve it.

Problem

The problem is that JPA will generate the SQL with all fields with underscores instead of camel case, even if we use annotation to specify it like below:

// field declaration in the entity class.
@Column(name = "paymentMethod")
private String paymentMethod;

error log when use JPA on a camel case field

as we can see in the above picture, the JPA generates a SQL statement like:

select ... p1_0.payment_method,... from payment_record p1_0

then we get the JdbcSQLSyntaxErrorException as result.

Workaround with trap

First, I come up with a workaround that, since the DB is not case-sensitive, maybe we can just specify the column name with all lowercase. Turn out the query is executed successfully.

@Column(name = "paymentmethod")
private String paymentMethod;

workaround with all lowercase on the field name

But I am a little worried about this workaround. So, I do some research on DB case-sensitive issues, and I found that:

In MySQL, databases correspond to directories within the data directory. Each table within a database corresponds to at least one file within the database directory (and possibly more, depending on the storage engine). Triggers also correspond to files. Consequently, the case sensitivity of the underlying operating system plays a part in the case sensitivity of database, table, and trigger names. This means such names are not case-sensitive in Windows, but are case-sensitive in most varieties of Unix. One notable exception is macOS, which is Unix-based but uses a default file system type (HFS+) that is not case-sensitive.
(excerpt from: https://dev.mysql.com/doc/refman/8.0/en/identifier-case-sensitivity.html)

So, this is obviously another trap that we might step into. We must find other ways to solve this problem or we may see that in the production environment, which is the worst situation.

Proper Solution

Finally, I found and property that can control the field naming strategy of JPA when it generates SQL statements (reference: https://www.baeldung.com/hibernate-field-naming-spring-boot). So I add the following setting to my application.yaml:

spring:
  jpa:
    hibernate:
      naming.physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

Then, we can see that the JPA generates the correct field name with the camel case.

Conclusion

In my opinion, setting the naming.physical-strategy is the correct way to solve this camel case field name issue when we are using Spring JPA if we can not change the DB schema due to any reason. Besides, if we want to build some custom naming strategy, it is totally OK to create our own Naming strategy implementation and set it in application.yaml.

If you enjoyed this article, please follow me here on Medium for more stories about Java and Spring boot. Thanks for reading.