hibernate-Criteria

hibernate-Criteria2010-04-29 15:53Criteria criteria = session.createCriteria(User.class);
List users = criteria.list();
---------------------------------------排序----------------------------------------
Criteria criteria = session.createCriteria(User.class);
criteria.addOrder(Order.asc("age"));
List users = criteria.list();
---------------------------------------分页----------------------------------------
Criteria criteria = session.createCriteria(User.class);
criteria.setFirstResult(51);
criteria.setMaxResult(50);
List users = criteria.list();
----------------------------------------函数---------------------------------------
使用Projections的avg()、rowCount()、count()、max()、min()、countDistinct()等方法，例如:
Criteria criteria = session.createCriteria(User.class);
criteria.setProjection(Projections.avg("age"));
List users = criteria.list();
----------------------------------------分组---------------------------------------
Projections的groupProperty()来对结果进行分组
Criteria criteria = session.createCriteria(User.class);
criteria.setProjection(Projections.groupProperty("age"));
List users = criteria.list();

如果想结合统计与分组功能，则可以使用ProjectionList，例如下面的程式会计算每个年龄各有多少個人：
ProjectionList projectionList = Projections.projectionList();
projectionList.add(Projections.groupProperty("age"));
projectionList.add(Projections.rowCount());
Criteria criteria = session.createCriteria(User.class);
criteria.setProjection(projectionList);
List users = criteria.list();
----------------------------------------类似查询---------------------------------------
如果有一個已知的物件，则可以根据这个物价作为查询的依据，看看是否有属性与之类似的物件，例如：
User user = new User();
user.setAge(new Integer(30));//找出age是30的用户
Criteria criteria = session.createCriteria(User.class);
criteria.add(Example.create(user));
List users = criteria.list();
----------------------------------------复合查询---------------------------------------
Criteria可以进行复合查询，即在原有的查詢基础上再进行查询，例如在Room对User的一对多关联中，在查询出所有的Room资料之后，希望再查询users中"age"为30的user资料：
Criteria roomCriteria = session.createCriteria(Room.class);
Criteria userCriteria = roomCriteria.createCriteria("users");
userCriteria.add(Restrictions.eq("age", new Integer(30)));
List rooms = roomCriteria.list(); // 只列出users属性中有user之"age"为30的Room
Iterator iterator = rooms.iterator();

---------------------------------------------------连接限定-----------------------------------------------------

Criteria criteria = session.createCriteria(Team.class).setFetchMode("students", FetchMode.EAGER).add(Restrictions.like("teamName","2",MatchMode.END));

List list = criteria.list();

------------------------------------QBE查询方式------------------------------------

Example exampleStu =Example.create(stu).ignoreCase()//忽略大小写

        .enableLike(MatchMode.ANYWHERE) //对所有String属性进行like匹配

        .excludeProperty("age");//不把age字段加入到where条件句中

            list= session.createCriteria(Student.class)

                        .add( exampleStu )

                        .list();


----------------------------------------------------------------------------------------------------------------------------------

Criteria实际上只是个容器，如果想要设定查询条件，则要使用add()方法加入Restrictions的条件限制，例如查詢age大于20且小于40的资料：
Criteria criteria = session.createCriteria(User.class);
criteria.add(Restrictions.gt("age", new Integer(20)));
criteria.add(Restrictions.lt("age", new Integer(40)));
List users = criteria.list();


也可以使用sqlRestriction()方法来提供SQL语法限定查询，例如查询name以cater开头的资料：
Criteria criteria = session.createCriteria(User.class);
criteria.add(Restrictions.sqlRestriction("{alias}.name LIKE (?)", "cater%", Hibernate.STRING));
List users = criteria.list();
其中alias将被替换为与User类别相关的名称，而？将被替换为cater%，也就是第二個参数所提供的值，在SQL拼写时，不必再写WHERE，如果有多个查询条件，例如BETWEEN子句的查询，则可以如下：
Criteria criteria = session.createCriteria(User.class);
Integer[] ages = {new Integer(20), new Integer(40)};
Type[] types = {Hibernate.INTEGER, Hibernate.INTEGER};
criteria.add(Restrictions.sqlRestriction("{alias}.age BETWEEN (?) AND (?)", ages, types));
List users = criteria.list();

-------------------------------------------------------------------------------------------------------------------------


Restrictions的幾個常用限定查詢方法如下表所示：
方法 說明
Restrictions.eq 等於
Restrictions.allEq 使用Map，使用key/value進行多個等於的比對
Restrictions.gt 大於 >
Restrictions.ge 大於等於 >=
Restrictions.lt 小於 <
Restrictions.le 小於等於 <=
Restrictions.between 對應SQL的BETWEEN子句
Restrictions.like 對應SQL的LIKE子句
Restrictions.in 對應SQL的in子句
Restrictions.and and關係
Restrictions.or or關係
Restrictions.sqlRestriction SQL限定查詢

---------------------------------------------------------------------------------------------------------

Criteria crit = session.createCriteria(Student.class);

if(name!=null){

   crit.add(Restrictions.like("name",name,MatchMode.ANYWHERE));

}

if(age!=0){

   crit.add(Restrictions.eq("age",new Integer(age)));

}

crit.addOrder(Order.asc("name"));

tx.commit();

return crit.list();