hibernate Restrictions开发总结

/**
 * 
 * @Title: findPayOrders 
 * @Description: TODO(根据订单ids获取列表) 
 * @param orderIds
 * @return
 * @throws Exception    设定文件 
 * List<OrderOd>    返回类型 
 * @throws 
 * @author oftoo
 */
public List<OrderOd> findPayOrders(String orderIds) throws Exception {
        Criteria criteria = this.orderDao.createCriteria();
        List<Integer> list = new ArrayList<Integer>();
        String[] sq = orderIds.split(",");
        for (int i = 0; i < sq.length; i++) {
            list.add(Integer.valueOf(sq[i]));
        }
        criteria.add(Restrictions.in("id", list));
        return this.orderDao.findByCriteria(criteria);
    }

Restrictions用法

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()); 
    } 
}