Creating custom Dialect in Hibernate

At times we have certain function that are available in proprietary DB but not supported by Hibernate. We can write our own Dialect to explicitly tell Hibernate how to interpret such function when it encounters them in HQL.

Let’s try to understand this using following example with MySQL 5 as database

In MySQL we have a function CHARSET which returns what character set is used in argument passed. More about this function can be read at MySQL site: http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_charset

Now this function is not supported in MySQL dialects e.g. MySQL5InnoDBDialect. So just to check what happens if we call this function with this dialect use following configuration in hibernate.cfg.xml

<property name="hibernate.dialect">org.hibernate.dialect.MySQL5InnoDBDialect</property>

     And try to run following code. Where Customer is some bean class created in application. HibernateUtil is a utility class with following functions

• getSession : This function will create and return instance of Hibernate Session.
•  closeSession : This function will check if passed instance of Hibernate Session is not null and if it is not null then it will close the session.

@SuppressWarnings(“unchecked”) public String getToUpperFromDB(String str) throws DBException {        Session session = null;        try        {               session = HibernateUtil.getSession();               Query qry = session.createQuery("SELECT charset('text in upper') from Customer");               List<String> retList = qry.list();               if(retList!=null && !retList.isEmpty())               {                      return retList.get(0);               }               else               {                      return null;               }        }        catch(HibernateException e)        {               throw new DBException("Error occured while getting upper case",e);        }        finally        {               HibernateUtil.closeSession(session);        } }

And call this function from simple System.out.println as shown.

System.out.println(dao.getToUpperFromDB("this text in upper"));

This will result in following Exception

Exception in thread "main" java.lang.IllegalStateException: No data type for node: org.hibernate.hql.internal.ast.tree.MethodNode  \-[METHOD_CALL] MethodNode: '('     +-[METHOD_NAME] IdentNode: 'charset' {originalText=charset}     \-[EXPR_LIST] SqlNode: 'exprList'        \-[QUOTED_STRING] LiteralNode: ''text in upper''

Because Hibernate does not know how to interpret function charset. So now we will create following class

package com.hibernate.custom.dialect; import org.hibernate.dialect.MySQL5InnoDBDialect; import org.hibernate.dialect.function.StandardSQLFunction; public class CustomMySQLDialect extends MySQL5InnoDBDialect{        public CustomMySQLDialect()        {               super();               registerFunction("charset", new StandardSQLFunction("charset"));        } }

Here we are creating a class named com.hibernate.custom.dialect.CustomMySQLDialect and in the constructor of the class we are calling function registerFunction which maps charset to normal SQL function charset. Now we will configure this new class in hibernate.cfg.xml as follows and rerun the same code.

<property name="hibernate.dialect">com.hibernate.custom.dialect.CustomMySQLDialect</property>

This will give following output

INFO: HHH000397: Using ASTQueryTranslatorFactory Hibernate: select charset('text in upper') as col_0_0_ from CUST_MST_TBL customer0_ latin1

So this is how one can use his/her own Dialect to tell hibernate what to do…. J

Same blog you can find on my blogging site