----------------------------------------方法说明
--------------------------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 }