CODEWITHSUNDEEP
hibernatespring-data-jpacriteria-api
tharanga-dinesh
tharanga-dinesh

Reputation: 567

JPA CriteriaBuilder substring with Expression

I have a CriteriaBuilder where I am trying to get characters starting from 1 to (LengthOfString - 5). However I am not able to get the desired output. Below is my desired output. 

select
            *
        from tbl_job job1_     

        inner join
            tbl_customer customer2_ 
                on job1_.customer_id=customer2_.id 
        where
            job1_.customer_id=customer2_.id 
                    and //

        group by SUBSTRING(job1_.code,1,(LENGTH(job1_.code)-5))

And I have tried this way, but my IDE depict the error under the (cb.length(root.get("code"))-5) "The operator - is undefined for the argument type(s) Expression, int".

final Specification<Job> specification = (root, query, cb) -> {
            query.orderBy(cb.asc(root.get("code"))); 
            query.groupBy(cb.substring(root.get("code"), 1 , (cb.length(root.get("code"))-5) ));
            return cb.and(
                    //...
        };

hat could be the reason for this? Thanks for any help advance.

Upvotes: 0

Views: 2477

Answers (2)

perissf
perissf

Reputation: 16273

Create an Expression<Integer> holding the result of the subtraction and use it in the substring method:

Expression<Integer> lengthMinus5 = cb.sum(cb.length(root.get("code")), -5);
Expression<Integer> one = cb.literal(1);
query.groupBy(cb.substring(root.get("code"), one , lengthMinus5 ));

Upvotes: 2

jokster
jokster

Reputation: 577

Try to inspect the types of the operands of cb.length(root.get("code"))-5. Left side is Expression, right side is int. There is no operator - handling that.

You have to include the calculation -5 in your expression.

Upvotes: 0

Related Questions