Grails on Groovy - Clear and Flush Current Hibernate Session Data and Evict All Query Cache Data | Clearing Hibernate Query Cache in Grails

Hibernate already has support for query cache. And we know that when we perform a big task there are huge number queries remain in our cache factory. And more important that in most cases we don't need this caches so as a result cache factory getting full with unusual cache data. This could be a performance issue - so it's better we clear cache on our own responsibility after a big task completed.

Below are the procedure to flush and clear current session (hibernate session) data so that cache factory have enough space for further execution.

import org.hibernate.SessionFactory import grails.util.Holders private static SessionFactory _sessionFactory static Boolean flushAndClearCache() { try { sessionFactory.currentSession.flush() sessionFactory.currentSession.clear() sessionFactory.getCache().evictEntityRegions() sessionFactory.getCache().evictCollectionRegions() sessionFactory.getCache().evictDefaultQueryRegion() sessionFactory.getCache().evictQueryRegions() return true } catch (Throwable ex) { log.error(ex.ex) return false } } static <T> T getBean(Class<T> requiredType) { try { return Holders.applicationContext.getBean(requiredType) } catch (Throwable e) { return null } } static SessionFactory getSessionFactory() { _sessionFactory = _sessionFactory ?: (_sessionFactory = getBean(SessionFactory)) }

Grails Groovy SessionFactory EnityKey SessionStatistics | Grails Get SQL Table Name From Domain Class | Grails Get SQL Table Field Name From Domain Class

Grails Groovy SessionFactory EnityKey SessionStatistics | Grails Get SQL Table Name From Domain Class | Grails Get SQL Table Field Name From Domain Class.

import org.hibernate.SessionFactory

import grails.util.Holders

import org.hibernate.engine.spi.EntityKey
import org.hibernate.stat.SessionStatistics
/**
 * Created by pritom on 27/07/2017.
 */
class HibernateSessionUtil {
    private static SessionFactory _sessionFactory

    public static void main(String[] args) {
        def domainInstance = "DomainClass".proxy(100L)
        checkIfObjectExistsInSession(domainInstance)
    }

    static void evictAllEntities() {
        SessionStatistics sessionStatistics = sessionFactory.currentSession.getStatistics()
        sessionStatistics.getEntityKeys().asList().each { EntityKey entityKey ->
            evict(entityKey.persisterClass.proxy(entityKey.identifier.toString().toLong()))
        }
    }

    static void evict(def instance) {
        sessionFactory.currentSession.evict(instance)
    }

    static Boolean checkIfObjectExistsInSession(def domainInstance) {
        SessionStatistics sessionStatistics = sessionFactory.currentSession.getStatistics()
        println("Total ${sessionStatistics.getEntityKeys().asList().size()} Object Exists in Session")
        Boolean exists = false
        sessionStatistics.getEntityKeys().asList().find { EntityKey entityKey ->
            println("EntityName=${entityKey.entityName},EntityId=${entityKey.identifier.toString()}")
            if (domainInstance.class.canonicalName.equals(entityKey.entityName) && domainInstance.id.toString().equals(entityKey.identifier.toString())) {
                exists = true
            }
        }
        return exists
    }

    static String getTableFieldName(Class clazz, String fieldName) {
        return sessionFactory.getClassMetadata(clazz).propertyMapping.getColumnNames(fieldName)[0]
    }

    static String getTableName(Class clazz) {
        return sessionFactory.getClassMetadata(clazz).getTableName()
    }

    static boolean flushAndClearCache() {
        try {
            sessionFactory.currentSession.flush()
            sessionFactory.currentSession.clear()
            sessionFactory.getCache().evictEntityRegions()
            sessionFactory.getCache().evictCollectionRegions()
            sessionFactory.getCache().evictDefaultQueryRegion()
            sessionFactory.getCache().evictQueryRegions()
            return true
        }
        catch (Exception ex) {
            ex.printStackTrace()
            return false
        }
    }

    static SessionFactory getSessionFactory() {
        _sessionFactory = _sessionFactory ?: (_sessionFactory = Holders.applicationContext.getBean(SessionFactory))
    }

    static {
        EntityKey.metaClass.getPersisterClass = {
            return persister.entityTuplizer.mappedClass
        }
    }
}

c

Grails Groovy Hibernate | Hibernate Criteria Builder | Projection | Custom Projection | Group By Projection | PropertyProjection

Grails Groovy Hibernate | Hibernate Criteria Builder | Projection | Custom Projection | Group By Projection | PropertyProjection.

It's easy to add projection custom. We can add custom projection and custom group by property.

import org.hibernate.criterion.Projections
import org.hibernate.criterion.Projection
import org.hibernate.criterion.PropertyProjection
import org.hibernate.Criteria
import org.hibernate.criterion.CriteriaQuery
import org.hibernate.HibernateException
org.hibernate.criterion.ProjectionList projectionList = []

ProjectionList projectionList = []
projectionList.add(new PropertyProjection("id") {
    @Override
    public String toSqlString(Criteria criteria, int position, CriteriaQuery criteriaQuery) throws HibernateException {
        return "this_.id as y0_"
    }
})
projectionList.add(new PropertyProjection("created") {
    @Override
    public String toSqlString(Criteria criteria, int position, CriteriaQuery criteriaQuery) throws HibernateException {
        return "MAX(this_.created) as y1_"
    }
})
projectionList.add(Projections.groupProperty("belongsTo.id"))
PropertyProjection groupBy = new PropertyProjection("id", true) {
    @Override
    public String toSqlString(Criteria criteria, int position, CriteriaQuery criteriaQuery) throws HibernateException {
        return "belongsto1_.id as y1_"
    }

    @Override
    public String toGroupSqlString(Criteria criteria, CriteriaQuery criteriaQuery) throws HibernateException {
        return "belongsto1_.id"
    }
}
projectionList.add(groupBy)
Closure closure = {
    setProjection(projectionList)
}
List list =  Domain.createCriteria().list {
    and closure
}

But you want to make it simpler then you can use below function easily:

import org.hibernate.criterion.CriteriaSpecification
import org.hibernate.criterion.Projections
import org.hibernate.type.DoubleType
import org.hibernate.type.Type

resultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP)
projections {
    groupProperty("id", "id")
    groupProperty("name", "name")
    addProjectionToList(Projections.sqlProjection(
            "sum(id * 0.2) as totalPrice",
            ["totalPrice"] as String[],
            [DoubleType.INSTANCE] as Type[],
    ), "complexSqlCalculation")
}

Grails | Groovy | Create Criteria | Hibernate Criteria Builder | Custom Criteria Order | Custom Sort By | Custom Order Criteria

Grails | Groovy | Create Criteria | Hibernate Criteria Builder | Custom Criteria Order | Custom Sort By | Custom Order Criteria. 

In Grails we may need sometime to add sort / order by with some aggregate function as sum of two fields. Suppose we have a Grails / Groovy domain which has two field named "amount" and "tax", now we want to sort by sum of these two fields. So we can do that using below sample code: 

import groovy.lang.Closure
import org.hibernate.Criteria
import org.hibernate.HibernateException
import org.hibernate.criterion.CriteriaQuery
import org.hibernate.criterion.Order as CriterionOrder

Closure closure = {
    addOrder(new CriterionOrder("amount", params.dir) {
        @Override
        String toSqlString(Criteria criteria, CriteriaQuery criteriaQuery) throws HibernateException {
        return "(this_.amount + this_.tax) asc"
        }
    })
    projections {
        property("id")
        property("amount")
    }
}
List list = Domain.createCriteria().list {
    and closure
}

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]

How can I determine whether an entity already exists in the Hibernate session cache

Hibernate use first & second level cache to store database data fetch from database and actually its Hibernate Session. So when your read data from database it stores in Hibernate Session. There is a separate Hibernate Session for each request. Sometimes we need to identify if any object exists in session or not. Below is a simple code snippet to determine if any object exists in current Hibernate Session or not. The most important thing is that you must use proxy to reference the object to check.

package com.pkm

import grails.util.Holders
import org.hibernate.SessionFactory
import org.hibernate.engine.spi.EntityKey
import org.hibernate.stat.SessionStatistics

/**
 * Created by pritom on 15/03/2017.
 * Session.html#getStatistics
 * SessionStatistics.html#getEntityKeys
 */
class HibernateSessionUtil {
    private static SessionFactory sessionFactory

    public static void main(String[] args) {
        def domainInstance = "DomainClass".proxy(100L)
        checkIfObjectExistsInSession(domainInstance)
    }

    static Boolean checkIfObjectExistsInSession(def domainInstance) {
        SessionStatistics sessionStatistics = sessionFactory.currentSession.getStatistics()
        println("Total ${sessionStatistics.getEntityKeys().asList().size()} Object Exists in Session")
        Boolean exists = false
        sessionStatistics.getEntityKeys().asList().find { EntityKey entityKey ->
            println("EntityName=${entityKey.entityName},EntityId=${entityKey.identifier.toString()}")
            if (domainInstance.class.canonicalName.equals(entityKey.entityName) &&
                    domainInstance.id.toString().equals(entityKey.identifier.toString())) {
                exists = true
            }
        }
        return exists
    }

    static {
        sessionFactory = sessionFactory ?: (sessionFactory = getBean(SessionFactory))
    }

    static <T> T getBean(Class<T> requiredType) {
        try {
            return Holders.applicationContext.getBean(requiredType)
        }
        catch (Exception e) {
            return null
        }
    }
}

Grails Hibernate check if and object is initialized

Hibernate.isInitialized(object)

Grails track hibernate transaction begin, flush, commit and rollback status

Package: org.springframework.orm.hibernate4

Class: HibernateTransactionManager

Hibernate evict an persistent object from hibernate session

package com.pkm.evict_entity

import grails.util.Holders
import org.hibernate.SessionFactory

/**
 * Created by pritom on 3/08/2016.
 */
class EvictEntityInstance {
    public static void _evict(def domainInstance) {
        getBean(SessionFactory).currentSession.evict(domainInstance)
    }

    public static <T> T getBean(Class<T> requiredType) {
        try {
            return Holders.applicationContext.getBean(requiredType)
        }
        catch (Exception e) {
            return null
        }
    }
}

Display Hibernate Sql Parameter Values

Configure Log4j in Hibernate

Create a file named 'log4j.properties' under main project, where 'hibernate.cfg.xml' exists with following contents. This configuration will log sql in a file, the location you specified and also log in your project IDE console.

# Log everything. Good for troubleshooting  
log4j.logger.org.hibernate=INFO
# Log all SQL DML statements as they are executed
log4j.logger.org.hibernate.SQL=TRACE
# Log all JDBC parameters(TRACE|INHERITED);
log4j.logger.org.hibernate.type=TRACE
log4j.logger.org.hibernate.hql.ast.AST=info
# Log all SQL DDL statements as they are executed(INHERITED|WARN)
log4j.logger.org.hibernate.tool.hbm2ddl=INHERITED
log4j.logger.org.hibernate.hql=debug
log4j.logger.org.hibernate.cache=info
log4j.logger.org.hibernate.jdbc=debug   
# Log all second-level cache activity
log4j.logger.org.hibernate.cache=INHERITED
# Log all OSCache activity - used by Hibernate
log4j.logger.com.opensymphony.oscache=INHERITED
# Log transaction related activity
log4j.logger.org.hibernate.transaction=INHERITED
# Log all JDBC resource acquisition
log4j.logger.org.hibernate.jdbc=INHERITED
# Log all JAAS authorization requests
log4j.logger.org.hibernate.secure=INHERITED
# Root logger option  
log4j.rootLogger=INFO, CUSTOM_FILE, CUSTOM_STD_OUT  

# Define the file appender
log4j.appender.CUSTOM_FILE=org.apache.log4j.DailyRollingFileAppender
# Set the DatePattern (Every hour interval)
log4j.appender.CUSTOM_FILE.DatePattern='['yyyy-MM-dd-HH'].log'
# Set the DatePattern (Every minute interval)
#log4j.appender.CUSTOM_FILE.DatePattern='['yyyy-MM-dd-HH-mm'].log'
# Set the append to false, should not overwrite
log4j.appender.CUSTOM_FILE.Append=true
# Set the name of the file.
# It'll be created every hour/minute with different 
# filename(appnded with yyyy-MM-dd-HH/yyyy-MM-dd-HH-mm).
log4j.appender.CUSTOM_FILE.File=C:/tmp/debug.log
# Define the layout for file appender
log4j.appender.CUSTOM_FILE.layout=org.apache.log4j.PatternLayout
log4j.appender.CUSTOM_FILE.layout.conversionPattern= %d{HH:mm:ss} %m%n

# Direct log messages to stdout  
log4j.appender.CUSTOM_STD_OUT=org.apache.log4j.ConsoleAppender  
log4j.appender.CUSTOM_STD_OUT.Target=System.out  
log4j.appender.CUSTOM_STD_OUT.layout=org.apache.log4j.PatternLayout  
log4j.appender.CUSTOM_STD_OUT.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n 
log4j.appender.CUSTOM_STD_OUT.layout.ConversionPattern=%d{ABSOLUTE} - %m%n

Execute following line of code when project start/run, before performing any CRUD operations:

ClassLoader loader = Thread.currentThread().getContextClassLoader();
URL url = loader.getResource("log4j.properties");
System.out.println(url);
PropertyConfigurator.configure(url);

You have to include log4j-1.2.16.jar in your project

When you perform query, output would be like this in your IDE consle:

/* When perform HQL */
10:30:29,149 - HQL: SELECT MIN(id) FROM com.pkm.domains.User

/* When perform SQL */
10:30:29,149 - SQL: select min(user0_.id) as col_0_0_ from User user0_
10:30:29,149 - throwQueryException() : no errors
10:30:29,191 - select min(user0_.id) as col_0_0_ from User user0_ limit ?
10:30:29,226 - Found [14] as column [col_0_0_]
10:30:29,228 - select user0_.id as id0_0_, user0_.date_updated as date2_0_0_, user0_.email as email0_0_, user0_.first_name as first4_0_0_, user0_.last_name as last5_0_0_, user0_.phone_number as phone6_0_0_, user0_.area_code as area7_0_0_ from User user0_ where user0_.id=?
10:30:29,230 - binding parameter [1] as [BIGINT] - 14
10:30:29,238 - Found [2014-05-10 10:23:08.0] as column [date2_0_0_]
10:30:29,238 - Found [pritomkucse@gmail.com] as column [email0_0_]
10:30:29,238 - Found [Pritom] as column [first4_0_0_]
10:30:29,239 - Found [Kumar Mondal] as column [last5_0_0_]

/* Update Query */
10:30:29,261 - update User set date_updated=?, email=?, first_name=?, last_name=?, phone_number=?, area_code=? where id=?
10:30:29,262 - binding parameter [1] as [TIMESTAMP] - Sat May 10 10:30:29 ALMT 2014
10:30:29,263 - binding parameter [2] as [VARCHAR] - pritomkucse@gmail.com
10:30:29,263 - binding parameter [3] as [VARCHAR] - Pritom
10:30:29,264 - binding parameter [4] as [VARCHAR] - Kumar Mondal
10:30:29,264 - binding parameter [7] as [BIGINT] - 14

Download complete example. This example also contains hibernate interceptor logic