2014年1月19日 星期日

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的使用上,

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





沒有留言:

張貼留言