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