Sunday, December 28, 2014

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!!!!

0 comments: