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