Postgresql custom expression with QueryDSL

SebR
September 19, 2023
212 views
0 votes
2 Answers

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

Answers

Chosen as BEST ANSWER

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

(Edit)

- scrhartley
- September 12, 2023 at 10:24 pm
- 0 votes
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
```
Login or Signup to reply.

Please signup or login to give your own answer.

Click here to cancel reply.