JPA CriteriaBuilder and Substring

Question

I have a CriteriaBuilder where I am trying to get characters starting from 0 to 10. However I am not able to get the desired output.

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Emp> cq = cb.createQuery(Emp.class);
    Root<Emp> c = cq.from(Emp.class);
    cb.substring(c.<String>get("projDesc"), 0, 10);
    cq.orderBy(cb.desc(c.get("salary")));
    Query query = em.createQuery(cq);
    .....

What could be the reason for this?

Don't you need to assign the cb.substring to something? Otherwise the effect will be lost, just like with `String.substring` — geert3, Dec 23 '14 at 13:08
@geert3 is right, if you run a SQL query with Substring, you need to call the function and add to the select statement, the same with the `CriteriaBuilder`. `projDesc` is a `Emp.class` property, but not the result of the substring, you need to add to the select statement of the `CriteriaBuilder`. — André, Dec 23 '14 at 13:15

André · Accepted Answer · 2014-12-23T13:07:22.520

6

From the javadoc

Create an expression for substring extraction. Extracts a substring of given length starting at the specified position. First position is 1.

Try doing cb.substring(c.<String>get("projDesc"), 1, 10);

I think you're forgetting to select the Expression<E>

Try cq.select(cb.substring(c.<String>get("projDesc"), 1, 10)) It will return List<String> if you need to Return the Emp you can use the

cb.construct(Emp.class, e.get("prop1"), e.get("prop2"), cb.substring(c.<String>get("projDesc"), 1, 10)));

edited Dec 23 '14 at 13:07

answered Dec 23 '14 at 12:45

André

2,184
1
22
30

Changing position from 0, to 1 didn't solve the issue. – Jacob Dec 23 '14 at 12:53
Then I require clarification, what is the actual output and the expected output? – André Dec 23 '14 at 12:54
Actual - `Manage to finish project within stipulated time, however due to other hindrances project was prolonged for an year and incidents beyond jurisdiction.` Expected `Manage to`. Thanks – Jacob Dec 23 '14 at 12:57
Edited according your comment. – André Dec 23 '14 at 13:14

score 2 · Answer 2 · answered Jan 11 '22 at 09:20

I also, faced same problem in which I have to substring first three char and fetch all accounts starting from 107 which is number. for that I have used CriteriaBuilder and substring method like below.

Predicate accountNumber = criteriaBuilder.equal(criteriaBuilder.substring(from.get("accountNumber").as(String.class), 0, 3), cwipAcc);

but unfortunately it is not working for CriteriaBuilder and substring. so I have used like query to resolve this issue by given code below.

Predicate accountNumber = criteriaBuilder.like(from.get("accountNumber").as(String.class), String.valueOf(cwipAcc) + "%");

here, I have just fetched all the records which is starting from 107 and so on.

Example:

public List<GLCharts> findAccountForCWIP(Long cwipAcc, Long glInfo) {
        Map<String, Object> data = new HashMap();
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<GLCharts> criteriaQuery = criteriaBuilder.createQuery(GLCharts.class);
        Root<GLCharts> from = criteriaQuery.from(GLCharts.class);

        Predicate accountNumber = criteriaBuilder.like(from.get("accountNumber").as(String.class), String.valueOf(cwipAcc) + "%");
        Predicate glCompanyInfo = criteriaBuilder.equal(from.join("gLCompanyInfo").get("id"), glInfo);

        Predicate finalPredicate = criteriaBuilder.and(accountNumber, glCompanyInfo);

        criteriaQuery.select(from).where(finalPredicate).orderBy(Stream.of(criteriaBuilder.asc(from.get("accountNumber"))).collect(Collectors.toList()));
        List<GLCharts> glChartsList = entityManager.createQuery(criteriaQuery).getResultList();

        return glChartsList;
    }

score -1 · Answer 3 · answered Apr 09 '19 at 05:44

I faced the problem because my requirement was to use a number into substr. Following is the sample code.

 @Override
    public List<SampleProfile> findNonSampleProfileBySequence(Long SampleNo) {
        List<SampleProfile> profiles = new ArrayList<>();
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<SampleProfile> criteriaQuery = criteriaBuilder.createQuery(SampleProfile.class);
        Root<SampleProfile> SampleProfileRoot = criteriaQuery.from(SampleProfile.class);
        List<Predicate> predicates = new ArrayList<Predicate>();
        if (SampleUtil.isValidLong(SampleNo)) {
            String SampleStr = Long.toString(SampleNo);
            if (StringUtils.isNotBlank(SampleStr) && SampleStr.length() > 5) {
                String SampleSequence = SampleStr.substring(5);
                predicates.add(criteriaBuilder.equal(criteriaBuilder.substring(SampleProfileRoot.get(SampleProfile_.id).as(String.class), 6), SampleSequence));
                predicates.add(criteriaBuilder.equal(SampleProfileRoot.get(SampleProfile_.address).get(Address_.department), SampleStr.substring(0,3)));
            }
        }
        if (!CollectionUtils.isEmpty(predicates)) {
            criteriaQuery.where(criteriaBuilder.and(Iterables.toArray(predicates, Predicate.class)));
            profiles = entityManager.createQuery(criteriaQuery).setMaxResults(AbstractJpaDAO.MAX_ROW_LIMIT).getResultList();
        }
        return profiles;
    }

Also note that for performance benefits you have to create an index on the same. The Cast Keyword is important as Hibernate Dialect will create query like this, thus, it has to match with your index.

CREATE INDEX MY_SCHEMA_OWNER.IDX_SUBSTR_SMP_CODE ON MY_SCHEMA_OWNER.SMP_PROFILE (SUBSTR(**CAST**(SMP_CODE AS VARCHAR2(255 CHAR)),6));

JPA CriteriaBuilder and Substring

3 Answers3