calling a mysql function returning varchar in hibernate

Question

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.

Community · Accepted Answer · 2017-05-23 12:19:17Z

1

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.

edited May 23, 2017 at 12:19

CommunityBot

11 silver badge

answered Dec 2, 2011 at 12:48

ManuPK

11.9k10 gold badges60 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user503285 Over a year ago

hi, thanks for the reply..but the idea you gave is not working in this format. Query query = this.entityManager.createQuery("select FIND_IN_SET()")..can u please give me an idea how to use with entity managers in hibernate.

ManuPK Over a year ago

are you using JPA? What error is you getting? In the meanwhile you should use the createNativeQuery.

Divyang Desai · Accepted Answer · 2016-11-06 19:02:35Z

1

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.

edited Nov 6, 2016 at 19:02

Divyang Desai

7,89414 gold badges56 silver badges83 bronze badges

answered Nov 6, 2016 at 18:43

Steven Spungin

29.5k7 gold badges97 silver badges85 bronze badges

Comments

user503285 · Accepted Answer · 2013-01-23 08:17:08Z

0

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)");

answered Jan 23, 2013 at 8:17

user503285

1435 silver badges15 bronze badges

Collectives™ on Stack Overflow

calling a mysql function returning varchar in hibernate

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related