Hibernate and dry-running HQL queries statically

Question

I'd like to "dry-run" Hibernate HQL queries. That is I'd like to know what actual SQL queries Hibernate will execute from given HQL query without actually executing the HQL query against real database.

I have access to hibernate mapping for tables, the HQL query string, the dialect for my database. I have also access to database if that is needed.

Now, how can I find out all the SQL queries Hibernate can generate from my HQL without actually executing the query against any database? Are there any tools for this?

Note, that many SQL queries can be generated from one HQL query and the set of generated SQL queries may differ based on the contents of database.

I am not asking how to log SQL queries while HQL query is executing.

Edit: I don't mind connecting to database to fetch some metadata, I just don't want to execute queries.

Edit: I also know what limits and offsets are applied to query. I also have the actual parameters that will be bind to query.

Answer 1

The short answer is "you can't". The long answer is below.

There are two approaches you can take:

A) Look into HQLQueryPlan class, particularly its getSqlStrings() method. It will not get you the exact SQL because further preprocessing is involved before query is actually executed (parameters are bound, limit / offset are applied, etc...) but it may be close enough to what you want.

The thing to keep in mind here is that you'll need an actual SessionFactory instance in order to construct HQLQueryPlan, which means you won't be able to do so without "connecting to any database". You can, however, use in-memory database (SqlLite and the likes) and have Hibernate auto-create necessary schema for it.

B) Start with ASTQueryTranslatorFactory and descend into AST / ANTLR madness. In theory you may be able to hack together a parser that would work without relying on metadata but I have a hardest time imagining what is it you're trying to do for this to be worth it. Perhaps you can clarify? There has to be a better approach.

Answer 2

Update: for an offline, dry-run of some HQL, using HQLQueryPlan directly is a good approach. If you want to intercept every query in the app, while it's running, and record the SQL, you'll have to use proxies and reflection as described below.

Take a look at this answer for Criteria Queries.

For HQL, it's the same concept - you have to cast to Hibernate implementation classes and/or access private members, so it's not a supported method, but it will work with a the 3.2-3.3 versions of Hibernate. Here is the code to access the query from HQL (query is the object returned by session.createQuery(hql_string):

Field f = AbstractQueryImpl.class.getDeclaredField("session");
f.setAccessible(true);
SessionImpl sessionImpl = (SessionImpl) f.get(query);
Method m = AbstractSessionImpl.class.getDeclaredMethod("getHQLQueryPlan", new Class[] { String.class, boolean.class });
m.setAccessible(true);
HQLQueryPlan plan = (HQLQueryPlan) m.invoke(sessionImpl, new Object[] { query.getQueryString(), Boolean.FALSE });
for (int i = 0; i < plan.getSqlStrings().length; ++i) {
  sql += plan.getSqlStrings()[i];
}

I would wrap all of that in a try/catch so you can go on with the query if the logging doesn't work.

It's possible to proxy your session and then proxy your queries so that you can log the sql and the parameters of every query (hql, sql, criteria) before it runs, without the code that builds the query having to do anything (as long as the initial session is retrieved from code you control).