List of usage examples for javax.persistence.criteria CriteriaBuilder and
Predicate and(Expression<Boolean> x, Expression<Boolean> y);
From source file:net.groupbuy.dao.impl.ProductDaoImpl.java
public List<Object[]> findSalesList(Date beginDate, Date endDate, Integer count) { CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder(); CriteriaQuery<Object[]> criteriaQuery = criteriaBuilder.createQuery(Object[].class); Root<Product> product = criteriaQuery.from(Product.class); Join<Product, OrderItem> orderItems = product.join("orderItems"); Join<Product, net.groupbuy.entity.Order> order = orderItems.join("order"); criteriaQuery.multiselect(product.get("id"), product.get("sn"), product.get("name"), product.get("fullName"), product.get("price"), criteriaBuilder.sum(orderItems.<Integer>get("quantity")), criteriaBuilder.sum(criteriaBuilder .prod(orderItems.<Integer>get("quantity"), orderItems.<BigDecimal>get("price")))); Predicate restrictions = criteriaBuilder.conjunction(); if (beginDate != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.greaterThanOrEqualTo(order.<Date>get("createDate"), beginDate)); }// ww w.j a v a2 s .c o m if (endDate != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.lessThanOrEqualTo(order.<Date>get("createDate"), endDate)); } restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(order.get("orderStatus"), OrderStatus.completed), criteriaBuilder.equal(order.get("paymentStatus"), PaymentStatus.paid)); criteriaQuery.where(restrictions); criteriaQuery.groupBy(product.get("id"), product.get("sn"), product.get("name"), product.get("fullName"), product.get("price")); criteriaQuery.orderBy(criteriaBuilder.desc(criteriaBuilder.sum( criteriaBuilder.prod(orderItems.<Integer>get("quantity"), orderItems.<BigDecimal>get("price"))))); TypedQuery<Object[]> query = entityManager.createQuery(criteriaQuery).setFlushMode(FlushModeType.COMMIT); if (count != null && count >= 0) { query.setMaxResults(count); } return query.getResultList(); }
From source file:com.alliander.osgp.adapter.ws.infra.specifications.JpaDeviceSpecifications.java
@Override public Specification<Device> forFirmwareModuleVersion(final FirmwareModuleFilterType firmwareModuleFilterType, final String firmwareModuleVersion) throws ArgumentNullOrEmptyException { if (StringUtils.isEmpty(firmwareModuleVersion)) { throw new ArgumentNullOrEmptyException("firmwareModuleVersion"); }/*from w w w . j a va2s. com*/ if (firmwareModuleFilterType == null) { throw new ArgumentNullOrEmptyException("firmwareModuleType"); } return new Specification<Device>() { @Override public Predicate toPredicate(final Root<Device> deviceRoot, final CriteriaQuery<?> query, final CriteriaBuilder cb) { String moduleFieldName = ""; switch (firmwareModuleFilterType) { case COMMUNICATION: moduleFieldName = "moduleVersionComm"; break; case FUNCTIONAL: moduleFieldName = "moduleVersionFunc"; break; case SECURITY: moduleFieldName = "moduleVersionSec"; break; case M_BUS: moduleFieldName = "moduleVersionMbus"; break; case MODULE_ACTIVE: moduleFieldName = "moduleVersionMa"; break; case ACTIVE_FIRMWARE: break; default: break; } final Subquery<Long> subquery = query.subquery(Long.class); final Root<DeviceFirmware> deviceFirmwareRoot = subquery.from(DeviceFirmware.class); subquery.select(deviceFirmwareRoot.get("device").get("id").as(Long.class)); subquery.where(cb.and( cb.like(cb.upper(deviceFirmwareRoot.get("firmware").<String>get(moduleFieldName)), firmwareModuleVersion.toUpperCase()), cb.equal(deviceFirmwareRoot.<Boolean>get("active"), true))); return cb.in(deviceRoot.get("id").as(Long.class)).value(subquery); } }; }
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;//w w w . j av a 2s .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 w w.java 2 s . c o m 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:gov.guilin.dao.impl.ProductDaoImpl.java
public List<Object[]> findSalesList(Date beginDate, Date endDate, Integer count) { CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder(); CriteriaQuery<Object[]> criteriaQuery = criteriaBuilder.createQuery(Object[].class); Root<Product> product = criteriaQuery.from(Product.class); Join<Product, OrderItem> orderItems = product.join("orderItems"); Join<Product, gov.guilin.entity.Order> order = orderItems.join("order"); criteriaQuery.multiselect(product.get("id"), product.get("sn"), product.get("name"), product.get("fullName"), product.get("price"), criteriaBuilder.sum(orderItems.<Integer>get("quantity")), criteriaBuilder.sum(criteriaBuilder .prod(orderItems.<Integer>get("quantity"), orderItems.<BigDecimal>get("price")))); Predicate restrictions = criteriaBuilder.conjunction(); if (beginDate != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.greaterThanOrEqualTo(order.<Date>get("createDate"), beginDate)); }/*from ww w . j a va 2s .co m*/ if (endDate != null) { restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.lessThanOrEqualTo(order.<Date>get("createDate"), endDate)); } restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(order.get("orderStatus"), OrderStatus.completed), criteriaBuilder.equal(order.get("paymentStatus"), PaymentStatus.paid)); criteriaQuery.where(restrictions); criteriaQuery.groupBy(product.get("id"), product.get("sn"), product.get("name"), product.get("fullName"), product.get("price")); criteriaQuery.orderBy(criteriaBuilder.desc(criteriaBuilder.sum( criteriaBuilder.prod(orderItems.<Integer>get("quantity"), orderItems.<BigDecimal>get("price"))))); TypedQuery<Object[]> query = entityManager.createQuery(criteriaQuery).setFlushMode(FlushModeType.COMMIT); if (count != null && count >= 0) { query.setMaxResults(count); } return query.getResultList(); }
From source file:com.yunguchang.data.ApplicationRepository.java
private TSysOrgEntity findRightFleetOfCoordinator(String coordinatorId) { CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<TSysOrgEntity> cq = cb.createQuery(TSysOrgEntity.class); Root<TBusBusinessRelaEntity> mapRoot = cq.from(TBusBusinessRelaEntity.class); Root<TSysUserEntity> userRoot = cq.from(TSysUserEntity.class); cq.select(mapRoot.get(TBusBusinessRelaEntity_.fleet)); Path<TSysOrgEntity> coordinatorOrg = userRoot.get(TSysUserEntity_.department); cq.where(cb.or(cb.and(cb.like(coordinatorOrg.get(TSysOrgEntity_.orgid), "001%" ), cb.equal(mapRoot.get(TBusBusinessRelaEntity_.fleet), coordinatorOrg)), cb.and(cb.notLike(coordinatorOrg.get(TSysOrgEntity_.orgid), "001%"), cb.equal(mapRoot.get(TBusBusinessRelaEntity_.busOrg), coordinatorOrg)) ),//from ww w . j av a 2 s . c om cb.equal(userRoot.get(TSysUserEntity_.userid), coordinatorId) ); return Iterables.getFirst(em.createQuery(cq).getResultList(), null); }
From source file:com.yunguchang.data.ApplicationRepository.java
public List<TBusApplyinfoEntity> getApplicationForSync(DateTime startTime, DateTime endTime, String status, Boolean isSend, PrincipalExt principalExt) { if (startTime == null) { startTime = DateTimeUtil.appStartTime; }/*from ww w.ja v a 2 s. c om*/ if (endTime == null) { endTime = DateTimeUtil.appEndTime; } CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<TBusApplyinfoEntity> cq = cb.createQuery(TBusApplyinfoEntity.class); Root<TBusApplyinfoEntity> applyRoot = cq.from(TBusApplyinfoEntity.class); applyRoot.fetch(TBusApplyinfoEntity_.passenger); applyRoot.fetch(TBusApplyinfoEntity_.coordinator); applyRoot.fetch(TBusApplyinfoEntity_.department); applyRoot.fetch(TBusApplyinfoEntity_.senduser, JoinType.LEFT); Predicate predicate = cb.conjunction(); predicate = cb.and(predicate, cb.or(cb.isNull(applyRoot.get(TBusApplyinfoEntity_.updateBySync)), cb.isFalse(applyRoot.get(TBusApplyinfoEntity_.updateBySync)))); if (status != null) { predicate = cb.and(predicate, // "2" -> "3" -> ? "4" -> ? cb.equal(applyRoot.get(TBusApplyinfoEntity_.status), status)); } if (isSend != null && isSend) { predicate = cb.and(predicate, cb.equal(applyRoot.get(TBusApplyinfoEntity_.issend), "1") // ); } if ("4".equals(status)) { Fetch<TBusApplyinfoEntity, TBusScheduleRelaEntity> scheduleFetch = applyRoot .fetch(TBusApplyinfoEntity_.schedule, JoinType.LEFT); Join<TBusApplyinfoEntity, TBusScheduleRelaEntity> scheduleJoin = (Join<TBusApplyinfoEntity, TBusScheduleRelaEntity>) scheduleFetch; scheduleJoin.fetch(TBusScheduleRelaEntity_.reciveuser, JoinType.LEFT); Fetch<TBusScheduleRelaEntity, TBusScheduleCarEntity> fetchScheduleCar = scheduleFetch .fetch(TBusScheduleRelaEntity_.scheduleCars, JoinType.LEFT); fetchScheduleCar.fetch(TBusScheduleCarEntity_.car, JoinType.LEFT).fetch(TAzCarinfoEntity_.depot, JoinType.LEFT); fetchScheduleCar.fetch(TBusScheduleCarEntity_.driver, JoinType.LEFT) .fetch(TRsDriverinfoEntity_.department, JoinType.LEFT); predicate = cb.and(predicate, cb.or(cb.between(applyRoot.get(TBusApplyinfoEntity_.updatedate), startTime, endTime), cb.between(scheduleJoin.get(TBusScheduleRelaEntity_.updatedate), startTime, endTime))); } else { predicate = cb.and(predicate, cb.between(applyRoot.get(TBusApplyinfoEntity_.updatedate), startTime, endTime)); } cq.where(predicate); TypedQuery<TBusApplyinfoEntity> query = em.createQuery(cq); // applySecurityFilter("applications", principalExt); return query.getResultList(); }
From source file:com.yunguchang.data.ApplicationRepository.java
public List<TBusApplyinfoEntity> getApplicationByScheduleId(String scheduleId, PrincipalExt principalExt) { applySecurityFilter("applications", principalExt); CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<TBusApplyinfoEntity> cq = cb.createQuery(TBusApplyinfoEntity.class); Root<TBusApplyinfoEntity> applyRoot = cq.from(TBusApplyinfoEntity.class); Fetch<TBusApplyinfoEntity, TBusScheduleRelaEntity> scheduleFetch = applyRoot .fetch(TBusApplyinfoEntity_.schedule, JoinType.LEFT); Join<TBusApplyinfoEntity, TBusScheduleRelaEntity> scheduleJoin = (Join<TBusApplyinfoEntity, TBusScheduleRelaEntity>) scheduleFetch; Predicate predicate = cb.conjunction(); predicate = cb.and(predicate, cb.equal(scheduleJoin.get(TBusScheduleRelaEntity_.uuid), scheduleId)); cq.where(predicate);/* w ww . ja va 2 s. c om*/ return em.createQuery(cq).getResultList(); }
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 w w w .j a v a 2 s . c o m 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); }
From source file:com.yunguchang.data.ApplicationRepository.java
public List<TBusApplyinfoEntity> getApplicationWithDetailByScheduleId(String scheduleId, PrincipalExt principalExt) {/*from w ww .j a va2s . c o m*/ applySecurityFilter("applications", principalExt); CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<TBusApplyinfoEntity> cq = cb.createQuery(TBusApplyinfoEntity.class); Root<TBusApplyinfoEntity> applyRoot = cq.from(TBusApplyinfoEntity.class); Fetch<TBusApplyinfoEntity, TBusScheduleRelaEntity> scheduleFetch = applyRoot .fetch(TBusApplyinfoEntity_.schedule, JoinType.LEFT); applyRoot.fetch(TBusApplyinfoEntity_.fleet); applyRoot.fetch(TBusApplyinfoEntity_.passenger); applyRoot.fetch(TBusApplyinfoEntity_.coordinator); applyRoot.fetch(TBusApplyinfoEntity_.department); Join<TBusApplyinfoEntity, TBusScheduleRelaEntity> scheduleJoin = (Join<TBusApplyinfoEntity, TBusScheduleRelaEntity>) scheduleFetch; Predicate predicate = cb.conjunction(); predicate = cb.and(predicate, cb.equal(scheduleJoin.get(TBusScheduleRelaEntity_.uuid), scheduleId)); cq.where(predicate); return em.createQuery(cq).getResultList(); }