Hibernate中使用Criteria查询及注解——(DeptTest.java)
来源:互联网 发布:欧芹和芹菜的区别 知乎 编辑:程序博客网 时间:2024/06/08 09:40
DeptTest.java
测试类:
先创建Session:
private Session session;private Transaction tx;@Beforepublic void beforeMethod(){session=new Configuration().configure().buildSessionFactory().openSession();}
关闭Session:
@Afterpublic void afterMethod() {if (session != null) {session.close();}}
开始测试:
/**
* 测试简单查询
*/
@Testpublic void testMethod1() {//查询所有的部门Criteria criteria=session.createCriteria(Dept.class);List<Dept> deptList=criteria.list();for (Dept dept : deptList) {System.out.println("部门编号:"+dept.getDeptno());System.out.println("部门名称:"+dept.getDname());System.out.println("部门编地址:"+dept.getLoc());}}
查詢部门在山西吕梁的部门名称
//查询部门地址在山西吕梁的部门名称@Testpublic void test2(){List<Dept> deptList=session.createCriteria(Dept.class).add(Restrictions.eq("loc", "山西吕梁")).list();for (Dept dept : deptList) {System.out.println(dept.getDeptno()+","+dept.getDname());}}
//查询工资高于5000的员工
@Testpublic void test3(){List<Emp> empList=session.createCriteria(Emp.class).add(Restrictions.gt("sal", 5000D)).list();for (Emp emp : empList) {System.out.println(emp.getEname()+","+emp.getSal());}}
//查询不属于任何部门的员工
@Testpublic void test4(){List<Emp> empList=session.createCriteria(Emp.class) .add(Restrictions.isNull("dept")).list();for (Emp emp : empList) {System.out.println(emp.getEname());}}
//查询职位是CLERK的员工
//ignoreCase:不区分大小写
@Testpublic void test5(){List<Emp> empList=session.createCriteria(Emp.class) .add(Restrictions.eq("job","ClERK").ignoreCase()) .list();for (Emp emp : empList) {System.out.println(emp.getEname()+","+emp.getDept().getDname());}}
/**
* in:等于列表中的某一个值
* not in:不等于列表中的任何一个值
* between 值1 and 值2:大于等于值1并且小于值2
* not between 值1 and 值2:小于值1或者大于值2
*/
//查询职位是ANALYST 或 SALESMAN 的员工
@Testpublic void test6(){List jobList=new ArrayList();jobList.add("SALESMAN");jobList.add("ANALYST");List<Emp> empoList=session.createCriteria(Emp.class) .add(Restrictions.in("job",jobList)) .list();for (Emp emp : empoList) {System.out.println(emp.getEname()+","+emp.getJob());}}
//查询工资在2000元到4000元的员工
@Testpublic void test7(){List<Emp> empList=session.createCriteria(Emp.class) .add(Restrictions.between("sal", 2000D, 4000D)) .list();for (Emp emp : empList) {System.out.println(emp.getEname()+","+emp.getSal());}}
/**
* like:字符串模式匹配
* ilike:字符串木事匹配,忽略大小写
*/
//查询姓名中包括s的职位(like)
@Testpublic void test8(){List<Emp> empList=session.createCriteria(Emp.class) .add(Restrictions.like("job","%s%")) .list();for (Emp emp : empList) {System.out.println(emp.getEname());}}
//查询姓名中包括s的职位(ilike)
@Testpublic void test9(){List<Emp> empList=session.createCriteria(Emp.class) .add(Restrictions.ilike("job","%s%")) .list();for (Emp emp : empList) {System.out.println(emp.getEname());}}
/**
* MatchMode静态常量
* 1.MatchMode.STRART: 员工姓名以s开头
* 2.MatchMode.END: 员工姓名以s结尾
* 3.MatchMode.ANYWHERE 员工姓名中包含s
* 4.MatchMode.EXACT 员工姓名中等于s精确匹配
*/
//查询职位中包括s的职位
@Testpublic void test10(){List<Emp> empList=session.createCriteria(Emp.class) .add(Restrictions.ilike("job","%s%",MatchMode.ANYWHERE)) .list();for (Emp emp : empList) {System.out.println(emp.getJob());}}
/**
*逻辑运算
*1.and:逻辑与
*2.or:逻辑或
*3.not:逻辑非
*/
//查询职位是accountant 或者是analyert的职位名称
@Testpublic void test11(){List<Emp> empList=session.createCriteria(Emp.class) .add(Restrictions.or(Restrictions.eq("job", "analyert").ignoreCase(), Restrictions.eq("job","accountant").ignoreCase())) .list();for (Emp emp : empList) {System.out.println(emp.getJob());}}
//查询职位是accountant 或者是analyert 或者是 engineer的职位名称
@Testpublic void test12(){List<Emp> empList=session.createCriteria(Emp.class) .add(Restrictions.disjunction() .add(Restrictions.eq("job", "analyert").ignoreCase()) .add(Restrictions.eq("job","accountant").ignoreCase()) .add(Restrictions.eq("job", "engineer").ignoreCase())) .list();for (Emp emp : empList) {System.out.println(emp.getJob());}}
/**
* 集合运算符
* 1. is empty:集合为空,不包含任何元素
* 2. is not empty:集合不为空
*/
//查询没有员工的部门
@Testpublic void test13(){List<Dept> deptList=session.createCriteria(Dept.class) .add(Restrictions.isEmpty("emps")) .list();for (Dept dept : deptList) {System.out.println(dept.getDname());}}
/**
* 动态查询
* 条件见下:(条件类:EmpCondition)
* 1.job是engineer
* 2.sal大于2000
* 3.入职时间在2006年12月31日到2008年12月31日之前
*/
@Testpublic void test14(){SimpleDateFormat format=new SimpleDateFormat("yyyy-MM-dd");EmpCondition empCondition=new EmpCondition();try {empCondition.setJob("engineer");empCondition.setSal(2000D);String hiredateStart="2006-12-31";String hiredateEnd="2008-12-31";Date hiredateStart1=format.parse(hiredateStart);Date hiredateEnd1=format.parse(hiredateEnd);empCondition.setHiredateStart(hiredateStart1);empCondition.setHiredateEnd(hiredateEnd1);} catch (ParseException e) {// TODO Auto-generated catch blocke.printStackTrace();}
//添加动态查询的条件
Criteria criteria=session.createCriteria(Emp.class);if(empCondition.getJob()!=null){criteria.add(Restrictions.ilike("job", empCondition.getJob()));}if(empCondition.getSal()!=null){criteria.add(Restrictions.gt("sal",empCondition.getSal()));}if(empCondition.getHiredateStart()!=null){criteria.add(Restrictions.ge("hiredate",empCondition.getHiredateStart()));}if(empCondition.getHiredateEnd()!=null){criteria.add(Restrictions.le("hiredate",empCondition.getHiredateEnd()));}//输出 List<Emp>emplist=criteria.list(); for (Emp emp : emplist) { System.err.println(emp.getEname()+","+emp.getJob()+","+emp.getSal()+","+emp.getHiredate()); } }
/**
* 分页查询
*/
//分页查询部门是SALESMAN 的所有员工
//并且工资降序
//显示总页数,总记录数,当前页号
@Testpublic void test15(){Criteria criteria =session.createCriteria(Emp.class) .add(Restrictions.eq("job", "SALESMAN").ignoreCase()) .setProjection(Projections.count("empno"));//获得总记录数Integer count=(Integer)criteria.uniqueResult();//分页int pageSize=2;int pageIndex=1;int totalPage=count%pageSize==0?count/pageSize:(count/pageSize)+1;criteria=session.createCriteria(Emp.class) .add(Restrictions.eq("job", "SALESMAN").ignoreCase()) .addOrder(Order.desc("sal"));//开始分页,设置从哪条记录开始查List<Emp> emplist=criteria.setFirstResult((pageIndex-1)*pageSize) .setMaxResults(pageSize) .list();for (Emp emp : emplist) {System.out.println(emp.getEname()+","+emp.getSal());}System.out.println("总页数:"+totalPage);System.err.println("总记录数:"+count);System.out.println("当前页号:"+pageIndex);}
/**
* 连接查询
* Criteria里面只有内连接和迫切做外链接
*/
//查询RESEARCH部们中姓名包括s的员工
@Testpublic void test16(){List<Emp> emplistList=session.createCriteria(Emp.class) .add(Restrictions.ilike("ename", "s",MatchMode.ANYWHERE)) .createCriteria("dept") .add(Restrictions.eq("dname","RESEARCH").ignoreCase()) .list();for (Emp emp : emplistList) {System.out.println(emp.getEname()+","+emp.getDept().getDname());}}//查询RESEARCH部门中姓名包含s的员工@Testpublic void test17(){List<Emp> empList=session.createCriteria(Emp.class,"e") .createAlias("dept", "d") .add(Restrictions.ilike("e.ename","s",MatchMode.ANYWHERE)) .add(Restrictions.eq("d.dname","RESEARCH").ignoreCase()) .list();for (Emp emp : empList) {System.out.println(emp.getEname()+","+emp.getDept().getDname());}}
//左外连接
//查询位置在山西吕梁的部门及其人数
//FetchMode.JOIN表示迫切做外链接查询策略
@Testpublic void test18(){List<Dept> deptList=session.createCriteria(Dept.class,"d") .setFetchMode("emps",FetchMode.JOIN) .add(Restrictions.eq("d.loc","山西吕梁").ignoreCase()) .list();for (Dept dept : deptList) {System.out.println(dept.getDname()+","+dept.getLoc()+","+dept.getEmps().size());}}
//去掉重复数据
@Testpublic void test19(){List<Dept> deptList=session.createCriteria(Dept.class,"d") .setFetchMode("emps", FetchMode.JOIN) .add(Restrictions.eq("d.loc","山西吕梁").ignoreCase()) .list();HashSet<Dept> set=new HashSet<Dept>(deptList);for (Dept dept : set) {System.out.println(dept.getDname()+","+dept.getLoc()+","+dept.getEmps().size());}}
/**
* 投影,分组,及DetachedCriteria
*/
//查询所有的部门名称】
@Testpublic void test20(){List<String> list=session.createCriteria(Dept.class) .setProjection(Property.forName("dname")) .list();for (String str : list) {System.out.println(str);}}
//查询两个或者两个以上的属性
//查询员工的姓名和入职时间
@Testpublic void test21(){List<Object[]> list=session.createCriteria(Emp.class) .setProjection( Projections.projectionList() .add(Property.forName("ename")) .add(Property.forName("hiredate")) ) .list();for (Object[] objects : list) {System.out.println(objects[0]+","+objects[1]);} }
/**
* 分组
* 1.groupProperty:分组
* 2.rowCount():统计记录数
* 3.avg:平均值
* 4.max:最大值
* 5.min:最小值
* 6.count:统计某一字段的非空记录数
* 7.sum:针对某一字段进行求和
*/
//统计各个部门的平均工资,最高工资,最低工资
@Testpublic void test22(){List<Object[]> list=session.createCriteria(Emp.class,"e") .createAlias("e.dept", "d") .setProjection( Projections.projectionList() .add(Projections.groupProperty("d.dname")) .add(Projections.avg("e.sal")) .add(Projections.max("e.sal")) .add(Projections.min("e.sal")) ) .list();for (Object[] objects : list) {System.out.println("部门名称"+objects[0]+",最高工资:"+objects[2]+"" +",平均工资:"+objects[1]+",最低工资:"+objects[3]);} }
//使用DetachedCriteria
//查询RESEARCH部门,姓名包含“a”的员工
@Testpublic void test23(){DetachedCriteria detachedCriteria =DetachedCriteria.forClass(Emp.class,"e") .createAlias("e.dept","d") .add(Restrictions.eq("d.dname","RESEARCH").ignoreCase()) .add(Restrictions.ilike("e.ename","a",MatchMode.ANYWHERE));List<Emp> list=detachedCriteria.getExecutableCriteria(session).list();for (Emp emp : list) {System.out.println(emp.getEname()+","+emp.getDept().getDname());} }
//查询工资高于平均工资的员工
@Testpublic void test24(){//平均工资DetachedCriteria detachedCriteria=DetachedCriteria .forClass(Emp.class,"e") .setProjection(Property.forName("sal").avg());//求工资大于平均工资List<Emp> emplist=session.createCriteria(Emp.class) .add(Property.forName("sal").gt(detachedCriteria)).list();for (Emp emp : emplist) {System.out.println(emp.getEname()+","+emp.getSal());}}
0 0
- Hibernate中使用Criteria查询及注解——(DeptTest.java)
- Hibernate中使用Criteria查询及注解——(Dept.java)
- Hibernate中使用Criteria查询及注解——(Emp.java)
- Hibernate中使用Criteria查询及注解——(hibernate.cfg.xml)
- Hibernate中使用Criteria查询及注解——(Emp.hbm.xml)
- Hibernate中使用Criteria查询及注解——(Dept.hbm.xml)
- Hibernate中使用Criteria查询及注解——(HibernateUtil)
- Hibernate中使用Criteria查询及注解——( EmpCondition)
- Hibernate中Criteria的使用(条件查询)
- Hibernate中使用Criteria查询实例
- hibernate中criteria查询
- hibernate中Criteria查询
- hibernate查询——criteria查询
- hibernate(十)HQL查询及Criteria条件查询
- Hibernate使用Criteria实现查询
- JAVA Hibernate 条件查询 Criteria
- Hibernate学习总结(6)——Criteria查询
- Hibernate(三)Criteria查询
- Hibernate中使用Criteria查询及注解——(HibernateUtil)
- php进阶
- 报错JedisConnectionException: Could not get a resource from the pool
- 蓝牙BLE 广播数据
- Async/Await,最佳做法..netframework4.5
- Hibernate中使用Criteria查询及注解——(DeptTest.java)
- C++——多态总结
- mybatis——【持久化框架】Mybatis简介与原理
- PTA 朋友圈
- UEditor在JavaWeb中的应用
- 产业互联网公司,做产品决策的三个步骤
- leetcode_53. Maximum Subarray-子数组最大和
- NYOJ 325 zb的生日
- hdu 3829 Cat VS Dog(最大独立集)