0

I want to call a function in my query. For example:

SELECT myT.col1, myT.col2, myT.col3, fun_get_calculatedCol(myT.col4) as calcCol FROM myTable myT

In my myTableRepository i can do the following:

@Query("SELECT myT.col1, myT.col2, myT.col3 from MyTable myT")
List<MyTable> getCustomColumns();

but adding a function call to the statement does not work:

@Query("SELECT myT.col1, myT.col2, myT.col3, fun_get_calculatedCol(myT.col4) as calcCol from MyTable myT")
List<MyTable> getCustomColumns();

Is there a way to get it work in JPQL?

My error:

Caused by: java.lang.IllegalStateException: No data type for node: org.hibernate.hql.internal.ast.tree.MethodNode 
\-[METHOD_CALL] MethodNode: '('
+-[METHOD_NAME] IdentNode: 'fun_get_calculatedCol' {originalText=fun_get_calculatedCol}
Adel
  • 88
  • 2
  • 11
  • are you getting any errors? please post that if so – Pankaj Gadge Jan 19 '18 at 07:32
  • "does not work". LOL. That doesnt really tell people anything, at all.Maybe JPQL does not support some random SQL function "fun_get_calculated"? which it doesnt. JPQL != SQL. –  Jan 19 '18 at 07:44
  • JPQL is a query language on it's own. It is **not** SQL, so you can't use any native SQL features in JPQL. –  Jan 19 '18 at 08:13
  • Is there a way to do it in JPQL? I found a solution with NamedNativeQuery and SqlResultSetMapping, but that's too much code for just one more column. I am still learning, so pls help me if you know a solution, thanks :) – Adel Jan 19 '18 at 08:16
  • 1
    As any JPA documentation would tell you, JPQL allows you to use "FUNCTION({sql_function_name}, args)" to invoke SQL functions (replacing "sql_function_name" with the name of the function). Perhaps read some docs ... –  Jan 19 '18 at 08:37
  • @DN1 you're right. This has been already discussed in https://stackoverflow.com/questions/42484199/how-to-use-a-custom-function-in-a-jpa-query – garfield Jan 19 '18 at 09:37

1 Answers1

0

You can define it as native query:

@Query(value = "SELECT myT.col1, myT.col2, myT.col3, fun_get_calculatedCol(myT.col4) as calcCol from MyTable myT", nativeQuery = true)
List<MyTable> getCustomColumns();
Georg Leber
  • 3,470
  • 5
  • 40
  • 63
  • I get the following exception: java.sql.SQLException: Invalid column name. In my myTable i have a "private static String calcCol" defined – Adel Jan 19 '18 at 08:03