Grails | Groovy | Hibernate | Query Builder | HibernateCriteriaBuilder | Criteria Builder | Criteria Builder To SQL | Grails Create Criteria To SQL Conversion | Grails Convert Query Builder To MySQL | Criteria Builder SQL Restriction | SQLRestriction | Grails SQL Restriction

Grails | Groovy | Hibernate | Query Builder | HibernateCriteriaBuilder | Criteria Builder | Criteria Builder To SQL | Grails Create Criteria To SQL Conversion | Grails Convert Query Builder To MySQL | Criteria Builder SQL Restriction | SQLRestriction | Grails SQL Restriction.

Below a query | criteria builder. We will convert below hibernate criteria builder to SQL our-self. So with this feature we can check each and every query generated by hibernate query builder and can take necessary step if we need any. With this feature we also can retrieve query binding parameters. At first we will see our grails domain structure I used in my example codes. Below example also to show how we can use SQL restriction in criteria builder. In SQL Restriction section we can write plain SQL.

class Subscription {
    Long id
    Account account
}

class Account {
    Long id
    User createdBy
}

class User {
    Long id
    String name
}

Now below is our criteria builder (as well as we called it closure will pass to criteria builder to execute it):

def test() {
    Closure closure = {
        HibernateCriteriaBuilder builder = delegate

        setMaxResults(2)
        String a1 = CriteriaToSql.createAlias(builder, "account")
        String a2 = CriteriaToSql.createAlias(builder, "createdBy", "${a1}.createdBy")

        println(builder.properties.sql_aliases)


        gt("id", -1L)
        ne("code", "NOT_EQ_CODE")
        gt("${a1}.id", -2L)
        gt("${a2}.id", -3L)

        sqlRestriction("exists (select a.id from account AS a where a.id=this_.id)")

        projections {
            property("code")
        }

        order("updated", "desc")
        order("id", "desc")
    }
    CriteriaToSql.check(Subscription.class, closure)
    render("")
}

And finally our desired class which will product SQL from given criteria builder:

import grails.orm.HibernateCriteriaBuilder
import org.apache.commons.lang.StringEscapeUtils
import org.hibernate.Criteria
import org.hibernate.engine.spi.LoadQueryInfluencers
import org.hibernate.engine.spi.SessionFactoryImplementor
import org.hibernate.internal.CriteriaImpl
import org.hibernate.internal.SessionImpl
import org.hibernate.loader.OuterJoinLoader
import org.hibernate.loader.OuterJoinableAssociation
import org.hibernate.loader.PropertyPath
import org.hibernate.loader.criteria.CriteriaJoinWalker
import org.hibernate.loader.criteria.CriteriaLoader
import org.hibernate.loader.criteria.CriteriaQueryTranslator
import org.hibernate.persister.entity.OuterJoinLoadable
import org.hibernate.sql.JoinType

import java.lang.reflect.Field
/**
 * Created by pritom on 17/07/2017.
 */
class CriteriaToSql {
    static int counter = 0

    static void check(Class domainClass, Closure closure) {
        Criteria criteria = domainClass.createCriteria().buildCriteria {
            and closure
        }

        CriteriaImpl c = (CriteriaImpl) criteria
        SessionImpl s = (SessionImpl) c.getSession()

        SessionFactoryImplementor factory = (SessionFactoryImplementor) s.getSessionFactory()
        String[] implementors = factory.getImplementors(c.getEntityOrClassName())
        LoadQueryInfluencers loadQueryInfluencers = new LoadQueryInfluencers()
        CriteriaLoader loader = new CriteriaLoader((OuterJoinLoadable) factory.getEntityPersister(implementors[0]), factory, c, implementors[0], loadQueryInfluencers)
        CriteriaQueryTranslator translator = new CriteriaQueryTranslator(factory, c, domainClass.canonicalName, "this_")
        CriteriaJoinWalker walker = new CriteriaJoinWalker(
                (OuterJoinLoadable) factory.getEntityPersister(implementors[0]),
                translator, factory, criteria, domainClass.canonicalName, loadQueryInfluencers
        )

        Field f = OuterJoinLoader.class.getDeclaredField("sql")
        f.setAccessible(true)
        String sql = (String) f.get(loader), printable = ""
        int index = 0, max = 250
        while (true) {
            if (sql.length() > (index * max) + max) {
                printable = printable + "\r\n" + sql.substring(index * max, max + (index * max))
                index++
            }
            else {
                printable = printable + "\r\n" + sql.substring(index * max)
                break
            }
        }
        println("SQL_QUERY=${printable}")

        f = CriteriaLoader.class.getDeclaredField("querySpaces")
        f.setAccessible(true)
        println("SQL_ALIASES=${walker.getAssociations()*.rhsAlias}")

        println("PARAMETERS=${translator.getQueryParameters().positionalParameterValues}")
    }

    static Map getSql(Class domainClass, Closure closure) {
        Criteria criteria = domainClass.createCriteria().buildCriteria {
            and closure
        }

        CriteriaImpl c = (CriteriaImpl) criteria
        SessionImpl s = (SessionImpl) c.getSession()

        SessionFactoryImplementor factory = (SessionFactoryImplementor) s.getSessionFactory()
        String[] implementors = factory.getImplementors(c.getEntityOrClassName())
        LoadQueryInfluencers loadQueryInfluencers = new LoadQueryInfluencers()
        CriteriaLoader loader = new CriteriaLoader((OuterJoinLoadable) factory.getEntityPersister(implementors[0]), factory, c, implementors[0], loadQueryInfluencers)
        CriteriaQueryTranslator translator = new CriteriaQueryTranslator(factory, c, domainClass.canonicalName, "this_")
        CriteriaJoinWalker walker = new CriteriaJoinWalker(
                (OuterJoinLoadable) factory.getEntityPersister(implementors[0]),
                translator, factory, criteria, domainClass.canonicalName, loadQueryInfluencers
        )

        Field f = OuterJoinLoader.class.getDeclaredField("sql")
        f.setAccessible(true)
        String sql = (String) f.get(loader)

        Map parts = [
                select: sql.substring(7, sql.indexOf("from")),
                from: sql.substring(sql.indexOf("from") + 5, sql.indexOf("where")),
                where: sql.substring(sql.indexOf("where") + 6),
                joins: [:]
        ]
        walker.getAssociations().each { OuterJoinableAssociation assoc ->
            String key = assoc.propertyPath.property
            PropertyPath propertyPath = assoc.propertyPath
            while (true) {
                if (propertyPath.parent?.property) {
                    key = propertyPath.parent.property + "." + key
                    propertyPath = propertyPath.parent
                }
                else {
                    break
                }
            }
            parts.joins[key] = [domain: assoc.propertyPath.property, alias: assoc.rhsAlias]
        }

        int lastIndex = -1
        translator.getQueryParameters().positionalParameterValues.each { param1 ->
            int index = parts.where.indexOf("?", lastIndex)
            if (index >= 0) {
                param1 = param1.rawValue()
                param1 = param1 instanceof String ?  "'${StringEscapeUtils.escapeSql(param1)}'" : param1
                parts.where = parts.where.substring(0, index) + param1 + parts.where.substring(index + 1)
                lastIndex = index + param1.toString().length()
            }
        }
        return parts
    }

    static String createAlias(HibernateCriteriaBuilder criteriaBuilder, String field, String aliasPath = field, JoinType joinType = JoinType.INNER_JOIN) {
        if (!criteriaBuilder.properties.lixt) {
            criteriaBuilder.metaClass.lixt = [:]
        }
        Criteria subCriteria = criteriaBuilder.criteria.subcriteriaList.find { it.path == aliasPath }

        if(subCriteria) {
            return subCriteria.alias
        }
        else {
            String alias = "${field}xxxxxxxx${++counter}"
            criteriaBuilder.createAlias(aliasPath, alias, joinType)
            /* The below variable is SQL alias path */
            String sqlAlias = alias.substring(0, 10) + (criteriaBuilder.properties.lixt.size() + 1) + "_"
            criteriaBuilder.properties.lixt[alias] = sqlAlias
            return alias
        }
    }
}

And finally we would have below output:

[PV4736-12429, MS2431-12503]

[PV4736-12429, MS2431-12503]

SQL_QUERY=
select this_.code as y0_ from subscription this_
inner join account accountxxx1_ on this_.account_id=accountxxx1_.id 
inner join user createdbyx2_ on accountxxx1_.created_by_id=createdbyx2_.id 
where (this_.id>? and this_.code<>? and accountxxx1_.id>? and createdbyx2_.id>?
and exists (select a.id from account AS a where a.id=this_.id))
order by this_.updated desc, this_.id desc

SQL_ALIASES=[accountxxx1_, createdbyx2_]

PARAMETERS=[-1, NOT_EQ_CODE, -2, -3]

Laravel 5.X: How Do I Get Raw SQL Query As String With Binders From Query Builder | Query Builder To Raw SQL

Laravel 5.X: How Do I Get Raw SQL Query As String With Binders From Query Builder. Its very easy and now few steps from here. It's interesting.

use Illuminate\Support\Facades\DB;

$model = DB::table('employee')
    ->where("employee.id", "<>", 0)
    ->whereIn("employee.id", array(1, 2))
    ->where("employee.type", "PERMANENT")
    ->where(function ($q) {
        $q->where("employee.id", 1)->orWhere("employee.id", 2);
    })
    ->limit(10)->offset(0)
    ->groupBy("employee.id")
    ->select("employee.id");

$replace = function ($sql, $bindings) {
    $pos = 0;
    $needle = '?';
    foreach ($bindings as $replace) {
        if (is_string($replace)) {
            $replace = DB::connection()->getPdo()->quote($replace);
        }
        $pos = strpos($sql, $needle, $pos);
        if ($pos !== false) {
            $sql = substr_replace($sql, $replace, $pos, strlen($needle));
            $pos = $pos + strlen($replace);
        }
    }
    return $sql;
};

$sql = $replace($model->toSql(), $model->getBindings());
echo "SQL=" . $sql;

And output would be like below:

SELECT `employee`.`id`
FROM `employee`
WHERE `employee`.`id` <> 0 AND `employee`.`id` IN (1, 2) AND
      `employee`.`type` = 'PERMANENT' AND (`employee`.`id` = 1 OR `employee`.`id` = 2)
GROUP BY `employee`.`id`
LIMIT 10 OFFSET 0