HQL与Criteria对照
HQL Criteria
查询所有 String hql = "from Employee";
List result =
(List)session.createQuery(hql)
.list();
List result =
(List)session.createCriteria(Employee.class)
.list();
条件过滤
=、!=
>、>=
result =
(List)session.createQuery(hql)
.setInteger...
HQL Criteria
查询所有 String hql = "from Employee";
List result =
(List)session.createQuery(hql)
.list();
List result =
(List)session.createCriteria(Employee.class)
.list();
条件过滤
=、!=
>、>=
<、<=
String hql = "from Employee e where e.age = :age";
List result =
(List)session.createQuery(hql)
.setInteger("age", age)
.list();
List result =
(List)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 result =
(List)session.createQuery(hql)
.setInteger("fromAge", fromAge)
.setInteger("toAge", toAge)
.list();
List result =
(List)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 result =
List result =
(List)session.createCriteria(Employee.class)
.add(Restrictions.isNull("department"))
// .add(Restrictions.isNotNull("department"))
.list();
(List)session.createQuery(hql)
.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 result =
(List)session.createQuery(hql)
.list();
List result =
(List)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 result =
(List)session.createQuery(hql)
.setString("name", name+"%")
.list();
List result =
(List)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 result =
(List)session.createQuery(hql)
.list();
List result = (List)
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 result =
(List)session.createQuery(hql)
.list();
List result = (List)
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