Hibernate API:http://docs.jboss.org/hibernate/orm/3.5/javadocs/overview-summary.html
转载地址:http://blog.163.com/zhaowenchao_vip/blog/static/1715151442012411101741608/
-
方法说明
方法 | 说明 |
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&< 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&< 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());
- }
- }