How do I use MySQL's Custom function in Hibernate's HQL statement?

Question: How do I use MySQL functions in hibernate?

1.hibernate supports the original SQL statement query, using Session.createsqlquery () to create query objects;

2. How do I use the MySQL function in hql?

You need to redefine Hibernate's dialect (dialect) to register your needs;

The concept of a dialect (dialect) is mentioned here:

　　Hibernate is still using SQL statements to perform database operations, although all relational databases support the use of standard SQL statements, but all databases have some extensions to standard SQL, so there are some differences in syntax details. Hibernate therefore needs to identify these differences based on the database. and dialects (dialect) are used to differentiate which database hibernate corresponds to .

Usually when you use spring to integrate hibernate, there is a configuration

< Propertyname= "Hibernateproperties">            <value> hibernate.dialect=org.hibernate.dialect.mysql5innodbdialect hibernate.show_sql =true Hibernate.hbm2ddl.auto=none hibernate.format_sql=true HIBERNATE.CACHE.P Rovider_class=org.hibernate.cache.ehcacheprovider Hibernate.release_mode=auto Hibernate.conn Ection.isolation=1 Hibernate.connection.autocommit=false Hibernate.cache.use_query_cache=fal                Se hibernate.cache.use_second_level_cache=false hibernate.jdbc.batch_size=25 Hibernate.jdbc.fetch_size=50</value>

Where the Red configuration statement is to specify the use of the MySQL dialect, for other different databases have a different dialect, click here for details.

First create a MySQL function that we named my_change_local (number bigint)

--This is a function created using the Navicat tool
BEGIN    DECLAREvarchar(255);     set stemp=';   Select  into  from where sms.id=number;     RETURN stemp; END

1. rewrite the dialect class, register the function

 Public class extends Mysql5innodbdialect {    public  mylocaldialect () {        Super();        Registerfunction ("my_change_local",new sqlfunctiontemplate (standardbasictypes.string, "My_change _local (? 1)));}    }

2. Modify the configuration file to point the database dialect to its own defined dialect class

　　will be Hibernate.dialect=org.hibernate.dialect.mysql5innodbdialect instead Hibernate.dialect=xxx.xxx.mylocaldialect

3. You can then use your own defined function in the HQL.

How do I use MySQL's Custom function in Hibernate's HQL statement?