16

I am in little hurry so, i just want to ask a quick question about querydsl. According to my research, query dsl doesn't support stored procedure but can support database functions. My Question is how can we invoke those database functions using querydsl?

Mani Rai
  • 665
  • 2
  • 9
  • 22

2 Answers2

27

You can use TemplateExpression based injections of arbitrary JPQL syntax into your query.

e.g.

query.where(Expressions.booleanTemplate("func1({0}, {1})", arg1, arg2));

If you use Hibernate 4.3 or any other JPA 2.1 compliant provider you can use the FUNCTION syntax to invoke SQL functions https://bugs.eclipse.org/bugs/show_bug.cgi?id=350843

So the example would turn into

query.where(Expressions.booleanTemplate("function('func1', {0}, {1})", arg1, arg2)"));
Timo Westkämper
  • 21,824
  • 5
  • 78
  • 111
  • Wow.. what a coincidence timo.. i just saw and read slide.. you got it almost all in it i need to know.. thank you.. and i ll definitely try your suggestion.. – Mani Rai Apr 10 '14 at 22:20
  • 2
    @Timo I'm trying this but i receive an: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected AST node – renanleandrof Sep 03 '15 at 19:20
  • Nice one. And also is it possible to handle more functions (nested inner functions) in a single call? – spr Jan 07 '17 at 05:44
  • so ... basically ... QueryDSL doesnt support typesafe functions although it would be possible to extract the types via @NamedStoredProcedureQuery. Thats sad. – specializt Apr 16 '19 at 12:49
  • Hi, @renanleandrof, have you solve the problem? I got the same error: `unexpected AST node`. My code: `Expressions.booleanTemplate("FIND_IN_SET({0}, {1})", arg1, arg2)` – Elva Aug 10 '21 at 02:26
  • @elva Yes. The problem was with the versions of querydsl in my project. Make sure you have the last one. – renanleandrof Aug 17 '21 at 12:56
0

example of call mysql function find_in_set:

query.where(Expressions.booleanTemplate("find_in_set({0}, {1}) > 0", 1, "1,2,3"));

'> 0' Is a must, else thorw unexpected AST node

watchingfun
  • 1