List of usage examples for javax.persistence.criteria CriteriaBuilder or
Predicate or(Expression<Boolean> x, Expression<Boolean> y);
From source file:org.oncoblocks.centromere.jpa.QueryCriteriaSpecification.java
public Predicate toPredicate(Root<T> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) { String key = queryCriteria.getKey(); Object value = queryCriteria.getValue(); Evaluation eval = queryCriteria.getEvaluation(); Path path = null;//from w ww . ja v a 2 s. co m if (key.contains(".")) { String[] bits = key.split("\\."); path = root.join(bits[0]).get(bits[1]); } else { path = root.get(key); } logger.debug(String.format("[CENTROMERE] Converting QueryCriteria to JPA specification: %s", queryCriteria.toString())); switch (eval) { case EQUALS: return criteriaBuilder.equal(path, value); case NOT_EQUALS: return criteriaBuilder.notEqual(path, value); case IN: return path.in(value); case NOT_IN: return criteriaBuilder.not(path.in(value)); case IS_NULL: return criteriaBuilder.isNull(path); case NOT_NULL: return criteriaBuilder.isNotNull(path); case GREATER_THAN: return criteriaBuilder.greaterThan(path, value.toString()); case GREATER_THAN_EQUALS: return criteriaBuilder.greaterThanOrEqualTo(path, value.toString()); case LESS_THAN: return criteriaBuilder.lessThan(path, value.toString()); case LESS_THAN_EQUALS: return criteriaBuilder.lessThanOrEqualTo(path, value.toString()); case BETWEEN: return criteriaBuilder.and(criteriaBuilder.greaterThan(path, ((List<?>) value).get(0).toString()), criteriaBuilder.lessThan(path, ((List<?>) value).get(1).toString())); case OUTSIDE: return criteriaBuilder.or(criteriaBuilder.greaterThan(path, ((List<?>) value).get(1).toString()), criteriaBuilder.lessThan(path, ((List<?>) value).get(0).toString())); case BETWEEN_INCLUSIVE: return criteriaBuilder.and( criteriaBuilder.greaterThanOrEqualTo(path, ((List<?>) value).get(0).toString()), criteriaBuilder.lessThanOrEqualTo(path, ((List<?>) value).get(1).toString())); case OUTSIDE_INCLUSIVE: return criteriaBuilder.or( criteriaBuilder.greaterThanOrEqualTo(path, ((List<?>) value).get(1).toString()), criteriaBuilder.lessThanOrEqualTo(path, ((List<?>) value).get(0).toString())); case LIKE: return criteriaBuilder.like(path, "%" + value.toString() + "%"); case NOT_LIKE: return criteriaBuilder.notLike(path, "%" + value.toString() + "%"); case STARTS_WITH: return criteriaBuilder.like(path, value.toString() + "%"); case ENDS_WITH: return criteriaBuilder.like(path, "%" + value.toString()); default: return criteriaBuilder.equal(root.get(queryCriteria.getKey()), queryCriteria.getValue()); } }
From source file:com.yunguchang.data.ApplicationRepository.java
public List<TBusApproveSugEntity> listApplyApproveInfo(DateTime startTime, DateTime endTime, PrincipalExt principalExtOrNull) { if (startTime == null) { startTime = DateTimeUtil.appStartTime; }//w ww.ja v a2s .c om if (endTime == null) { endTime = DateTimeUtil.appEndTime; } CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<TBusApproveSugEntity> cq = cb.createQuery(TBusApproveSugEntity.class); Root<TBusApproveSugEntity> approveRoot = cq.from(TBusApproveSugEntity.class); approveRoot.fetch(TBusApproveSugEntity_.application); approveRoot.fetch(TBusApproveSugEntity_.user, JoinType.LEFT); cq.select(approveRoot); Predicate predicate = cb.and( cb.between(approveRoot.get(TBusApproveSugEntity_.operatedate), new Timestamp(startTime.getMillis()), new Timestamp(endTime.getMillis())), cb.or(cb.isNull(approveRoot.get(TBusApproveSugEntity_.updateBySync)), cb.isFalse(approveRoot.get(TBusApproveSugEntity_.updateBySync)))); cq.where(predicate); TypedQuery<TBusApproveSugEntity> query = em.createQuery(cq); applySecurityFilter("applications", principalExtOrNull); return query.getResultList(); }
From source file:com.yunguchang.data.ApplicationRepository.java
public List<TAzCarinfoEntity> listAllCandidateCars(String[] applicationIds, String keyword, Integer offset, Integer limit, PrincipalExt principalExt) { CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<TAzCarinfoEntity> cq = cb.createQuery(TAzCarinfoEntity.class); Root<TAzCarinfoEntity> carRoot = cq.from(TAzCarinfoEntity.class); carRoot.fetch(TAzCarinfoEntity_.driver, JoinType.LEFT); cq.select(carRoot);/*from w ww . j a va 2s . c om*/ Subquery<TBusScheduleCarEntity> overlapScheduleCarSubQuery = cq.subquery(TBusScheduleCarEntity.class); applyOverlapScheduleCarSubquery(overlapScheduleCarSubQuery, applicationIds, carRoot, null, cb, principalExt); Predicate predicate = cb.and(cb.equal(carRoot.get(TAzCarinfoEntity_.clzt), "02"), cb.or(cb.equal(carRoot.get(TAzCarinfoEntity_.repairingState), RepairingState.NONE.id()), cb.isNull(carRoot.get(TAzCarinfoEntity_.repairingState)) ), cb.not(cb.exists(overlapScheduleCarSubQuery)) ); if (keyword != null) { predicate = cb.and(predicate, cb.or(cb.like(carRoot.get(TAzCarinfoEntity_.sysOrg).get(TSysOrgEntity_.orgname), "%" + keyword + "%"), cb.like(carRoot.get(TAzCarinfoEntity_.cphm), "%" + keyword + "%"))); } cq.where(predicate); cq.orderBy(cb.asc(carRoot.get(TAzCarinfoEntity_.cphm))); TypedQuery<TAzCarinfoEntity> query = em.createQuery(cq); if (offset != null) { query.setFirstResult(offset); } if (limit != null) { query.setMaxResults(limit); } applySecurityFilter("cars", principalExt); return query.getResultList(); }
From source file:com.ims.service.SupportingDataService.java
/** * SQL: select * from ims_product_info where CATEGORY_CODE='xxx' and PRODUCT_CODE like '%yyy%' and (CUST_PROD_CODE like '%aaa%' or CUST_PROD_SECOND_CODE like '%aaa%') * ??//from w w w . j a v a2s . c o m * * @param prodSearchCriteria * @return */ public List<ProductInfo> findProductInfoListFrom(final ProdSearchCriteria prodSearchCriteria) { Specification<ProductInfo> speci = new Specification<ProductInfo>() { @Override public Predicate toPredicate(Root<ProductInfo> root, CriteriaQuery<?> query, CriteriaBuilder cb) { List<Predicate> predicates = new ArrayList<Predicate>(); Path<String> custProdCode = root.<String>get(ProductInfo.CUST_PROD_CODE); Path<String> custProdCode2 = root.<String>get(ProductInfo.CUST_SEC_PROD_CODE); Predicate orClause = null; if (StringUtils.isNotBlank(prodSearchCriteria.getProdCategoryCode())) { predicates.add(cb.equal(root.get(ProductInfo.CATEGORY_CODE), prodSearchCriteria.getProdCategoryCode())); } if (StringUtils.isNotEmpty(prodSearchCriteria.getProdCode())) { predicates.add(cb.like(root.<String>get(ProductInfo.PRODUCT_CODE), getLikeString(prodSearchCriteria.getProdCode()))); } if (StringUtils.isNotBlank(prodSearchCriteria.getCustProdCode())) { //??orpredicates?where? query.where(cb.and(predicates.toArray(new Predicate[predicates.size()]))); //Predicate p1 = cb.or(cb.like(custProdCode, getLikeString(prodSearchCriteria.getCustProdCode())), cb.like(custProdCode2, getLikeString(prodSearchCriteria.getCustProdCode()))); //predicates.add(p1); //??orPredicate?where????query.where(cb.and(predicates.toArray(new Predicate[predicates.size()])),orClause); String param = getLikeString(prodSearchCriteria.getCustProdCode()); orClause = cb.or(cb.like(custProdCode, param), cb.like(custProdCode2, param)); } //select * from ims_product_info where CATEGORY_CODE=? and (PRODUCT_CODE like ?) and (CUST_PROD_CODE like ? or CUST_PROD_SECOND_CODE like ?) //?? //query.where(cb.and(predicates.toArray(new Predicate[predicates.size()]))); //?? if (orClause != null) { query.where(cb.and(predicates.toArray(new Predicate[predicates.size()])), orClause); } else { query.where(cb.and(predicates.toArray(new Predicate[predicates.size()]))); } return null; } }; return productInfoRepository.findAll(speci); }
From source file:net.shopxx.dao.impl.OrderDaoImpl.java
public Page<Order> findPage(Order.Type type, Order.Status status, Member member, Goods goods, Boolean isPendingReceive, Boolean isPendingRefunds, Boolean isUseCouponCode, Boolean isExchangePoint, Boolean isAllocatedStock, Boolean hasExpired, Pageable pageable) { CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder(); CriteriaQuery<Order> criteriaQuery = criteriaBuilder.createQuery(Order.class); Root<Order> root = criteriaQuery.from(Order.class); criteriaQuery.select(root);/*from w ww . j a v a 2 s . c o m*/ Predicate restrictions = criteriaBuilder.conjunction(); if (type != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("type"), type)); } if (status != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("status"), status)); } if (member != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("member"), member)); } if (goods != null) { Subquery<Product> productSubquery = criteriaQuery.subquery(Product.class); Root<Product> productSubqueryRoot = productSubquery.from(Product.class); productSubquery.select(productSubqueryRoot); productSubquery.where(criteriaBuilder.equal(productSubqueryRoot.get("goods"), goods)); Subquery<OrderItem> orderItemSubquery = criteriaQuery.subquery(OrderItem.class); Root<OrderItem> orderItemSubqueryRoot = orderItemSubquery.from(OrderItem.class); orderItemSubquery.select(orderItemSubqueryRoot); orderItemSubquery.where(criteriaBuilder.equal(orderItemSubqueryRoot.get("order"), root), criteriaBuilder.in(orderItemSubqueryRoot.get("product")).value(productSubquery)); restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.exists(orderItemSubquery)); } if (isPendingReceive != null) { Predicate predicate = criteriaBuilder.and( criteriaBuilder.or(root.get("expire").isNull(), criteriaBuilder.greaterThan(root.<Date>get("expire"), new Date())), criteriaBuilder.equal(root.get("paymentMethodType"), PaymentMethod.Type.cashOnDelivery), criteriaBuilder.notEqual(root.get("status"), Order.Status.completed), criteriaBuilder.notEqual(root.get("status"), Order.Status.failed), criteriaBuilder.notEqual(root.get("status"), Order.Status.canceled), criteriaBuilder.notEqual(root.get("status"), Order.Status.denied), criteriaBuilder.lessThan(root.<BigDecimal>get("amountPaid"), root.<BigDecimal>get("amount"))); if (isPendingReceive) { restrictions = criteriaBuilder.and(restrictions, predicate); } else { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.not(predicate)); } } if (isPendingRefunds != null) { Predicate predicate = criteriaBuilder.or( criteriaBuilder.and( criteriaBuilder.or( criteriaBuilder.and(root.get("expire").isNotNull(), criteriaBuilder.lessThanOrEqualTo(root.<Date>get("expire"), new Date())), criteriaBuilder.equal(root.get("status"), Order.Status.failed), criteriaBuilder.equal(root.get("status"), Order.Status.canceled), criteriaBuilder.equal(root.get("status"), Order.Status.denied)), criteriaBuilder.greaterThan(root.<BigDecimal>get("amountPaid"), BigDecimal.ZERO)), criteriaBuilder.and(criteriaBuilder.equal(root.get("status"), Order.Status.completed), criteriaBuilder.greaterThan(root.<BigDecimal>get("amountPaid"), root.<BigDecimal>get("amount")))); if (isPendingRefunds) { restrictions = criteriaBuilder.and(restrictions, predicate); } else { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.not(predicate)); } } if (isUseCouponCode != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("isUseCouponCode"), isUseCouponCode)); } if (isExchangePoint != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("isExchangePoint"), isExchangePoint)); } if (isAllocatedStock != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("isAllocatedStock"), isAllocatedStock)); } if (hasExpired != null) { if (hasExpired) { restrictions = criteriaBuilder.and(restrictions, root.get("expire").isNotNull(), criteriaBuilder.lessThanOrEqualTo(root.<Date>get("expire"), new Date())); } else { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.or(root.get("expire").isNull(), criteriaBuilder.greaterThan(root.<Date>get("expire"), new Date()))); } } criteriaQuery.where(restrictions); return super.findPage(criteriaQuery, pageable); }
From source file:net.shopxx.dao.impl.OrderDaoImpl.java
public Long count(Order.Type type, Order.Status status, Member member, Goods goods, Boolean isPendingReceive, Boolean isPendingRefunds, Boolean isUseCouponCode, Boolean isExchangePoint, Boolean isAllocatedStock, Boolean hasExpired) {/*w ww . j av a2 s . c o m*/ CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder(); CriteriaQuery<Order> criteriaQuery = criteriaBuilder.createQuery(Order.class); Root<Order> root = criteriaQuery.from(Order.class); criteriaQuery.select(root); Predicate restrictions = criteriaBuilder.conjunction(); if (type != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("type"), type)); } if (status != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("status"), status)); } if (member != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("member"), member)); } if (goods != null) { Subquery<Product> productSubquery = criteriaQuery.subquery(Product.class); Root<Product> productSubqueryRoot = productSubquery.from(Product.class); productSubquery.select(productSubqueryRoot); productSubquery.where(criteriaBuilder.equal(productSubqueryRoot.get("goods"), goods)); Subquery<OrderItem> orderItemSubquery = criteriaQuery.subquery(OrderItem.class); Root<OrderItem> orderItemSubqueryRoot = orderItemSubquery.from(OrderItem.class); orderItemSubquery.select(orderItemSubqueryRoot); orderItemSubquery.where(criteriaBuilder.equal(orderItemSubqueryRoot.get("order"), root), criteriaBuilder.in(orderItemSubqueryRoot.get("product")).value(productSubquery)); restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.exists(orderItemSubquery)); } if (isPendingReceive != null) { Predicate predicate = criteriaBuilder.and( criteriaBuilder.or(root.get("expire").isNull(), criteriaBuilder.greaterThan(root.<Date>get("expire"), new Date())), criteriaBuilder.equal(root.get("paymentMethodType"), PaymentMethod.Type.cashOnDelivery), criteriaBuilder.notEqual(root.get("status"), Order.Status.completed), criteriaBuilder.notEqual(root.get("status"), Order.Status.failed), criteriaBuilder.notEqual(root.get("status"), Order.Status.canceled), criteriaBuilder.notEqual(root.get("status"), Order.Status.denied), criteriaBuilder.lessThan(root.<BigDecimal>get("amountPaid"), root.<BigDecimal>get("amount"))); if (isPendingReceive) { restrictions = criteriaBuilder.and(restrictions, predicate); } else { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.not(predicate)); } } if (isPendingRefunds != null) { Predicate predicate = criteriaBuilder.or( criteriaBuilder.and( criteriaBuilder.or( criteriaBuilder.and(root.get("expire").isNotNull(), criteriaBuilder.lessThanOrEqualTo(root.<Date>get("expire"), new Date())), criteriaBuilder.equal(root.get("status"), Order.Status.failed), criteriaBuilder.equal(root.get("status"), Order.Status.canceled), criteriaBuilder.equal(root.get("status"), Order.Status.denied)), criteriaBuilder.greaterThan(root.<BigDecimal>get("amountPaid"), BigDecimal.ZERO)), criteriaBuilder.and(criteriaBuilder.equal(root.get("status"), Order.Status.completed), criteriaBuilder.greaterThan(root.<BigDecimal>get("amountPaid"), root.<BigDecimal>get("amount")))); if (isPendingRefunds) { restrictions = criteriaBuilder.and(restrictions, predicate); } else { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.not(predicate)); } } if (isUseCouponCode != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("isUseCouponCode"), isUseCouponCode)); } if (isExchangePoint != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("isExchangePoint"), isExchangePoint)); } if (isAllocatedStock != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("isAllocatedStock"), isAllocatedStock)); } if (hasExpired != null) { if (hasExpired) { restrictions = criteriaBuilder.and(restrictions, root.get("expire").isNotNull(), criteriaBuilder.lessThanOrEqualTo(root.<Date>get("expire"), new Date())); } else { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.or(root.get("expire").isNull(), criteriaBuilder.greaterThan(root.<Date>get("expire"), new Date()))); } } criteriaQuery.where(restrictions); return super.count(criteriaQuery, null); }
From source file:net.shopxx.dao.impl.OrderDaoImpl.java
public List<Order> findList(Order.Type type, Order.Status status, Member member, Goods goods, Boolean isPendingReceive, Boolean isPendingRefunds, Boolean isUseCouponCode, Boolean isExchangePoint, Boolean isAllocatedStock, Boolean hasExpired, Integer count, List<Filter> filters, List<net.shopxx.Order> orders) { CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder(); CriteriaQuery<Order> criteriaQuery = criteriaBuilder.createQuery(Order.class); Root<Order> root = criteriaQuery.from(Order.class); criteriaQuery.select(root);//from w ww . ja va 2 s . com Predicate restrictions = criteriaBuilder.conjunction(); if (type != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("type"), type)); } if (status != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("status"), status)); } if (member != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("member"), member)); } if (goods != null) { Subquery<Product> productSubquery = criteriaQuery.subquery(Product.class); Root<Product> productSubqueryRoot = productSubquery.from(Product.class); productSubquery.select(productSubqueryRoot); productSubquery.where(criteriaBuilder.equal(productSubqueryRoot.get("goods"), goods)); Subquery<OrderItem> orderItemSubquery = criteriaQuery.subquery(OrderItem.class); Root<OrderItem> orderItemSubqueryRoot = orderItemSubquery.from(OrderItem.class); orderItemSubquery.select(orderItemSubqueryRoot); orderItemSubquery.where(criteriaBuilder.equal(orderItemSubqueryRoot.get("order"), root), criteriaBuilder.in(orderItemSubqueryRoot.get("product")).value(productSubquery)); restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.exists(orderItemSubquery)); } if (isPendingReceive != null) { Predicate predicate = criteriaBuilder.and( criteriaBuilder.or(root.get("expire").isNull(), criteriaBuilder.greaterThan(root.<Date>get("expire"), new Date())), criteriaBuilder.equal(root.get("paymentMethodType"), PaymentMethod.Type.cashOnDelivery), criteriaBuilder.notEqual(root.get("status"), Order.Status.completed), criteriaBuilder.notEqual(root.get("status"), Order.Status.failed), criteriaBuilder.notEqual(root.get("status"), Order.Status.canceled), criteriaBuilder.notEqual(root.get("status"), Order.Status.denied), criteriaBuilder.lessThan(root.<BigDecimal>get("amountPaid"), root.<BigDecimal>get("amount"))); if (isPendingReceive) { restrictions = criteriaBuilder.and(restrictions, predicate); } else { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.not(predicate)); } } if (isPendingRefunds != null) { Predicate predicate = criteriaBuilder.or( criteriaBuilder.and( criteriaBuilder.or( criteriaBuilder.and(root.get("expire").isNotNull(), criteriaBuilder.lessThanOrEqualTo(root.<Date>get("expire"), new Date())), criteriaBuilder.equal(root.get("status"), Order.Status.failed), criteriaBuilder.equal(root.get("status"), Order.Status.canceled), criteriaBuilder.equal(root.get("status"), Order.Status.denied)), criteriaBuilder.greaterThan(root.<BigDecimal>get("amountPaid"), BigDecimal.ZERO)), criteriaBuilder.and(criteriaBuilder.equal(root.get("status"), Order.Status.completed), criteriaBuilder.greaterThan(root.<BigDecimal>get("amountPaid"), root.<BigDecimal>get("amount")))); if (isPendingRefunds) { restrictions = criteriaBuilder.and(restrictions, predicate); } else { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.not(predicate)); } } if (isUseCouponCode != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("isUseCouponCode"), isUseCouponCode)); } if (isExchangePoint != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("isExchangePoint"), isExchangePoint)); } if (isAllocatedStock != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("isAllocatedStock"), isAllocatedStock)); } if (hasExpired != null) { if (hasExpired) { restrictions = criteriaBuilder.and(restrictions, root.get("expire").isNotNull(), criteriaBuilder.lessThanOrEqualTo(root.<Date>get("expire"), new Date())); } else { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.or(root.get("expire").isNull(), criteriaBuilder.greaterThan(root.<Date>get("expire"), new Date()))); } } criteriaQuery.where(restrictions); return super.findList(criteriaQuery, null, count, filters, orders); }
From source file:net.groupbuy.dao.impl.ProductDaoImpl.java
public Long count(Member favoriteMember, Boolean isMarketable, Boolean isList, Boolean isTop, Boolean isGift, Boolean isOutOfStock, Boolean isStockAlert) { CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder(); CriteriaQuery<Product> criteriaQuery = criteriaBuilder.createQuery(Product.class); Root<Product> root = criteriaQuery.from(Product.class); criteriaQuery.select(root);/*from ww w . j a v a 2 s . c o m*/ Predicate restrictions = criteriaBuilder.conjunction(); if (favoriteMember != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.join("favoriteMembers"), favoriteMember)); } if (isMarketable != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("isMarketable"), isMarketable)); } if (isList != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("isList"), isList)); } if (isTop != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("isTop"), isTop)); } if (isGift != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("isGift"), isGift)); } Path<Integer> stock = root.get("stock"); Path<Integer> allocatedStock = root.get("allocatedStock"); if (isOutOfStock != null) { if (isOutOfStock) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.isNotNull(stock), criteriaBuilder.lessThanOrEqualTo(stock, allocatedStock)); } else { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.or(criteriaBuilder.isNull(stock), criteriaBuilder.greaterThan(stock, allocatedStock))); } } if (isStockAlert != null) { Setting setting = SettingUtils.get(); if (isStockAlert) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.isNotNull(stock), criteriaBuilder.lessThanOrEqualTo(stock, criteriaBuilder.sum(allocatedStock, setting.getStockAlertCount()))); } else { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.or(criteriaBuilder.isNull(stock), criteriaBuilder.greaterThan(stock, criteriaBuilder.sum(allocatedStock, setting.getStockAlertCount())))); } } criteriaQuery.where(restrictions); return super.count(criteriaQuery, null); }
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 ww . j a v a 2 s. c om*/ 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:org.openregistry.core.repository.jpa.JpaPersonRepository.java
public List<Person> searchByCriteria(final SearchCriteria searchCriteria) throws RepositoryAccessException { final String givenName = searchCriteria.getGivenName(); final String familyName = searchCriteria.getFamilyName(); final Date birthDate = searchCriteria.getDateOfBirth(); final String searchCriteriaName = searchCriteria.getName(); // search by role criteria final String sponsorNetID = searchCriteria.getSponsorNetID(); final OrganizationalUnit organizationalUnit = searchCriteria.getOrganizationalUnit(); final Date roleExpDate = searchCriteria.getRoleExpDate(); final Type roleType = searchCriteria.getAffiliationType(); final CriteriaBuilder criteriaBuilder = this.entityManager.getCriteriaBuilder(); final CriteriaQuery<JpaPersonImpl> c = criteriaBuilder.createQuery(JpaPersonImpl.class); c.distinct(true);//from ww w . j a va 2 s . com final Root<JpaPersonImpl> person = c.from(JpaPersonImpl.class); final Join<JpaPersonImpl, JpaNameImpl> name = person.join(JpaPersonImpl_.names); final Join<JpaPersonImpl, JpaRoleImpl> role = person.join(JpaPersonImpl_.roles); // person.fetch(JpaPersonImpl_.names); // person.fetch(JpaPersonImpl_.roles); // person.fetch(JpaPersonImpl_.identifiers); final Predicate pBirthDate; if (birthDate != null) { pBirthDate = criteriaBuilder.equal(person.get(JpaPersonImpl_.dateOfBirth), birthDate); } else { pBirthDate = null; } Predicate combined = null; if (StringUtils.hasText(givenName) && StringUtils.hasText(familyName)) { // final Predicate pGivenName = criteriaBuilder.equal(name.get(JpaNameImpl_.given), givenName ); Expression<String> pattern = criteriaBuilder.literal((String) givenName + "%"); final Predicate pGivenName = criteriaBuilder.like(name.get(JpaNameImpl_.given), pattern); final Predicate pFamilyName = criteriaBuilder.equal(name.get(JpaNameImpl_.family), familyName); combined = criteriaBuilder.and(pGivenName, pFamilyName); } else if (StringUtils.hasText(givenName)) { combined = criteriaBuilder.equal(name.get(JpaNameImpl_.given), givenName); } else if (StringUtils.hasText(familyName)) { combined = criteriaBuilder.equal(name.get(JpaNameImpl_.family), familyName); } else if (StringUtils.hasText(searchCriteriaName)) { final Predicate pGivenName = criteriaBuilder.equal(name.get(JpaNameImpl_.given), searchCriteriaName); final Predicate pFamilyName = criteriaBuilder.equal(name.get(JpaNameImpl_.family), searchCriteriaName); combined = criteriaBuilder.or(pGivenName, pFamilyName); } Predicate pRoleCombined = null; if (roleType != null) { //final Join<JpaPersonImpl,JpaRoleImpl> role = person.join(JpaPersonImpl_.roles); //final Join<JpaPersonImpl,JpaRoleImpl> role = name.join(JpaPersonImpl_.roles); pRoleCombined = criteriaBuilder.equal(role.get(JpaRoleImpl_.affiliationType), roleType); } if (organizationalUnit != null && StringUtils.hasText(organizationalUnit.getName())) { Predicate orgUnitP = criteriaBuilder.equal(role.get(JpaRoleImpl_.organizationalUnit), organizationalUnit); if (pRoleCombined != null) pRoleCombined = criteriaBuilder.and(pRoleCombined, orgUnitP); else pRoleCombined = orgUnitP; //pRoleCombined = criteriaBuilder.and(pRoleCombined, orgUnitP); } if (StringUtils.hasText(sponsorNetID)) { Person person1 = findByIdentifier(Type.IdentifierTypes.NETID.name(), sponsorNetID); if (person1 != null) { Predicate sponsorP = criteriaBuilder.equal(role.get(JpaRoleImpl_.sponsorId), person1.getId()); if (pRoleCombined != null) pRoleCombined = criteriaBuilder.and(pRoleCombined, sponsorP); else pRoleCombined = sponsorP; } else { pRoleCombined = criteriaBuilder.or(); } } if (roleExpDate != null) { Predicate expDateP = criteriaBuilder.between(role.get(JpaRoleImpl_.end), new Date(), roleExpDate); if (pRoleCombined != null) pRoleCombined = criteriaBuilder.and(pRoleCombined, expDateP); else pRoleCombined = expDateP; } Predicate pComplete = criteriaBuilder.and(); if (pBirthDate != null) pComplete = criteriaBuilder.and(pComplete, pBirthDate); if (combined != null) pComplete = criteriaBuilder.and(pComplete, combined); if (pRoleCombined != null) pComplete = criteriaBuilder.and(pComplete, pRoleCombined); c.select(person).where(pComplete); // if (pBirthDate != null && combined != null) { // c.select(person).where(criteriaBuilder.and(pBirthDate, combined)); // } else if (pBirthDate != null) { // c.select(person).where(pBirthDate); // } else { // c.select(person).where(combined); // } final List<JpaPersonImpl> persons = this.entityManager.createQuery(c).setMaxResults(MAX_QUERY_LIMIT) .getResultList(); return new ArrayList<Person>(persons); }