目录:
- HQL
- QBC
- SQL
1)HQL:
HQL是Hibernate查询语言的简称,是一种面向对象的查询语言,使用类,对象,和属性,没有表和字段的概念。
- 使用HQL查询的基本步骤:获得session ----》 编写HQL语句 ------》 创建Query对象 -------》 执行查询,获得结果。
-
String hql = "from Employee"; Query query = session.createQuery(hql); List<Employee> list = query.list(); Iterator itor = list.iterator(); while(itor.hasNext()){ Employee e = (Employee) itor.next(); System.out.println(e.getName()); }
- 对查询结果排序 String hql = "from Employee as e order by e.age desc";
- 查询部分属性
String hql = "select e.name, e.age from Employee as e"; Query query = session.createQuery(hql); List<Employee> list = query.list(); Iterator itor = list.iterator(); while(itor.hasNext()){ Object[] obj = (Object[])itor.next(); System.out.println(obj[0] + "--" + obj[1]); }
- HQL聚集函数:count(), min(), max(), sum(), avg()
String hql = "select count(e) from Employee as e"; Query query = session.createQuery(hql); Long count = (Long)query.uniqueResult();
- 分组查询 String hql = "select a.employee.name, count(*) from Address a group by a.employee";
- 动态实例查询:在属性查询中返回的是对象数组,不易操作,为了提高检索效率,可将检索出的属性分装到一个实体类。需要提供相应构造方法。
String hql = "select new Address(a.id,a.shortName) from Address a"; Query query = session.createQuery(hql); List list = query.list(); Iterator itor = list.iterator(); while(itor.hasNext()){ Address a = (Address)itor.next(); System.out.println(a.getShortName()); }
- 分页查询
//从第一个对象开始查询 query.setFirstResult(0); //返回三个对象 query.setMaxResults(3);
- 条件查询
- 按参数位置
String hql = "from Employee e where e.name like ?"; Query query = session.createQuery(hql); query.setString(0, "dudu");
- 按参数名
String hql = "from Employee e where e.name = :ename"; Query query = session.createQuery(hql); query.setParameter("ename","dudu");
- 按参数位置
- 连接查询
- 内连接inner join String hql = "select a from Address a inner join a.employee e where e.name = 'dudu'";
- 隐式内连接(在HQL语句中看不到join,但是实际上已经发生内连接) String hql = "select a from Address a, Employee e where a.employee = e and e.name = 'dudu'";
- 左外连接 left join
- 右外连接right join
- 交叉连接:HQL中的内连接和外连接主要为关联类连接查询,对于相互之间毫无关系的对象,可以使用交叉连接进行查询。 String hql = "select Person p, User u where p.id = u.id";
子查询: 放在括号内。 子查询关键字有all, any, some, in, exists 如in(....)
- 相关子查询:子查询使用外层查询中的对象名。e.address为set类型。
String hql = "select Employee e where (select count(*) from e.address) > 1";
- 无关子查询:子查询与外层查询语句无关。
String hql = "select Book b where b.unitPrice < (select avg(b1.unitPrice) from Book b1)";
2)QBC:
Order By Criteria 。Criteria是Hibernate API 提供的一个查询接口, 又称为对象查询。
3) SQL: