Automatic reserved word escaping for Hibernate tables and columns

Question

I am trying to use one Hibernate mapping for several different databases: H2, Oracle, MySql.

Each database has a different list of reserved words.

I would like Hibernate to automatically escape the reserved words.

I know I can:

• use backticks to force escaping (escape everything just to be safe)
• change all identifiers so they are certainly not keywords in any database (make them ugly)
• tie the schema to a specific set of databases, escaping the union of keywords (will break if I add new database to the mix)

Is there a more elegant solution?

Begrime

In my opinion this is an omission in hibernate, and the severity of the omission is tantamount to it being a bug. – Glossy

Answer 1

AFAIK, Hibernate doesn't maintain a list of reserved keyword (per database) so I think you should look at database identifier escaping.

If you are using Hibernate 3.5+, try hibernate.globally_quoted_identifiers=true to quote all database identifiers (this is something they added for JPA 2.0, see the secion 2.13 Naming of Database Objects of the spec for the JPA way to activate this if you are using JPA).

Prior to version 3.5, Hibernate doesn't offer any configuration option for global escaping. Implementing a custom NamingStrategy to escape everything transparently would be the recommended way.

See also

• Database independant Column/Table name escaping?
• HHH-2578 - redesign SessionFactory building - my understanding is that fixing this issue would make automatic escaping of keywords (through dialects) possible.

Jolin

Thank you. I am quite puzzled that this is not a default setting, considering that lack of escaping can only cause you a headache, and there is nothing positive to it... hmm, maybe case insensitivity. – Begrime

@Roman I guess there must be a good reason (I hope so) but I don't know it (maybe case insensitivity indeed). – Jolin

Answer 2

We are using Oracle and H2 and I stumbled upon a similar issue.

I tried the Hibernate option hibernate.auto_quote_keyword=true but Oracle did not digest it well, raising an ORA-00904 on all quoted column names.

Adding spring.jpa.hibernate.naming.physical-strategy= org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl on top of the above made it work (cf https://mcmap.net/q/190020/-spring-boot-jpa-quoted-reserved-word-column-name-not-working)

Coheir