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());