Postgresql custom expression with QueryDSL

Question

I have a trouble with writing custom boolean template for where clause using QueryDSL. Simply I need to use arraycontains function to know that table.languages contains values at array of strings from filter.

Why Hibernate can't execute this? The raw sql is fine.

Code (variables are simplified, on screenshot it will be different):

    final var test = new JPAQuery<>(this.entityManager)
               .select(table)
               .from(table)
               .where(whereFromFilter(filter))
               .fetch()
    //...some code
    private static Predicate[] whereFromFilter(final Filter filter) {
      final var result = new ArrayList<Predicate>();
      //...some code
      result.add(
                Expressions.booleanTemplate(
                    "arraycontains(replace(replace(({0})::text,'[','{'),']','}')::text[],string_to_array({1},',')) = true",
                    table.languages,
                    "'" + String.join(",", filter.languages) + "'"
                )
      );
      //...some code
      return result.toArray(Predicate[]::new);
    }

table.languages in DB it's a json object that simply array of strings (so in template I'm converting it to array of strings):

I also try this code in raw sql console and it works fine:

SELECT arraycontains(replace(replace((table.languages)::text,'[','{'),']','}')::text[],string_to_array('CONFIG,PYTHON', ',')) is true
FROM table

Another filter (not with custom boolean template) works fine:

result.add(table.issueCountHigh.goe(filter.issueCountHighMin));

In debug mode I see that code from custom filter looks similar to code from another filter (that 100% works):

So, what HqlBaseParser can't parse? SQL functions?

stacktrace:

{
"statusCode": 500,
"statusDescription": "Internal Server Error",
"stacktrace": "java.lang.NullPointerException\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.identPrimary(HqlBaseParser.java:4770)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.primaryExpression(HqlBaseParser.java:1090)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.atom(HqlBaseParser.java:3866)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.unaryExpression(HqlBaseParser.java:3703)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.multiplyExpression(HqlBaseParser.java:3570)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.additiveExpression(HqlBaseParser.java:3197)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.concatenation(HqlBaseParser.java:682)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.relationalExpression(HqlBaseParser.java:2959)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.equalityExpression(HqlBaseParser.java:2815)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.negatedExpression(HqlBaseParser.java:2774)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.logicalAndExpression(HqlBaseParser.java:2685)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.logicalOrExpression(HqlBaseParser.java:2645)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.expression(HqlBaseParser.java:2323)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.exprList(HqlBaseParser.java:4404)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.identPrimary(HqlBaseParser.java:4763)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.primaryExpression(HqlBaseParser.java:1090)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.atom(HqlBaseParser.java:3866)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.unaryExpression(HqlBaseParser.java:3703)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.multiplyExpression(HqlBaseParser.java:3570)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.additiveExpression(HqlBaseParser.java:3197)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.concatenation(HqlBaseParser.java:682)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.relationalExpression(HqlBaseParser.java:2959)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.equalityExpression(HqlBaseParser.java:2815)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.negatedExpression(HqlBaseParser.java:2774)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.logicalAndExpression(HqlBaseParser.java:2685)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.logicalOrExpression(HqlBaseParser.java:2645)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.expression(HqlBaseParser.java:2323)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.exprList(HqlBaseParser.java:4404)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.identPrimary(HqlBaseParser.java:4763)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.primaryExpression(HqlBaseParser.java:1090)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.atom(HqlBaseParser.java:3866)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.unaryExpression(HqlBaseParser.java:3703)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.multiplyExpression(HqlBaseParser.java:3570)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.additiveExpression(HqlBaseParser.java:3197)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.concatenation(HqlBaseParser.java:682)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.relationalExpression(HqlBaseParser.java:2959)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.equalityExpression(HqlBaseParser.java:2815)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.negatedExpression(HqlBaseParser.java:2774)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.logicalAndExpression(HqlBaseParser.java:2685)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.logicalOrExpression(HqlBaseParser.java:2645)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.expression(HqlBaseParser.java:2323)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.logicalExpression(HqlBaseParser.java:2071)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.whereClause(HqlBaseParser.java:540)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.queryRule(HqlBaseParser.java:831)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.selectStatement(HqlBaseParser.java:336)\n\tat org.hibernate.hql.internal.antlr.HqlBaseParser.statement(HqlBaseParser.java:200)\n\tat org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:294)\n\tat org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:189)\n\tat org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:144)\n\tat org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:113)\n\tat org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:73)\n\tat org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:162)\n\tat org.hibernate.internal.AbstractSharedSessionContract.getQueryPlan(AbstractSharedSessionContract.java:604)\n\tat org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:716)\n\tat org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:113)\n\tat java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)\n\tat java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)\n\tat java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)\n\tat java.base/java.lang.reflect.Method.invoke(Method.java:566)\n\tat org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:362)\n\tat com.sun.proxy.$Proxy140.createQuery(Unknown Source)\n\tat java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)\n\tat java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)\n\tat java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)\n\tat java.base/java.lang.reflect.Method.invoke(Method.java:566)\n\tat org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler.invoke(SharedEntityManagerCreator.java:315)\n\tat com.sun.proxy.$Proxy140.createQuery(Unknown Source)\n\tat com.querydsl.jpa.impl.AbstractJPAQuery.createQuery(AbstractJPAQuery.java:101)\n\tat com.querydsl.jpa.impl.AbstractJPAQuery.createQuery(AbstractJPAQuery.java:94)\n\tat com.querydsl.jpa.impl.AbstractJPAQuery.fetch(AbstractJPAQuery.java:201)\n\tat

If you want to execute raw SQL, shouldn't you be using JPASQLQuery, rather than JPAQuery? JPQL is a different language than SQL, although "heavily inspired by SQL". — scrhartley
– scrhartley, Commented Sep 12, 2023 at 17:02
@scrhartley the problem is that I've already a lot of code in JPAQuery that work fine and I've a trouble only with this part of code. May exist a way to write it correctly? — Se Br
– Se Br, Commented Sep 12, 2023 at 17:12
Does this question about String literals seem relevant? stackoverflow.com/questions/23887678/… — scrhartley
– scrhartley, Commented Sep 12, 2023 at 18:37

Se Br · Accepted Answer · 2023-09-13 10:54:45Z

Solved problem by:

create function in DB using liqubase script:

        <createProcedure>
            CREATE OR REPLACE FUNCTION jsonb_array_contains(array_jsonb jsonb, string_comma character varying )
            RETURNS bool
            LANGUAGE plpgsql
            AS $$
            BEGIN
                RETURN arraycontains(
                        replace(
                            replace(
                                (array_jsonb)::text,
                                '[',
                                '{'
                            ),
                            ']',
                            '}'
                        )::text[],
                        string_to_array(string_comma, ',')
                    );
            END;
            $$;
        </createProcedure>

call this function in boolen template:

             result.add(
                Expressions.booleanTemplate(
                    "FUNCTION('jsonb_array_contains',({0}),'"
                        + String.join(",", filter.languages)
                        + "') = true",
                    table.languages
                )
            );

scrhartley · Accepted Answer · 2023-09-13 12:58:58Z

0

Try switching to this type of cast (The CAST operator in PostgreSQL) and additionally replacing the cast to text[] with another string_to_array, so we have eliminated all the colons and square brackets the parser may choke on:

arraycontains(string_to_array(replace(replace(CAST({0} AS text),'[','{'),']','}'),','),string_to_array({1},',')) = true

edited Sep 13, 2023 at 12:58

answered Sep 12, 2023 at 20:24

scrhartley

1,6421 gold badge4 silver badges10 bronze badges

4 Comments

Se Br Over a year ago

after changing type casting to this way, nothing changed:(

scrhartley Over a year ago

I'm surprised that you don't get a different type of error, at least. My version gets past the parser, when run locally. I would have thought that perhaps it would have started to complain about any functions it can't find in the database.

Se Br Over a year ago

I've tested different options with type casting and rewriting the entire sql-query, but hibernate always can't parse something like 'as', 'text[]' and other stuff. Helps only creating a custom function in DB and calling it (see my answer).

scrhartley Over a year ago

Apologies: I accidentally turned the text[] into a text. I've updated the answer to replace the cast to text[] with a call to string_to_array.

Collectives™ on Stack Overflow

Postgresql custom expression with QueryDSL

2 Answers 2

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related