Hibernate高级查询

1.QBE

 适合列表页属性并搜索

public List findTestByConn(Class test){

     Example example = Example.create(test);

     example.ignoreCase().enableLike(MatchMode.ANYWHERE);

     example.excludeZeroes();//表示如果test对象中属性值为NULL或数字类型为0的不加入查询

   Criteria criteria = this.getSession().createCriteria(Test.class).add(example);

    return criteria.list();

}

注:该方法支持关系查询,

this.getSession().createCriteria(Test.class).add(example)

.createCriteri(Test2.class).add(example);

2.集合过滤

this.getSession().createFilter(customer.getOrders(),"where this.price >1000 order by this.price").list();

同样的效果:this.getSession().createQuery("from Order o where o.customer=:customer and o.price >1000 order by o.price").setEntity("customer",customer).list();

3.Query

String sql ="select id from T_test ";
    SQLQuery query = getHibernateSession().createSQLQuery(sql);
    query.addScalar("id", Hibernate.STRING);//此处不定义，可能拿不到正确的结果集
    List<String> list = query.list();

4.QBC（可支持查询部分字段）

DetachedCriteria detachedCriteria = getDetachedCriteriaByColum(columName, test.class, "t");

//忽略大小写模糊匹配

detachedCriteria.add(Restrictions.like("t.property1", "abc", MatchMode.ANYWHERE).ignoreCase());

//不等于

detachedCriteria.add(Restrictions.ne("t.property2", "aaa"));

//等于

detachedCriteria.add(Restrictions.eq("t.property3", true));

//插入部分SQL限制结果集

detachedCriteria.add(Restrictions.sqlRestriction(this_.property6 not in(select id from test2));

//排序

detachedCriteria.addOrder(Order.asc("t.property4")).addOrder(Order.asc("t.property5"));

List list = getHibernateTemplate().findByDetachedCriteria(detachedCriteria);

//提供对查询部分字段的封装，适合单表

 public DetachedCriteria getDetachedCriteriaByColum(String[] columName,
   Class className, String alias) {
  DetachedCriteria detachedCriteria = DetachedCriteria.forClass(
    className, alias);
  ProjectionList projectList = Projections.projectionList();
  if (null == columName) {
   return null;
  }

  for (String colum : columName) {
   projectList
     .add(Projections.property(alias + "." + colum).as(colum));
  }

  detachedCriteria.setProjection(projectList);
  detachedCriteria.setResultTransformer(Transformers
    .aliasToBean(className));
  return detachedCriteria;
 }

 5.Projections类

    Criteria criteria = session.createCriteria(cat.class);

      criteria.setProjection(Projections.projectionList().add(Projections.rowCount())); //查询总行数

      criteria.add(Projections.avg(propertyName)); //求平构值

      criteria.add(Projections.min(propertyName)); //最小值

      criteria.add(Projections.max(propertyName)); //最大值

      criteria.add(Projections.sum(propertyName)); //求和

      criteria.add(Projections.groupProperty(propertyName)); //分组