Hibernate Criteria Restrictions Apply

/**  
 * Filename:    hibernate→com.happy.test.metadata→AllProperty.java 
 * Description:   
 * Copyright:   Copyright (c)2011 
 * Company:     Idcorner 
 * @author:     happy   qiaolevip@gmail.com
 * @version:    1.0  
 * Create at:   12:29:56 AM 
 *  
 * Modification History:  
 * Date         Author      Version     Description  
 * ------------------------------------------------------------------  
 * 12:29:56 AM   Joe        1.0        1.0 Version  
 */
package com.happy.test.criteria;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.TreeSet;

import org.hibernate.Criteria;
import org.hibernate.Hibernate;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;

import com.happy.dao.BaseHibernateDAO;
import com.happy.entity.AwardNums;
import com.happy.test.util.HandleList;

/**
 * @author happy
 * 
 */
public class Restriction extends BaseHibernateDAO {

	public static void main(String[] args) {
		Restriction query = new Restriction();
		query.query();
	}

	/**
	 * 获取查询别名
	 */
	@SuppressWarnings("unchecked")
	public void queryAlias() {
		Criteria criteria = getSession().createCriteria(AwardNums.class);
		// 获取别名：eg：this
		System.out.println(criteria.getAlias());
		HandleList.print(criteria.list());
	}

	/**
	 * 对查询排序
	 */
	@SuppressWarnings("unchecked")
	public void queryOrder() {
		Criteria criteria = getSession().createCriteria(AwardNums.class);
		criteria.addOrder(Order.desc("id"));
		HandleList.print(criteria.list());
	}

	/**
	 * query by Between
	 */
	@SuppressWarnings("unchecked")
	public void queryBetween() {
		Criteria criteria = getSession().createCriteria(AwardNums.class);
		// 第一个参数是属性，第一个是小的数值，第三个是大的数值
		criteria.add(Restrictions.between("id", 17720820, 17720830));
		HandleList.print(criteria.list());
	}

	/**
	 * query by idEq
	 */
	@SuppressWarnings("unchecked")
	public void queryIdEq() {
		Criteria criteria = getSession().createCriteria(AwardNums.class);
		// 第一个参数是属性，第一个是小的数值，第三个是大的数值
		criteria.add(Restrictions.idEq(17720820));
		HandleList.print(criteria.list());
	}

	/**
	 * 忽略大小写查询query by ilike
	 */
	@SuppressWarnings("unchecked")
	public void queryIlike() {
		Criteria criteria = getSession().createCriteria(AwardNums.class);
		// 忽略大小写，05结束的
		criteria.add(Restrictions.ilike("numbers", "05", MatchMode.END));
		HandleList.print(criteria.list());
	}

	/**
	 * 正常like查询 query by like sql:this_.numbers like ?
	 */
	@SuppressWarnings("unchecked")
	public void queryLike() {
		Criteria criteria = getSession().createCriteria(AwardNums.class);
		// 05介绍的
		criteria.add(Restrictions.like("numbers", "05", MatchMode.END));
		HandleList.print(criteria.list());
	}

	/**
	 * 正常like查询 query by like sql:this_.numbers like ?
	 */
	@SuppressWarnings("unchecked")
	public void queryLike2() {
		Criteria criteria = getSession().createCriteria(AwardNums.class);
		// 05开始的
		criteria.add(Restrictions.like("numbers", "05%"));
		HandleList.print(criteria.list());
	}

	@SuppressWarnings("unchecked")
	public void queryInArray() {
		Criteria criteria = getSession().createCriteria(AwardNums.class);
		// new int[]错误的
		Integer[] arrInt = new Integer[] { 17720808, 17720809, 17720810 };
		criteria.add(Restrictions.in("id", arrInt));
		HandleList.print(criteria.list());
	}

	@SuppressWarnings("unchecked")
	public void queryInList() {
		Criteria criteria = getSession().createCriteria(AwardNums.class);
		// new int[]错误的
		List<Integer> list = new ArrayList<Integer>();
		list.add(17720808);
		list.add(17720809);
		list.add(17720810);
		criteria.add(Restrictions.in("id", list));
		HandleList.print(criteria.list());
	}

	@SuppressWarnings("unchecked")
	public void queryInSet() {
		Criteria criteria = getSession().createCriteria(AwardNums.class);
		// new int[]错误的
		Set<Integer> set = new TreeSet<Integer>();
		set.add(17720808);
		set.add(17720809);
		set.add(17720810);
		criteria.add(Restrictions.in("id", set));
		HandleList.print(criteria.list());
	}

	@SuppressWarnings("unchecked")
	public void queryInMap() {
		Criteria criteria = getSession().createCriteria(AwardNums.class);
		Map<String, Integer> map = new HashMap<String, Integer>();
		// String不能命名重复，否者会过滤重复的
		map.put("1", 17720808);
		map.put("2", 17720809);
		map.put("3", 17720810);
		criteria.add(Restrictions.in("id", map.values()));
		HandleList.print(criteria.list());
	}

	@SuppressWarnings("unchecked")
	public void queryIsEmpty() {
		Criteria criteria = getSession().createCriteria(AwardNums.class);
		// Property path [com.happy.entity.AwardNums.issue] does not reference a
		// collection
		criteria.add(Restrictions.isEmpty("issue"));
		HandleList.print(criteria.list());
	}

	@SuppressWarnings("unchecked")
	public void queryIsNotEmpty() {
		Criteria criteria = getSession().createCriteria(AwardNums.class);
		// Property path [com.happy.entity.AwardNums.issue] does not reference a
		// collection
		criteria.add(Restrictions.isNotEmpty("issue"));
		HandleList.print(criteria.list());
	}

	/**
	 * this_.id is null
	 */
	@SuppressWarnings("unchecked")
	public void queryIsNull() {
		Criteria criteria = getSession().createCriteria(AwardNums.class);
		// this_.id is null
		criteria.add(Restrictions.isNull("id"));
		HandleList.print(criteria.list());
	}

	/**
	 * this_.id is not null
	 */
	@SuppressWarnings("unchecked")
	public void queryIsNotNull() {
		Criteria criteria = getSession().createCriteria(AwardNums.class);
		// this_.id is not null
		criteria.add(Restrictions.isNotNull("id"));
		HandleList.print(criteria.list());
	}

	@SuppressWarnings("unchecked")
	public void querySizeEq() {
		Criteria criteria = getSession().createCriteria(AwardNums.class);
		// Unknown collection role: com.happy.entity.AwardNums.id
		criteria.add(Restrictions.sizeEq("id", 15));
		HandleList.print(criteria.list());
	}

	/**
	 * this_.id < 17720812
	 */
	@SuppressWarnings("unchecked")
	public void querySqlRestriction() {
		Criteria criteria = getSession().createCriteria(AwardNums.class);
		String alias = criteria.getAlias();
		// _很重要的
		criteria.add(Restrictions.sqlRestriction(alias + "_.id < 17720812"));
		HandleList.print(criteria.list());
	}

	/**
	 * 仅需要：{alias}.id即可
	 */
	@SuppressWarnings("unchecked")
	public void querySqlRestrictionEasy() {
		Criteria criteria = getSession().createCriteria(AwardNums.class);
		// {alias}.id
		criteria.add(Restrictions.sqlRestriction("{alias}.id < 17720812"));
		HandleList.print(criteria.list());
	}

	/**
	 * lower(this_.numbers) like lower(?)
	 */
	@SuppressWarnings("unchecked")
	public void querySqlRestrictionType() {
		Criteria criteria = getSession().createCriteria(AwardNums.class);
		// lower(this_.numbers) like lower(?)
		criteria.add(Restrictions
				.sqlRestriction("lower({alias}.numbers) like lower(?)", "%09",
						Hibernate.STRING));
		HandleList.print(criteria.list());
	}

	/**
	 * where 1=1(可组合使用)
	 */
	@SuppressWarnings("unchecked")
	public void queryConjunction() {
		Criteria criteria = getSession().createCriteria(AwardNums.class);
		// 永真情况下查询
		criteria.add(Restrictions.conjunction());
		HandleList.print(criteria.list());
	}

	/**
	 * where 1=1(和conjunction一样还不知区别)
	 */
	@SuppressWarnings("unchecked")
	public void queryDisjunction() {
		Criteria criteria = getSession().createCriteria(AwardNums.class);
		// 永真情况下查询
		criteria.add(Restrictions.disjunction());
		HandleList.print(criteria.list());
	}

	/**
	 * where 1=1(和conjunction一样还不知区别)
	 */
	@SuppressWarnings("unchecked")
	public void queryNaturalId() {
		Criteria criteria = getSession().createCriteria(AwardNums.class);
		// 永真情况下查询
		criteria.add(Restrictions.naturalId());
		HandleList.print(criteria.list());
	}

	@SuppressWarnings("unchecked")
	public void queryCompare() {
		Criteria criteria = getSession().createCriteria(AwardNums.class);
		// this_.id=? eq&&ne this_.id<>?
		criteria.add(Restrictions.eq("id", 17719845));
		// this_.id>=? ge&&le this_.id<=?
		criteria.add(Restrictions.ge("id", 17719845));
		// this_.id>? gt&&lt this_.id<?
		criteria.add(Restrictions.gt("id", 17719845));
		// this_.id<=?
		criteria.add(Restrictions.le("id", 17719845));
		// this_.id<?
		criteria.add(Restrictions.lt("id", 17719845));
		// this_.id<>?
		criteria.add(Restrictions.ne("id", 17719845));
		HandleList.print(criteria.list());
	}

	@SuppressWarnings("unchecked")
	public void queryCompareProperty() {
		Criteria criteria = getSession().createCriteria(AwardNums.class);
		// this_.issue=this_.numbers eq&&ne this_.id<>?
		criteria.add(Restrictions.eqProperty("id", "issue"));
		// this_.id>=this_.issue ge&&le this_.id<=?
		criteria.add(Restrictions.geProperty("id", "issue"));
		// this_.id>this_.issue gt&&lt this_.id<?
		criteria.add(Restrictions.gtProperty("id", "issue"));
		// this_.id<=this_.issue
		criteria.add(Restrictions.leProperty("id", "issue"));
		// this_.id<this_.issue
		criteria.add(Restrictions.ltProperty("id", "issue"));
		// this_.id<>this_.issue
		criteria.add(Restrictions.neProperty("id", "issue"));
		HandleList.print(criteria.list());
	}

	/**
	 * this_.id=? or this_.id=?
	 */
	@SuppressWarnings("unchecked")
	public void queryOr() {
		Criteria criteria = getSession().createCriteria(AwardNums.class);
		// 或者查询
		criteria.add(Restrictions.or(Restrictions.eq("id", 17719845),
				Restrictions.eq("id", 17719846)));
		HandleList.print(criteria.list());
	}

	/**
	 * this_.id=? and this_.issue=?
	 */
	@SuppressWarnings("unchecked")
	public void queryAnd() {
		Criteria criteria = getSession().createCriteria(AwardNums.class);
		// 并且查询
		criteria.add(Restrictions.and(Restrictions.eq("id", 17719845),
				Restrictions.eq("issue", "2011100")));
		HandleList.print(criteria.list());
	}

	/**
	 * not this_.id=?
	 */
	@SuppressWarnings("unchecked")
	public void query() {
		Criteria criteria = getSession().createCriteria(AwardNums.class);
		// 排除查询
		criteria.add(Restrictions.not(Restrictions.eq("id", 17719841)));
		HandleList.print(criteria.list());
	}
}