Functions and Operators not working in Eclipselink

Question

I have this problem, REPLACE function is not working with eclipselink version 2.5.2.

Here is my code:

String sSql = " SELECT e FROM br.com.megasoft.protocolo.entity.Assunto e  WHERE  ( REPLACE(REPLACE(REPLACE( UPPER(e.titulo), '/', ''), '-', ''), '.', '') LIKE  UPPER('A') )";
TypedQuery<?> query = getEntityManager().createQuery(sSql, Class.forName(this.tabela));

And the second parameter has the value of: class br.com.megasoft.protocolo.entity.Assunto

It gives this exception:

Caused by: Exception [EclipseLink-0] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.JPQLException
Exception Description: Syntax error parsing [ FROM br.com.megasoft.protocolo.entity.Assunto e  WHERE  ( REPLACE(REPLACE(REPLACE( UPPER(e.titulo), '/', ''), '-', ''), '.', '') LIKE  UPPER( :valorPesq10) ) ]. 
[54, 150] The expression is not a valid conditional expression.
    at org.eclipse.persistence.internal.jpa.jpql.HermesParser.buildException(HermesParser.java:155)
    at org.eclipse.persistence.internal.jpa.jpql.HermesParser.validate(HermesParser.java:334)
    at org.eclipse.persistence.internal.jpa.jpql.HermesParser.populateQueryImp(HermesParser.java:278)
    at org.eclipse.persistence.internal.jpa.jpql.HermesParser.buildQuery(HermesParser.java:163)
    at org.eclipse.persistence.internal.jpa.EJBQueryImpl.buildEJBQLDatabaseQuery(EJBQueryImpl.java:142)
    at org.eclipse.persistence.internal.jpa.EJBQueryImpl.buildEJBQLDatabaseQuery(EJBQueryImpl.java:116)
    at org.eclipse.persistence.internal.jpa.EJBQueryImpl.<init>(EJBQueryImpl.java:102)
    at org.eclipse.persistence.internal.jpa.EJBQueryImpl.<init>(EJBQueryImpl.java:86)
    at org.eclipse.persistence.internal.jpa.EntityManagerImpl.createQuery(EntityManagerImpl.java:1603)
    ... 50 more

It works completely fine when I'm using Hibernate.

When I run simple SQL, without REPLACE, it works fine.

Obs: Eclipselink 2.5.2 is using JPA 2.1. I'm using Tomcat8, Eclipse Kepler.

Answer 1

I have found the solution, thank you guys for the useful informations.

The sql needs to be this way:

SELECT e FROM br.com.megasoft.protocolo.entity.Assunto e  WHERE  FUNCTION('REPLACE',  e.titulo, '/', '') LIKE  UPPER('A')

This way, I will be using the FUNCTION defined in JPA 2.1 spec.

Answer 2

I have searched in the SPEC and in this oracle page (http://docs.oracle.com/cd/E17904_01/apirefs.1111/e13946/ejb3_langref.html#ejb3_langref_string_fun) and there is no REPLACE for JPQL function.

I believe that this is a Hibernate function: http://lkumarjain.blogspot.com.br/2013/07/compare-string-by-removing-white-chars.html

In other words, REPLACE belongs to HQL and not JPQL.