Hibernate and SQL keyword Column Names

I have recently experienced some problems with Hibernate and the generation of SQL Tables. I am using Hibernate Annotations, and some of my entities have annotations that look something like:

@Column(nullable = false)
String myProperty;

@Column
@Temporal(TemporalType.TIMESTAMP)
@GeneratedValue(strategy = GenerationType.AUTO)
Date timestamp;

@Column
boolean insert;

@Column
boolean update;

@Column
boolean delete;

The problem is that Hibernate is throwing the following errors:

DEBUG [org.hibernate.tool.hbm2ddl.SchemaUpdate] - <create table MyTable (id bigint not null auto_increment, modifyingUsername varchar(255) not null, timestamp datetime, insert bit, update bit, delete bit, primary key (id))>

ERROR [org.hibernate.tool.hbm2ddl.SchemaUpdate] - <Unsuccessful: create table TextMetaDataHistory (id bigint not null auto_increment, modifyingUsername varchar(255) not null, timestamp datetime, insert bit, update bit, delete bit,primary key (id))>

ERROR [org.hibernate.tool.hbm2ddl.SchemaUpdate] - <Syntax error or access violation message from server: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'insert bit, update bit, delete bit' at line 1">

Upon further investigation, it seems the problem is with the SQL that Hibernate is generating. Unfortunately some of my Java variable names (such as "timestamp", "insert", "update" and "delete") are themselves SQL keywords.

The Debug information above shows that the SQL being generated is not correct SQL syntax. In particular, if you type in MySQL:

create table MyTable (id bigint not null auto_increment, modifyingUsername varchar(255) not null, timestamp datetime, insert bit, update bit, delete bit, primary key (id))

you'll get an error.

If you manually edit this so it, instead, reads:

create table MyTable (id bigint not null auto_increment, modifyingUsername varchar(255) not null, `timestamp` datetime, `insert` bit, `update` bit, `delete` bit, primary key (id))

the SQL will run beautifully. All I've done here is insert quotes (the "`" quote, which is found on a standard keyboard above Tab, and using Shift on this key produces "~").

All I need to do now is work out how to get Hibernate to generate SQL with this included!!!
More news soon... (I hope!)

Update: Ok, so I decided, in the end, to fix this just by manually changing my Hibernate annotations to give an explicit name for each "questionable" column that includes the quotes. Thus the modified annotations are now:

@Column(name="`timestamp`")
@Temporal(TemporalType.TIMESTAMP)
@GeneratedValue(strategy = GenerationType.AUTO)
Date timestamp;

@Column(name="`insert`")
boolean insert;

@Column(name="`update`")
boolean update;

@Column(name="`delete`")
boolean delete;

NB: The "`" quote I'm using is the one above on a US keyboard, sharing the key with "~". Other quotation marks didn't work for me.

This now works - but it does seem unusual that I have to do this. I'm surprised Hibernate doesn't do this automatically for all Column names to avoid such "keyword" problems. Having said that, some "newbie" Hibernate users who have experienced similar problems, have suggested modifying Hibernate to do this, and the response from more experienced Hibernate programmers in at least one case was "Read the manual - stop wasting time" or something to that effect. Makes me wonder if I'm missing something in proposing this solution?

If you know of a better way to fix this problem, please post a comment on this blog! In the meantime, using the @Column(name="`whatever`") annotation is fixing my immediate problem, and doesn't seem to have any bad side effects, so I'll just stick with it!



PS: By the way, to get Hibernate to log the SQL it was using, I set:

log4j.logger.org.hibernate=debug

in the log4j.properties file I'm using..