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.

@Shachaf.Gortler I try `path.substring(path.locate(",", count))` but locate function don't start from N occurrence but from a index — Hayi, Jun 23 '16 at 15:28
okey @SkyWalker I share the part when i execute a test query, If you want something else tell me. — Hayi, Jun 23 '16 at 15:59

Maciej Dobrowolski · Answer 1 · 2016-06-24T07:17:59.693

2

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.

edited Jun 24 '16 at 07:17

answered Jun 23 '16 at 15:31

Maciej Dobrowolski

11,561
5
45
67

thanks @MaciejDobrowolski but why the `Integer.class` ? the function must return a string – Hayi Jun 23 '16 at 15:41
@Youssef sorry, I read wrong function definition :) corrected, thanks for pointing that out – Maciej Dobrowolski Jun 23 '16 at 15:42
thanks @MaciejDobrowolski but now i got a exception `No data type for node` i made a update in my question so you can see exception log. – Hayi Jun 23 '16 at 15:46
thanks MaciejDobrowolski but the solution was a combination between your answer and @DraganBozanovic 's answer. so i'm confused what answer to award – Hayi Jun 24 '16 at 11:34

score 1 · Answer 2 · edited May 23 '17 at 12:22

1

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:

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

edited May 23 '17 at 12:22

Community

1
1

answered Jun 23 '16 at 15:51

SkyWalker

28,384
14
74
132

@Youssef If possible, You can share your code through github so that i can generate locally and can help. – SkyWalker Jun 23 '16 at 16:06
it's a big project how can i share it ? and also i work with svn not with github – Hayi Jun 23 '16 at 16:10
@Youssef you can follow this https://help.github.com/articles/importing-from-subversion/ – SkyWalker Jun 23 '16 at 16:13
i can't it's a private project for the company i work and you have to generate a big database to use my code – Hayi Jun 23 '16 at 16:17

score 1 · Answer 3 · edited May 23 '17 at 11:52

1

Register the custom function in the Hibernate dialect:

registerFunction("substring_index", new StandardSQLFunction("SUBSTRING_INDEX", StandardBasicTypes.STRING));

Then you will be able to invoke it from JPQL/HQL (and with the Querydsl on top of JPA).

edited May 23 '17 at 11:52

Community

1
1

answered Jun 23 '16 at 16:12

Dragan Bozanovic

23,102
5
43
110

but i use the literal `1` just for test it can change dynamically or have as value a other integer column. – Hayi Jun 23 '16 at 16:15
@Youssef Ok, I was not aware of that. – Dragan Bozanovic Jun 23 '16 at 16:20
1

@Youssef You could register the custom function to be able to call it from JPA queries. Edited the answer. – Dragan Bozanovic Jun 23 '16 at 16:38
thanks DraganBozanovic the solution was a combination between your answer and @MaciejDobrowolski 's answer. so i'm confused what answer to award – Hayi Jun 24 '16 at 11:33
2

@Youssef You shouldn't worry about that, wait until the end of bounty period and let the community decide by voting. :) Also, maybe some other good answer shows up as well in the following days... – Dragan Bozanovic Jun 24 '16 at 14:21
Okey @DraganBozanovic i update my question with my solution for the people who will face this problem. .. and if you can help me up there http://stackoverflow.com/q/38015366/1383538 i will be thankfull – Hayi Jun 25 '16 at 01:02

Return substring starting from the N occurrence

3 Answers3

Exceptions occur from this:

Exception: No data type for node

Resource Link: