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