CODEWITHSUNDEEP
javamysqlstringhibernatefunction
user503285
user503285

Reputation: 143

calling a mysql function returning varchar in hibernate

I am trying to use mysql find_in_set string function in hibernate but unfortunately I cant able to use it and even tried by creating these register functions 

registerFunction("findInSet",newSQLFunctionTemplate(Hibernate.STRING,"FIND_IN_SET(?1,?2)"));
registerFunction("findInSet",newSQLFunctionTemplate(Hibernate.INTEGER,"FIND_IN_SET(?1,?2)"));
registerFunction("findInSet",new StandardSQLFunction("find_in_set", Hibernate.INTEGER));

using mysqldialect but no one is working..can any one please tell me how to use the mysql string functions in hibernate. or any changes to the above register functions.

Thanks in Advance,

Best Regards, Raja.

Upvotes: 2

Views: 5664

Answers (3)

Steven Spungin
Steven Spungin

Reputation: 29209

This works for me:

package mypackage;

import org.hibernate.dialect.MySQL5InnoDBDialect;
import org.hibernate.dialect.function.SQLFunctionTemplate;
import org.hibernate.type.BooleanType;

public class CustomSqlDialect extends MySQL5InnoDBDialect {
    public CustomSqlDialect() {
        registerFunction("find_in_set", new SQLFunctionTemplate(IntegerType.INSTANCE, "find_in_set(?1, ?2)");
    }
}

And set this in your JPA configuration file:

<property name="hibernate.dialect" value="mypackage.CustomSqlDialect"/>

Finally ,for find_in_set you should make sure the query conditional is find_in_set(...) > 0. Using find_in_set() without the comparison operator will fail to execute with an AST error.

Also, you should obviously override the appropriate dialect and package name.

Upvotes: 1

user503285
user503285

Reputation: 143

created a class by extending mysqldialect like shown below

public class MySqlDialectExtended extends MySQLDialect {

public MySqlDialectExtended() {
    super();
    registerFunction("date_add_interval", new SQLFunctionTemplate(Hibernate.DATE, "date_add(?1, INTERVAL ?2 ?3)"));
    registerFunction("date_sub_interval", new SQLFunctionTemplate(Hibernate.DATE, "DATE_SUB(?1, INTERVAL ?2 ?3)"));
    registerFunction("weekofyear", new StandardSQLFunction("weekofyear", Hibernate.INTEGER));
    registerFunction("group_concat", new StandardSQLFunction("group_concat", Hibernate.STRING));
}

}

and used it like this in query

new Query(" >= date_sub_interval(CURRENT_DATE(),1, DAY)");

Upvotes: 0

ManuPK
ManuPK

Reputation: 11839

It is simple to call a function if your function is returning a string or varchar value.

session.createSQLQuery("select my_super_fn(:param1)")

Where my_super_fn is the name of your function with parameter param1.

To test the syntax try this code which gets you the current server date.

Date d = (Date)session.createSQLQuery("select CURDATE()").uniqueResult();
System.out.println(d);

For other type of functions refer a question here on how to do it.

Upvotes: 1

Related Questions