List of usage examples for javax.persistence.criteria CriteriaQuery orderBy
CriteriaQuery<T> orderBy(List<Order> o);
From source file:com.qpark.eip.core.model.analysis.AnalysisDao.java
private List<String> getFlowProcessTypeIds(final String modelVersion, final String flowId) { final CriteriaBuilder cb = this.em.getCriteriaBuilder(); final CriteriaQuery<String> q = cb.createQuery(String.class); final Root<FlowProcessType> f = q.from(FlowProcessType.class); q.select(f.<String>get(FlowProcessType_.id)); q.where(cb.equal(f.<String>get(FlowProcessType_.modelVersion), modelVersion), cb.equal(f.<String>get(FlowProcessType_.parentId), flowId)); q.orderBy(cb.asc(f.<Long>get(FlowProcessType_.hjid))); final TypedQuery<String> typedQuery = this.em.createQuery(q); final List<String> value = typedQuery.getResultList(); return value; }
From source file:net.sf.gazpachoquest.qbe.ByExampleSpecification.java
public <T extends Persistable> Specification<T> byExampleOnEntity(final T example, final SearchParameters sp) { Validate.notNull(example, "example must not be null"); return new Specification<T>() { @Override/*w w w . ja v a 2 s.c o m*/ public Predicate toPredicate(final Root<T> rootPath, final CriteriaQuery<?> query, final CriteriaBuilder builder) { Class<T> type = rootPath.getModel().getBindableJavaType(); ManagedType<T> mt = em.getMetamodel().entity(type); List<Predicate> predicates = new ArrayList<Predicate>(); predicates.addAll(byExample(mt, rootPath, example, sp, builder)); predicates.addAll(byExampleOnXToOne(mt, rootPath, example, sp, builder)); // 1 level deep only predicates.addAll(byExampleOnManyToMany(mt, rootPath, example, sp, builder)); // order by query.orderBy(JpaUtil.buildJpaOrders(sp.getOrders(), rootPath, builder)); return JpaUtil.andPredicate(builder, predicates); } //https://github.com/jaxio/generated-projects/tree/master/jsf2-spring-conversation/src/main/generated-java/com/jaxio/appli/repository/support public <T extends Persistable> List<Predicate> byExample(final ManagedType<T> mt, final Path<T> mtPath, final T mtValue, final SearchParameters sp, final CriteriaBuilder builder) { List<Predicate> predicates = new ArrayList<Predicate>(); for (SingularAttribute<? super T, ?> attr : mt.getSingularAttributes()) { if (attr.getPersistentAttributeType() == MANY_TO_ONE || attr.getPersistentAttributeType() == ONE_TO_ONE || attr.getPersistentAttributeType() == EMBEDDED || attr.getJavaType().isAssignableFrom(Map.class)) { continue; } Object attrValue = getValue(mtValue, attr); if (attrValue != null) { if (attr.getJavaType() == String.class) { if (isNotEmpty((String) attrValue)) { SingularAttribute<? super T, String> stringAttribute = mt .getSingularAttribute(attr.getName(), String.class); predicates.add(JpaUtil.stringPredicate(mtPath.get(stringAttribute), attrValue, sp, builder)); } } else { SingularAttribute<? super T, ?> attribute = mt.getSingularAttribute(attr.getName(), attr.getJavaType()); // apply equal predicates.add(builder.equal(mtPath.get(attribute), attrValue)); } } } return predicates; } /** * Construct a join predicate on collection (eg many to many, List) */ public <T extends Persistable> List<Predicate> byExampleOnManyToMany(final ManagedType<T> mt, final Root<T> mtPath, final T mtValue, final SearchParameters sp, final CriteriaBuilder builder) { List<Predicate> predicates = new ArrayList<Predicate>(); for (PluralAttribute<T, ?, ?> pa : mt.getDeclaredPluralAttributes()) { if (pa.getCollectionType() == PluralAttribute.CollectionType.LIST) { List<?> value = (List<?>) getValue(mtValue, mt.getAttribute(pa.getName())); if (value != null && !value.isEmpty()) { ListJoin<T, ?> join = mtPath.join(mt.getDeclaredList(pa.getName())); predicates.add(join.in(value)); } } if (pa.getCollectionType() == PluralAttribute.CollectionType.SET) { Set<?> value = (Set<?>) getValue(mtValue, mt.getAttribute(pa.getName())); if (value != null && !value.isEmpty()) { SetJoin<T, ?> join = mtPath.join(mt.getDeclaredSet(pa.getName())); predicates.add(join.in(value)); } } } return predicates; } /** * Invoke byExample method for each not null x-to-one association * when their pk is not set. This allows you * to search entities based on an associated entity's properties * value. */ @SuppressWarnings("unchecked") public <T extends Persistable, M2O extends Persistable> List<Predicate> byExampleOnXToOne( final ManagedType<T> mt, final Root<T> mtPath, final T mtValue, final SearchParameters sp, final CriteriaBuilder builder) { List<Predicate> predicates = new ArrayList<Predicate>(); for (SingularAttribute<? super T, ?> attr : mt.getSingularAttributes()) { if (attr.getPersistentAttributeType() == MANY_TO_ONE || attr.getPersistentAttributeType() == ONE_TO_ONE) { // M2O m2oValue = (M2O) getValue(mtValue, mt.getAttribute(attr.getName())); if (m2oValue != null) { Class<M2O> m2oType = (Class<M2O>) attr.getBindableJavaType(); ManagedType<M2O> m2oMt = em.getMetamodel().entity(m2oType); Path<M2O> m2oPath = (Path<M2O>) mtPath.get(attr); predicates.addAll(byExample(m2oMt, m2oPath, m2oValue, sp, builder)); } } } return predicates; } private <T> Object getValue(final T example, final Attribute<? super T, ?> attr) { Object value = null; try { if (attr.getJavaMember() instanceof Method) { value = ((Method) attr.getJavaMember()).invoke(example, new Object[0]); } else if (attr.getJavaMember() instanceof Field) { value = ReflectionUtils.getField((Field) attr.getJavaMember(), example); } if (value instanceof ValueHolderInterface) { value = ((ValueHolderInterface) value).getValue(); } } catch (IllegalAccessException e) { throw new RuntimeException(e); } catch (InvocationTargetException e) { throw new RuntimeException(e); } return value; } }; }
From source file:eu.uqasar.service.dataadapter.CubesDataService.java
/** * @param project/*w ww. j a v a 2 s .co m*/ * @param metric * @return */ public CubesMetricMeasurement getLatestMeasurementByProjectAndMetric(String project, String metric) { logger.info("Obtaining measurements for the project: " + project + " and 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_.cubeName), project); Predicate condition2 = cb.equal(root.get(CubesMetricMeasurement_.cubesMetric), metric); Predicate condition3 = cb.and(condition, condition2); query.where(condition3); query.orderBy(cb.desc(root.get(CubesMetricMeasurement_.timeStamp))); List<CubesMetricMeasurement> measurements = em.createQuery(query).getResultList(); CubesMetricMeasurement measurement = null; if (measurements != null && measurements.size() > 0 && measurements.get(0) != null) { measurement = measurements.get(0); // Get the most "fresh" result } return measurement; }
From source file:org.jdal.dao.jpa.JpaDao.java
/** * Create a TypedQuery from a request page * @param page request page/*w w w .j a v a 2 s . c o m*/ * @return new TypedQuery */ @SuppressWarnings("unchecked") private <K> TypedQuery<K> getCriteriaQuery(Page<K> page) { CriteriaQuery<K> criteria = getCriteria(page); CriteriaQuery<Long> countCriteria = (CriteriaQuery<Long>) getCriteria(page); CriteriaBuilder cb = em.getCriteriaBuilder(); Root<?> root = countCriteria.getRoots().iterator().next(); countCriteria.select(cb.count(root)); page.setCount((em.createQuery(countCriteria).getSingleResult()).intValue()); criteria.orderBy(getOrder(page, criteria)); // Add default select to entity class if none was set. if (criteria.getSelection() == null) { criteria.select((Selection<? extends K>) root); } return em.createQuery(criteria); }
From source file:edu.umm.radonc.ca_dash.model.ActivityFacade.java
public List<ActivityAIPC> itemsDateRange(Date start, Date end, int[] range) { CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery cq = cb.createQuery(ActivityAIPC.class); Root<ActivityAIPC> rt = cq.from(ActivityAIPC.class); cq.where(cb.and(rt.get(ActivityAIPC_.fromdateofservice).isNotNull(), cb.and(cb.notEqual(rt.get(ActivityAIPC_.procedurecodeser), 528), cb.notEqual(rt.get(ActivityAIPC_.procedurecodeser), 529), cb.notEqual(rt.get(ActivityAIPC_.procedurecodeser), 530), cb.between(rt.get(ActivityAIPC_.fromdateofservice), start, end)))); cq.orderBy(cb.asc(rt.get(ActivityAIPC_.fromdateofservice))); Query q = em.createQuery(cq); q.setMaxResults(range[1] - range[0] + 1); q.setFirstResult(range[0]);// w ww .j a v a 2 s. com return q.getResultList(); }
From source file:org.pushio.webapp.support.persistence.DynamicSpecifications.java
/** * @see ??JPA//from w w w. j a v a 2s. c om * @param filters * @param entityClazz * @param isDistinct trueSQLdistinctfalse? * @return */ public static <T> Specification<T> bySearchFilter(final Collection<SearchFilter> filters, final Class<T> entityClazz, final boolean isDistinct, final List<OrderParam> orderParams) { return new Specification<T>() { @Override public Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder builder) { if (orderParams != null && orderParams.size() > 0) { /* CriteriaQuery<Foo> criteriaQuery = criteriaBuilder.createQuery(Foo.class); Root<Foo> from = criteriaQuery.from(Foo.class); CriteriaQuery<Foo> select = criteriaQuery.select(from); criteriaQuery.orderBy(criteriaBuilder.asc(from.get("name"))); */ List<Order> orders = new ArrayList<Order>(orderParams.size()); for (OrderParam orderParam : orderParams) { if (orderParam != null && orderParam.getField() != null) { String fields[] = StringUtil.split(orderParam.getField(), '.'); Path expression = (fields.length > 1) ? root.join(fields[0]) : root.get(fields[0]); for (int i = 1, len = fields.length; i < len; ++i) { expression = expression.get(fields[i]); } if (expression != null) { Order order = (orderParam.getType() == null || orderParam.getType().equalsIgnoreCase("asc")) ? builder.asc(expression) : builder.desc(expression); orders.add(order); // query.orderBy(order); } } } query.orderBy(orders); } query.distinct(isDistinct); if (Collections3.isNotEmpty(filters)) { List<Predicate> predicates = Lists.newArrayList(); for (SearchFilter filter : filters) { // nested path translate, Task??"user.name"filedName, ?Task.user.name String[] names = StringUtils.split(filter.fieldName, '.'); Path expression = null; ////// ? boolean hasJoin = names[0].startsWith("[join]"); String fn = hasJoin ? names[0].substring(6) : names[0]; boolean isNotDateType = !(filter.value instanceof Date); try { expression = hasJoin ? root.join(fn) : root.get(fn); if (isNotDateType && isDateType(expression.getJavaType())) { // filter.value?? filter.value = parseDate(filter.value.toString(), filter.operator); } } catch (Exception e) { // logger.error(e.getMessage(), e); continue; // ?? } boolean isPropertyNotValid = false; for (int i = 1; i < names.length; i++) { try { expression = expression.get(names[i]); if (isNotDateType && isDateType(expression.getJavaType())) { filter.value = parseDate(filter.value.toString(), filter.operator); } } catch (Exception e) { // logger.error(e.getMessage(), e); isPropertyNotValid = true; // break; // ?? } } if (expression == null || isPropertyNotValid) { continue; } /////// // logic operator switch (filter.operator) { case EQ: predicates.add(builder.equal(expression, filter.value)); break; case LIKE: predicates.add(builder.like(expression, "%" + filter.value + "%")); break; case GT: predicates.add(builder.greaterThan(expression, (Comparable) filter.value)); break; case LT: predicates.add(builder.lessThan(expression, (Comparable) filter.value)); break; case GTE: predicates.add(builder.greaterThanOrEqualTo(expression, (Comparable) filter.value)); break; case LTE: predicates.add(builder.lessThanOrEqualTo(expression, (Comparable) filter.value)); break; case ORLIKE: if (filter.value instanceof String) { String value = (String) filter.value; String[] values = value.split(","); Predicate[] like = new Predicate[values.length]; for (int i = 0; i < values.length; i++) { like[i] = builder.like(expression, "%" + values[i] + "%"); } predicates.add(builder.or(like)); } break; case ANDLIKE: if (filter.value instanceof String) { String value = (String) filter.value; String[] values = value.split(","); Predicate[] like = new Predicate[values.length]; for (int i = 0; i < values.length; i++) { like[i] = builder.like(expression, "%" + values[i] + "%"); } predicates.add(builder.and(like)); } break; case ANDNOTLIKE: if (filter.value instanceof String) { String value = (String) filter.value; String[] values = value.split(","); Predicate[] like = new Predicate[values.length]; for (int i = 0; i < values.length; i++) { like[i] = builder.notLike(expression, "%" + values[i] + "%"); } predicates.add(builder.and(like)); } break; case OREQ: if (filter.value instanceof String) { String value = (String) filter.value; String[] values = value.split(","); Predicate[] like = new Predicate[values.length]; for (int i = 0; i < values.length; i++) { like[i] = builder.equal(expression, values[i]); } predicates.add(builder.or(like)); } break; case ANDNOTEQ: if (filter.value instanceof String) { String value = (String) filter.value; String[] values = value.split(","); Predicate[] like = new Predicate[values.length]; for (int i = 0; i < values.length; i++) { like[i] = builder.notEqual(expression, values[i]); } predicates.add(builder.and(like)); } break; case NOTEQ: predicates.add(builder.notEqual(expression, (Comparable) filter.value)); break; case NOTLIKE: predicates.add(builder.notLike(expression, "%" + filter.value + "%")); break; case NULL: predicates.add(builder.isNull(expression)); break; case NOTNULL: predicates.add(builder.isNotNull(expression)); break; // case IN: // predicates.add(builder.in(expression).in(values)); // break; } } // ? and ??? if (!predicates.isEmpty()) { return builder.and(predicates.toArray(new Predicate[predicates.size()])); } } return builder.conjunction(); } }; }
From source file:org.querybyexample.jpa.GenericRepository.java
/** * Find and load a list of E instance.//from w ww . j a v a 2s . co m * * @param entity a sample entity whose non-null properties may be used as * search hints * @param searchParameters carries additional search information * @return the entities matching the search. */ @SuppressWarnings("unchecked") @Transactional(readOnly = true) public List<E> find(E entity, SearchParameters sp) { if (sp.hasNamedQuery()) { return byNamedQueryUtil.findByNamedQuery(sp); } CriteriaBuilder builder = entityManager.getCriteriaBuilder(); CriteriaQuery<E> criteriaQuery = builder.createQuery(type); if (sp.getDistinct()) { criteriaQuery.distinct(true); } Root<E> root = criteriaQuery.from(type); // predicate Predicate predicate = getPredicate(root, builder, entity, sp); if (predicate != null) { criteriaQuery = criteriaQuery.where(predicate); } // left join for (SingularAttribute<?, ?> arg : sp.getLeftJoins()) { root.fetch((SingularAttribute<E, ?>) arg, JoinType.LEFT); } // order by criteriaQuery.orderBy(orderByUtil.buildJpaOrders(sp.getOrders(), root, builder, sp)); TypedQuery<E> typedQuery = entityManager.createQuery(criteriaQuery); applyCacheHints(typedQuery, sp); applyPagination(typedQuery, sp); List<E> entities = typedQuery.getResultList(); log.debug("Returned {} elements", entities.size()); return entities; }
From source file:eu.uqasar.service.dataadapter.JiraDataService.java
/** * //from w ww. j av a 2 s. c o m * @param metric * @return * @throws uQasarException */ public int countMeasurementsPerProjectByMetricWithinPeriod(Project project, String metric, String period) throws uQasarException { logger.info("Count measurements for metric: " + metric); 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); Date from = getDateForPeriod(period); Date to = DateTime.now().toDate(); Predicate condition3 = cb.between(root.get(JiraMetricMeasurement_.timeStamp), from, to); Predicate condition4 = cb.and(condition1, condition2, condition3); query.where(condition4); 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.openmeetings.app.data.user.Organisationmanagement.java
/** * // www . j a va2 s. c om * @param user_level * @return */ public List<Organisation> getOrganisations(int start, int max, String orderby, boolean asc) { try { CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<Organisation> cq = cb.createQuery(Organisation.class); Root<Organisation> c = cq.from(Organisation.class); Predicate condition = cb.equal(c.get("deleted"), "false"); cq.where(condition); cq.distinct(asc); if (asc) { cq.orderBy(cb.asc(c.get(orderby))); } else { cq.orderBy(cb.desc(c.get(orderby))); } TypedQuery<Organisation> q = em.createQuery(cq); q.setFirstResult(start); q.setMaxResults(max); List<Organisation> ll = q.getResultList(); return ll; } catch (Exception ex2) { log.error("[getOrganisations]", ex2); } return null; }
From source file:net.groupbuy.dao.impl.BaseDaoImpl.java
protected List<T> findList(CriteriaQuery<T> criteriaQuery, Integer first, Integer count, List<Filter> filters, List<Order> orders) { Assert.notNull(criteriaQuery);/*from ww w . j ava 2s.com*/ Assert.notNull(criteriaQuery.getSelection()); Assert.notEmpty(criteriaQuery.getRoots()); CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder(); Root<T> root = getRoot(criteriaQuery); addRestrictions(criteriaQuery, filters); addOrders(criteriaQuery, orders); if (criteriaQuery.getOrderList().isEmpty()) { if (OrderEntity.class.isAssignableFrom(entityClass)) { criteriaQuery.orderBy(criteriaBuilder.asc(root.get(OrderEntity.ORDER_PROPERTY_NAME))); } else { criteriaQuery.orderBy(criteriaBuilder.desc(root.get(OrderEntity.CREATE_DATE_PROPERTY_NAME))); } } TypedQuery<T> query = entityManager.createQuery(criteriaQuery).setFlushMode(FlushModeType.COMMIT); if (first != null) { query.setFirstResult(first); } if (count != null) { query.setMaxResults(count); } return query.getResultList(); }