Criteria查询

                                                                    Criteria查询

1.查询所有（两种重载方式）

 

//查询部门的所有信息
    @Test
    public void selectAllDept(){
        //Criteria criteria = session.createCriteria(Dept.class);
        Criteria criteria = session.createCriteria("cn.happy.entity.Dept");
        List<Dept> list = criteria.list();
        for (Dept dept : list) {
            
            System.out.println(dept.getDeptName());
        }
    }

 

 

 

2.带条件的查询

 

 //带条件查询
    @Test
    public void selectBy(){
        
        
        Criteria criteria = session.createCriteria(Dept.class);
        criteria = criteria.add(Restrictions.eq("deptName", "财务部"));
        List<Dept> list = criteria.list();
        for (Dept dept : list) {    
            System.out.println("部门名称："+dept.getDeptName()+"\t部门编号："+dept.getDeptNo());
        }
        
    }    

 

 

3.关联查询 

//运用别名查询  查询部门名称为  财务部 的员工  最终定位到的是员工  所以映射员工字节码
    @Test
    public void aliziatest(){
        Criteria criteria = session.createCriteria(Emp.class);
        //给员工表中外键属性dept起个别名
        criteria.createAlias("dept", "d");
        criteria = criteria.add(Restrictions.eq("d.deptName", "财务部"));
        List<Emp> list = criteria.list();
        for (Emp emp : list) {
            System.out.println("职工姓名:"+emp.getEmpName()+"\t部门名称："+emp.getDept().getDeptName());
        }
        
    }

4.范围查询

//查询员工姓名是邹乐或者范子阳的员工信息
    @Test
    public void intest(){
        Criteria criteria = session.createCriteria(Emp.class);
        
        List<String> lists=new ArrayList<String>();
        lists.add("邹乐");
        lists.add("范子阳");
        criteria=criteria.add(Restrictions.in("empName", lists));
        
        List<Emp> list = criteria.list();
        for (Emp emp : list) {
            System.out.println("职工姓名:"+emp.getEmpName()+"\t部门名称："+emp.getDept().getDeptName());
        }
    }

5.字符串模式匹配

 //字符串模式匹配
    //查询员工姓名包含“范”的员工信息 ilike（忽略大小写）
    //字符串模式匹配
    @Test
    public void stringTest(){
        Criteria criteria = session.createCriteria(Emp.class);
        criteria=criteria.add(Restrictions.ilike("empName","%范%"));
        List<Emp> list = criteria.list();
        for (Emp emp : list) {
            System.out.println("职工姓名:"+emp.getEmpName()+"\t部门名称："+emp.getDept().getDeptName());
        }
        
    }

 

6.逻辑运算符

//字符串模式匹配
    //查询员工编号为1，员工姓名包含“范”的员工信息 ilike（忽略大小写）
    //字符串模式匹配
    @Test
    public void stringTests(){
        Criteria criteria = session.createCriteria(Emp.class);
        
        Criterion criterion = Restrictions.and(Restrictions.eq("empNo", 1), Restrictions.ilike("empName","范",MatchMode.START));
        criteria=criteria.add(criterion);
        List<Emp> list = criteria.list();
        for (Emp emp : list) {
            System.out.println("职工姓名:"+emp.getEmpName()+"\t部门名称："+emp.getDept().getDeptName());
        }
        
    }

 

 7.动态查询

//动态查询
    @Test
    public void dongtaiTest(){
        Criteria criteria = session.createCriteria("cn.happy.entity.Emp");
        Emp emp=new Emp();
        emp.setEmpNo(2);
        //emp.setEmpName("邹乐");
        
        if(emp.getEmpName()!=null){
            
            criteria.add(Restrictions.eq("empName", emp.getEmpName()));
        }
        if(emp.getEmpNo()!=null){
            criteria.add(Restrictions.eq("empNo", emp.getEmpNo()));
        }
        
        List<Emp> list = criteria.list();
        for (Emp emp2 : list) {
            System.out.println("职工姓名:"+emp2.getEmpName()+"\t部门名称："+emp2.getDept().getDeptName());
        }
    }
    

 

 8.集合运算

 //集合运算
    @Test
    public void collTest(){
        Criteria criteria = session.createCriteria(Dept.class);
        criteria.add(Restrictions.isEmpty("emps"));
        List<Dept> list = criteria.list();
        for (Dept dept : list) {
            System.out.println("部门名称："+dept.getDeptName());
        }
    }

 

 9.排序

    //排序
    //按员工编号进行排序
    @Test
    public void orderByeNo(){
        Criteria criteria = session.createCriteria("cn.happy.entity.Emp");
        criteria = criteria.addOrder(Order.desc("empNo"));
        List<Emp> list = criteria.list();
        for (Emp emp : list) {
            System.out.println("职工姓名:"+emp.getEmpName()+"\t部门名称："+emp.getDept().getDeptName());
        }
        
    }
    

10.分页

    //分页  将员工信息分页查询  每页只查询三条数据
    @Test
    public void fenye(){
        Criteria criteria = session.createCriteria(Emp.class);
        CountProjection count2 = Projections.count("empNo");
        criteria.setProjection(count2);   
        Integer count = ((Long)criteria.uniqueResult()).intValue();
        System.out.println("总记录数:"+count);
        
        criteria.addOrder(Order.desc("empNo"));
        int pageSize=3;
        int pageIndex=2;
       
        criteria.setFirstResult((pageIndex-1)*pageSize);
        criteria.setMaxResults(pageSize);
        
        
        List<Emp> list = criteria.list();
        for (Emp emp : list) {
            System.out.println("员工姓名："+emp.getEmpName()+"部门名称："+emp.getDept().getDeptName());
        }
    }
    

 

11.DetachedCriteria查询

//DetachedCriteria查询
    @Test
    public void detacahedCriteria(){
        DetachedCriteria detachedCriteria=DetachedCriteria.forClass(Emp.class);
        detachedCriteria.createAlias("dept", "d");
        detachedCriteria.add(Restrictions.eq("d.deptName", "财务部"));
        List<Emp> list = detachedCriteria.getExecutableCriteria(session).list();
        for (Emp emp : list) {
            
            System.out.println(emp.getEmpName());
        }
        
    }