26

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?

Pascal Thivent
  • 562,542
  • 136
  • 1,062
  • 1,124
Roman Zenka
  • 3,514
  • 3
  • 31
  • 36
  • In my opinion this is an omission in hibernate, and the severity of the omission is tantamount to it being a bug. – Mike Nakis Aug 30 '13 at 11:36

2 Answers2

33

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

Pascal Thivent
  • 562,542
  • 136
  • 1,062
  • 1,124
  • 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. – Roman Zenka Jul 31 '10 at 17:19
  • @Roman I guess there must be a *good* reason (I hope so) but I don't know it (maybe case insensitivity indeed). – Pascal Thivent Aug 03 '10 at 04:39
1

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://stackoverflow.com/a/48113507/19623995)

Maxime Dutaut
  • 112
  • 5