Hibernate Restrictions用法

Hibernate  API:http://docs.jboss.org/hibernate/orm/3.5/javadocs/overview-summary.html

转载地址：http://blog.163.com/zhaowenchao_vip/blog/static/1715151442012411101741608/

1. 方法说明

方法	说明
Restrictions.eq	＝
Restrictions.ne Restrictions.allEq	＜＞ 利用Map来进行多个等于的限制
Restrictions.gt	＞
Restrictions.ge	＞＝
Restrictions.lt	＜
Restrictions.le	＜＝
Restrictions.between	BETWEEN
Restrictions.like	LIKE
Restrictions.in	in
Restrictions.and	and
Restrictions.or	or
Restrictions.sqlRestriction	用SQL限定查询

2，QBC常用限定方法

Restrictions.eq --> equal,等于.

Restrictions.ne --> not-equals,不等于

Restrictions.allEq --> 参数为Map对象,使用key/value进行多个等于的比对,相当于多个Restrictions.eq的效果

Restrictions.gt --> great-than > 大于

Restrictions.ge --> great-equal >= 大于等于

Restrictions.lt --> less-than, < 小于

Restrictions.le --> less-equal <= 小于等于

Restrictions.between --> 对应SQL的between子句

Restrictions.like --> 对应SQL的LIKE子句

Restrictions.in --> 对应SQL的in子句

Restrictions.and --> and 关系

Restrictions.or --> or 关系

Restrictions.isNull --> 判断属性是否为空,为空则返回true

Restrictions.isNotNull --> 与isNull相反

Restrictions.sqlRestriction --> SQL限定的查询

Order.asc --> 根据传入的字段进行升序排序

Order.desc --> 根据传入的字段进行降序排序

MatchMode.EXACT --> 字符串精确匹配.相当于"like 'value'"

MatchMode.ANYWHERE --> 字符串在中间匹配.相当于"like '%value%'"

MatchMode.START --> 字符串在最前面的位置.相当于"like 'value%'"

MatchMode.END --> 字符串在最后面的位置.相当于"like '%value'"

例子：

 
 

  
  
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); 
// id=17720820 
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()); 
} 
}