Using a CASE Statement/If Condition in HQL select query

SELECT name, 

CASE WHEN name = 'Name' then 'Name is Name' else 'Name is not Name' end
From Domain

HQL Use MySQL Functions By Implementing Custom Dialect

Datasource.groovy File

hibernate {
    cache.use_second_level_cache = true
    cache.use_query_cache = true
    cache.region.factory_class = 'net.sf.ehcache.hibernate.EhCacheRegionFactory'
    cache.provider_class = 'net.sf.ehcache.hibernate.EhCacheProvider'
    format_sql = false
    use_sql_comments = false
}
grails.cache.config = {
    cache {
        name 'org.hibernate.cache.UpdateTimestampsCache'
        eternal true
        maxEntriesLocalHeap 500
        persistence {
            strategy localTempSwap
        }
    }
}

/* environment specific settings */
environments {
    development {
        dataSource {
            pooled = true
            url = "jdbc:mysql://localhost/db_name?useUnicode=yes&characterEncoding=UTF-8"
            driverClassName = "com.mysql.jdbc.Driver"
            username = "root"
            password = ""
            dialect = "com.pkm.custom.CustomMySQLDialect"
            dbCreate = "update"
            properties {
                maxActive = 1000
                maxIdle = 100
                minIdle = 50
                initialSize = 1
                minEvictableIdleTimeMillis = 60000
                timeBetweenEvictionRunsMillis = 60000
                numTestsPerEvictionRun = 3
                maxWait = 10000
                testOnBorrow = true
                testWhileIdle = true
                testOnReturn = true
                validationQuery = "SELECT 1"
                minEvictableIdleTimeMillis = 1800000
                timeBetweenEvictionRunsMillis = 1800000
            }
            logSql = false
            loggingSql = false
        }
    }
    test {
        dataSource {
            
        }
    }
    production {
        dataSource {
            
        }
    }
}
log4j = {
    debug 'org.hibernate.SQL'
    trace 'org.hibernate.type.descriptor.sql.BasicBinder'
}

Custom Dialect (.groovy) Used In Datasource

import org.hibernate.dialect.MySQL5InnoDBDialect
import org.hibernate.dialect.function.SQLFunctionTemplate
import org.hibernate.dialect.function.StandardSQLFunction
import org.hibernate.type.IntegerType
import org.hibernate.type.StringType
import org.hibernate.type.TimestampType

class CustomMySQLDialect extends MySQL5InnoDBDialect {
    public CustomMySQLDialect() {
        super()

        /* convert_tz('2015-01-01', '+00:00', '+10:00') = '2015-01-01 10:00' */
        /* convert_tz('2015-01-01 00:00', '+00:00', '+10:00') = '2015-01-01 10:00' */
        /* convert_tz('2015-01-01 20:00', '+00:00', '-10:00') = '2015-01-01 10:00' */
        registerFunction("convert_tz", new StandardSQLFunction("convert_tz"))

        /* group_concat(name) = 'a,b,c,c,d' */
        registerFunction("group_concat", new StandardSQLFunction("group_concat", new StringType()))

        /* group_concat_unique(name) = 'a,b,c,d' */
        registerFunction("group_concat_unique", new SQLFunctionTemplate(new StringType(), "group_concat(DISTINCT ?1)"))

        /* date_add_interval('2015-01-01', DAY, 10) = '2015-01-11' */
        /* date_add_interval('2015-01-20', DAY, -10) = '2015-01-10' */
        registerFunction("date_add_interval", new SQLFunctionTemplate(TimestampType.INSTANCE, "date_add(?1, INTERVAL ?3 ?2)"))

        /* index_of('am', 'i am good') = 3 */
        /* index_of('asm', 'i am good') = 0 */
        registerFunction("index_of", new SQLFunctionTemplate(new IntegerType(), "LOCATE(?1, ?2)"))

        /* format(number, decimal_place, locale) = format(200.343, 2, 'en_AU') = '200.34' */
        registerFunction("format", new StandardSQLFunction("format", new StringType()))
    }

    @Override
    public String transformSelectString(String select) {
        select = super.transformSelectString(select);
        return select;
    }
}

Hibernate HQL CRUD Example

HQLTest.java

package com.pkm.commands;

import com.pkm.domains.User;
import com.pkm.utils.HibernateUtil;
import java.util.List;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.Transaction;

public class HQLTest {
    public static void main(String[] args) {
        Session session = null;
     Transaction transaction = null;
        try {
            session = HibernateUtil.getSessionFactory().openSession();
            transaction = session.beginTransaction();
            
            /* INSERT QUERY */
            SQLQuery sQLQuery = session.createSQLQuery("INSERT INTO "
                    + "User(email, first_name, last_name, phone_number, area_code) "
                    + "Values(:email, :first_name, :last_name, :phone_number, :area_code)");
            sQLQuery.setParameter("email", "pritomkucse@gmail.com");
            sQLQuery.setParameter("first_name", "Pritom");
            sQLQuery.setParameter("last_name", "Kumar");
            sQLQuery.setParameter("phone_number", "01727499452");
            sQLQuery.setInteger("area_code", 88);
            int insertResult = sQLQuery.executeUpdate();
            System.out.println("Insert Result: " + insertResult);
            
            /* SELECT QUERY*/
            Query query = session.createQuery("SELECT MIN(id) FROM User");
            query.setMaxResults(1);
            List queryList = query.list();
            System.out.println(queryList);
            
            query = session.createQuery("FROM User");
            query.setMaxResults(5);
            query.setReadOnly(true);
            queryList = query.list();
            for(Object object : queryList) {
                User user = (User) object;
                System.out.println("Email: " + user.getEmail() + ", First Name: " 
                        + user.getFirstName() + ", Last Name: " + user.getLastName() 
                        + ", Phone Area: " + user.getAreaCode() 
                        + ", Phone Number: " + user.getPhoneNumber());
            }
            
            /* UPDATE QUERY */
            User user = (User) queryList.get(0);
            query = session.createQuery("UPDATE User SET email=:email WHERE id = :id");
            query.setParameter("id", user.getId());
            query.setParameter("email", "sdfsdfsdf@gmail.com");
            int updateResult = query.executeUpdate();
            System.out.println("Update Result: " + updateResult);
            
            /* DELETE QUERY */
            user = (User) queryList.get(1);
            query = session.createQuery("DELETE FROM User WHERE id = :id");
            query.setParameter("id", user.getId());
            updateResult = query.executeUpdate();
            System.out.println("Delete Result: " + updateResult);
            
            transaction.commit();
        } catch (Exception ex) {
            if(transaction != null) {
                transaction.rollback();
            }
            System.out.println("Error: " + ex.getMessage());
        } finally {
            if(session != null) {
                session.close();
            }
        }
    }
}

You have to include antlr.jar to use HQL in your hibernate program.

Data saved in database as follows:

The java entity class used for this example:

package com.pkm.domains;

import java.util.Date;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;

@Entity
public class User implements java.io.Serializable {   
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", unique = true, nullable = false)
    private Long id;
    public void setId(Long id) {
        this.id = id;
    }
    public Long getId() {
        return this.id;
    }
    
    @Column(name = "email", unique = true, nullable = false, length = 100)
    private String email;
    public void setEmail(String email) {
        this.email = email;
    }
    public String getEmail() {
        return this.email;
    }
     
    @Column(name = "first_name", nullable = false)
    private String firstName;
    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }
    public String getFirstName() {
        return this.firstName;
    }
     
    @Column(name = "last_name", nullable = true)
    private String lastName;
    public void setLastName(String lastName) {
        this.lastName = lastName;
    }
    public String getLastName() {
        return this.lastName;
    }
     
    @Column(name = "phone_number", nullable = false)
    private String phoneNumber;
    public void setPhoneNumber(String phoneNumber) {
        this.phoneNumber = phoneNumber;
    }
    public String getPhoneNumber() {
        return this.phoneNumber;
    }
     
    @Column(name = "area_code", nullable = true)
    private int areaCode;
    public void setAreaCode(int areaCode) {
        this.areaCode = areaCode;
    }
    public int getAreaCode() {
        return this.areaCode;
    }
    
    @Temporal(TemporalType.TIMESTAMP)
    @Column(name = "date_updated", unique = false, nullable = true, length = 100)
    private Date dateUpdated;
    public void setDateUpdated(Date dateUpdated) {
        this.dateUpdated = dateUpdated;
    }
    public Date getDateUpdated() {
        return this.dateUpdated;
    }
    
    public User() {
        this.dateUpdated = new Date();        
    }
}

Output as following

Hibernate: INSERT INTO User(email, first_name, last_name, phone_number, area_code) Values(?, ?, ?, ?, ?)
Insert Result: 1
Hibernate: select min(user0_.id) as col_0_0_ from User user0_ limit ?
[14]
Hibernate: select user0_.id as id0_, user0_.date_updated as date2_0_, user0_.email as email0_, user0_.first_name as first4_0_, user0_.last_name as last5_0_, user0_.phone_number as phone6_0_, user0_.area_code as area7_0_ from User user0_ limit ?
Email: sdfsdfsdf@gmail.com, First Name: Pritom, Last Name: Kumar Mondal, Phone Area: null, Phone Number: null
Email: 1399089957760@gmail.com, First Name: First name changed, Last Name: Kumar Mondal, Phone Area: null, Phone Number: null
Email: 1399089987865@gmail.com, First Name: Pritom, Last Name: Kumar Mondal, Phone Area: null, Phone Number: null
Email: 1399089987945@gmail.com, First Name: First name changed, Last Name: Kumar Mondal, Phone Area: null, Phone Number: null
Email: 1399090021059@gmail.com, First Name: Pritom, Last Name: Kumar Mondal, Phone Area: null, Phone Number: null
Hibernate: update User set email=? where id=?
Update Result: 1
Hibernate: delete from User where id=?
Delete Result: 1