How to replace characters in JPQL @Query

user_1729795 0
Viewed 1105

My db has a table RESOURCE like:

ID   NAME
1    some_resource
2    another_resource

And DEPARTMENTS

ID   NAME    RESOURCE_NAME
1    dep1    some-resource
2    dep2    another-resource

I need to be able to match them by resource name in a query, and in order to do so I was expecting to use the SQL Server function REPLACE:

@Query("select res from Resource res where res.name in " +
            "(select FUNCTION('REPLACE', dep.resourceName, '-', '_') from Department dep)")
    Page<Resource> findByAll(Pageable pageable);

However this throws an exception:

Caused by: org.hibernate.QueryException: No data type for node: org.hibernate.hql.internal.ast.tree.MethodNode 
 \-[METHOD_CALL] MethodNode: 'FUNCTION (REPLACE)'
    +-[METHOD_NAME] IdentNode: 'REPLACE' {originalText=REPLACE}
spring-data-jpa jpql jpa hibernate sql-server
1 Answers

You also need to register the standard SQL function with Hibernate, something like this:

public class MySQLServerDialect extends SQLServerDialect {
    public SQLServerDialect() {
        super();
        registerFunction("REPLACE", new StandardSQLFunction("REPLACE"));
    }
}
user_18632290
Related