HQL与Criteria对照

	HQL	Criteria
查询所有	String hql = "from Employee"; List<Employee> result = (List<Employee>)session.createQuery(hql) .list();	List<Employee> result = (List<Employee>)session.createCriteria(Employee.class) .list();
条件过滤 =、!= >、>= <、<=	String hql =  "from Employee e where e.age = :age"; List<Employee> result =   (List<Employee>)session.createQuery(hql) .setInteger("age", age) .list();	List<Employee> result = (List<Employee>)session.createCriteria(Employee.class) .add(Restrictions.eq("age", age)) // .add(Restrictions.ne("age", age)) // .add(Restrictions.gt("age", age)) // .add(Restrictions.ge("age", age)) // .add(Restrictions.lt("age", age)) // .add(Restrictions.le("age", age)) .list();
条件过滤 between and	String hql = "from Employee e where e.age between :fromAge and :toAge"; List<Employee> result = (List<Employee>)session.createQuery(hql) .setInteger("fromAge", fromAge) .setInteger("toAge", toAge) .list();	List<Employee> result = (List<Employee>)session.createCriteria(Employee.class) .add(Restrictions.between("age", fromAge, toAge)) .list();
条件过滤 is null	String hql = "from Employee e where e.department is null"; //String  hql  =  "from  Employee  e  where  e.department is not null"; List<Employee> result = (List<Employee>)session.createQuery(hql) .list();	List<Employee> result = (List<Employee>)session.createCriteria(Employee.class) .add(Restrictions.isNull("department")) //  .add(Restrictions.isNotNull("department")) .list();
条件过滤 集合属性 size	//String hql = "from Department d where d.employees is empty"; String hql =  "from Department d where d.employees is not empty"; String hql = "from Department d where d.employees.size > 5"; String hql = "from Department d where d.employees.size < 5"; List<Department> result = (List<Department>)session.createQuery(hql) .list();	List<Department> result = (List<Department>)session.createCriteria(Department.cla ss) // .add(Restrictions.isEmpty("employees")) .add(Restrictions.isNotEmpty("employees")) // .add(Restrictions.sizeGt("employees", 5)) // .add(Restrictions.sizeLt("employees", 5)) .list();
条件过滤 通配符	String hql = "from Employee e where e.name like :name";    List<Employee> result = (List<Employee>)session.createQuery(hql) .setString("name", name+"%") .list();	List<Employee> result = (List<Employee>)session.createCriteria(Employee.class) .add(Restrictions.like("name", name + "%")) //  .add(Restrictions.like("name", name, MatchMode.START)) .list();
条件过滤 多条件	String hql =  "from Employee e where  (e.department is not null and e.age > 25) or e.age < 25";    List<Employee> result = (List<Employee>)session.createQuery(hql) .list();	List<Employee> result = (List<Employee>) session.createCriteria(Employee.class) .add(Restrictions.or(    Restrictions.and(           Restrictions.isNotNull("department"),       Restrictions.gt("age", 25)    ),    Restrictions.lt("age", 25))   ) .list();
条件过滤 in	String hql = "from Employee e where e.id in (1, 2, 3)";    List<Employee> result = (List<Employee>)session.createQuery(hql) .list();	List<Employee> result = (List<Employee>) session.createCriteria(Employee.class) .add(Restrictions.in("id", new Integer[]{1,2,3})) .list();
排序	String hql ="from Employee e order by e.age"; String  hql  ="from  Employee  e  order  by  e.age  desc";	session.createCriteria(Employee.class) .addOrder(Order.asc("age")) .addOrder(Order.desc("age"))
投影 select 1 列	String hql = "select e.name from Employee e"; Query query = session.createQuery(hql); List<Object> result = (List<Object>)query.list();	List<Object> result = (List<Object>)session.createCriteria(Employee.class)    .setProjection(Projections.property("name"))    .list();
投影 select 多列	String hql = "select e.name, e.age from Employee e"; Query query = session.createQuery(hql); List<Object[]> result = (List<Object[]>)query.list();	List<Object[]> result = (List<Object[]>)session.createCriteria(Employee.class)    .setProjection(Projections.projectionList()      .add(Projections.property("name"))      .add(Projections.property("age"))    )    .list();
聚集 Count Max Min  Avg Sum	String hql = "select count(age), max(age), min(age), avg(age), sum(age) from Employee";    Object[] result =  (Object[])session.createQuery(hql) .uniqueResult();	Object[] result = (Object[])   session.createCriteria(  Employee.class) .setProjection(Projections.projectionList()   .add(Projections.rowCount())   .add(Projections.max("age"))   .add(Projections.min("age"))   .add(Projections.avg("age"))   .add(Projections.sum("age")) ) .uniqueResult();
投影 Result Transfe r	String hql = "select new com.jeff.vo.StatInfo(count(e.id) min(e.age), avg(e.age)) from Emp Query query = session.createQuer List<StatInfo> result = (List<StatInfo>)query.list();	List<StatInfo> result = (List<StatInfo>)session.createCriteria(Employee.class) .setProjection(Projections.projectionList()   .add(Projections.count("id").as("count"))   .add(Projections.max("age").as("maxAge"))   .add(Projections.min("age").as("minAge"))   .add(Projections.avg("age").as("avgAge")) ) .setResultTransformer(Transformers.aliasToBean(StatI nfo.class)) .list();
分组 Group by	String hql = "select e.department.name, count(age), max(age), min(age), avg(age), sum(age) from Employee e group by e.department";     List<Object[]> result = (List<Object[]>)session.createQuery(hql) .list();	List<Object[]> result = (List<Object[]>)session.createCriteria(     Employee.class) .setProjection(Projections.projectionList()   .add(Projections.property("department"))   .add(Projections.rowCount())   .add(Projections.max("age"))   .add(Projections.min("age"))   .add(Projections.avg("age"))   .add(Projections.sum("age"))   .add(Projections.groupProperty("department")) ) .list();
联结 Join	//String hql = "from Employee e where e.department.id = 9"; //隐式联结 String hql = "select e from Employee e join e.department d where d.id = 9"; //显式联结 Query query = session.createQuery(hql); List<Employee> result = (List<Employee>)query.list();	List<Employee> result = (List<Employee>)session.createCriteria(Employee.class) //createCriteria是Session的方法 .add(Restrictions.gt("age", 25)) .createCriteria("department") //createCriteria是 Criteria的方法 .add(Restrictions.eq("id", 9)) .list(); List<Employee> result = (List<Employee>)session.createCriteria(Employee.class) .createAlias("department", "d") .add(Restrictions.gt("age", 25)) .add(Restrictions.eq("d.id", 9)) .list();
动态抓取 Fetch	String hql = "from Department d left join fetch d.employees where d.id = 9"; Query query = session.createQuery(hql); department = (Department)query.uniqueResult();	department = (Department)session.createCriteria(Department.class) .setFetchMode("employees", FetchMode.JOIN) .add(Restrictions.eq("id", 9)) .uniqueResult();
命名查询 Named Query	Query query = session.getNamedQuery("com.jeff.model.Employee .getByAge"); query.setInteger("ageParam", 25); List<Employee> result = (List<Employee>)query.list();
离线查询 Detache dCriter ia		DetachedCriteria cond = DetachedCriteria.forClass(Employee.class) .add(Restrictions.eq("age", 25)); List<Employee> result = cond.getExecutableCriteria(session).list();