Hibernate: use backticks for MySQL but not for HSQL

Question

The project I'm working on (which uses Java, Spring, Hibernate) has recently been changed from Oracle to MySQL. There are several cases where some of the properties in the code are reserved words in MySQL, such as "release".

There are several solutions: 1) rename the properties in the code and subsequent getter / setter methods, as well as update the code that calls these methods; 2) annotate the property in the code using @Column (name = "` release` "). This says hibernate indicates the name when talking to the database.

I would rather stay away from the first approach in order to reduce the chance of breaking more things. The second approach is "good", except that it is specific to MySQL. In our dev. we use HSQL, which does not like backlinks around these column names.

I looked at the org.hibernate.mapping.Column class, and I see that it has getQuotedName methods that I can potentially override if I could subclass Column and tell Hibernate to use my own column class.

What is the best way to solve this problem based on the preferred approach a) not to have codebase refactoring (b / c change of property names, getter / setter methods, etc.) and b) wish that the application still works in HSQL and MySQL.

It would be wise to have a property in the properties file that you could switch to enable / disable the column naming fix. This reminds me, I tried using a custom naming strategy and overriding the "columnName" method to surround the column name in the opposite direction ... this does not work even in MySQL.

Answer 1

Solving back ticks sounds good. But if that doesn't work or you don’t want to use the undocumented function of a particular JPA provider: why not use column names that are not reserved at all in any (or most common) databases.

You do not need to change the name of your java properties, you should only specify the column name for them.

Answer 2

Use the hints. This is your way of telling Hibernate that you want this identifier to be correctly specified. It is just a coincidence that backlinks are also used as a quote character for MySQL. This backlink will be translated to any quotation mark character for your database. Regarding adding quotes as the default behavior, I'm not sure if Hibernate has such an option. Nothing in the documentation mentions a possible option, and I don’t remember that there was anything like that in the same test suite. But I do not think this is a good idea, since "reserved keywords" are exceptions, not the rule.