Hibernate translation capabilities

Question

Hi

Our project must be able to run both in Oracle and SQL Server. The problem is we have a number of HQL + native queries with non-standard operators (i.e. bitand and || ) and functions ( i.e. SUBSTR ) that work fine in Oracle but not in SQL Server.

I wonder if Hibernate is capable of translating them dynamically. I suppose that with HQL maybe it does, because it creates an AST but I doubt the same applies with native queries.

Additional question: what's the best approach of dealing with these troublesome queries ? Conditionals, subclassing, others ... the goal is not to modify the code a lot.

Thanx in advance

Answer 1

I suggest moving the HQL queries from the code to an external .hbm file and to use named queries before switching the Database. The HQL queries shouldn't be a problem as you already said. Native queries are a problem and you have to find the equivalent for the other DBMS. But by putting the queries into the external file you can then configure the sessionfactory to use the database specific .hbm file and do not need to change the code which depends only on the named query which can be a native query or a HQL To get a named Query you can do the foloowing:

Query query = session.getNamedQuery("YourNamedHQLorSQLQuery");

Answer 2

Use custom Dialects for HQL. Instead of using ||, create your own function called concat. Then, in the SQL Server dialect add this to the constructor:

registerFunction("concat", new VarArgsSQLFunction(Hibernate.STRING, "", "+", ""));

You don't have to change the Oracle dialect because Oracle already has a concat function so it just passes through, but for other functions, you may need to register new functions in both.

For SQL queries, since you're building them dynamically anyway, you could use base class methods, for example super.addBitAndClause(leftSide, rightSide).

You can even get to the dialect dynamically, although Hibernate didn't make it easy by putting on the interface:

Dialect d = ((SessionFactoryImpl)sessionFactory).getDialect()