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