List of usage examples for javax.persistence.criteria CriteriaBuilder lower
Expression<String> lower(Expression<String> x);
From source file:eu.uqasar.service.ProcessService.java
private List<Predicate> getFilterPredicates(final ProcessesFilterStructure filter, CriteriaBuilder cb, Root<Process> from) { List<Predicate> predicates = new ArrayList<>(); if (filter == null) { return predicates; }//from ww w. jav a 2s . c o m if (filter.getStartDate() != null) { predicates.add(cb.equal(from.get(Process_.startDate), filter.getStartDate())); } if (filter.getEndDate() != null) { predicates.add(cb.equal(from.get(Process_.endDate), filter.getEndDate())); } if (!StringUtils.isEmpty(filter.getName())) { Predicate firstName = cb.like(cb.lower(from.get(Process_.name)), LIKE_WILDCARD + filter.getName().toLowerCase() + LIKE_WILDCARD); predicates.add((firstName)); } return predicates; }
From source file:eu.uqasar.service.user.UserService.java
public User getByFullNameWithUsername(final String fullNameWithUsername) { logger.infof("loading User with fullName and username %s ...", fullNameWithUsername); User entity = null;/* w w w .ja va 2s . c om*/ CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<User> query = cb.createQuery(User.class); Root<User> root = query.from(User.class); Expression<String> fullName = cb.concat(cb.concat(root.get(User_.firstName), " "), root.get(User_.lastName)); Expression<String> userName = cb.concat(" (", cb.concat(root.get(User_.userName), ")")); query.where(cb.equal(cb.lower(cb.concat(fullName, userName)), fullNameWithUsername.toLowerCase())); List<User> resultList = em.createQuery(query).setMaxResults(1).getResultList(); if (!resultList.isEmpty()) { return resultList.get(0); } return entity; }
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() + "%")); }//from ww w. java2 s .c om } } 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); }
From source file:name.marcelomorales.siqisiqi.openjpa.impl.OrmFinderImpl.java
protected Predicate newFullTextPredicate(CriteriaBuilder cb, Root<T> p, String terms) { LinkedList<Predicate> predicatesOr = Lists.newLinkedList(); Iterable<Path<String>> fullTexts = settings.getFullTexts(p, persistentClass); if (fullTexts != null) { for (Path<String> x : fullTexts) { StringTokenizer tokenizer = new StringTokenizer(terms, " \t\n\r\f,.;:/"); LinkedList<Predicate> predicatesAnd = Lists.newLinkedList(); while (tokenizer.hasMoreTokens()) { String token = "%" + tokenizer.nextToken() + "%"; predicatesAnd.add(cb.like(cb.lower(x), StringUtils.lowerCase(token))); }/*from w ww .j av a 2s .c om*/ predicatesOr.add(cb.and(predicatesAnd.toArray(new Predicate[predicatesAnd.size()]))); } } final Predicate[] restrictions = predicatesOr.toArray(new Predicate[predicatesOr.size()]); return cb.or(restrictions); }
From source file:se.inera.intyg.intygstjanst.persistence.model.dao.impl.CertificateDaoImpl.java
@Override public List<Certificate> findCertificate(Personnummer civicRegistrationNumber, List<String> types, LocalDate fromDate, LocalDate toDate, List<String> careUnits) { CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder(); CriteriaQuery<Certificate> query = criteriaBuilder.createQuery(Certificate.class); Root<Certificate> root = query.from(Certificate.class); root.fetch("states", JoinType.LEFT); if (civicRegistrationNumber == null) { return Collections.emptyList(); }/*from w ww . j a v a 2s. c o m*/ List<Predicate> predicates = new ArrayList<>(); // meta data has to match civic registration number predicates.add(criteriaBuilder.equal(root.get("civicRegistrationNumber"), DaoUtil.formatPnrForPersistence(civicRegistrationNumber))); // filter by certificate types if (types != null && !types.isEmpty()) { predicates.add(criteriaBuilder.lower(root.<String>get("type")).in(toLowerCase(types))); } // filter by care unit if (careUnits != null && !careUnits.isEmpty()) { predicates.add(root.<String>get("careUnitId").in(careUnits)); } query.where(predicates.toArray(new Predicate[predicates.size()])); // order by signed date query.orderBy(criteriaBuilder.asc(root.get("signedDate"))); List<Certificate> tmpResult = entityManager.createQuery(query).getResultList(); List<Certificate> result = filterDuplicates(tmpResult); // expect a small number, so lets filter in memory return new DateFilter(result).filter(fromDate, toDate); }
From source file:org.businessmanager.dao.GenericDaoImpl.java
@SuppressWarnings("unchecked") private List<Predicate> createFilterList(Map<SingularAttribute<T, ?>, Object> filterAttributes, boolean enableLikeSearch, CriteriaBuilder queryBuilder, Root<T> rootQuery) { List<Predicate> predicateList = new ArrayList<Predicate>(); if (filterAttributes != null) { Iterator<SingularAttribute<T, ?>> iter = filterAttributes.keySet().iterator(); while (iter.hasNext()) { SingularAttribute<T, ?> key = iter.next(); Object value = filterAttributes.get(key); if (enableLikeSearch) { String searchKey = value.toString(); if (!value.toString().contains("%")) { searchKey = "%" + value.toString() + "%"; }// w w w.ja va2 s . co m Expression<String> lowerKey = queryBuilder.lower((Expression<String>) rootQuery.get(key)); Predicate predicate = queryBuilder.like(lowerKey, searchKey.toLowerCase()); predicateList.add(predicate); } else { Predicate predicate = queryBuilder.equal(rootQuery.get(key), value); predicateList.add(predicate); } } } return predicateList; }
From source file:eu.uqasar.service.user.UserService.java
private List<Predicate> getFilterPredicates(final UserFilterStructure filter, CriteriaBuilder cb, Root<User> from) {/*from ww w . jav a 2 s . co m*/ List<Predicate> predicates = new ArrayList<>(); if (filter == null) { return predicates; } if (filter.getRole() != null) { predicates.add(cb.equal(from.get(User_.userRole), filter.getRole())); } if (filter.getSource() != null) { predicates.add(cb.equal(from.get(User_.source), filter.getSource())); } if (filter.getStatus() != null) { predicates.add(cb.equal(from.get(User_.registrationStatus), filter.getStatus())); } if (!StringUtils.isEmpty(filter.getName())) { Predicate firstName = cb.like(cb.lower(from.get(User_.lastName)), LIKE_WILDCARD + filter.getName().toLowerCase() + LIKE_WILDCARD); Predicate lastName = cb.like(from.get(User_.firstName), LIKE_WILDCARD + filter.getName() + LIKE_WILDCARD); Predicate userName = cb.like(from.get(User_.userName), LIKE_WILDCARD + filter.getName() + LIKE_WILDCARD); Expression<String> fullName = cb.concat(cb.concat(from.get(User_.firstName), " "), from.get(User_.lastName)); Expression<String> un = cb.concat(" (", cb.concat(from.get(User_.userName), ")")); Predicate fn = cb.like(fullName, LIKE_WILDCARD + filter.getName() + LIKE_WILDCARD); Expression<String> fnu = cb.concat(fullName, un); Predicate ff1 = cb.like(fnu, LIKE_WILDCARD + filter.getName() + LIKE_WILDCARD); predicates.add(cb.or(firstName, lastName, userName, fn, ff1)); } return predicates; }
From source file:com.zero.dao.impl.BaseDaoImpl.java
private void addRestrictions(CriteriaQuery<T> criteriaQuery, List<Filter> filters) { if (criteriaQuery == null || filters == null || filters.isEmpty()) { return;//from w ww .j a v 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(); for (Filter filter : filters) { 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:org.apereo.lap.dao.RiskConfidenceRepositoryImpl.java
public List<RiskConfidence> findByUserCourseDate(final String user, final String course) { CriteriaBuilder builder = entityManager.getCriteriaBuilder(); CriteriaQuery<RiskConfidence> criteria = builder.createQuery(RiskConfidence.class); Root<RiskConfidence> root = criteria.from(RiskConfidence.class); EntityType<RiskConfidence> type = entityManager.getMetamodel().entity(RiskConfidence.class); criteria.orderBy(builder.desc(root.get("dateCreated"))); List<RiskConfidence> lastRiskConfidences = entityManager.createQuery(criteria).setFirstResult(0) .setMaxResults(1).getResultList(); if (lastRiskConfidences == null || lastRiskConfidences.isEmpty()) { logger.warn("No risk confidence records found"); return new ArrayList<RiskConfidence>(); }/*from w w w. jav a 2 s. c om*/ RiskConfidence lastRickConfidence = lastRiskConfidences.get(0); builder = entityManager.getCriteriaBuilder(); criteria = builder.createQuery(RiskConfidence.class); root = criteria.from(RiskConfidence.class); type = entityManager.getMetamodel().entity(RiskConfidence.class); Predicate groupPredicate = builder.equal(root.get("groupId"), lastRickConfidence.getGroupId()); if (!StringUtils.isEmpty(user) && !StringUtils.isEmpty(course)) { criteria.where(groupPredicate, builder.equal( builder.lower( root.get(type.getDeclaredSingularAttribute("alternativeId", String.class))), user.toLowerCase()), builder.equal( builder.lower(root.get(type.getDeclaredSingularAttribute("courseId", String.class))), course.toLowerCase())); } else if (!StringUtils.isEmpty(user)) { criteria.where(groupPredicate, builder.equal( builder.lower( root.get(type.getDeclaredSingularAttribute("alternativeId", String.class))), user.toLowerCase())); } else if (!StringUtils.isEmpty(course)) { criteria.where(groupPredicate, builder.equal( builder.lower(root.get(type.getDeclaredSingularAttribute("courseId", String.class))), course.toLowerCase())); } else { criteria.where(groupPredicate); } return entityManager.createQuery(criteria).getResultList(); }
From source file:com.zero.dao.impl.BaseDaoImpl.java
private void addRestrictions(CriteriaQuery<T> criteriaQuery, Pageable pageable) { if (criteriaQuery == null || pageable == null) { return;/*from w w w . j a v a 2 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.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); }