CODEWITHSUNDEEP
sql-serverhibernatejpaspring-data-jpajpql
codependent
codependent

Reputation: 24472

How to replace characters in JPQL @Query

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}

Upvotes: 2

Views: 3178

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522421

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"));
    }
}

Upvotes: 1

Related Questions