Restrictions用法

1. 方法说明

方法	说明
Restrictions.eq	＝
Restrictions.ne Restrictions.allEq	＜＞ 利用Map来进行多个等于的限制
Restrictions.gt	＞
Restrictions.ge	＞＝
Restrictions.lt	＜
Restrictions.le	＜＝
Restrictions.between	BETWEEN
Restrictions.like	LIKE
Restrictions.in	in
Restrictions.and	and
Restrictions.or	or
Restrictions.sqlRestriction	用SQL限定查询

2，QBC常用限定方法

Restrictions.eq --> equal,等于.

Restrictions.ne --> not-equals,不等于

Restrictions.allEq --> 参数为Map对象,使用key/value进行多个等于的比对,相当于多个Restrictions.eq的效果

Restrictions.gt --> great-than > 大于

Restrictions.ge --> great-equal >= 大于等于

Restrictions.lt --> less-than, < 小于

Restrictions.le --> less-equal <= 小于等于

Restrictions.between --> 对应SQL的between子句

Restrictions.like --> 对应SQL的LIKE子句

Restrictions.in --> 对应SQL的in子句

Restrictions.and --> and 关系

Restrictions.or --> or 关系

Restrictions.isNull --> 判断属性是否为空,为空则返回true

Restrictions.isNotNull --> 与isNull相反

Restrictions.sqlRestriction --> SQL限定的查询

Order.asc --> 根据传入的字段进行升序排序

Order.desc --> 根据传入的字段进行降序排序

MatchMode.EXACT --> 字符串精确匹配.相当于"like 'value'"

MatchMode.ANYWHERE --> 字符串在中间匹配.相当于"like '%value%'"

MatchMode.START --> 字符串在最前面的位置.相当于"like 'value%'"

MatchMode.END --> 字符串在最后面的位置.相当于"like '%value'"

例子：

1. import java.util.ArrayList;
2. import java.util.HashMap;
3. import java.util.List;
4. import java.util.Map;
5. import java.util.Set;
6. import java.util.TreeSet;
7. import org.hibernate.Criteria;
8. import org.hibernate.Hibernate;
9. import org.hibernate.criterion.MatchMode;
10. import org.hibernate.criterion.Order;
11. import org.hibernate.criterion.Restrictions;
12. import com.happy.dao.BaseHibernateDAO;
13. import com.happy.entity.AwardNums;
14. import com.happy.test.util.HandleList;
16. public class Restriction extends BaseHibernateDAO {
17. public static void main(String[] args) {
18. Restriction query = new Restriction();
19. query.query();
20. }
22. @SuppressWarnings("unchecked")
23. public void queryAlias() {
24. Criteria criteria = getSession().createCriteria(AwardNums.class);
25. // 获取别名：eg：this
26. System.out.println(criteria.getAlias());
27. HandleList.print(criteria.list());
28. }
30. @SuppressWarnings("unchecked")
31. public void queryOrder() {
32. Criteria criteria = getSession().createCriteria(AwardNums.class);
33. criteria.addOrder(Order.desc("id"));
34. HandleList.print(criteria.list());
35. }
37. @SuppressWarnings("unchecked")
38. public void queryBetween() {
39. Criteria criteria = getSession().createCriteria(AwardNums.class);
40. // 第一个参数是属性，第一个是小的数值，第三个是大的数值
41. criteria.add(Restrictions.between("id", 17720820, 17720830));
42. HandleList.print(criteria.list());
43. }
45. @SuppressWarnings("unchecked")
46. public void queryIdEq() {
47. Criteria criteria = getSession().createCriteria(AwardNums.class);
48. // id=17720820
49. criteria.add(Restrictions.idEq(17720820));
50. HandleList.print(criteria.list());
51. }
53. @SuppressWarnings("unchecked")
54. public void queryIlike() {
55. Criteria criteria = getSession().createCriteria(AwardNums.class);
56. // 忽略大小写，05结束的
57. criteria.add(Restrictions.ilike("numbers", "05", MatchMode.END));
58. HandleList.print(criteria.list());
59. }
61. @SuppressWarnings("unchecked")
62. public void queryLike() {
63. Criteria criteria = getSession().createCriteria(AwardNums.class);
64. // 05介绍的
65. criteria.add(Restrictions.like("numbers", "05", MatchMode.END));
66. HandleList.print(criteria.list());
67. }
69. @SuppressWarnings("unchecked")
70. public void queryLike2() {
71. Criteria criteria = getSession().createCriteria(AwardNums.class);
72. // 05开始的
73. criteria.add(Restrictions.like("numbers", "05%"));
74. HandleList.print(criteria.list());
75. }
76. @SuppressWarnings("unchecked")
77. public void queryInArray() {
78. Criteria criteria = getSession().createCriteria(AwardNums.class);
79. // new int[]错误的
80. Integer[] arrInt = new Integer[] { 17720808, 17720809, 17720810 };
81. criteria.add(Restrictions.in("id", arrInt));
82. HandleList.print(criteria.list());
83. }
84. @SuppressWarnings("unchecked")
85. public void queryInList() {
86. Criteria criteria = getSession().createCriteria(AwardNums.class);
87. // new int[]错误的
88. List<Integer> list = new ArrayList<Integer>();
89. list.add(17720808);
90. list.add(17720809);
91. list.add(17720810);
92. criteria.add(Restrictions.in("id", list));
93. HandleList.print(criteria.list());
94. }
95. @SuppressWarnings("unchecked")
96. public void queryInSet() {
97. Criteria criteria = getSession().createCriteria(AwardNums.class);
98. // new int[]错误的
99. Set<Integer> set = new TreeSet<Integer>();
100. set.add(17720808);
101. set.add(17720809);
102. set.add(17720810);
103. criteria.add(Restrictions.in("id", set));
104. HandleList.print(criteria.list());
105. }
106. @SuppressWarnings("unchecked")
107. public void queryInMap() {
108. Criteria criteria = getSession().createCriteria(AwardNums.class);
109. Map<String, Integer> map = new HashMap<String, Integer>();
110. // String不能命名重复，否者会过滤重复的
111. map.put("1", 17720808);
112. map.put("2", 17720809);
113. map.put("3", 17720810);
114. criteria.add(Restrictions.in("id", map.values()));
115. HandleList.print(criteria.list());
116. }
117. @SuppressWarnings("unchecked")
118. public void queryIsEmpty() {
119. Criteria criteria = getSession().createCriteria(AwardNums.class);
120. // Property path [com.happy.entity.AwardNums.issue] does not reference a
121. // collection
122. criteria.add(Restrictions.isEmpty("issue"));
123. HandleList.print(criteria.list());
124. }
125. @SuppressWarnings("unchecked")
126. public void queryIsNotEmpty() {
127. Criteria criteria = getSession().createCriteria(AwardNums.class);
128. // Property path [com.happy.entity.AwardNums.issue] does not reference a
129. // collection
130. criteria.add(Restrictions.isNotEmpty("issue"));
131. HandleList.print(criteria.list());
132. }
134. @SuppressWarnings("unchecked")
135. public void queryIsNull() {
136. Criteria criteria = getSession().createCriteria(AwardNums.class);
137. // this_.id is null
138. criteria.add(Restrictions.isNull("id"));
139. HandleList.print(criteria.list());
140. }
142. @SuppressWarnings("unchecked")
143. public void queryIsNotNull() {
144. Criteria criteria = getSession().createCriteria(AwardNums.class);
145. // this_.id is not null
146. criteria.add(Restrictions.isNotNull("id"));
147. HandleList.print(criteria.list());
148. }
149. @SuppressWarnings("unchecked")
150. public void querySizeEq() {
151. Criteria criteria = getSession().createCriteria(AwardNums.class);
152. // Unknown collection role: com.happy.entity.AwardNums.id
153. criteria.add(Restrictions.sizeEq("id", 15));
154. HandleList.print(criteria.list());
155. }
157. @SuppressWarnings("unchecked")
158. public void querySqlRestriction() {
159. Criteria criteria = getSession().createCriteria(AwardNums.class);
160. String alias = criteria.getAlias();
161. // _很重要的
162. criteria.add(Restrictions.sqlRestriction(alias + "_.id < 17720812"));
163. HandleList.print(criteria.list());
164. }
166. @SuppressWarnings("unchecked")
167. public void querySqlRestrictionEasy() {
168. Criteria criteria = getSession().createCriteria(AwardNums.class);
169. // {alias}.id
170. criteria.add(Restrictions.sqlRestriction("{alias}.id < 17720812"));
171. HandleList.print(criteria.list());
172. }
174. @SuppressWarnings("unchecked")
175. public void querySqlRestrictionType() {
176. Criteria criteria = getSession().createCriteria(AwardNums.class);
177. // lower(this_.numbers) like lower(?)
178. criteria.add(Restrictions
179. .sqlRestriction("lower({alias}.numbers) like lower(?)", "%09",
180. Hibernate.STRING));
181. HandleList.print(criteria.list());
182. }
184. @SuppressWarnings("unchecked")
185. public void queryConjunction() {
186. Criteria criteria = getSession().createCriteria(AwardNums.class);
187. // 永真情况下查询
188. criteria.add(Restrictions.conjunction());
189. HandleList.print(criteria.list());
190. }
192. @SuppressWarnings("unchecked")
193. public void queryDisjunction() {
194. Criteria criteria = getSession().createCriteria(AwardNums.class);
195. // 永真情况下查询
196. criteria.add(Restrictions.disjunction());
197. HandleList.print(criteria.list());
198. }
200. @SuppressWarnings("unchecked")
201. public void queryNaturalId() {
202. Criteria criteria = getSession().createCriteria(AwardNums.class);
203. // 永真情况下查询
204. criteria.add(Restrictions.naturalId());
205. HandleList.print(criteria.list());
206. }
207. @SuppressWarnings("unchecked")
208. public void queryCompare() {
209. Criteria criteria = getSession().createCriteria(AwardNums.class);
210. // this_.id=? eq&&ne this_.id<>?
211. criteria.add(Restrictions.eq("id", 17719845));
212. // this_.id>=? ge&&le this_.id<=?
213. criteria.add(Restrictions.ge("id", 17719845));
214. // this_.id>? gt&&lt this_.id<?
215. criteria.add(Restrictions.gt("id", 17719845));
216. // this_.id<=?
217. criteria.add(Restrictions.le("id", 17719845));
218. // this_.id<?
219. criteria.add(Restrictions.lt("id", 17719845));
220. // this_.id<>?
221. criteria.add(Restrictions.ne("id", 17719845));
222. HandleList.print(criteria.list());
223. }
224. @SuppressWarnings("unchecked")
225. public void queryCompareProperty() {
226. Criteria criteria = getSession().createCriteria(AwardNums.class);
227. // this_.issue=this_.numbers eq&&ne this_.id<>?
228. criteria.add(Restrictions.eqProperty("id", "issue"));
229. // this_.id>=this_.issue ge&&le this_.id<=?
230. criteria.add(Restrictions.geProperty("id", "issue"));
231. // this_.id>this_.issue gt&&lt this_.id<?
232. criteria.add(Restrictions.gtProperty("id", "issue"));
233. // this_.id<=this_.issue
234. criteria.add(Restrictions.leProperty("id", "issue"));
235. // this_.id<this_.issue
236. criteria.add(Restrictions.ltProperty("id", "issue"));
237. // this_.id<>this_.issue
238. criteria.add(Restrictions.neProperty("id", "issue"));
239. HandleList.print(criteria.list());
240. }
242. @SuppressWarnings("unchecked")
243. public void queryOr() {
244. Criteria criteria = getSession().createCriteria(AwardNums.class);
245. // 或者查询
246. criteria.add(Restrictions.or(Restrictions.eq("id", 17719845),
247. Restrictions.eq("id", 17719846)));
248. HandleList.print(criteria.list());
249. }
251. @SuppressWarnings("unchecked")
252. public void queryAnd() {
253. Criteria criteria = getSession().createCriteria(AwardNums.class);
254. // 并且查询
255. criteria.add(Restrictions.and(Restrictions.eq("id", 17719845),
256. Restrictions.eq("issue", "2011100")));
257. HandleList.print(criteria.list());
258. }
260. @SuppressWarnings("unchecked")
261. public void query() {
262. Criteria criteria = getSession().createCriteria(AwardNums.class);
263. // 排除查询
264. criteria.add(Restrictions.not(Restrictions.eq("id", 17719841)));
265. HandleList.print(criteria.list());
266. }
267. }