1)Get/load主键查询
//1)主键查询
// Dept dept = (Dept)session.load(Dept.class,1);
// Dept dept = (Dept)session.get(Dept.class,1);
System.out.println(dept);
结果
Hibernate: select dept0_.deptId as deptId0_0_, dept0_.deptName as deptName0_0_ from t_dept dept0_ where dept0_.deptId=? com.cx.hqlQuery.Dept@302552ec
2) 对象导航查询
//2)对象导航查询 Dept dept = (Dept)session.get(Dept.class,1); System.out.println(dept.getEmployees());
结果
Hibernate: select dept0_.deptId as deptId0_0_, dept0_.deptName as deptName0_0_ from t_dept dept0_ where dept0_.deptId=? Hibernate: select employees0_.dept_id as dept4_0_1_, employees0_.empId as empId1_, employees0_.empId as empId1_0_, employees0_.empName as empName1_0_, employees0_.salary as salary1_0_, employees0_.dept_id as dept4_1_0_ from t_employee employees0_ where employees0_.dept_id=? [com.cx.hqlQuery.Employee@7bedc48a, com.cx.hqlQuery.Employee@38afe297]
3)HQL查询
使用hql查询的时候,映射文件auto-import="true"要设置为true,如果是false,在写hql的时候,要指定类的全名。
//3)HQL查询
//a查询全部列
Query q = session.createQuery("from Employee");
System.out.println(q.list());
//b查询指定列
Query q = session.createQuery("select d.deptId , d.deptName from Dept d");
System.out.println(q.list());
//c查询指定列,自动封装为对象(必须提供有参数的构造器)
Query q = session.createQuery("select new Dept(d.deptId,d.deptName) from Dept d");
System.out.println(((Dept)q.list().get(0)).getDeptName());
//d 条件查询
//条件查询-占位符
Query q = session.createQuery("from Dept d where deptName = ?");
// q.setString(0,"综合部");
q.setParameter(0,"综合部");
System.out.println(q.list());
//条件查询,命名参数
Query q = session.createQuery("from Dept d where deptId=:myId or deptName=:name");
q.setParameter("myId",1);
q.setParameter("name","综合部");
System.out.println(q.list());
//范围
Query q = session.createQuery("from Dept d where deptId between ? and ?");
q.setParameter(0,1);
q.setParameter(1,6);
System.out.println(q.list());
//模糊查询
Query q = session.createQuery("from Dept d where deptName like ?");
q.setString(0,"%部%");
System.out.println(q.list());
//e.聚合函数统计
Query q = session.createQuery("select count (*) from Dept");
Long num = (Long)q.uniqueResult();
System.out.println(num);
//分组查询,统计每个部门的人数
Query q = session.createQuery("select e.dept , count(*) from Employee e group by e.dept");
System.out.println(q.list());
连接查询
@Test public void testJoin(){ Session session = sf.openSession(); session.beginTransaction(); //内连接 映射已经配置好了关系,关联的时候,直接写对象的属性即可 Query q = session.createQuery("from Dept d inner join d.employees"); // System.out.println(q.list()); List<Object []> list= q.list(); for (int i = 0 ; i < list.size() ; i++) { Object[] obj = list.get(i); Employee employee = (Employee) obj[1]; Dept dept = (Dept) obj[0]; System.out.println(employee.getEmpName() + "-" + dept.getDeptName()); } //2)左外连接 // Query q = session.createQuery("from Dept d left join d.employees"); // System.out.println(q.list()); // //3)右外连接 // Query q = session.createQuery("from Employee e right join e.dept"); // System.out.println(q.list()); session.getTransaction().commit(); session.close(); }
迫切连接查询
// g. 连接查询 - 迫切连接 @Test public void fetch() { Session session = sf.openSession(); session.beginTransaction(); //1) 迫切内连接 【使用fetch, 会把右表的数据,填充到左表对象中!】 // Query q = session.createQuery("from Dept d inner join fetch d.emps"); // q.list(); //2) 迫切左外连接 Query q = session.createQuery("from Dept d left join fetch d.emps"); q.list(); session.getTransaction().commit(); session.close(); }
4) Criteria 查询
@Test public void criteria() { Session session = sf.openSession(); session.beginTransaction(); Criteria criteria = session.createCriteria(Employee.class); // 构建条件 criteria.add(Restrictions.eq("empId", 12)); // criteria.add(Restrictions.idEq(12)); // 主键查询 System.out.println(criteria.list()); session.getTransaction().commit(); session.close(); }
5)本地sql查询
// 不能跨数据库平台: 如果该了数据库,sql语句有肯能要改。 @Test public void sql() { Session session = sf.openSession(); session.beginTransaction(); SQLQuery q = session.createSQLQuery("SELECT * FROM t_Dept limit 5;") .addEntity(Dept.class); // 也可以自动封装 System.out.println(q.list()); session.getTransaction().commit(); session.close(); }