Hibernate学习-11-几种查询详解

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