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]

The Grails Hibernate Dynamica/On Fly CriteriaBuilder

The HibernateCriteriaBuilder is a great Grails feature, that you should know when working with the Grails framework. It is an alternative way to create dynamic or static queries with a groovy syntax based on the Criteria API of Hibernate.


Although there is some documentation about it in the Grails Pages, this does not demonstrate some advanced features like joins or pagination. There are several blog posts about the HibernateCriteriaBuilder, but most of them only show a simple example. I want to share my experience with a more complex example that demonstrates some features like:

• query attributes of associated entities using joins
• pagination parameters and their consequences
• reuse of the criteria definition for similar domain classes

Query attributes of associated entities using joins

The example code uses a parcel instance as an example to carry parameter values to construct the criteria.

def example = new Parcel(parcelNumber: '1234%');

def crit = Parcel.createCriteria()
def parcels = crit.list {
  ilike("parcelNumber", example.parcelNumber)
  gt("parcelPrice", example.parcelPrice)
  recipient {
    ilike("lastName", example.recipient.lastName)
  }
  depot {
     address {
       ilike("city", example.depot.address.city)
       ilike("postalCode", example.depot.address.postalCode)
     }
  }
}

Dynamic query parameters

We extend the example to include the filtering only when the value of the example object is not empty.
def example = new Parcel(parcelNumber: '1234%');

def crit = Parcel.createCriteria()

def parcels = crit.list {
    if (example.parcelNumber) ilike("parcelNumber", example.parcelNumber)
    if (example.parcelPrice) gt("parcelPrice", example.parcelPrice)
    if (example.recipient) {
        recipient {
            if (example.recipient?.lastName) {
                ilike("lastName", example.recipient.lastName)
            }
        }
    }
    if (example.depot?.address) {
        depot {
            address {
                if (example.depot.address.city) {
                    ilike("city", example.depot.address.city)
                }
                if (example.depot.address.postalCode) {
                    ilike("postalCode", example.depot.address.postalCode)
                }
            }
        }
    }
}

Reuse of the criteria definition

We have 2 domain classes with the same properties: Parcel and ParcelArchive. We want to reuse the criteria definition, but we must be careful, because extracting it with your IDE ("extract method" refactoring) could break your code. You can use the groovy with.{closure} to extract the criteria:

// search Parcel

def example = new Parcel(parcelNumber: '1234%');

def crit = Parcel.createCriteria()
def parcels = crit.list {
    buildSearchCriteria(example, crit)
}

// search ParcelArchive

def example = new ParcelArchive(parcelNumber: '1234%');

def crit = ParcelArchive.createCriteria()
def parcels = crit.list {
    buildSearchCriteria(example, crit)
}

// criteria method for both

private static void buildSearchCriteria(def example, HibernateCriteriaBuilder crit) {
    crit.with {  
    // make the criteriaBuilder the receiver, because the definitions have been extracted a method
        if (example.parcelNumber) {
            ilike("parcelNumber", example.parcelNumber)
            if (example.parcelPrice) {
                gt("parcelPrice", example.parcelPrice)
            }
            if (example.recipient) {
                recipient {
                    if (example.recipient?.lastName) {
                        ilike("lastName", example.recipient.lastName)
                    }
                }
            }
            if (example.depot?.address) {
                depot {
                    address {
                        if (example.depot.address.city) i {
                            like("city", example.depot.address.city)
                        }
                        if (example.depot.address.postalCode) {
                            ilike("postalCode", example.depot.address.postalCode)
                        }
                    }
                }
            }
        }
    }
} 

Check and create criteria dynamically

package com.pritom

import grails.orm.HibernateCriteriaBuilder
import org.codehaus.groovy.grails.commons.GrailsApplication

class DataService { 

    /**
     * Now if you want to search schoolName against a Student you 

     * have to something like below:
     */
    def searchStudentBySchoolName() {
        def key = "discpline.school.name";
        def value = "Some Value";
        def criteria = Student.createCriteria();
        criteria.list {
            buildSearchCriteria(criteria, key.trim("."), value);
        }
    }

    /**
     * isDomainClass() ? Somehow check that the name is a domain class
     * Otherwise it is a property in the domain.
     * 
     * Implement getDomainClassByName() to get the domain class dynamically
     */

    def buildSearchCriteria(HibernateCriteriaBuilder criteria, String[] fields, String value) {
        criteria.with {
            for (Integer index2 = 0; index2 < fields.size() - 1; index2++) {
                if(fields[index2] != null && fields[index2 + 1] != null && isDomainClass(fields[index2])
                        && !isDomainClass(fields[index2 + 1])) {
                    def domainClass = getDomainClassByName(fields[index2].getMetaClass().getTheClass());
                    def domainCriteria = domainClass.createCriteria();
                    'in' (fields[index2], domainCriteria.list {
                        like(fields[index2 + 1], "%" + value + "%")
                    });
                } else if(fields[index2] != null && fields[index2 + 1] != null && isDomainClass(fields[index2])
                        && isDomainClass(fields[index2 + 1])) {
                    def domainClass = getDomainClassByName(fields[index2]).getMetaClass().getTheClass();
                    String current2 = fields[index2];
                    fields[index2] = null;
                    def domainCriteria = domainClass.createCriteria();
                    'in' (current2, domainCriteria.list{
                        buildSearchCriteria(domainCriteria, fields, value)
                    });
                }
            }
        }
    }
} 

/* The domain classes are below: */

class Student {
    Integer id;
    String studentId;
    Integer age;
     det belongsTo = [
        discipline: Discipline
    ]
} 

class Discipline {
    Integer id;
    String disciplineName; 

     def belongsTo = [
        school: School
    ]
}

class School {
    Integer id;
    String schoolName;
} 

http://www.viaboxxsystems.de/the-grails-hibernatecriteriabuilder