Java Code Examples for org.hibernate.Criteria#addOrder()

@SuppressWarnings("unchecked")
public List<Mpp> getMedecinePackagesFromIngredients(String searchString, String lang, List<String> types, int first, int count) {
	log.debug("Getting medecine packages from ingredients for " + searchString + " from " + first + ", count=" + count);
	Session sess = getSessionFactory().getCurrentSession();
	Criteria c = sess.createCriteria(Mpp.class);
	addLangRestriction(c, lang);
	addTypesRestriction(c, types);

	c.createAlias("compositions", "comp").createAlias("comp.ingredient", "ingrd");
	c.add(Restrictions.or(Restrictions.ilike("name", searchString, MatchMode.START),
			Restrictions.ilike("ingrd.name", searchString, MatchMode.START)));

	c.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
	c.setFirstResult(first);
	c.setMaxResults(count);
	c.addOrder(Order.asc("name"));
	List<Mpp> result = c.list();
	return result;
}
 

/**
 * 根据entityClass，生成带排序的Criteria.
 * 
 * @param <T>
 *            实体类型
 * @param entityClass
 *            类型
 * @param orderBy
 *            排序字段名
 * @param isAsc
 *            是否正序
 * @param criterions
 *            条件
 * @return Criteria
 */
public <T> Criteria createCriteria(Class<T> entityClass, String orderBy,
        boolean isAsc, Criterion... criterions) {
    if (StringUtils.hasText(orderBy)) {
        Criteria criteria = createCriteria(entityClass, criterions);

        if (isAsc) {
            criteria.addOrder(Order.asc(orderBy));
        } else {
            criteria.addOrder(Order.desc(orderBy));
        }

        return criteria;
    } else {
        return createCriteria(entityClass, criterions);
    }
}
 

@Override
protected Collection<ECRF> handleFindByTrialActiveSorted(Long trialId, Boolean active, boolean sort, PSFVO psf) throws Exception {
	org.hibernate.Criteria ecrfCriteria = createEcrfCriteria(null);
	SubCriteriaMap criteriaMap = new SubCriteriaMap(ECRF.class, ecrfCriteria);
	if (trialId != null) {
		ecrfCriteria.add(Restrictions.eq("trial.id", trialId.longValue()));
	}
	if (active != null) {
		ecrfCriteria.add(Restrictions.eq("active", active.booleanValue()));
	}
	CriteriaUtil.applyPSFVO(criteriaMap, psf);
	if (sort) {
		ecrfCriteria.addOrder(Order.asc("trial"));
		Criteria visitCriteria = ecrfCriteria.createCriteria("visit", CriteriaSpecification.LEFT_JOIN);
		visitCriteria.addOrder(Order.asc("token"));
		Criteria groupCriteria = ecrfCriteria.createCriteria("group", CriteriaSpecification.LEFT_JOIN);
		groupCriteria.addOrder(Order.asc("token"));
		ecrfCriteria.addOrder(Order.asc("position"));
	}
	return ecrfCriteria.list();
}
 

@Test
public void givenProductData_whenCountByCategoryWithAliasUsingCriteria_thenOK() {
    Criteria criteria = session.createCriteria(Product.class);
    criteria = criteria.setProjection(Projections.projectionList()
        .add(Projections.groupProperty("category"))
        .add(Projections.alias(Projections.rowCount(), "count")));
    criteria.addOrder(Order.asc("count"));
    List<Object[]> resultList = criteria.list();

    assertNotNull(resultList);
    assertEquals(3, resultList.size());
    assertEquals("category2", resultList.get(0)[0]);
    assertEquals(1L, resultList.get(0)[1]);
    assertEquals("category3", resultList.get(1)[0]);
    assertEquals(1L, resultList.get(1)[1]);
    assertEquals("category1", resultList.get(2)[0]);
    assertEquals(2L, resultList.get(2)[1]);
}
 

/**
 * 设置分页参数到Criteria对象,辅助函数.
 * 
 * @param c Hibernate Criteria
 * @param pageRequest 分页请求参数
 * 
 * @return {@link Criteria}
 */
protected Criteria setPageRequestToCriteria( Criteria c,  PageRequest pageRequest) {
	Assert.isTrue(pageRequest.getPageSize() > 0, "分页大小必须大于0");

	c.setFirstResult(pageRequest.getOffset());
	c.setMaxResults(pageRequest.getPageSize());

	if (pageRequest.isOrderBySetted()) {
		for (Sort sort : pageRequest.getSort()) {
			Order order = null;
			if (sort.getDir().equals(Sort.ASC)) {
				order = Order.asc(sort.getProperty());
			} else {
				order = Order.desc(sort.getProperty());
			}
			c.addOrder(order);
		}
	}
	
	return c;
}
 

/**
 * This implementation does not support the 'offset' parameter. The maxResultSize parameter is not used (limiting
 * the result needs to be done after security {@link org.geomajas.internal.layer.vector.GetFeaturesEachStep}). If
 * you expect large results to be returned enable scrollableResultSet to retrieve only as many records as needed.
 */
public Iterator<?> getElements(Filter filter, int offset, int maxResultSize) throws LayerException {
	try {
		Session session = getSessionFactory().getCurrentSession();
		Criteria criteria = session.createCriteria(getFeatureInfo().getDataSourceName());
		if (filter != null) {
			if (filter != Filter.INCLUDE) {
				CriteriaVisitor visitor = new CriteriaVisitor((HibernateFeatureModel) featureModel, dateFormat);
				Criterion c = (Criterion) filter.accept(visitor, criteria);
				if (c != null) {
					criteria.add(c);
				}
			}
		}

		// Sorting of elements.
		if (getFeatureInfo().getSortAttributeName() != null) {
			if (SortType.ASC.equals(getFeatureInfo().getSortType())) {
				criteria.addOrder(Order.asc(getFeatureInfo().getSortAttributeName()));
			} else {
				criteria.addOrder(Order.desc(getFeatureInfo().getSortAttributeName()));
			}
		}

		criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);

		if (isScrollableResultSet()) {
			return (Iterator<?>) new ScrollIterator(criteria.scroll());
		} else {
			List<?> list = criteria.list();
			return list.iterator();
		}
	} catch (HibernateException he) {
		throw new HibernateLayerException(he, ExceptionCode.HIBERNATE_LOAD_FILTER_FAIL, getFeatureInfo()
				.getDataSourceName(), filter.toString());
	}
}
 

@Test
public final void whenHQLCriteriaSortingByMultipAttr_thenSortedResults() {
    final Criteria criteria = session.createCriteria(Foo.class, "FOO");
    criteria.addOrder(Order.asc("name"));
    criteria.addOrder(Order.asc("id"));
    final List<Foo> fooList = criteria.list();
    for (final Foo foo : fooList) {
        System.out.println("Id: " + foo.getId() + ", FirstName: " + foo.getName());
    }
}
 

private void searchCollectionsAddOrder(String sortColumn, String sortDirection, Criteria criteria) {
	if (StringUtils.hasText(sortColumn)) {
		if ("owner".equals(sortColumn)){
			sortColumn = "owner.userName";
			criteria.createAlias("owner", "owner");
		}
		Order order;
		if ("ASC".equals(sortDirection)){
			order = Order.asc(sortColumn);
		} else {
			order = Order.desc(sortColumn);
		}
		criteria.addOrder(order);
	}
}
 

/**
 * Add order directly to criteria.
 */
private static void addOrder(Criteria c, String sort, String order, boolean ignoreCase) {
    if (HibernateQueryConstants.ORDER_DESC.equals(order)) {
        c.addOrder( ignoreCase ? Order.desc(sort).ignoreCase() : Order.desc(sort));
    }
    else {
        c.addOrder( ignoreCase ? Order.asc(sort).ignoreCase() : Order.asc(sort) );
    }
}
 

/**
 * 创建Criteria对象，有排序功能。
 *
 * @param <T>
 * @param entityClass
 * @param orderBy
 * @param isAsc
 * @param criterions
 * @return
 */
private <T> Criteria createCriteria(Class<T> entityClass, boolean isAsc,
		Criterion... criterions) {
	Criteria criteria = createCriteria(entityClass, criterions);
	if (isAsc) {
		criteria.addOrder(Order.asc("asc"));
	} else {
		criteria.addOrder(Order.desc("desc"));
	}
	return criteria;
}
 

/**
 * @see SakaiPersonManager#findSakaiPerson(String)
 */
public List findSakaiPerson(final String simpleSearchCriteria)
{
	if (log.isDebugEnabled())
	{
		log.debug("findSakaiPerson(String {})", simpleSearchCriteria);
	}
	if (simpleSearchCriteria == null || simpleSearchCriteria.length() < 1)
		throw new IllegalArgumentException("Illegal simpleSearchCriteria argument passed!");

	final String match = PERCENT_SIGN + simpleSearchCriteria + PERCENT_SIGN;
	final HibernateCallback hcb = new HibernateCallback()
	{
		public Object doInHibernate(Session session) throws HibernateException
		{
			final Criteria c = session.createCriteria(SakaiPersonImpl.class);
			c.add(Expression.disjunction().add(Expression.ilike(UID, match)).add(Expression.ilike(GIVENNAME, match)).add(
					Expression.ilike(SURNAME, match)));
			c.addOrder(Order.asc(SURNAME));
			// c.setCacheable(cacheFindSakaiPersonString);
			return c.list();
			
		}
	};

	log.debug("return getHibernateTemplate().executeFind(hcb);");
	List hb = (List) getHibernateTemplate().execute(hcb);
	if (photoService.overRidesDefault()) {
		return getDiskPhotosForList(hb);
	} else {
		return hb;
	}
}
 

public List<FacebookDataFeed> getAllDataFeedsByCode(String code, Integer exportLimit) {
	Criteria criteria = getCurrentSession().createCriteria(FacebookDataFeed.class);
       criteria.add(Restrictions.eq("code", code));
       criteria.setMaxResults(exportLimit);
       criteria.addOrder(Order.desc("feedCreatedAt"));
	return criteria.list();
}
 

@SuppressWarnings("unchecked")
@Override
public List<E> getByCriteriaWithAliasByOrder(Criterion criterion, String order, String[] orderBy, Integer count, String aliasTable, Criterion aliasCriterion) {
	Session session = getCurrentSession();
	List<E> fetchedList = new ArrayList<E>();
	//logger.info("Entity: " + entityClass + ", current Session = " + session);
	Criteria criteria = session.createCriteria(entityClass);
	criteria.add(criterion); 
	criteria.createAlias(aliasTable, aliasTable, org.hibernate.sql.JoinType.LEFT_OUTER_JOIN).add(aliasCriterion);
	if (orderBy != null) {
		for(int i = 0; i< orderBy.length; i++){
			if (order != null && order.equalsIgnoreCase("desc")) {
				criteria.addOrder(Order.desc(orderBy[i]));
			} else {
				criteria.addOrder(Order.asc(orderBy[i]));
			}
		}
	}
	if(count != null && count > 0){
		criteria.setMaxResults(count);
	}
	try {	
		fetchedList = criteria.list();
		return fetchedList;
	} catch (Exception e) {
		logger.error("getByCriteriaWithAliasByOrder failed, criteria = " + criterion.toString(), e);
		throw new HibernateException("getByCriteriaWithAliasByOrder failed, criteria = " + criterion.toString());
	}
}
 

/**
 * Add order directly to criteria.
 */
private static void addOrder(Criteria c, String sort, String order, boolean ignoreCase) {
    if (DynamicFinder.ORDER_DESC.equals(order)) {
        c.addOrder(ignoreCase ? Order.desc(sort).ignoreCase() : Order.desc(sort));
    } else {
        c.addOrder(ignoreCase ? Order.asc(sort).ignoreCase() : Order.asc(sort));
    }
}
 

@Test
public final void whenCriteriaSortingStringNullsFirstDesc_thenNullsFirst() {
    final Criteria criteria = session.createCriteria(Foo.class, "FOO");
    criteria.addOrder(Order.desc("name").nulls(NullPrecedence.FIRST));
    final List<Foo> fooList = criteria.list();
    assertNull(fooList.get(0).getName());
    for (final Foo foo : fooList) {
        System.out.println("Id: " + foo.getId() + ", FirstName: " + foo.getName());
    }
}
 

private void addOrderingCriteria(Criteria criteria, String sortingColumn, boolean sortingAscending) {
	if (!StringUtilities.isEmpty(sortingColumn)) {
		if (sortingAscending) {
			criteria.addOrder(Order.asc(sortingColumn));
		} else {
			criteria.addOrder(Order.desc(sortingColumn));
		}
	} else {
		// sorting descending by date as default
		criteria.addOrder(Order.desc("date"));
	}
}
 

public Pager<Book> showBookByClickTimes(int pageNo, int pageSize) {
	Session session = sessionFactory.getCurrentSession();
	Criteria criteria = session.createCriteria(Book.class);
	long recordTotal = ((Long) criteria.setProjection(Projections.rowCount()).uniqueResult()).longValue();
	criteria.setProjection(null);
	criteria.addOrder(Order.desc("clickTimes"));
	criteria.setFirstResult((pageNo - 1) * pageSize);
	criteria.setMaxResults(pageSize);
	List<Book> results = criteria.list();
	Pager<Book> page=new Pager<Book>(pageSize, pageNo, recordTotal, results);
	return page;
	
}
 

public List<Menu> findHierarchicalMenu() {
    Criteria criteria = currentSession().createCriteria(Menu.class);
    criteria.add(Restrictions.isNull("parentOid"));
    criteria.addOrder(Order.asc("index"));
    return list(criteria);
}
 

/**
 * Apply Order to Criteria
 * @param page the page
 * @param criteria the criteria
 */
protected void applyOrder(Page<?> page, Criteria criteria) {
	Order order = createOrder(criteria, page.getSortName(), Page.Order.ASC.equals(page.getOrder()));
	if (order != null) 
		criteria.addOrder(order);
}
 

@Override
public List<SbiDataSet> loadPaginatedSearchSbiDataSet(String search, Integer page, Integer item_per_page, IEngUserProfile finalUserProfile,
		Boolean seeTechnical, Integer[] ids, boolean spatialOnly) {
	Session session = null;
	List<SbiDataSet> list = null;

	try {
		session = getSession();
		Criteria c = session.createCriteria(SbiDataSet.class);
		c.addOrder(Order.asc("label"));

		if (page != null && item_per_page != null) {
			c.setFirstResult((page - 1) * item_per_page);
			c.setMaxResults(item_per_page);
		}

		c.add(Restrictions.like("label", search == null ? "" : search, MatchMode.ANYWHERE).ignoreCase());
		c.add(Restrictions.eq("active", true));

		if (ids != null && ids.length > 0) {
			c.add(Restrictions.in("id.dsId", ids));
		}

		if (spatialOnly) {
			c.add(Restrictions.like("dsMetadata", IFieldMetaData.FieldType.SPATIAL_ATTRIBUTE.toString(), MatchMode.ANYWHERE));
		}

		if (finalUserProfile != null) {

			logger.debug("For final user take only owned, enterprise and shared");

			SbiDomains scopeUserDomain = DAOFactory.getDomainDAO().loadSbiDomainByCodeAndValue("DS_SCOPE", SpagoBIConstants.DS_SCOPE_USER);
			SbiDomains scopeEnterpriseDomain = DAOFactory.getDomainDAO().loadSbiDomainByCodeAndValue("DS_SCOPE", SpagoBIConstants.DS_SCOPE_ENTERPRISE);
			SbiDomains scopeTechnicalDomain = DAOFactory.getDomainDAO().loadSbiDomainByCodeAndValue("DS_SCOPE", SpagoBIConstants.DS_SCOPE_TECHNICAL);

			Disjunction or = Restrictions.disjunction();

			// OWNER OR

			// take owned datasets
			or.add(Restrictions.eq("owner", ((UserProfile) finalUserProfile).getUserId().toString()));

			// get categories
			Set<Domain> categoryList = UserUtilities.getDataSetCategoriesByUser(finalUserProfile);

			if (categoryList != null) {
				if (categoryList.size() > 0) {
					SbiDomains[] categoryArray = new SbiDomains[categoryList.size()];
					int i = 0;
					for (Iterator iterator = categoryList.iterator(); iterator.hasNext();) {
						Domain domain = (Domain) iterator.next();
						String domainCd = domain.getDomainCode();
						String valueCd = domain.getValueCd();
						SbiDomains sbiDomain = DAOFactory.getDomainDAO().loadSbiDomainByCodeAndValue(domainCd, valueCd);
						categoryArray[i] = sbiDomain;
						i++;
					}
					// (IN CATEGORY AND (SCOPE=USER OR SCOPE=ENTERPRISE)) OR SCOPE=TECHNICAL
					Conjunction andCategories = Restrictions.conjunction();
					andCategories.add(Restrictions.in("category", categoryArray));

					Disjunction orScope = Restrictions.disjunction();
					orScope.add(Restrictions.eq("scope", scopeUserDomain));
					orScope.add(Restrictions.eq("scope", scopeEnterpriseDomain));

					andCategories.add(orScope);

					if (seeTechnical != null && seeTechnical) {
						Disjunction orTechnical = Restrictions.disjunction();
						orTechnical.add(andCategories);
						orTechnical.add(Restrictions.eq("scope", scopeTechnicalDomain));
						or.add(orTechnical);
					} else {
						or.add(andCategories);
					}

				}
			} else {
				// if no categoryList take also all USER and ENTERPRISE dataset
				// SCOPE=USER OR SCOPE=ENTERPRISE)
				or.add(Restrictions.eq("scope", scopeUserDomain));
				or.add(Restrictions.eq("scope", scopeEnterpriseDomain));
			}

			c.add(or);
		}

		list = c.list();
		initialize(list);

	} catch (Exception e) {
		throw new SpagoBIDAOException("An unexpected error occured while loading datasets", e);
	} finally {
		if (session != null && session.isOpen()) {
			session.close();
		}
		logger.debug("OUT");
	}
	return list;
}