How to write a HQL select query using extractvalue function in select clause of query in Java?

Question

I'm writing code with hibernate wherein I have to use the MySQL extractvalue function, in the SELECT clause of the query. However, I am getting the error:

java.lang.IllegalArgumentException: org.hibernate.QueryException: No data type for node: org.hibernate.hql.internal.ast.tree.MethodNode
 \-[METHOD_CALL] MethodNode: '('
    +-[METHOD_NAME] IdentNode: 'EXTRACTVALUE' {originalText=EXTRACTVALUE}

The corresponding MySQL query (mentioned below) works fine when executed in MySQL db, but not via Java:

SELECT EXTRACTVALUE(XMLRAW_DATA_IN_STRING_FORMAT,'//ns:Id') as ErrorId,
FROM TEMP_TABLE;

What hibernate version do you use? What hibernate dialect do you use? The function `extractvalue` should be declared in hibernate dialect. If no, you should extend dialect and declare this function by yourself. — SternK, Feb 06 '20 at 11:05
We use hibernate 5.3.7 and cannot declare extractvalue in hibernate dialect since there are some places where we do need entire data and not just the extracted value. Kindly suggest how do I extend dialect and declare this function by myself? — jaihind, Feb 06 '20 at 11:57

score 0 · Answer 1 · answered Feb 08 '20 at 12:19

I do not have installed MySql database. So, I will provide below example for MariaDB that is quite similar.

You should declare your hibernate dialect that extends the most appropriate for you exist dialect. It will register the function extractvalue that you have to use.

import org.hibernate.dialect.MariaDB103Dialect;
import org.hibernate.dialect.function.StandardSQLFunction;
import org.hibernate.type.StandardBasicTypes;

public class MyMariaDBDialect extends MariaDB103Dialect
{
   public MyMariaDBDialect()
   {
      super();
      registerFunction("extractvalue", new StandardSQLFunction("extractvalue", StandardBasicTypes.TEXT));
   }
}

Then you should declare this dialect in your spring boot application.properties:

spring.jpa.properties.hibernate.dialect=com.app.MyMariaDBDialect

And now you can use this function:

public interface DataRepository extends JpaRepository<Data, Long>
{
   @Query("select extractvalue(d.data, :path) from Data d where d.id = :id")
   String findDataElement(@Param("id") Long id, @Param("path") String path);
}

The problem with this approach is that the filtered value will be stored in the member variable of corresponding class. We do need the original value of raw data as well in some places and hence the issue. Is there any other way to resolve this issue? — jaihind, Feb 10 '20 at 13:29
You can try to use hibernate [@Formula](https://docs.jboss.org/hibernate/orm/5.4/userguide/html_single/Hibernate_User_Guide.html#mapping-column-formula) for the value calculated based on the `extractvalue` call. If it is not help, please update your question with the entity declaration and jpql/hql that you need. — SternK, Feb 10 '20 at 13:54

score 0 · Answer 2 · answered Mar 09 '20 at 13:07

The extractvalue annotation of HQL will be the choice only if you have to use the extracted value in your application. If you want to use both extracted value and the raw data then this solution won't work. We had to use both extracted value and the raw value so we had to put separate logic on fetched raw value in Java code to extract the required value separately.

How to write a HQL select query using extractvalue function in select clause of query in Java?

2 Answers2