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)); //分组