Return substring starting from the N occurrence

Question

How to use this function in QueryDsl in Querying JPA

SUBSTRING_INDEX(str,delim,count)

Returns the substring from string str before count occurrences of the delimiter delim.

UPDATE 1 : After trying @MaciejDobrowolski solution like this :

QAcheteur ach = new QAcheteur("ach");
new JPAQuery(entityManager).from(ach)
 .list( Expressions.stringTemplate("SUBSTRING_INDEX({0},',',1)", ach.ancestors)  );

I got this error :

java.lang.IllegalStateException: No data type for node: org.hibernate.hql.internal.ast.tree.MethodNode 
 \-[METHOD_CALL] MethodNode: '('
    +-[METHOD_NAME] IdentNode: 'SUBSTRING_INDEX' {originalText=SUBSTRING_INDEX}
    \-[EXPR_LIST] SqlNode: 'exprList'
       +-[DOT] DotNode: 'acheteur1_.ancestors' {propertyName=ancestors,dereferenceType=PRIMITIVE,getPropertyPath=ancestors,path=ach.ancestors,tableAlias=acheteur1_,className=persistence.Acheteur,classAlias=ach}
       |  +-[ALIAS_REF] IdentNode: 'acheteur1_.ID_ACHETEUR' {alias=ach, className=persistence.Acheteur, tableAlias=acheteur1_}
       |  \-[IDENT] IdentNode: 'ancestors' {originalText=ancestors}
       +-[QUOTED_STRING] LiteralNode: '',''
       \-[NUM_INT] LiteralNode: '3'

UPDATE 2 : (Solution) Following @DraganBozanovic's answer i create my custom dialect to get ride of No data type for node: org.hibernate.hql.internal.ast.tree.MethodNode because SUBSTRING_INDEX is unknown in JPA so we use our own dialect to make it work.

package dialect;

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

public class CustomMySQLDialect extends MySQL5Dialect {

    public CustomMySQLDialect() {
        super();
        registerFunction("substring_index", new StandardSQLFunction("substring_index", StandardBasicTypes.STRING));
        registerFunction("replace", new StandardSQLFunction("replace", StandardBasicTypes.STRING));
        ....
    }
}

And in JPA configuration

<bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
      ...
      ...
     <property name="jpaProperties">
         <props>
            <prop key="hibernate.dialect">dialect.CustomMySQLDialect</prop>
         </props>
      </property>
</bean>

P.S : I decide to write the solution because it was a combination of two answers.

Answer 1

The simpliest way would be to use Expressions.stringTemplate

Expressions.stringTemplate("SUBSTRING_INDEX({0},',',3)", columnPath)

update

Ok, I've managed to get it work. I am using H2 Database, so I used function SUBSTR.

QAcheteur ach = new QAcheteur("ach");
new JPASQLQuery(entityManager, new H2Templates())
        .from(ach)
        .list(Expressions.stringTemplate("SUBSTR({0}, 1, 3)", ach.ancestors));

The key was to use not JPAQuery, but JPASQLQuery as this query uses native function. All you have to do is to follow this tutorial.

Answer 2

Exceptions occur from this:

Expressions.stringTemplate("SUBSTRING_INDEX({0},',',3)", ach.ancestors)

Exception: No data type for node

SQL queries use column names while HQL queries use Class properties. You're selecting artifact_id from Classification but the Classification class has no property named 'artifact_id'. To fix it, use the class property in your HQL.

SELECT artifactId FROM Classification

Resource Link:

1. No data type for node: org.hibernate.hql.internal.ast.tree.IdentNode HQL