Hibernate之查询

一,概述

1)get/load主键查询

2)对象导航查询

3)HQL(Hibernate Query Language)查询,Hibernate提供的面向对象的查询语言

4)Criteria查询,完全面向对象(Query By Criteria)

5)SQLQuery查询,本地SQL查询.缺点:不能跨数据库平台,如果更改了数据库,SQL语句可能要改.使用场景:对于复杂SQL,HQL实现不了的情况,可以使用本地SQL查询.

二,查询

1)get/load查询

Dept dept=(Dept) session.get(Dept.class, 1); Dept dept=(Dept) session.load(Dept.class, 1);

2)对象导航查询

Dept dept=(Dept) session.get(Dept.class, 1); System.out.println(dept.getDeptName()); System.out.println(dept.getEmps());

3)HQL查询

a)使用HQL查询时,auto-import="true",直接写对象名即可 如果auto-import="false",就要用对象的全名查询.

b)查询全部列

Query q = session.createQuery("from Dept");//True // Query q = session.createQuery("select d from Dept d");//True // Query q = session.createQuery("select * from Dept");// False // System.out.println(q.list());c)查询指定列(返回一个对象数组Object[])

Query q=session.createQuery("select d.deptId,d.deptName from Dept d"); System.out.println(q.list());d)查询指定的列封装为对象,必须要提供带参的构造器

Query q = session.createQuery("select new Dept(d.deptId,d.deptName) from Dept d"); System.out.println(q.list());e)条件查询:占位符

Query q=session.createQuery("from Dept where deptName=?"); q.setString(0, "宣传部"); //q.setParameter(0, "宣传部");//通用 System.out.println(q.list());f)条件查询:命名参数

Query q=session.createQuery("from Dept where deptId=:myId and deptName=:myName"); q.setParameter("myId", 1); q.setParameter("myName", "宣传部"); System.out.println(q.list());g)条件查询:范围

Query q=session.createQuery("from Dept where deptId between ? and ?"); q.setParameter(0, 1); q.setParameter(1, 4); System.out.println(q.list()); h)条件查询:模糊查询

Query q=session.createQuery("from Dept where deptName like ?"); q.setParameter(0, "%部%"); System.out.println(q.list());i)聚合函数查询

Query q=session.createQuery("select count(*) from Dept"); Long count = (Long) q.uniqueResult(); System.out.println(count);j)分组查询

// 数据库写法SELECT dept_id,COUNT(*) FROM t_employee GROUP BY dept_id; Query q=session.createQuery("select e.dept,count(*) from Employee e group by e.dept"); System.out.println(q.list());k)内连接查询

// 数据库写法:SELECT e.empName,e.salary,d.deptName FROM t_dept d INNER JOIN t_employee e ON e.dept_id=d.deptId; Query q = session.createQuery("from Employee e inner join e.dept"); // System.out.println(q.list()); List<Object[]> list = q.list(); l)外连接查询

// 1)左外连接: // 数据库写法: // Query q = session.createQuery("from Employee e left join e.dept"); // System.out.println(q.list()); // 右外连接 Query q = session.createQuery("from Dept d right join d.emps"); System.out.println(q.list()); m)迫切连接

// 迫切内连接 // fetch关键字:迫切连接,把右表数据填充到左表 // Query q = // session.createQuery("from Employee e inner join fetch e.dept"); // System.out.println(q.list()); Query q = session.createQuery("from Employee e left join fetch e.dept"); System.out.println(q.list());n)不讲HQL写死,将HQL写到XXX.hbm.xml中

<!-- 存放sql语句 --> <query name="getAllDept"> from Dept where deptId=:myId and deptName=:myName </query> <query name="getAllDept2"> <![CDATA[ from Dept where deptId < ? ]]> </query>使用: // HQL语句放到配置文件中,通过代码获取 //Query q = session.getNamedQuery("getAllDept"); //Query q = session.createQuery("from Dept where deptId < ?"); Query q = session.getNamedQuery("getAllDept2"); // q.setParameter("myId", 1); // q.setParameter("myName", "宣传部"); q.setParameter(0, 5); System.out.println(q.list());

4)Criteria查询

//Criteria 查询 Criteria criteria = session.createCriteria(Employee.class); //添加条件 //criteria.add(Restrictions.eq("empId", 6)); //criteria.add(Restrictions.idEq(6));//主键查询 criteria.add(Restrictions.between("empId", 4, 10)); System.out.println(criteria.list()); System.out.println(criteria.list().get(0));

5)SQLQuery查询

//SQLQuery q = session.createSQLQuery("SELECT * FROM t_dept;"); //查询并且封装到相应的实体对象中 SQLQuery q = session.createSQLQuery("SELECT * FROM t_dept;").addEntity(Dept.class); System.out.println(q.list());