一,概述
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());