CriteriaBuilder returns nothing

user_6336410 0
Viewed 382

I'm working on CriteriaBuilder. If the record exists, it will correctly return a value. But I need the query to return a value, e.g. an empty string or "," if the record is not found. Currently, if it doesn't find the record, it returns nothing. It just jumps.

    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
    CriteriaQuery<Long> criteriaQuery = criteriaBuilder.createQuery(Long.class);
    Root<Stock> stock = criteriaQuery.from(Stock.class);
    criteriaQuery.select(stock.get(storage))
        .where(stock.get(ITEM_NR).in(eans));
    return entityManager.createQuery(criteriaQuery).getResultList();

So now is 1,2,4 instead r 1,2,,4

If 3 doesn't exist.

Please help.

criteria-api criteria jpa hibernate java
2 Answers

I think you can solve this by using IFNULL:

criteriaQuery.select(criteriaBuilder.selectCase().when(criteriaBuilder.isNull(stock.get(storage),-1).otherwise(stock.get(storage)))

I didn't test this code but I think this is the right track to go with. Please try and give us an update on the results

user_58993840

The result you get is normal (expected) behaviour. You are requesting all entries that match another set (IN), then the result will only contain information about those entries, and NOT any information about entries that did not match your cirteria.

If you want to achieve this behaviour, you will have to map all entries using a conditional mapping.

This can be done a couple of ways:

  • create a complex criteria query that will project the result of the storage field conditionally on existence in the earns set...
  • create a second criteria query that returns only those stock's that are NOT in the earns set and handle those separately
  • write the query as a (non criteria) JPA query
  • get all Stock items and loop/stream and map them in Java code to determine if they are part of the earns set

I think the real question to me is; what is the context of the result that you return from the query. It seems that there is only information about storage without the context of the Stock that it belongs to... But that is for you to determine...

user_163851870
Related