本文共 14721 字，大约阅读时间需要 49 分钟。
刚接触Hibernate的时候，就有一个概念，全自动的ORM框架，不用写SQL语句，但是实际我们还是有了另一个名词HQL，这难道是来搞笑的吗？
其实不然，当你接触到Criteria这个名词的时候，你就知道全自动的魅力了
全自动，无需sql，hql，它以Java OOP的思想来操作数据库
使用简单，上手快
他对sql语句进行了高级的封装，所以性能不高
对于特别复杂的sql，Criteria无能为力，不过hibernate有sql和hql帮他善后
@Test /*查询所有的部门信息*/ public void t1CriteriaSelectAll(){ Criteria criteria = session.createCriteria(Dept.class); List 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楼'} * */ }
@Test /*查询薪水大于10k的员工信息*/ public void t3CriteriaSalGt10000(){ Criteria criteria = session.createCriteria(Emp.class); /*加条件*/ criteria.add(Restrictions.gt("sal",10000d)); List 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} * */ }
@Test /*查询薪水在5k----10k之间的，between*/ public void t4CriteriaSalBetween(){ Criteria criteria = session.createCriteria(Emp.class); /*加条件*/ criteria.add(Restrictions.between("sal",5000d,10000d)); List 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楼'}} * */ }
@Test /*查询没有部门的员工*/ /*查询一的一方没有*/ public void t5CriteriaisNull(){ Criteria criteria = session.createCriteria(Emp.class); /*加条件*/ criteria.add(Restrictions.isNull("dept")); List 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} * */ }
@Test /*查询没有员工的部门*/ /*查询多的一方没有*/ public void t6CriteriaisEmpty(){ Criteria criteria = session.createCriteria(Dept.class); /*加条件*/ criteria.add(Restrictions.isEmpty("emps")); List 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楼'} * */ }
@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 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楼'}} * */ }
@Test /*查询职位是程序猿1或者是财务猿1的员工信息，使用in*/ public void t8CriteriaIn(){ Criteria criteria = session.createCriteria(Emp.class); /*加条件*/ List jobs=new ArrayList (); jobs.add("程序猿1"); jobs.add("财务猿1"); criteria.add(Restrictions.in("job",jobs)); List 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楼'}} * */ }
@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 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楼'}} * */ }
@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 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楼'}} * */ }
@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 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 * */ }
@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 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和 Criteria的区别 * 相同点：都能用来 做查询操作 * 不同点： * 01.DetachedCriteria在创建的时候 不需要session！ * 02.真正执行查询的时候getExecutableCriteria(session)才使用session * 03.DetachedCriteria自身可以作为一个参数
@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 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} * * */ }