How to Create Custom Dialect class in Hibernate?

Creating a Custom Dialect in Hibernate:

In order to create any new Dialect class we need to extends org.hibernate.dialect.Dialect class. Dialect class is a abstract class.

If you look at the Dialect class,

public abstract class Dialect implements ConversionContext {

protected Dialect() {

// here we are registering the functions, ColumnType, HibernateTypes

// for registering function's we can use registerFunction() method.

// for registering columntype's we can use registerColumnType() method.

// for registering hibernatetype's we can use registerHibernateType() method.

}

// some others methods

}

For connecting MySQL database, Hibernate people created a dialect class called 'org.hibernate.dialect.MySQLDialect'.

public class MySQLDialect extends MySQLDialect {

      public MySQLDialect() {

  // here they resisted mysql functions, column types

}

// some other methods

}

In the same way, in order to connect with Oracle database, hibernate people developed a 5 dialect classes based on Oracle Version.

org.hibernate.dialect.OracleDialect

org.hibernate.dialect.Oracle8iDialect

org.hibernate.dialect.Oracle9Dialect

org.hibernate.dialect.Oracle9iDialect

org.hibernate.dialect.Oracle10gDialect

if you see the OracleDialect class signature

public class OracleDialect extends Oracle9Dialect {

public OracleDialect() {

// here they resisted oracle functions, column types

}

}

Now we can see how to create Custom Dialect classes.

My requirement is, based on current time, i want to display greeting message say for example if time is 10:01:59 AM then i need to display "Morning" other time is 14:12:15 PM then i need to display "Afternoon". So for this one there is no function exists in any database. 

so for this one what i did is, i created one function in mysql database i.e say_gretting and i am using that function. In order to use that function i need to one new class and extends MySQLDialect class. Finally i need to register say_greeting function in Custom dialect class.

package com.varasofttech.dialect;

import org.hibernate.dialect.MySQL5Dialect;
import org.hibernate.dialect.function.StandardSQLFunction;

public class VaraSoftTechMySQLDialect extends MySQL5Dialect {
    public VaraSoftTechMySQLDialect() {
        super();
         // here i am registring custom function to display greeting message.
        registerFunction("say_greeting", new StandardSQLFunction("say_greeting"));
    }
}

Finally this dialect class, we need to register in hiberante.cfg.xml file.

<!-- Dialect class -->
<property name="hibernate.dialect">com.varasofttech.dialect.VaraSoftTechMySQLDialect</property>

Now lets see how to create 'say_greeting' function in mysql.

DROP FUNCTION IF EXISTS say_greeting;
DELIMITER $$
CREATE FUNCTION say_greeting(name TEXT)
  RETURNS TEXT
BEGIN
  DECLARE greetingMessage CHAR(10);
  SET greetingMessage = (SELECT IF((SELECT TIME_FORMAT(now(),'%H') from DUAL) < 12,'Morning', IF((SELECT TIME_FORMAT(now(),'%H') from DUAL) < 15,'Afternoon','Evening')));
  RETURN CONCAT('Hello ', name, ', Good ', greetingMessage, '!');
END;
$$
DELIMITER ;

SELECT say_greeting('ranga') from DUAL;

Now write client application in order to use say_greeting function. Here i am using NativeSQLQuery.

        Configuration configuration = new Configuration();
        configuration.configure("hibernate.cfg.xml");
        
        ServiceRegistry serviceRegistry = new StandardServiceRegistryBuilder()
                        .applySettings(configuration.getProperties())
                        .build();
        
        SessionFactory sessionFactory = configuration.buildSessionFactory(serviceRegistry); 
        Session session = sessionFactory.openSession();
        

        SQLQuery query  = session.createSQLQuery("SELECT say_greeting('ranga') from DUAL");
        String greetingMessage = (String) query.list().get(0);
        System.out.println(greetingMessage);
        
        session.close();
        
        sessionFactory.close();

Output:

Dec 29, 2014 6:45:09 AM org.hibernate.dialect.Dialect <init>

INFO: HHH000400: Using dialect: com.varasofttech.dialect.VaraSoftTechMySQLDialect

Hibernate: 
    SELECT
        say_greeting('ranga') 
    from

        DUAL

Hello ranga, Good Evening!

Reference:
http://myjourneyonjava.blogspot.in/2014/12/displaying-greeting-message-based-on.html

Happy Coding!!!!