Hql 查询 (Hibernate Query language)
在hibernate里做复杂查询 需要用到 hql语句
面向对象查询语句 类名 属性名
基本原则 :
sql hql
表 类名
字段 属性名
sql 和 hql 区别
hql * 不能单独使用 ,但可以组合使用 count(*)
hql 不能使用 join on
查询表的所有内容:
Session session = HibernateUtils.getSession();
String hql ="from Emp" ;
Query query = session.createQuery(hql);
List<Emp>lsit=query.list();
HibernateUtils.closeSession()
list 即为所查结果的集合
条件查询:
Session session = HibrenateUtils.getSession;
String hql ="from Emp where sex = ? and age >? and ename like ?";
Query query = session.createQuery(hql);
query.setString(0,"男"); 给占位符赋值,从 0开始
query.setInteger(1,18);
query.setString(2,"%张%");
List<Emp>list = query.list();
Hibernate.closeSession();
查询类的部分属性值
Session session = HibernateUtils.getSession();
String hql ="select ename, salary from Emp where sex = ? "; ename ,salary为Emp类的属性
Query query = session.createQuery(hql);
query.setString(0,"男");
List<Object [ ] >list=query.list() 部分属性不能封装成Emp对象,通过反射 list.get(0).getClass.getSimpleName()
HibernateUtils.closeSession();
分组查询
Session session = HibernateUtils.getSession();
String hql = "select job ,count (empno), max(salary),sum(salary),avg(salary) from Emp group by job havingcout(empno)>=1 oder by count(empno) desc";
Query query = session.createQuery(hql);
List<Object [ ] >list = query.lsit();
HibernateUtils.closeSession();
表连接查询
Session session = HibernateUtils.getSession();
String hql="select d.dname count(e.empno),sum(e.salary) from Emp e join e.dept d group by d.dname havingcount(e.empno)>=1 oder by count(emp) desc"; join 连接 emp类的外键属性 dept
Query query = session.createQuery(hql);
List<Object [ ] >list = query.lsit();
HibernateUtils.closeSession();
分页查询
query.setFirstResult(0); (pageNo-1)*pageSize
query.setMaxResults(2); 每页显示数量 pageSize
命名hql查询
在*.hbm.xml中 为hql语句起个名字 , 不用修改代码
<query name=" findLikeByHql">
from Emp where ename like ? and age> ?
</query>
Session session = HibernateUtils.getSession();
Query query = session.getNameQuery("findLikeByHql");
query.setString(0,"%张%");
query.setInteger(1,18);
Iterator<Emp> it = query.iterate();
while(it.hasNext()){
Emp emp = (Emp)it.next();
}
Hibernate.closeSession();
Criater 查询
seesion.get(Emp.class ,1001); 根据主键直接查询
编程 查询;不需要sql hql 将查询的条件用对象来封装
Session session = HibernateUtils.getSession();
Criteria c = session.createCriteria(Emp.class);
Criterion condition1= Restrictions.like("ename","%张%");
Criterion condition2= Restrictions.gt("age",18);
Criterion condition3= Restrictions.eq("sex","男");
c.add(condition1);
c.add(condition2);
c.add(condition3);
c.addOrder(Order.desc("age"));
List<Emp>list =c.list();
Hibernate.closeSession();