Mapping a table called "group" in Hibernate for DB2 and HSQLDB

Question

I have a table called group, that I am trying to map using hibernate for DB2 and HSQLDB. Table name group is a reserved word and it must be quoted in HSQLDB. However DB2 does not like quoted table name.

So this mapping works in HSQLDB but not in DB2:

@Entity
@Table(name="`group`")
public class Group {

Mapping results in following error in DB2 (making a query that involves Group table):

Caused by: com.ibm.db2.jcc.b.SqlException: DB2 SQL error: SQLCODE: -204, SQLSTATE: 42704, SQLERRMC: SCHEMA_NAME.group
    at com.ibm.db2.jcc.b.hh.c(hh.java:1662)
    at com.ibm.db2.jcc.b.hh.d(hh.java:1650)
    at com.ibm.db2.jcc.b.hh.a(hh.java:1219)
    at com.ibm.db2.jcc.c.db.g(db.java:139)
    at com.ibm.db2.jcc.c.db.a(db.java:39)
    at com.ibm.db2.jcc.c.t.a(t.java:34)
    at com.ibm.db2.jcc.c.sb.f(sb.java:142)
    at com.ibm.db2.jcc.b.hh.n(hh.java:1190)
    at com.ibm.db2.jcc.b.ih.eb(ih.java:1997)
    at com.ibm.db2.jcc.b.ih.d(ih.java:2439)
    at com.ibm.db2.jcc.b.ih.V(ih.java:492)
    at com.ibm.db2.jcc.b.ih.executeQuery(ih.java:475)
    at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
    at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
    at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)
    at org.hibernate.loader.Loader.doQuery(Loader.java:674)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
    at org.hibernate.loader.Loader.loadCollection(Loader.java:1994)
    ... 71 more

And this works in DB2 but not in HSQLDB:

@Entity
@Table(name="group")
public class Group {

Mapping results in following error in HSQLDB (creating the Group table):

WARN hibernate.ExtendedAnnotatedSessionFactoryBean - Unsuccessful schema statement: create table group  ( *details omitted* )
java.sql.SQLException: Unexpected token: GROUP in statement [create table group]
    at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
    at org.hsqldb.jdbc.jdbcStatement.fetchResult(Unknown Source)
    at org.hsqldb.jdbc.jdbcStatement.executeUpdate(Unknown Source)
    at org.springframework.orm.hibernate3.LocalSessionFactoryBean.executeSchemaStatement(LocalSessionFactoryBean.java:1000)
    at org.springframework.orm.hibernate3.LocalSessionFactoryBean.executeSchemaScript(LocalSessionFactoryBean.java:972)
    at org.springframework.orm.hibernate3.LocalSessionFactoryBean$2.doInHibernate(LocalSessionFactoryBean.java:912)
    at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:373)
    at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:338)
    at org.springframework.orm.hibernate3.LocalSessionFactoryBean.createDatabaseSchema(LocalSessionFactoryBean.java:906)
  ...

I am using org.hibernate.dialect.DB2Dialect and org.hibernate.dialect.HSQLDialect for DB2 and HSQLDB, respectively.

How can I make the same mapping work for the both databases simultaneously?

Answer 1

Using backticks (like in your first sample) should work but you have to use them everywhere the table name is mentioned.

Dialect has openQuote() and closeQuote() methods that translate those backticks into quotes appropriate for given database. Those default to double quote (") for most databases including DB2 and HSQL. I don't have either on hand right now to try this but I've had it working for DB2 / Oracle / MySQL combination in the past.

Can you post a specific error and the stack trace you're getting?

All that said, if you can change the table name to a non-reserved word I would strongly recommend that you do - you'll save yourself a lot of headache in the end.

Answer 2

I ended up doing create alias groups for group; in DB2 and changing table name in HSQLDB, which solved my problem. Now I map groups table, and there are no longer this problem.