Criteria 查詢方式

在hibernate中，

你可以使用criteria的查詢方式，

不需要下SQL或HQL指令，

也可避免因為換資料庫造成SQL的不相容，

而需要重新改寫SQL的負擔，

最簡單的查詢方式如下：

   Criteria criteria = session.createCriteria(Member.class);
   List<Member> list = criteria.list();

輸出SQL：
select this_.id as id11_0_, this_.name as name11_0_, this_.age as age11_0_ from member this_

會查詢出所有的資料放在list中，

當然，也可以加上條件，

使用add以及Restrictions去增加搜尋條件，

例如使用gt(大於)和lt(小於)：

   Criteria criteria = session.createCriteria(Member.class);
   criteria.add(Restrictions.gt("age", 20));
   criteria.add(Restrictions.lt("age", 30));

輸出SQL：
select this_.id as id11_0_, this_.name as name11_0_, this_.age as age11_0_ from member this_ where this_.age>? and this_.age<?

會搜尋出年齡在20到30之間的，

如果要包含20和30這兩個數字，

可以使用ge(大於等於)和le(小於等於)：

   Criteria criteria = session.createCriteria(Member.class);
   criteria.add(Restrictions.ge("age", 20));
   criteria.add(Restrictions.le("age", 30));

輸出SQL：
select this_.id as id11_0_, this_.name as name11_0_, this_.age as age11_0_ from member this_ where this_.age>=? and this_.age<=?

在criteria中預設條件都是用AND連接，

如果要使用OR的話，

需要使用Restrictions.or方式：

   Criteria criteria = session.createCriteria(Member.class);
   criteria.add(Restrictions.or(Restrictions.ge("age", 20),
     Restrictions.le("age", 30)));

輸出SQL：
select this_.id as id11_0_, this_.name as name11_0_, this_.age as age11_0_ from member this_ where (this_.age>=? or this_.age<=?)

另外也有判斷null或非null的限制式：

   Criteria criteria = session.createCriteria(Member.class);
   criteria.add(Restrictions.isNull("name"));
   
   Criteria criteria1 = session.createCriteria(Member.class);
   criteria1.add(Restrictions.isNotNull("name"));

輸出SQL：
select this_.id as id11_0_, this_.name as name11_0_, this_.age as age11_0_ from member this_ where this_.name is null
select this_.id as id11_0_, this_.name as name11_0_, this_.age as age11_0_ from member this_ where this_.name is not null

其他比較常用的BETWEEN、LIKE、IN：

   Criteria criteria = session.createCriteria(Member.class);
   criteria.add(Restrictions.between("age", 20, 30));

   Criteria criteria1 = session.createCriteria(Member.class);
   criteria1.add(Restrictions.like("name", "%m"));

   Criteria criteria2 = session.createCriteria(Member.class);
   String[] names = { "Jeff", "Tom", "Davis" };
   criteria2.add(Restrictions.in("name", names));

輸出SQL：
select this_.id as id11_0_, this_.name as name11_0_, this_.age as age11_0_ from member this_ where this_.age between ? and ?
select this_.id as id11_0_, this_.name as name11_0_, this_.age as age11_0_ from member this_ where this_.name like ?
select this_.id as id11_0_, this_.name as name11_0_, this_.age as age11_0_ from member this_ where this_.name in (?, ?, ?)

記得IN的使用上，

後一個參數要給一個物件的陣列作為查詢條件。