Restrictions.sqlRestriction

了hibernate的查询,其中学到构建Criteria查询,在Criteria查询中
使用sqlRestriction()方法来提供SQL语法作限定查询,如:
Criteria criteria = session.createCriteria(User.class);
criteria.add(Restrictions.sqlRestriction(“{类名}.name LIKE(?)", "cater%", Hibernate.STRING));
List users = criteria.list();
其中Criteria对象由session创建,在Criteria对象中添加Restrictions.sqlRestriction来作为限定条件,在Restrictions.sqlRestriction()中有三个参数,第一个是String类型,是SQL语句的条件部分,第二个参数是参数值,第三个参数是类型.第二个和第三个参数都没什么,关键是第一个参数,"{类名}.name LIKE(?)" {}中的类名是表示对应PO类的别名.在上面写的语句中对应应该为User类的别名.
自己这么理解后,先是将其换为{User}.name...,报错,再加上包名还是报错,看了尹老拷的PPT,根据下面举的例子,应该是{alias}.name...,运行正常,在该成其他的,报错,看来那里还必须写成{alias},那alias究竟是什么意思呢?我英语不好,上网一搜:
alias，别名，指能够代替表示某人或某物的称呼。比如，文学作品中的“笔名”就是作者真实姓名的一个alias。alias一词源于拉丁语alias，意思是“其他的某个东西”。在IT中，alias至少有下面两种意思：　　1）在某些计算机操作系统以及程序语言中，用别名表示某个数据对象可以使含义清晰，具有可读性。可以仅定义一次数据对象，之后程序员可以用多个别名来引用数据对象。　　2）苹果机的麦金托什操作系统中，aliaa是一个特定程序的桌面图标，也可以表示某个数据对象。

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.TreeSet;
import org.hibernate.Criteria;
import org.hibernate.Hibernate;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;
import com.happy.dao.BaseHibernateDAO;
import com.happy.entity.AwardNums;
import com.happy.test.util.HandleList;
/**
* @author happy
*
*/
public class Restriction extends BaseHibernateDAO {
public static void main(String[] args) {
Restriction query = new Restriction();
query.query();
}
/**
* 获取查询别名
*/
@SuppressWarnings("unchecked")
public void queryAlias() {
Criteria criteria = getSession().createCriteria(AwardNums.class);
// 获取别名：eg：this
System.out.println(criteria.getAlias());
HandleList.print(criteria.list());
}
/**
* 对查询排序
*/
@SuppressWarnings("unchecked")
public void queryOrder() {
Criteria criteria = getSession().createCriteria(AwardNums.class);
criteria.addOrder(Order.desc("id"));
HandleList.print(criteria.list());
}
/**
* query by Between
*/
@SuppressWarnings("unchecked")
public void queryBetween() {
Criteria criteria = getSession().createCriteria(AwardNums.class);
// 第一个参数是属性，第一个是小的数值，第三个是大的数值
criteria.add(Restrictions.between("id", 17720820, 17720830));
HandleList.print(criteria.list());
}
/**
* query by idEq
*/
@SuppressWarnings("unchecked")
public void queryIdEq() {
Criteria criteria = getSession().createCriteria(AwardNums.class);
// id=17720820
criteria.add(Restrictions.idEq(17720820));
HandleList.print(criteria.list());
}
/**
* 忽略大小写查询query by ilike
*/
@SuppressWarnings("unchecked")
public void queryIlike() {
Criteria criteria = getSession().createCriteria(AwardNums.class);
// 忽略大小写，05结束的
criteria.add(Restrictions.ilike("numbers", "05", MatchMode.END));
HandleList.print(criteria.list());
}
/**
* 正常like查询 query by like sql:this_.numbers like ?
*/
@SuppressWarnings("unchecked")
public void queryLike() {
Criteria criteria = getSession().createCriteria(AwardNums.class);
// 05介绍的
criteria.add(Restrictions.like("numbers", "05", MatchMode.END));
HandleList.print(criteria.list());
}
/**
* 正常like查询 query by like sql:this_.numbers like ?
*/
@SuppressWarnings("unchecked")
public void queryLike2() {
Criteria criteria = getSession().createCriteria(AwardNums.class);
// 05开始的
criteria.add(Restrictions.like("numbers", "05%"));
HandleList.print(criteria.list());
}
@SuppressWarnings("unchecked")
public void queryInArray() {
Criteria criteria = getSession().createCriteria(AwardNums.class);
// new int[]错误的
Integer[] arrInt = new Integer[] { 17720808, 17720809, 17720810 };
criteria.add(Restrictions.in("id", arrInt));
HandleList.print(criteria.list());
}
@SuppressWarnings("unchecked")
public void queryInList() {
Criteria criteria = getSession().createCriteria(AwardNums.class);
// new int[]错误的
List<Integer> list = new ArrayList<Integer>();
list.add(17720808);
list.add(17720809);
list.add(17720810);
criteria.add(Restrictions.in("id", list));
HandleList.print(criteria.list());
}
@SuppressWarnings("unchecked")
public void queryInSet() {
Criteria criteria = getSession().createCriteria(AwardNums.class);
// new int[]错误的
Set<Integer> set = new TreeSet<Integer>();
set.add(17720808);
set.add(17720809);
set.add(17720810);
criteria.add(Restrictions.in("id", set));
HandleList.print(criteria.list());
}
@SuppressWarnings("unchecked")
public void queryInMap() {
Criteria criteria = getSession().createCriteria(AwardNums.class);
Map<String, Integer> map = new HashMap<String, Integer>();
// String不能命名重复，否者会过滤重复的
map.put("1", 17720808);
map.put("2", 17720809);
map.put("3", 17720810);
criteria.add(Restrictions.in("id", map.values()));
HandleList.print(criteria.list());
}
@SuppressWarnings("unchecked")
public void queryIsEmpty() {
Criteria criteria = getSession().createCriteria(AwardNums.class);
// Property path [com.happy.entity.AwardNums.issue] does not reference a
// collection
criteria.add(Restrictions.isEmpty("issue"));
HandleList.print(criteria.list());
}
@SuppressWarnings("unchecked")
public void queryIsNotEmpty() {
Criteria criteria = getSession().createCriteria(AwardNums.class);
// Property path [com.happy.entity.AwardNums.issue] does not reference a
// collection
criteria.add(Restrictions.isNotEmpty("issue"));
HandleList.print(criteria.list());
}
/**
* this_.id is null
*/
@SuppressWarnings("unchecked")
public void queryIsNull() {
Criteria criteria = getSession().createCriteria(AwardNums.class);
// this_.id is null
criteria.add(Restrictions.isNull("id"));
HandleList.print(criteria.list());
}
/**
* this_.id is not null
*/
@SuppressWarnings("unchecked")
public void queryIsNotNull() {
Criteria criteria = getSession().createCriteria(AwardNums.class);
// this_.id is not null
criteria.add(Restrictions.isNotNull("id"));
HandleList.print(criteria.list());
}
@SuppressWarnings("unchecked")
public void querySizeEq() {
Criteria criteria = getSession().createCriteria(AwardNums.class);
// Unknown collection role: com.happy.entity.AwardNums.id
criteria.add(Restrictions.sizeEq("id", 15));
HandleList.print(criteria.list());
}
/**
* this_.id < 17720812
*/
@SuppressWarnings("unchecked")
public void querySqlRestriction() {
Criteria criteria = getSession().createCriteria(AwardNums.class);
String alias = criteria.getAlias();
// _很重要的
criteria.add(Restrictions.sqlRestriction(alias + "_.id < 17720812"));
HandleList.print(criteria.list());
}
/**
* 仅需要：{alias}.id即可
*/
@SuppressWarnings("unchecked")
public void querySqlRestrictionEasy() {
Criteria criteria = getSession().createCriteria(AwardNums.class);
// {alias}.id
criteria.add(Restrictions.sqlRestriction("{alias}.id < 17720812"));
HandleList.print(criteria.list());
}
/**
* lower(this_.numbers) like lower(?)
*/
@SuppressWarnings("unchecked")
public void querySqlRestrictionType() {
Criteria criteria = getSession().createCriteria(AwardNums.class);
// lower(this_.numbers) like lower(?)
criteria.add(Restrictions
.sqlRestriction("lower({alias}.numbers) like lower(?)", "%09",
Hibernate.STRING));
HandleList.print(criteria.list());
}
/**
* where 1=1(可组合使用)
*/
@SuppressWarnings("unchecked")
public void queryConjunction() {
Criteria criteria = getSession().createCriteria(AwardNums.class);
// 永真情况下查询
criteria.add(Restrictions.conjunction());
HandleList.print(criteria.list());
}
/**
* where 1=1(和conjunction一样还不知区别)
*/
@SuppressWarnings("unchecked")
public void queryDisjunction() {
Criteria criteria = getSession().createCriteria(AwardNums.class);
// 永真情况下查询
criteria.add(Restrictions.disjunction());
HandleList.print(criteria.list());
}
/**
* where 1=1(和conjunction一样还不知区别)
*/
@SuppressWarnings("unchecked")
public void queryNaturalId() {
Criteria criteria = getSession().createCriteria(AwardNums.class);
// 永真情况下查询
criteria.add(Restrictions.naturalId());
HandleList.print(criteria.list());
}
@SuppressWarnings("unchecked")
public void queryCompare() {
Criteria criteria = getSession().createCriteria(AwardNums.class);
// this_.id=? eq&&ne this_.id<>?
criteria.add(Restrictions.eq("id", 17719845));
// this_.id>=? ge&&le this_.id<=?
criteria.add(Restrictions.ge("id", 17719845));
// this_.id>? gt&&lt this_.id<?
criteria.add(Restrictions.gt("id", 17719845));
// this_.id<=?
criteria.add(Restrictions.le("id", 17719845));
// this_.id<?
criteria.add(Restrictions.lt("id", 17719845));
// this_.id<>?
criteria.add(Restrictions.ne("id", 17719845));
HandleList.print(criteria.list());
}
@SuppressWarnings("unchecked")
public void queryCompareProperty() {
Criteria criteria = getSession().createCriteria(AwardNums.class);
// this_.issue=this_.numbers eq&&ne this_.id<>?
criteria.add(Restrictions.eqProperty("id", "issue"));
// this_.id>=this_.issue ge&&le this_.id<=?
criteria.add(Restrictions.geProperty("id", "issue"));
// this_.id>this_.issue gt&&lt this_.id<?
criteria.add(Restrictions.gtProperty("id", "issue"));
// this_.id<=this_.issue
criteria.add(Restrictions.leProperty("id", "issue"));
// this_.id<this_.issue
criteria.add(Restrictions.ltProperty("id", "issue"));
// this_.id<>this_.issue
criteria.add(Restrictions.neProperty("id", "issue"));
HandleList.print(criteria.list());
}
/**
* this_.id=? or this_.id=?
*/
@SuppressWarnings("unchecked")
public void queryOr() {
Criteria criteria = getSession().createCriteria(AwardNums.class);
// 或者查询
criteria.add(Restrictions.or(Restrictions.eq("id", 17719845),
Restrictions.eq("id", 17719846)));
HandleList.print(criteria.list());
}
/**
* this_.id=? and this_.issue=?
*/
@SuppressWarnings("unchecked")
public void queryAnd() {
Criteria criteria = getSession().createCriteria(AwardNums.class);
// 并且查询
criteria.add(Restrictions.and(Restrictions.eq("id", 17719845),
Restrictions.eq("issue", "2011100")));
HandleList.print(criteria.list());
}
/**
* not this_.id=?
*/
@SuppressWarnings("unchecked")
public void query() {
Criteria criteria = getSession().createCriteria(AwardNums.class);
// 排除查询
criteria.add(Restrictions.not(Restrictions.eq("id", 17719841)));
HandleList.print(criteria.list());
}
}