Using HibernateInterceptor to make database schema name configurable

To fit in our DTAP (Development Test Acceptance Production) cycle I needed to make our application database schema name configurable. The idea is that multiple instances of the application and database can run side-by-side on the same server so each developer and tester can have their own environment if needed.

The problem I quickly realized is that our application uses two different schemas but only one Hibernate Session and the configuration is done using annotations where the schema name for each bean is hardcoded on the @Table annotation. No chance to replace that at runtime, except maybe messing with Reflection which I'm not going to get into now.

So the solution I went with was replacing the schema name on the query just before it is send to the DB using a HibernateInterceptor as described in this blog entry.

Here's how the solution looked in short:

public class ConfigurableSchemaNameHibernateInterceptor extends EmptyInterceptor {
  public static final String DB_SCHEMA1_PATTERN = "schema1";
  public static final String DB_SCHEMA2_PATTERN = "schema2";

  @Override
  public String onPrepareStatement(String sql) {
    String prepedStatement = super.onPrepareStatement(sql);
    prepedStatement = prepedStatement.replaceAll("(?i)" + DB_SCHEMA1_PATTERN, schema1ConfiguredName);
    prepedStatement = prepedStatement.replaceAll("(?i)" + DB_SCHEMA2_PATTERN, schema1ConfiguredName);

    logger.debug("Query replaced, old sql: " + sql);
    logger.debug("Query replaced, new sql: " + prepedStatement);

    return prepedStatement;
  }
}

Maybe this is not too clean and a bit error prone (could accidently replace some genuine content..). But I learned some new things about Hibernate so I'm blogging this anyway. Next time I'll refactor it to use multiple Hibernate sessions and no harcoded schema name.