Hibernate-ORM:15.Hibernate中的Criteria查询

 

 

 

------------吾亦无他,唯手熟尔，谦卑若愚，好学若饥-------------

 

 

 

 

本篇博客讲师Hibernate中的Criteria查询！

一，Criteria简介：

　　刚接触Hibernate的时候，就有一个概念，全自动的ORM框架，不用写SQL语句，但是实际我们还是有了另一个名词HQL，这难道是来搞笑的吗？

　　其实不然，当你接触到Criteria这个名词的时候，你就知道全自动的魅力了

二，Criteria的优缺点：

　　优点：

　　　　全自动，无需sql，hql，它以Java OOP的思想来操作数据库

　　　　使用简单，上手快

　　缺点：

　　　　他对sql语句进行了高级的封装，所以性能不高

　　　　对于特别复杂的sql，Criteria无能为力，不过hibernate有sql和hql帮他善后

三，具体使用

　　我将使用Criteria做 13 个案例，方便大家学习和查阅

　　1.使用Criteria查询全部

 

    @Test
    /*查询所有的部门信息*/
    public void t1CriteriaSelectAll(){
        Criteria criteria = session.createCriteria(Dept.class);
        List<Dept> depts = criteria.list();
        for (Dept dept:depts){
            System.out.println(dept);
        }
        /*
        Hibernate: select this_.deptId as deptId1_0_0_, this_.name as name2_0_0_, this_.location as location3_0_0_ from Dept this_
        Dept{deptId=1, name='xx部', location='1楼'}
        Dept{deptId=2, name='研发部', location='2楼'}
        Dept{deptId=3, name='销售部', location='3楼'}
        * */
    }

 

　　2.带条件查询（=　　等于的操作）

 

    @Test
    /*带条件查询，查name=研发部的部门信息
    * *******************************
    * criteria.add(Criterion类型)
    * Criterion 是一个接口  , 规范
    * Restrictions 是一个类， 约束，给我们的查询增加各种条件
    * Restrictions所有的方法返回值都是Criterion或者是其实现类，方法的修饰符都是static
    * */
    public void t2CriteriaSelectDeptByParamter(){
        Criteria criteria = session.createCriteria(Dept.class);
        /*加条件*/
        criteria.add(Restrictions.eq("name","研发部"));
        Dept dept = (Dept) criteria.uniqueResult();
        System.out.println(dept);

        /*
        Hibernate: select this_.deptId as deptId1_0_0_, this_.name as name2_0_0_, this_.location as location3_0_0_ from Dept this_ where this_.name=?
        Dept{deptId=2, name='研发部', location='2楼'}
        * */
    }

 

　　3.带条件查询（>　　大于的操作）

 

    @Test
    /*查询薪水大于10k的员工信息*/
    public void t3CriteriaSalGt10000(){
        Criteria criteria = session.createCriteria(Emp.class);
        /*加条件*/
        criteria.add(Restrictions.gt("sal",10000d));
        List<Emp> emps = criteria.list();
        for (Emp emp:emps){
            System.out.println(emp);
        }

        /*
        Hibernate: select this_.empId as empId1_1_0_, this_.name as name2_1_0_, this_.sal as sal3_1_0_, this_.job as job4_1_0_, this_.deptNo as deptNo5_1_0_ from Emp this_ where this_.sal>?
        Hibernate: select dept0_.deptId as deptId1_0_0_, dept0_.name as name2_0_0_, dept0_.location as location3_0_0_ from Dept dept0_ where dept0_.deptId=?
        Emp{empId=4, name='aaA', job='程序猿1', sal=100000.0, dept=Dept{deptId=2, name='研发部', location='2楼'}}
        Emp{empId=5, name='aB', job='程序猿2', sal=50000.0, dept=Dept{deptId=2, name='研发部', location='2楼'}}
        Emp{empId=6, name='AC', job='程序猿3', sal=60000.0, dept=Dept{deptId=2, name='研发部', location='2楼'}}
        Emp{empId=7, name='AD', job='Boss', sal=5000000.0, dept=null}
        * */
    }

 

　　4.带条件查询（between　　在俩者之间的操作）

 

    @Test
    /*查询薪水在5k----10k之间的，between*/
    public void t4CriteriaSalBetween(){
        Criteria criteria = session.createCriteria(Emp.class);
        /*加条件*/
        criteria.add(Restrictions.between("sal",5000d,10000d));
        List<Emp> emps = criteria.list();
        for (Emp emp:emps){
            System.out.println(emp);
        }

        /*
        Hibernate: select this_.empId as empId1_1_0_, this_.name as name2_1_0_, this_.sal as sal3_1_0_, this_.job as job4_1_0_, this_.deptNo as deptNo5_1_0_ from Emp this_ where this_.sal between ? and ?
        Hibernate: select dept0_.deptId as deptId1_0_0_, dept0_.name as name2_0_0_, dept0_.location as location3_0_0_ from Dept dept0_ where dept0_.deptId=?
        Emp{empId=1, name='a', job='财务猿1', sal=10000.0, dept=Dept{deptId=1, name='xx部', location='1楼'}}
        Emp{empId=2, name='Ab', job='财务猿2', sal=5000.0, dept=Dept{deptId=1, name='xx部', location='1楼'}}
        Emp{empId=3, name='bAa', job='财务猿3', sal=6000.0, dept=Dept{deptId=1, name='xx部', location='1楼'}}
        * */
    }

 

　　5.查询表关联中一对多，一的一方没有数据的

 

    @Test
    /*查询没有部门的员工*/
    /*查询一的一方没有*/
    public void t5CriteriaisNull(){
        Criteria criteria = session.createCriteria(Emp.class);
        /*加条件*/
        criteria.add(Restrictions.isNull("dept"));
        List<Emp> emps = criteria.list();
        for (Emp emp:emps){
            System.out.println(emp);
        }

        /*
        Hibernate: select this_.empId as empId1_1_0_, this_.name as name2_1_0_, this_.sal as sal3_1_0_, this_.job as job4_1_0_, this_.deptNo as deptNo5_1_0_ from Emp this_ where this_.deptNo is null
        Emp{empId=7, name='AD', job='Boss', sal=5000000.0, dept=null}
        * */
    }

 

　　6.查询表关联中一对多，多的一方没有数据的

 

    @Test
    /*查询没有员工的部门*/
    /*查询多的一方没有*/
    public void t6CriteriaisEmpty(){
        Criteria criteria = session.createCriteria(Dept.class);
        /*加条件*/
        criteria.add(Restrictions.isEmpty("emps"));
        List<Dept> depts = criteria.list();
        for (Dept dept:depts){
            System.out.println(dept);
        }

        /*
       Hibernate: select this_.deptId as deptId1_0_0_, this_.name as name2_0_0_, this_.location as location3_0_0_ from Dept this_ where not exists (select 1 from Emp where this_.deptId=deptNo)
        Dept{deptId=3, name='销售部', location='3楼'}
        * */
    }

 

　　7.查询使用or（就是什么或者什么，只要满足一个即可）

 

    @Test
    /*查询职位是程序猿1或者是财务猿1的员工信息，使用or*/
    public void t7CriteriaOr(){
        Criteria criteria = session.createCriteria(Emp.class);
        /*加条件*/
        criteria.add(Restrictions.or(
                Restrictions.eq("job","程序猿1"),
                Restrictions.eq("job","财务猿1")
        ));
        List<Emp> emps = criteria.list();
        for (Emp emp:emps){
            System.out.println(emp);
        }

        /*
        Hibernate: select this_.empId as empId1_1_0_, this_.name as name2_1_0_, this_.sal as sal3_1_0_, this_.job as job4_1_0_, this_.deptNo as deptNo5_1_0_ from Emp this_ where (this_.job=? or this_.job=?)
        Hibernate: select dept0_.deptId as deptId1_0_0_, dept0_.name as name2_0_0_, dept0_.location as location3_0_0_ from Dept dept0_ where dept0_.deptId=?
        Hibernate: select dept0_.deptId as deptId1_0_0_, dept0_.name as name2_0_0_, dept0_.location as location3_0_0_ from Dept dept0_ where dept0_.deptId=?
        Emp{empId=1, name='a', job='财务猿1', sal=10000.0, dept=Dept{deptId=1, name='xx部', location='1楼'}}
        Emp{empId=4, name='aaA', job='程序猿1', sal=100000.0, dept=Dept{deptId=2, name='研发部', location='2楼'}}
        * */
    }

 

　　8.查询使用in（就是等于　　在多个值其中一个的时候　　再查询展示）

 

    @Test
    /*查询职位是程序猿1或者是财务猿1的员工信息，使用in*/
    public void t8CriteriaIn(){
        Criteria criteria = session.createCriteria(Emp.class);
        /*加条件*/
        List<String> jobs=new ArrayList<String>();
        jobs.add("程序猿1");
        jobs.add("财务猿1");
        criteria.add(Restrictions.in("job",jobs));
        List<Emp> emps = criteria.list();
        for (Emp emp:emps){
            System.out.println(emp);
        }

        /*
        Hibernate: select this_.empId as empId1_1_0_, this_.name as name2_1_0_, this_.sal as sal3_1_0_, this_.job as job4_1_0_, this_.deptNo as deptNo5_1_0_ from Emp this_ where this_.job in (?, ?)
        Hibernate: select dept0_.deptId as deptId1_0_0_, dept0_.name as name2_0_0_, dept0_.location as location3_0_0_ from Dept dept0_ where dept0_.deptId=?
        Hibernate: select dept0_.deptId as deptId1_0_0_, dept0_.name as name2_0_0_, dept0_.location as location3_0_0_ from Dept dept0_ where dept0_.deptId=?
        Emp{empId=1, name='a', job='财务猿1', sal=10000.0, dept=Dept{deptId=1, name='xx部', location='1楼'}}
        Emp{empId=4, name='aaA', job='程序猿1', sal=100000.0, dept=Dept{deptId=2, name='研发部', location='2楼'}}
        * */
    }

 

　　9.and操作（可以多个条件）

 

    @Test
    /*这个disjunction()后可以用add来拼接多个条件*/
    /*查询职位是程序猿1或者是财务猿1的员工信息，
     * 查询职务是 程序猿1或者是财务猿1的  员工信息  使用  disJunction
     *
     *
     * Restrictions.disjunction  返回值是一个 DisJunction 类
     * DisJunction 类 extends  Junction 类
     *  Junction 类有一个方法叫add（）===》criteria.add（）
     *
     *  public Junction add(Criterion criterion) {
         criteria.add(criterion);
         return this;
         }
     */
    public void t9CriteriaAdd(){
        Criteria criteria = session.createCriteria(Emp.class);
        /*加条件*/
        /*.add可以拼接多个条件*/
        criteria.add(Restrictions.disjunction().add(Restrictions.eq("job","程序猿1")).add(Restrictions.eq("job","财务猿1")));
        List<Emp> emps = criteria.list();
        for (Emp emp:emps){
            System.out.println(emp);
        }

        /*
        Hibernate: select this_.empId as empId1_1_0_, this_.name as name2_1_0_, this_.sal as sal3_1_0_, this_.job as job4_1_0_, this_.deptNo as deptNo5_1_0_ from Emp this_ where (this_.job=? or this_.job=?)
        Hibernate: select dept0_.deptId as deptId1_0_0_, dept0_.name as name2_0_0_, dept0_.location as location3_0_0_ from Dept dept0_ where dept0_.deptId=?
        Hibernate: select dept0_.deptId as deptId1_0_0_, dept0_.name as name2_0_0_, dept0_.location as location3_0_0_ from Dept dept0_ where dept0_.deptId=?
        Emp{empId=1, name='a', job='财务猿1', sal=10000.0, dept=Dept{deptId=1, name='xx部', location='1楼'}}
        Emp{empId=4, name='aaA', job='程序猿1', sal=100000.0, dept=Dept{deptId=2, name='研发部', location='2楼'}}
        * */
    }

 

　　10.模糊查询like（或者ilike）

 

    @Test
    /**
     *  like 和 ilike的区别
     *
     *  like  模糊查询
     *  ilike 模糊并且忽略大小写查询
     *
     *  MatchMode:  我们的value值出现的位置
     *  anywhere: 前后
     *  start: 前
     *  end: 后
     */
    public void t10CriteriaLike(){
        Criteria criteria = session.createCriteria(Emp.class);
        /*加条件*/
        /*.add可以拼接多个条件*/
        criteria.add(Restrictions.like("name","b", MatchMode.END));
        List<Emp> emps = criteria.list();
        for (Emp emp:emps){
            System.out.println(emp);
        }

        /*
        Hibernate: select this_.empId as empId1_1_0_, this_.name as name2_1_0_, this_.sal as sal3_1_0_, this_.job as job4_1_0_, this_.deptNo as deptNo5_1_0_ from Emp this_ where this_.name like ?
        Hibernate: select dept0_.deptId as deptId1_0_0_, dept0_.name as name2_0_0_, dept0_.location as location3_0_0_ from Dept dept0_ where dept0_.deptId=?
        Hibernate: select dept0_.deptId as deptId1_0_0_, dept0_.name as name2_0_0_, dept0_.location as location3_0_0_ from Dept dept0_ where dept0_.deptId=?
        Emp{empId=2, name='Ab', job='财务猿2', sal=5000.0, dept=Dept{deptId=1, name='xx部', location='1楼'}}
        Emp{empId=5, name='aB', job='程序猿2', sal=50000.0, dept=Dept{deptId=2, name='研发部', location='2楼'}}
        * */
    }

 

　　11.聚合函数（avg(),sum(),count(),max(),min()这些）

 

    @Test
    /**
     *   聚合函数
     *   setProjection 需要我们传递一个Projection
     *   Projections类中的所有方法返回值都是Projection或者其实现类
     *   如果设值之后，没有清空，那么之前的参数会被带入下次的查询！
     */
    public void t11CriteriaProjectionList(){
        Criteria criteria = session.createCriteria(Emp.class);
        /*加条件*/
        /*.add可以拼接多个条件*/
        criteria.setProjection(Projections.projectionList()
            .add(Projections.max("sal"))
            .add(Projections.min("sal"))
            .add(Projections.avg("sal"))
            .add(Projections.sum("sal"))
        );
        List<Object[]> list = criteria.list();
        for (Object[] o:list){
            System.out.println("最高薪水："+o[0]);
            System.out.println("最低薪水："+o[1]);
            System.out.println("平均薪水："+o[2]);
            System.out.println("总薪水："+o[3]);
        }

        /*
        Hibernate: select max(this_.sal) as y0_, min(this_.sal) as y1_, avg(this_.sal) as y2_, sum(this_.sal) as y3_ from Emp this_
        最高薪水：5000000.0
        最低薪水：5000.0
        平均薪水：747285.7142857143
        总薪水：5231000.0
        * */
    }

 

　　12.带条件的分页+降序排（使用Criteria查询）

 

    @Test
    /*
    * 查询姓名中包含b的员工，并且按照薪水降序排序
    * */
    public void t12CriteriaPage(){
        int count=((Long)session.createCriteria(Emp.class)
            .add(Restrictions.ilike("name","b",MatchMode.ANYWHERE))
                .setProjection(Projections.count("name")).uniqueResult()).intValue();
        System.out.println(count);
        //设置当前页和页大小
        int pageIndex=2;
        int pageSize=2;
        //计算总页数
        int totalPage=(count%pageSize==0)?(count/pageSize):(count/pageSize+1);
        //根据薪水进行降序排序
        Criteria criteria = session.createCriteria(Emp.class)
                .add(Restrictions.ilike("name","b",MatchMode.ANYWHERE))
                .addOrder(Order.desc("sal"));
        //设置 起始页和页大小
        List<Emp> emps=criteria.setFirstResult((pageIndex-1)*pageSize)
                .setMaxResults(pageSize).list();
        /*遍历*/
        for (Emp emp:emps){
            System.out.println(emp);
        }

        /*
        Hibernate: select count(this_.name) as y0_ from Emp this_ where lower(this_.name) like ?
        3
        Hibernate: select this_.empId as empId1_1_0_, this_.name as name2_1_0_, this_.sal as sal3_1_0_, this_.job as job4_1_0_, this_.deptNo as deptNo5_1_0_ from Emp this_ where lower(this_.name) like ? order by this_.sal desc limit ?, ?
        Hibernate: select dept0_.deptId as deptId1_0_0_, dept0_.name as name2_0_0_, dept0_.location as location3_0_0_ from Dept dept0_ where dept0_.deptId=?
        Emp{empId=2, name='Ab', job='财务猿2', sal=5000.0, dept=Dept{deptId=1, name='xx部', location='1楼'}}
        * */
    }

 

四，DetachedCriteria的使用

　　1.DetachedCriteria和Criteria 的相同与区别

　　　　* DetachedCriteria和 Criteria的区别
　　　　* 相同点：都能用来 做查询操作
　　　　* 不同点：
　　　　* 01.DetachedCriteria在创建的时候 不需要session！
　　　　* 02.真正执行查询的时候getExecutableCriteria(session)才使用session
　　　　* 03.DetachedCriteria自身可以作为一个参数

 

　　2.DetacjedCriteria的具体使用案例

 

    @Test
    /*
    *  DetachedCriteria和 Criteria的区别
     * 相同点：都能用来 做查询操作
     * 不同点：
     *    01.DetachedCriteria在创建的时候 不需要session！
     *    02.真正执行查询的时候getExecutableCriteria(session)才使用session
     *    03.DetachedCriteria自身可以作为一个参数
     *
     *  薪水  大于    平均值的员工信息
    * */
    public void t13DetachedCriteria(){
        //得到DetachedCriteria对象
        DetachedCriteria criteria=DetachedCriteria.forClass(Emp.class)
                .setProjection(Projections.avg("sal"));
        /*执行查询*/
        double avg=(Double)criteria.getExecutableCriteria(session).uniqueResult();
        System.out.println("薪水的平均值是："+avg);

        /*薪水大于 平均值的员工信息*/
        List<Emp> list=session.createCriteria(Emp.class)
                .add(Property.forName("sal").gt(criteria))
                .list();
        for (Emp emp:list){
            System.out.println(emp);
        }

        /*
        Hibernate: select avg(this_.sal) as y0_ from Emp this_
        薪水的平均值是：747285.7142857143
        Hibernate: select this_.empId as empId1_1_0_, this_.name as name2_1_0_, this_.sal as sal3_1_0_, this_.job as job4_1_0_, this_.deptNo as deptNo5_1_0_ from Emp this_ where this_.sal > (select avg(this_.sal) as y0_ from Emp this_)
        Emp{empId=7, name='AD', job='Boss', sal=5000000.0, dept=null}
        *
        * */
    }

 

 

 

 

 

作者：晨曦Dawn

转载请注明出处，博客地址：https://www.cnblogs.com/DawnCHENXI/p/9141579.html

如果上方博客有错误，请您指出，感激不尽！！！！！！！！！！！！！！！！！！！！！！！