List of usage examples for javax.persistence.criteria CriteriaBuilder greaterThanOrEqualTo
<Y extends Comparable<? super Y>> Predicate greaterThanOrEqualTo(Expression<? extends Y> x, Y y);
From source file:net.groupbuy.dao.impl.CouponCodeDaoImpl.java
public Long count(Coupon coupon, Member member, Boolean hasBegun, Boolean hasExpired, Boolean isUsed) { CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder(); CriteriaQuery<CouponCode> criteriaQuery = criteriaBuilder.createQuery(CouponCode.class); Root<CouponCode> root = criteriaQuery.from(CouponCode.class); criteriaQuery.select(root);/*from w w w . jav a 2s .c om*/ Predicate restrictions = criteriaBuilder.conjunction(); Path<Coupon> couponPath = root.get("coupon"); if (coupon != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(couponPath, coupon)); } if (member != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("member"), member)); } if (hasBegun != null) { if (hasBegun) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.or(couponPath.get("beginDate").isNull(), criteriaBuilder.lessThanOrEqualTo(couponPath.<Date>get("beginDate"), new Date()))); } else { restrictions = criteriaBuilder.and(restrictions, couponPath.get("beginDate").isNotNull(), criteriaBuilder.greaterThan(couponPath.<Date>get("beginDate"), new Date())); } } if (hasExpired != null) { if (hasExpired) { restrictions = criteriaBuilder.and(restrictions, couponPath.get("endDate").isNotNull(), criteriaBuilder.lessThan(couponPath.<Date>get("endDate"), new Date())); } else { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.or(couponPath.get("endDate").isNull(), criteriaBuilder.greaterThanOrEqualTo(couponPath.<Date>get("endDate"), new Date()))); } } if (isUsed != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("isUsed"), isUsed)); } criteriaQuery.where(restrictions); return super.count(criteriaQuery, null); }
From source file:com.ims.service.ProductStockInfoService.java
public List<ProductStockInfo> findProdStockInfoListFrom(final ProdStockSearchCriteria stockSearchCriteria) { Specification<ProductStockInfo> speci = new Specification<ProductStockInfo>() { @Override/*w ww.j av a 2 s. c om*/ public Predicate toPredicate(Root<ProductStockInfo> root, CriteriaQuery<?> query, CriteriaBuilder cb) { List<Predicate> predicates = new ArrayList<Predicate>(); predicates.add(cb.equal(root.get("stockType"), stockSearchCriteria.getStockType())); if (StringUtils.isNotEmpty(stockSearchCriteria.getProdCategoryCode())) { predicates.add(cb.equal(root.get("categoryCode"), stockSearchCriteria.getProdCategoryCode())); } if (StringUtils.isNotEmpty(stockSearchCriteria.getProdCode())) { predicates.add(cb.like(root.<String>get("productCode"), "%" + stockSearchCriteria.getProdCode() + "%")); } String compareCode = stockSearchCriteria.getCompareCode(); if (StringUtils.isNotEmpty(compareCode) && stockSearchCriteria.isIncludeComparedValue()) { Path<ProductAmount> productAmount = root.<ProductAmount>get("productAmount"); if (CompareCode.isEqual(compareCode)) { predicates.add( cb.equal(productAmount.get("totalAmount"), stockSearchCriteria.getStockAmount())); } else if (CompareCode.isGreater(compareCode)) { predicates.add(cb.greaterThan(productAmount.<Integer>get("totalAmount"), stockSearchCriteria.getStockAmount())); } else if (CompareCode.isLess(compareCode)) { predicates.add(cb.lessThan(productAmount.<Integer>get("totalAmount"), stockSearchCriteria.getStockAmount())); } else if (CompareCode.isGreaterOrEqual(compareCode)) { predicates.add(cb.greaterThanOrEqualTo(productAmount.<Integer>get("totalAmount"), stockSearchCriteria.getStockAmount())); } else if (CompareCode.isLessOrEqual(compareCode)) { predicates.add(cb.lessThanOrEqualTo(productAmount.<Integer>get("totalAmount"), stockSearchCriteria.getStockAmount())); } else if (CompareCode.isEqualAlertAmount(compareCode)) { predicates.add(cb.equal(productAmount.get("totalAmount"), root.get("alertStockAmount"))); } else if (CompareCode.isGreaterThanAlertAmount(compareCode)) { predicates.add(cb.greaterThan(productAmount.<String>get("totalAmount"), root.<String>get("alertStockAmount"))); } else if (CompareCode.isLessThanAlertAmount(compareCode)) { predicates.add(cb.lessThan(productAmount.<String>get("totalAmount"), root.<String>get("alertStockAmount"))); } if (stockSearchCriteria.isTransformAction() && ((CompareCode.isLess(compareCode) || CompareCode.isLessOrEqual(compareCode)))) { predicates.add(cb.greaterThan(productAmount.<Integer>get("totalAmount"), 0)); } } query.where(cb.and(predicates.toArray(new Predicate[predicates.size()]))) .orderBy(cb.desc(root.get("categoryCode")), cb.asc(root.get("productCode"))); return null; } }; return productStockInfoRepository.findAll(speci); }
From source file:com.carser.viamais.vo.TransactionFilter.java
private Predicate[] getPredicates(CriteriaBuilder cb, Root<Transaction> transaction) { List<Predicate> predicates = new ArrayList<>(); if (StringUtils.hasLength(pattern)) { String[] words = pattern.split(" "); for (String word : words) { Predicate model = cb.like(transaction.get(Transaction_.car).get(Car_.model).get(Model_.name), "%" + word + "%"); Predicate customer = cb.like(transaction.get(Transaction_.customer).get(Customer_.name), "%" + word + "%"); predicates.add((cb.or(model, customer))); }//w ww . ja va 2 s .c o m } if (StringUtils.hasLength(rg)) { predicates.add(cb.like(transaction.get(Transaction_.customer).get(Customer_.rg), "%" + rg + "%")); } if (StringUtils.hasLength(cpf)) { predicates.add(cb.like(transaction.get(Transaction_.customer).get(Customer_.cpf).as(String.class), "%" + cpf + "%")); } if (StringUtils.hasLength(renavam)) { predicates.add(cb.like(transaction.get(Transaction_.car).get(Car_.renavam), "%" + renavam + "%")); } if (StringUtils.hasLength(plate)) { predicates.add(cb.equal(transaction.get(Transaction_.car).get(Car_.licensePlate), plate)); } if (StringUtils.hasLength(chassi)) { predicates.add(cb.like(transaction.get(Transaction_.car).get(Car_.chassi), "%" + chassi + "%")); } Expression<Date> date = cb.function("date", Date.class, transaction.get(Transaction_.transactionDate)); if (startDate != null) { predicates.add(cb.greaterThanOrEqualTo(date, startDate)); } if (finalDate != null) { predicates.add(cb.lessThanOrEqualTo(date, finalDate)); } if (StringUtils.hasLength(type)) { switch (type) { case "sale": predicates.add(cb.equal(transaction.get(Transaction_.type), Sale.class.getSimpleName())); break; case "purchase": predicates.add(cb.equal(transaction.get(Transaction_.type), Purchase.class.getSimpleName())); break; default: throw new BusinessException("transaction type not supported"); } } if (StringUtils.hasLength(advertiser)) { predicates.add(cb.equal(transaction.get(Transaction_.advertiser), advertiser)); } if (StringUtils.hasLength(financial)) { predicates.add(cb.like(cb.treat(transaction, Sale.class).get(Sale_.financial), financial)); } if (seller != null) { predicates.add(cb.equal(transaction.get(Transaction_.seller).get(Seller_.id), seller)); } return (predicates.toArray(new Predicate[predicates.size()])); }
From source file:ca.uhn.fhir.jpa.dao.BaseFhirResourceDao.java
private Predicate addPredicateDateFromRange(CriteriaBuilder theBuilder, From<ResourceIndexedSearchParamDate, ResourceIndexedSearchParamDate> theFrom, DateRangeParam theRange) { Date lowerBound = theRange.getLowerBoundAsInstant(); Date upperBound = theRange.getUpperBoundAsInstant(); Predicate lb = null;/*from w w w . j av a 2 s . c o m*/ if (lowerBound != null) { Predicate gt = theBuilder.greaterThanOrEqualTo(theFrom.<Date>get("myValueLow"), lowerBound); Predicate lt = theBuilder.greaterThanOrEqualTo(theFrom.<Date>get("myValueHigh"), lowerBound); lb = theBuilder.or(gt, lt); // Predicate gin = builder.isNull(from.get("myValueLow")); // Predicate lbo = builder.or(gt, gin); // Predicate lin = builder.isNull(from.get("myValueHigh")); // Predicate hbo = builder.or(lt, lin); // lb = builder.and(lbo, hbo); } Predicate ub = null; if (upperBound != null) { Predicate gt = theBuilder.lessThanOrEqualTo(theFrom.<Date>get("myValueLow"), upperBound); Predicate lt = theBuilder.lessThanOrEqualTo(theFrom.<Date>get("myValueHigh"), upperBound); ub = theBuilder.or(gt, lt); // Predicate gin = builder.isNull(from.get("myValueLow")); // Predicate lbo = builder.or(gt, gin); // Predicate lin = builder.isNull(from.get("myValueHigh")); // Predicate ubo = builder.or(lt, lin); // ub = builder.and(ubo, lbo); } if (lb != null && ub != null) { return (theBuilder.and(lb, ub)); } else if (lb != null) { return (lb); } else { return (ub); } }
From source file:com.sammyun.dao.impl.BaseDaoImpl.java
private void addRestrictions(CriteriaQuery<T> criteriaQuery, List<Filter> filters) { if (criteriaQuery == null || filters == null || filters.isEmpty()) { return;/*w ww .j a v a 2s .c om*/ } Root<T> root = getRoot(criteriaQuery); if (root == null) { return; } CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder(); Predicate restrictions = criteriaQuery.getRestriction() != null ? criteriaQuery.getRestriction() : criteriaBuilder.conjunction(); for (Filter filter : filters) { if (filter == null || StringUtils.isEmpty(filter.getProperty())) { continue; } /** */ if (filter.getMold() == Mold.dl || filter.getMold() == Mold.dg) { if (filter.getOperator() == Operator.lt && filter.getValue() != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.lessThan(root.<Date>get(filter.getProperty()), DateUtil.parseDate(filter.getValue().toString()))); } else if (filter.getOperator() == Operator.gt && filter.getValue() != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.greaterThan(root.<Date>get(filter.getProperty()), DateUtil.parseDate(filter.getValue().toString()))); } else if (filter.getOperator() == Operator.le && filter.getValue() != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.lessThanOrEqualTo(root.<Date>get(filter.getProperty()), DateUtil.parseDate(filter.getValue().toString()))); } else if (filter.getOperator() == Operator.ge && filter.getValue() != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.greaterThanOrEqualTo(root.<Date>get(filter.getProperty()), DateUtil.parseDate(filter.getValue().toString()))); } } else { if (filter.getOperator() == Operator.eq && filter.getValue() != null) { if (filter.getIgnoreCase() != null && filter.getIgnoreCase() && filter.getValue() instanceof String) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(criteriaBuilder.lower(root.<String>get(filter.getProperty())), ((String) filter.getValue()).toLowerCase())); } else { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get(filter.getProperty()), filter.getValue())); } } else if (filter.getOperator() == Operator.ne && filter.getValue() != null) { if (filter.getIgnoreCase() != null && filter.getIgnoreCase() && filter.getValue() instanceof String) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.notEqual( criteriaBuilder.lower(root.<String>get(filter.getProperty())), ((String) filter.getValue()).toLowerCase())); } else { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.notEqual(root.get(filter.getProperty()), filter.getValue())); } } else if (filter.getOperator() == Operator.gt && filter.getValue() != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.gt(root.<Number>get(filter.getProperty()), (Number) filter.getValue())); } else if (filter.getOperator() == Operator.lt && filter.getValue() != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.lt(root.<Number>get(filter.getProperty()), (Number) filter.getValue())); } else if (filter.getOperator() == Operator.ge && filter.getValue() != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.ge(root.<Number>get(filter.getProperty()), (Number) filter.getValue())); } else if (filter.getOperator() == Operator.le && filter.getValue() != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.le(root.<Number>get(filter.getProperty()), (Number) filter.getValue())); } else if (filter.getOperator() == Operator.like && filter.getValue() != null && filter.getValue() instanceof String) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder .like(root.<String>get(filter.getProperty()), (String) filter.getValue())); } else if (filter.getOperator() == Operator.in && filter.getValue() != null) { restrictions = criteriaBuilder.and(restrictions, root.get(filter.getProperty()).in(filter.getValue())); } else if (filter.getOperator() == Operator.isNull) { restrictions = criteriaBuilder.and(restrictions, root.get(filter.getProperty()).isNull()); } else if (filter.getOperator() == Operator.isNotNull) { restrictions = criteriaBuilder.and(restrictions, root.get(filter.getProperty()).isNotNull()); } } } criteriaQuery.where(restrictions); }
From source file:gov.gtas.repository.CaseDispositionRepositoryImpl.java
@Override public Pair<Long, List<Case>> findByCriteria(CaseRequestDto dto) { CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<Case> q = cb.createQuery(Case.class); Root<Case> root = q.from(Case.class); List<Predicate> predicates = new ArrayList<>(); TypedQuery<Case> typedQuery = em.createQuery(q); // sorting/*w w w . ja v a 2 s. c o m*/ if (dto.getSort() != null) { List<Order> orders = new ArrayList<>(); for (SortOptionsDto sort : dto.getSort()) { Expression<?> e = root.get(sort.getColumn()); Order order; if ("desc".equalsIgnoreCase(sort.getDir())) { order = cb.desc(e); } else { order = cb.asc(e); } orders.add(order); } q.orderBy(orders); } if (dto.getFlightId() != null) { predicates.add(cb.equal(root.<Long>get("flightId"), dto.getFlightId())); } if (dto.getPaxId() != null) { predicates.add(cb.equal(root.<Long>get("paxId"), dto.getPaxId())); } if (dto.getPaxName() != null) { String likeString = String.format("%%%s%%", dto.getPaxName().toUpperCase()); predicates.add(cb.like(root.<String>get("paxName"), likeString)); } if (dto.getLastName() != null) { // map this to full pax name String likeString = String.format("%%%s%%", dto.getLastName().toUpperCase()); predicates.add(cb.like(root.<String>get("paxName"), likeString)); } if (dto.getStatus() != null) { String likeString = String.format("%%%s%%", dto.getStatus().toUpperCase()); predicates.add(cb.like(root.<String>get("status"), likeString)); } if (dto.getFlightNumber() != null) { predicates.add(cb.equal(root.<Long>get("flightNumber"), dto.getFlightNumber())); } if (dto.getRuleCatId() != null) { predicates.add(cb.equal(root.<Long>get("highPriorityRuleCatId"), dto.getRuleCatId())); } Predicate etaCondition; if (dto.getEtaStart() != null && dto.getEtaEnd() != null) { Path<Date> eta = root.<Date>get("flightETADate"); Predicate startPredicate = cb.or(cb.isNull(eta), cb.greaterThanOrEqualTo(eta, dto.getEtaStart())); Predicate endPredicate = cb.or(cb.isNull(eta), cb.lessThanOrEqualTo(eta, dto.getEtaEnd())); etaCondition = cb.and(startPredicate, endPredicate); predicates.add(etaCondition); } q.select(root).where(predicates.toArray(new Predicate[] {})); typedQuery = em.createQuery(q); // total count CriteriaQuery<Long> countQuery = cb.createQuery(Long.class); countQuery.select(cb.count(countQuery.from(Case.class))).where(predicates.toArray(new Predicate[] {})); Long count = em.createQuery(countQuery).getSingleResult(); // pagination int pageNumber = dto.getPageNumber(); int pageSize = dto.getPageSize(); int firstResultIndex = (pageNumber - 1) * pageSize; typedQuery.setFirstResult(firstResultIndex); typedQuery.setMaxResults(dto.getPageSize()); logger.debug(typedQuery.unwrap(org.hibernate.Query.class).getQueryString()); List<Case> results = typedQuery.getResultList(); return new ImmutablePair<>(count, results); }
From source file:ca.uhn.fhir.jpa.dao.SearchBuilder.java
private Predicate createPredicateDateFromRange(CriteriaBuilder theBuilder, From<ResourceIndexedSearchParamDate, ResourceIndexedSearchParamDate> theFrom, DateRangeParam theRange) { Date lowerBound = theRange.getLowerBoundAsInstant(); Date upperBound = theRange.getUpperBoundAsInstant(); Predicate lb = null;/*from w w w. j a v a2 s .c o m*/ if (lowerBound != null) { Predicate gt = theBuilder.greaterThanOrEqualTo(theFrom.<Date>get("myValueLow"), lowerBound); Predicate lt = theBuilder.greaterThanOrEqualTo(theFrom.<Date>get("myValueHigh"), lowerBound); if (theRange.getLowerBound().getPrefix() == ParamPrefixEnum.STARTS_AFTER || theRange.getLowerBound().getPrefix() == ParamPrefixEnum.EQUAL) { lb = gt; } else { lb = theBuilder.or(gt, lt); } } Predicate ub = null; if (upperBound != null) { Predicate gt = theBuilder.lessThanOrEqualTo(theFrom.<Date>get("myValueLow"), upperBound); Predicate lt = theBuilder.lessThanOrEqualTo(theFrom.<Date>get("myValueHigh"), upperBound); if (theRange.getUpperBound().getPrefix() == ParamPrefixEnum.ENDS_BEFORE || theRange.getUpperBound().getPrefix() == ParamPrefixEnum.EQUAL) { ub = lt; } else { ub = theBuilder.or(gt, lt); } } if (lb != null && ub != null) { return (theBuilder.and(lb, ub)); } else if (lb != null) { return (lb); } else { return (ub); } }
From source file:com.sammyun.dao.impl.BaseDaoImpl.java
private void addRestrictions(CriteriaQuery<T> criteriaQuery, Pageable pageable) { if (criteriaQuery == null || pageable == null) { return;/*from w w w . ja v a2 s.c o m*/ } Root<T> root = getRoot(criteriaQuery); if (root == null) { return; } CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder(); Predicate restrictions = criteriaQuery.getRestriction() != null ? criteriaQuery.getRestriction() : criteriaBuilder.conjunction(); if (StringUtils.isNotEmpty(pageable.getSearchProperty()) && StringUtils.isNotEmpty(pageable.getSearchValue())) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder .like(root.<String>get(pageable.getSearchProperty()), "%" + pageable.getSearchValue() + "%")); } if (pageable.getFilters() != null) { for (Filter filter : pageable.getFilters()) { if (filter == null || StringUtils.isEmpty(filter.getProperty())) { continue; } /** */ if (filter.getMold() == Mold.dl || filter.getMold() == Mold.dg) { if (filter.getOperator() == Operator.lt && filter.getValue() != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.lessThan(root.<Date>get(filter.getProperty()), DateUtil.parseDate(filter.getValue().toString()))); } else if (filter.getOperator() == Operator.gt && filter.getValue() != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.greaterThan(root.<Date>get(filter.getProperty()), DateUtil.parseDate(filter.getValue().toString()))); } else if (filter.getOperator() == Operator.le && filter.getValue() != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.lessThanOrEqualTo(root.<Date>get(filter.getProperty()), DateUtil.parseDate(filter.getValue().toString()))); } else if (filter.getOperator() == Operator.ge && filter.getValue() != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.greaterThanOrEqualTo(root.<Date>get(filter.getProperty()), DateUtil.parseDate(filter.getValue().toString()))); } } else { if (filter.getOperator() == Operator.eq && filter.getValue() != null) { if (filter.getIgnoreCase() != null && filter.getIgnoreCase() && filter.getValue() instanceof String) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal( criteriaBuilder.lower(root.<String>get(filter.getProperty())), ((String) filter.getValue()).toLowerCase())); } else { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get(filter.getProperty()), filter.getValue())); } } else if (filter.getOperator() == Operator.ne && filter.getValue() != null) { if (filter.getIgnoreCase() != null && filter.getIgnoreCase() && filter.getValue() instanceof String) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.notEqual( criteriaBuilder.lower(root.<String>get(filter.getProperty())), ((String) filter.getValue()).toLowerCase())); } else { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.notEqual(root.get(filter.getProperty()), filter.getValue())); } } else if (filter.getOperator() == Operator.gt && filter.getValue() != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder .gt(root.<Number>get(filter.getProperty()), (Number) filter.getValue())); } else if (filter.getOperator() == Operator.lt && filter.getValue() != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder .lt(root.<Number>get(filter.getProperty()), (Number) filter.getValue())); } else if (filter.getOperator() == Operator.ge && filter.getValue() != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder .ge(root.<Number>get(filter.getProperty()), (Number) filter.getValue())); } else if (filter.getOperator() == Operator.le && filter.getValue() != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder .le(root.<Number>get(filter.getProperty()), (Number) filter.getValue())); } else if (filter.getOperator() == Operator.like && filter.getValue() != null && filter.getValue() instanceof String) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder .like(root.<String>get(filter.getProperty()), (String) filter.getValue())); } else if (filter.getOperator() == Operator.in && filter.getValue() != null) { restrictions = criteriaBuilder.and(restrictions, root.get(filter.getProperty()).in(filter.getValue())); } else if (filter.getOperator() == Operator.isNull) { restrictions = criteriaBuilder.and(restrictions, root.get(filter.getProperty()).isNull()); } else if (filter.getOperator() == Operator.isNotNull) { restrictions = criteriaBuilder.and(restrictions, root.get(filter.getProperty()).isNotNull()); } } } } criteriaQuery.where(restrictions); }
From source file:net.shopxx.dao.impl.StatisticDaoImpl.java
public List<Statistic> analyze(Statistic.Period period, Date beginDate, Date endDate) { Assert.notNull(period);/* ww w . j ava 2s . c om*/ CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder(); CriteriaQuery<Statistic> criteriaQuery = criteriaBuilder.createQuery(Statistic.class); Root<Statistic> root = criteriaQuery.from(Statistic.class); switch (period) { case year: criteriaQuery.select(criteriaBuilder.construct(Statistic.class, root.get("year"), criteriaBuilder.sum(root.<Long>get("registerMemberCount")), criteriaBuilder.sum(root.<Long>get("createOrderCount")), criteriaBuilder.sum(root.<Long>get("completeOrderCount")), criteriaBuilder.sum(root.<BigDecimal>get("createOrderAmount")), criteriaBuilder.sum(root.<BigDecimal>get("completeOrderAmount")))); criteriaQuery.groupBy(root.get("year")); break; case month: criteriaQuery.select(criteriaBuilder.construct(Statistic.class, root.get("year"), root.get("month"), criteriaBuilder.sum(root.<Long>get("registerMemberCount")), criteriaBuilder.sum(root.<Long>get("createOrderCount")), criteriaBuilder.sum(root.<Long>get("completeOrderCount")), criteriaBuilder.sum(root.<BigDecimal>get("createOrderAmount")), criteriaBuilder.sum(root.<BigDecimal>get("completeOrderAmount")))); criteriaQuery.groupBy(root.get("year"), root.get("month")); break; case day: criteriaQuery.select(criteriaBuilder.construct(Statistic.class, root.get("year"), root.get("month"), root.get("day"), root.<Long>get("registerMemberCount"), root.<Long>get("createOrderCount"), root.<Long>get("completeOrderCount"), root.<BigDecimal>get("createOrderAmount"), root.<BigDecimal>get("completeOrderAmount"))); break; } Predicate restrictions = criteriaBuilder.conjunction(); if (beginDate != null) { Calendar calendar = DateUtils.toCalendar(beginDate); int year = calendar.get(Calendar.YEAR); int month = calendar.get(Calendar.MONTH); int day = calendar.get(Calendar.DAY_OF_MONTH); restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.or(criteriaBuilder.greaterThan(root.<Integer>get("year"), year), criteriaBuilder.and(criteriaBuilder.equal(root.<Integer>get("year"), year), criteriaBuilder.greaterThan(root.<Integer>get("month"), month)), criteriaBuilder.and(criteriaBuilder.equal(root.<Integer>get("year"), year), criteriaBuilder.equal(root.<Integer>get("month"), month), criteriaBuilder.greaterThanOrEqualTo(root.<Integer>get("day"), day)))); } if (endDate != null) { Calendar calendar = DateUtils.toCalendar(endDate); int year = calendar.get(Calendar.YEAR); int month = calendar.get(Calendar.MONTH); int day = calendar.get(Calendar.DAY_OF_MONTH); restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.or(criteriaBuilder.lessThan(root.<Integer>get("year"), year), criteriaBuilder.and(criteriaBuilder.equal(root.<Integer>get("year"), year), criteriaBuilder.lessThan(root.<Integer>get("month"), month)), criteriaBuilder.and(criteriaBuilder.equal(root.<Integer>get("year"), year), criteriaBuilder.equal(root.<Integer>get("month"), month), criteriaBuilder.lessThanOrEqualTo(root.<Integer>get("day"), day)))); } criteriaQuery.where(restrictions); return entityManager.createQuery(criteriaQuery).getResultList(); }
From source file:utils.jpa.EntityResource.java
private Query createCommonQuery(CriteriaBuilder cb, CriteriaQuery cq, Root root, TableSearchQuery tb, List<String> fieldsSearchBy) { List<Predicate> orPredicates = new ArrayList<>(); List<Predicate> andPredicates = new ArrayList<>(); //setting where conditions if (fieldsSearchBy != null && !fieldsSearchBy.isEmpty()) { for (String field : fieldsSearchBy) { if (tb.getFilter() != null && !tb.getFilter().isEmpty()) { orPredicates.add(cb.like(cb.lower(cb.concat(root.<String>get(field), "::text")), "%" + tb.getFilter().toLowerCase() + "%")); }/* w w w .ja v a 2s .c o m*/ } } else { List<Field> filterColumns = SearchFields.getAll(entityClass); for (Field field : filterColumns) { EntityFilter entityFilter = tb.getFilterParameters().get(field.getName()); if ((Collection.class.isAssignableFrom(field.getType()) || List.class.isAssignableFrom(field.getType())) && (entityFilter == null || !entityFilter.getEntityFilterType().equals(EntityFilterType.EMPTY))) { continue; } if (entityFilter == null) { if (tb.getFilter() != null && !tb.getFilter().isEmpty()) { orPredicates.add(cb.like(cb.lower(cb.concat(root.<String>get(field.getName()), "::text")), "%" + tb.getFilter().toLowerCase() + "%")); } continue; } switch (entityFilter.getEntityFilterType()) { case WILDCARD: andPredicates.add(cb.like(cb.lower(cb.concat(root.<String>get(field.getName()), "::text")), "%" + entityFilter.getValue().toLowerCase() + "%")); break; case EMPTY: if (Collection.class.isAssignableFrom(field.getType()) || List.class.isAssignableFrom(field.getType())) { andPredicates.add(entityFilter.getValue().equals("hide") ? cb.isEmpty(root.<Collection>get(field.getName())) : cb.isNotEmpty(root.<Collection>get(field.getName()))); } else { andPredicates.add( entityFilter.getValue().equals("hide") ? root.<Object>get(field.getName()).isNull() : root.<Object>get(field.getName()).isNotNull()); } break; case MIN_MAX: andPredicates.add(cb.greaterThanOrEqualTo(root.<Date>get(field.getName()), new Date(Long.valueOf(entityFilter.getValues().get(0))))); andPredicates.add(cb.lessThanOrEqualTo(root.<Date>get(field.getName()), new Date(Long.valueOf(entityFilter.getValues().get(1))))); break; case MIN: andPredicates.add(cb.greaterThanOrEqualTo(root.<Date>get(field.getName()), new Date(Long.valueOf(entityFilter.getValue())))); break; case MAX: andPredicates.add(cb.lessThanOrEqualTo(root.<Date>get(field.getName()), new Date(Long.valueOf(entityFilter.getValue())))); break; } } } if (orPredicates.size() > 0 && andPredicates.size() > 0) { cq.where(cb.or(cb.or(orPredicates.toArray(new Predicate[0])), cb.and(andPredicates.toArray(new Predicate[0])))); } else if (orPredicates.size() > 0) { cq.where(cb.or(orPredicates.toArray(new Predicate[0]))); } else if (andPredicates.size() > 0) { cq.where(cb.and(andPredicates.toArray(new Predicate[0]))); } return em.createQuery(cq); }