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;

Answer 1

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

1. 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));
   }
}

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

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

3. 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);
}

Answer 2

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.