Creating a field with a reserved word name with JPA

Question

@Column(name="open") 

Using the sqlserver dialect with hibernation.

 [SchemaUpdate] Unsuccessful: create table auth_session (id numeric(19,0) identity not null, active tinyint null, creation_date datetime not null, last_modified datetime not null, maxidle int null, maxlive int null, open tinyint null, sessionid varchar(255) not null, user_id numeric(19,0) not null, primary key (id), unique (sessionid)) [SchemaUpdate] Incorrect syntax near the keyword 'open'. 

I would expect hibernation to use the quoted identifier when creating the table.

Any ideas on how to handle this ... other than renaming the field?

Answer 1

There was the same problem, but with the table name Transaction . If you install

 hibernate.globally_quoted_identifiers=true 

Then all database identifiers will be enclosed in quotation marks.

Found my answer here. Special character in hibernate table name gives error

And found all the available settings here https://docs.jboss.org/hibernate/orm/5.2/userguide/html_single/appendices/Configurations.html

Can't find the best docs for this though.

In my case, the setting was in my Spring properties file. As already mentioned in the comments, these can be other configuration files related to hibernation.

Answer 2

With Hibernate as a provider of JPA 1.0, you can avoid the reserved keyword by wrapping it in backlinks:

 @Column(name="`open`") 

This is the syntax inherited from Hiberate Core:

5.4. Identifiers with SQL Codes

You can force Hibernate to quote the identifier in the generated SQL including the table or column name in the backticks in the map document. Hibernate will use the correct quote style for SQL Dialect. These are usually double quotes, but SQL Server uses brackets and MySQL uses backreferences.

 <class name="LineItem" table="`Line Item`"> <id name="id" column="`Item Id`"/><generator class="assigned"/></id> <property name="itemNumber" column="`Item #`"/> ... </class> 

In JPA 2.0, the syntax is standardized and becomes:

 @Column(name="\"open\"") 

References

• Hibernate Reference Guide
  • 5.4. Identifiers with SQL Codes
• JPA 2.0 Specification
  • 2.13 Naming Database Objects

Related Questions

• Hibernate, MySQL and a table named "Repeat" - strange behavior
• Automatic Reservation of a Reserved Word for Hibernate Tables and Columns

Answer 3

If you use as below, it should work

 @Column(name="[order]") private int order; 

Answer 4

 @Column(name="\"open\"") 

This will work for sure, the same problem happened to me when I was learning sleep mode.

Answer 5

Manually escaping reserved keywords

If you use JPA, you can avoid double quotes:

 @Column(name = "\"open\"") 

If you use the native Hibernate API, you can avoid them with the help of reverse checkmarks:

 @Column(name = "'open'") 

Automatically escaping reserved keywords

If you want to automatically escape the reserved keywords, you can set the Hibernate-specific hibernate.globally_quoted_identifiers configuration property to true :

 <property name="hibernate.globally_quoted_identifiers" value="true" /> 

Read more in this article .

Answer 6

No - change the column name.

It depends on the database, and you simply cannot create such a column. In the end, hibernate sends the DDL to the database. If you cannot create a valid DDL with this column name, this means that sleep mode also cannot be. I do not think that quoting will solve the problem, even if you write DDL.

Even if you manage to avoid the name, change it. He will work with this database, but will not work with another.

Answer 7

Some JPA implementations (like the one I'm using, DataNucleus) will automatically quote the identifier for you, so you will never get this.