你可以使用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的使用上,
後一個參數要給一個物件的陣列作為查詢條件。
沒有留言:
張貼留言