Friday, August 21, 2009

Escaping database column name when using Hibernate

Changing the database engine when using Hibernate turned out to be not as easy as changing the .hbm.xml file.
At first I used Apache Derby (client-server) as the database engine, just for testing purposes. I tried to change it to Microsoft SQL Server, by using the JDBC driver provided and changing the .hbm.xml file. A problem happened, a String field whose length I set to 40000, cannot be handled, so I need to truncate it to 8000. Later when I change it to Oracle, there cannot be 2 columns that have LONG VARCHAR2 type in a single table, so I need to further reduce it to 4000 characters.
The problem does not stop there. I use Hibernate Annotations, so every field is configured in the source code (instead of in the .hbm.xml file). I can define the column names if I want:
public class Entry {
  String message;

  @Column(name = "log_time")
  Date time;
In that case, the message field will be stored in a database column called message, but the time field will be stored in a database column called log_time since I have defined a @Column annotation.
The sad truth is that Hibernate does not escape column names mentioned in queries, so some DBMS'es regard them as keywords. Example include exception in Oracle, size in Derby, and so on.
We can force Hibernate to escape identifier names on the SQL queries by putting backticks (`) on the column name. The backticks will be converted to different identifier escaper for different database systems. For example, `column` in MySQL, [column] in Microsoft SQL, and "column" in Apache Derby.
So, our entity class becomes something like:
public class LogEntry {
  @Column(name = "`level`")
  String level;

  @Column(name = "`exception`", length = 4000)
  String exception;

  // etc.
Unfortunately, we must duplicate the name (one on the field, and one on the annotations), so it's not so easy to maintain (e.g. we do a rename-refactoring to the field name, we may not notice that the annotation's column name is not changed together).
I still wonder why doesn't Hibernate always escape the column name.

No comments:

Post a Comment