List of usage examples for javax.persistence.criteria CriteriaBuilder and
Predicate and(Expression<Boolean> x, Expression<Boolean> y);
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 w w . ja va 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.findList(criteriaQuery, null, count, filters, orders); }
From source file:com.zero.dao.impl.BaseDaoImpl.java
private void addRestrictions(CriteriaQuery<T> criteriaQuery, Pageable pageable) { if (criteriaQuery == null || pageable == null) { return;//w w w .j av a 2s .co 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.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:edu.umm.radonc.ca_dash.model.TxInstanceFacade.java
public int itemsDateRangeCount(Date startDate, Date endDate) { CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery cq = cb.createQuery(TxInstance.class); Root<TxInstance> rt = cq.from(TxInstance.class); cq.select(cb.count(rt.get(TxInstance_.activityinstanceser))); cq.where(cb.and(rt.get(TxInstance_.completed).isNotNull(), cb.between(rt.get(TxInstance_.completed), startDate, endDate))); Query q = em.createQuery(cq); return ((Long) (q.getSingleResult())).intValue(); }
From source file:eu.uqasar.service.dataadapter.JiraDataService.java
/** * /*from w w w .j a v a 2 s. c o m*/ * @param metric * @return * @throws uQasarException */ private List<JiraMetricMeasurement> getMeasurementsByMetricAndDate(String metric, Date date) throws uQasarException { logger.info("Get measurements for metric: " + metric); CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<JiraMetricMeasurement> query = cb.createQuery(JiraMetricMeasurement.class); Root<JiraMetricMeasurement> root = query.from(JiraMetricMeasurement.class); Predicate condition = cb.equal(root.get(JiraMetricMeasurement_.jiraMetric), metric); Predicate condition2 = cb.equal(root.get(JiraMetricMeasurement_.timeStamp), date); Predicate condition3 = cb.and(condition, condition2); query.where(condition3); query.orderBy(cb.desc(root.get(JiraMetricMeasurement_.timeStamp))); return em.createQuery(query).getResultList(); }
From source file:eu.uqasar.service.dataadapter.JiraDataService.java
/** * //w ww . java2s . c o m * @param metric * @param date * @return * @throws uQasarException */ public int countMeasurementsByMetricAndDate(String metric, Date date) throws uQasarException { logger.info("Count measurements for metric: " + metric + " and date: " + date); CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<JiraMetricMeasurement> query = cb.createQuery(JiraMetricMeasurement.class); Root<JiraMetricMeasurement> root = query.from(JiraMetricMeasurement.class); Predicate condition = cb.equal(root.get(JiraMetricMeasurement_.jiraMetric), metric); Predicate condition2 = cb.equal(root.get(JiraMetricMeasurement_.timeStamp), date); Predicate condition3 = cb.and(condition, condition2); query.where(condition3); query.orderBy(cb.desc(root.get(JiraMetricMeasurement_.timeStamp))); Integer res = em.createQuery(query).getResultList().size(); logger.info("Results' count: " + res); return res; }
From source file:org.openlmis.migration.tool.openlmis.referencedata.repository.custom.impl.OlmisFacilityTypeApprovedProductRepositoryImpl.java
@Override public Collection<FacilityTypeApprovedProduct> searchProducts(UUID facilityId, UUID programId, boolean fullSupply) { checkNotNull(facilityId);/*from w w w .j av a2 s .c o m*/ CriteriaBuilder builder = entityManager.getCriteriaBuilder(); CriteriaQuery<FacilityTypeApprovedProduct> query = builder.createQuery(FacilityTypeApprovedProduct.class); Root<FacilityTypeApprovedProduct> ftap = query.from(FacilityTypeApprovedProduct.class); Root<Facility> facility = query.from(Facility.class); Join<Facility, FacilityType> fft = facility.join("type"); Join<FacilityTypeApprovedProduct, FacilityType> ft = ftap.join("facilityType"); Join<FacilityTypeApprovedProduct, ProgramOrderable> pp = ftap.join("programOrderable"); Join<ProgramOrderable, Program> program = pp.join("program"); Predicate conjunction = builder.conjunction(); if (programId != null) { conjunction = builder.and(conjunction, builder.equal(program.get("id"), programId)); } conjunction = builder.and(conjunction, builder.equal(fft.get("id"), ft.get("id"))); conjunction = builder.and(conjunction, builder.equal(facility.get("id"), facilityId)); conjunction = builder.and(conjunction, builder.equal(pp.get("fullSupply"), fullSupply)); conjunction = builder.and(conjunction, builder.isTrue(pp.get("active"))); query.select(ftap); query.where(conjunction); Join<ProgramOrderable, OrderableDisplayCategory> category = pp.join("orderableDisplayCategory"); Join<ProgramOrderable, Orderable> orderable = pp.join("product"); query.orderBy(builder.asc(category.get("orderedDisplayValue").get("displayOrder")), builder.asc(category.get("orderedDisplayValue").get("displayName")), builder.asc(orderable.get("productCode"))); return entityManager.createQuery(query).getResultList(); }
From source file:eu.uqasar.service.dataadapter.JiraDataService.java
/** * @param metric/*from w w w. j av a 2s .c om*/ * @param project * @return * @throws uQasarException */ public List<JiraMetricMeasurement> getMeasurementsPerProjectByMetricWithLatestDate(Project project, String metric) throws uQasarException { logger.info("Count measurements for project + " + project.getAbbreviatedName() + " and metric: " + metric + "with latest date"); CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<JiraMetricMeasurement> query = cb.createQuery(JiraMetricMeasurement.class); Root<JiraMetricMeasurement> root = query.from(JiraMetricMeasurement.class); Predicate condition1 = cb.equal(root.get(JiraMetricMeasurement_.jiraMetric), metric); Predicate condition2 = cb.equal(root.get(JiraMetricMeasurement_.project), project); Predicate condition4 = cb.and(condition1, condition2); query.where(condition4); query.orderBy(cb.desc(root.get(JiraMetricMeasurement_.timeStamp))); List<JiraMetricMeasurement> returnResults = em.createQuery(query).getResultList(); Date newDate = null; for (JiraMetricMeasurement jMM : returnResults) { if (newDate == null || jMM.getTimeStamp().compareTo(newDate) > 0) { newDate = jMM.getTimeStamp(); } } return getMeasurementsByMetricAndDate(metric, newDate); }
From source file:de.whs.poodle.repositories.StatisticsRepository.java
public List<Statistic> getStatistics(FeedbackSearchCriteria s, int instructorId, int limit) { CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<Statistic> cq = cb.createQuery(Statistic.class); Root<Statistic> statistic = cq.from(Statistic.class); /* fetch the exercises immediately so they don't have to * be lazy-fetched one by one while rendering the table rows. */ statistic.fetch("exercise"); // join we need to match the course Join<Statistic, Course> course = statistic.join("courseTerm").join("course"); // create empty "and" predicate Predicate where = cb.conjunction(); // only non-empty Predicate notEmpty = cb.isFalse(statistic.get("empty")); where = cb.and(where, notEmpty); // only statistics for courses that the instructor has access to Predicate hasInstructorAccessToCourse = cb.isTrue(cb.function("has_instructor_access_to_course", Boolean.class, course.get("id"), cb.literal(instructorId))); where = cb.and(where, hasInstructorAccessToCourse); // filter by courseId if (s.getCourseId() != 0) { Predicate courseMatches = cb.equal(course.get("id"), s.getCourseId()); where = cb.and(where, courseMatches); }//from w w w . ja va2s.c om // filter by student if (s.getStudent() != null) { Predicate studentMatches = cb.equal(statistic.get("student").get("id"), s.getStudent().getId()); where = cb.and(where, studentMatches); } cq.where(where); // order by date cq.orderBy(cb.desc(statistic.get("completedAt"))); return em.createQuery(cq).setMaxResults(limit).getResultList(); }
From source file:eu.uqasar.service.dataadapter.CubesDataService.java
/** * // ww w. j a v a 2s .c om * @param metric * @param date * @return * @throws uQasarException */ public int countMeasurementsByMetricAndDate(String metric, Date date) throws uQasarException { logger.info("Count measurements for metric: " + metric + "and date: " + date); CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<CubesMetricMeasurement> query = cb.createQuery(CubesMetricMeasurement.class); Root<CubesMetricMeasurement> root = query.from(CubesMetricMeasurement.class); Predicate condition = cb.equal(root.get(CubesMetricMeasurement_.cubesMetric), metric); Predicate condition2 = cb.equal(root.get(CubesMetricMeasurement_.timeStamp), date); Predicate condition3 = cb.and(condition, condition2); query.where(condition3); query.orderBy(cb.desc(root.get(CubesMetricMeasurement_.timeStamp))); return em.createQuery(query).getResultList().size(); }
From source file:eu.uqasar.service.dataadapter.CubesDataService.java
/** * // w ww.j a v a2s . c o m * @param metric * @return * @throws uQasarException */ private List<CubesMetricMeasurement> getMeasurementsByMetricAndDate(String metric, Date date) throws uQasarException { logger.info("Get measurements for metric: " + metric); CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<CubesMetricMeasurement> query = cb.createQuery(CubesMetricMeasurement.class); Root<CubesMetricMeasurement> root = query.from(CubesMetricMeasurement.class); Predicate condition = cb.equal(root.get(CubesMetricMeasurement_.cubesMetric), metric); Predicate condition2 = cb.equal(root.get(CubesMetricMeasurement_.timeStamp), date); Predicate condition3 = cb.and(condition, condition2); query.where(condition3); query.orderBy(cb.desc(root.get(CubesMetricMeasurement_.timeStamp))); return em.createQuery(query).getResultList(); }