Getting the SQL for HQL and Criteria Queries

OK, so, I already showed how we can get the SQL that was generated from a LINQ query. Of course, we can do the same for both HQL and Criteria APIs as well (QueryOver is just a wrapper around Criteria, mind you).

So, for HQL (and SQL), it goes like this:

   1: public static class QueryExtensions

   2: {

   3:     private static readonly PropertyInfo sessionProperty = typeof (QueryImpl).GetProperty("Session", BindingFlags.NonPublic | BindingFlags.Instance);

   4:  

   5:     public static String ToSql(this IQuery query)

   6:     {

   7:         if (query is QueryImpl)

   8:         {

   9:             var session = sessionProperty.GetValue(query, null) as ISession;

  10:             var sessionImpl = session.GetSessionImplementation();

  11:             var translatorFactory = new ASTQueryTranslatorFactory();

  12:             var translators = translatorFactory.CreateQueryTranslators(query.QueryString, null, false, sessionImpl.EnabledFilters, sessionImpl.Factory);

  13:  

  14:             return translators[0].SQLString;

  15:         }

  16:         else if (query is SqlQueryImpl)

  17:         {

  18:             return (query.QueryString);

  19:         }

  20:  

  21:         throw (new NotSupportedException("Query type is not supported."));

  22:     }

  23: }

You can pass any implementation if IQuery, such as one produced from ISession.CreateQuery() or ISession.CreateSQLQuery(). The static field is merely for performance reasons.

As for Criteria:

   1: public static class CriteriaExtensions

   2: {

   3:     public static String ToSql(this ICriteria criteria)

   4:     {

   5:         var criteriaImpl = criteria as CriteriaImpl;

   6:         var sessionImpl = criteriaImpl.Session;

   7:         var factory = sessionImpl.Factory;

   8:         var implementors = factory.GetImplementors(criteriaImpl.EntityOrClassName);

   9:         var loader = new CriteriaLoader(factory.GetEntityPersister(implementors[0]) as IOuterJoinLoadable, factory, criteriaImpl, implementors[0], sessionImpl.EnabledFilters);

  10:  

  11:         return loader.SqlString.ToString();

  12:     }

  13: }

And finally, QueryOver, just a small wrapper around the Criteria version:

   1: public static class QueryOverExtensions

   2: {

   3:     public static String ToSql(this IQueryOver queryOver)

   4:     {

   5:         var criteria = queryOver.UnderlyingCriteria;

   6:         return (criteria.ToSql());

   7:     }

   8: }

Hope you find this useful! Winking smile

Published by

Ricardo Peres

Team Leader at Dixons Carphone. Microsoft MVP.

Leave a Reply

Your email address will not be published. Required fields are marked *