Hi,
Apologies if this has been asked and answered. My searches have not returned anything helpful.

I want to capture the SQL generated by hibernate for a particular HQL query. I have already enabled

hibernate.show_sql=true
hibernate.format_sql=true

but the app produces quite a few queries and it is extremely difficult to locate the one that needs to be debugged. I'm hoping that there is a way through the hibernate API to get the translated HQL so that I can print it to a simple log and run the query.

I have tried using Query.setComment and it does not seem to work for me. My thinking was that I could add a comment to the query and search for it. But I cannot get this API to work, and even if I could, adding

log4j.logger.org.hibernate.SQL=DEBUG

to my log4j logger isn't working either - the queries still go to the console.

So apparantly my searching and log4j skills are lacking, but I guarantee I'm not lacking the appreciation for anyone that could shed a little light on how to capture the SQL of a query that needs fixing.

Thanks for any help

You have the right debug configuration to capture the SQL statements. You need to define another appender for log4j if you want those statements to go somewhere else as well.

e.g. this properties file will log all messages to console and the org.hibernate.SQL package to both console and a file (sql.log) (I think, little rusty in log4j)

Thanks for the reply,
In either case I can't seem to get hibernate to log the sql to anything other than the console. Even if I could, this solution would only be useful if Query.setComment also worked, but even when I set a big obvious string as the comment, it is definately not among all the SQL being written to the console.

And even if setComment worked that still means I'm logging these hundreds of SQL statments to a log and searching the log for that comment. That's kind of a hacked solution. Is there really no direct API that would allow me to simply translate a particular HQL query to SQL? I'm new to hibernate, but not to coding, and I can't believe that this is how everyone debugs their HQL.

So you have a query producing the wrong results. You have a log with a ton of queries, one of which is the bad one. I have to believe that everyone here isn't pouring through giant logs every time they want to find and debug their query.

Sorry. you can tell I'm a bit frustrated.

I found a solution, thought I'd share:

protected String getSQLString(String hql) {
QueryTranslator translator = astQueryTranslatorFactory.createQueryTranslator(hql, hql, Collections.EMPTY_MAP, (SessionFactoryImplementor)sessionFactory);
translator.compile(Collections.EMPTY_MAP, true);
return translator.getSQLString();
}

If anyone has any better ideas, I'd love to hear em.