package junit.test;
import hibch05.dao.HibernateSessionFactory;
import hibch05.pojo.Dept;
import hibch05.pojo.Emp;
import java.util.Iterator;
import java.util.List;
import java.util.Set;
import org.hibernate.Query;
import org.hibernate.Session;
import org.junit.Test;
public class Ch05Test3 {
@Test
public void test() {
Session session = HibernateSessionFactory.getSession();
// 获取Query对象
Query query = session.createQuery("from Emp");
query.setFirstResult((3 - 1) * 5);
query.setMaxResults(5);
List<Emp> list = query.list();
for (Emp emp : list) {
System.out.println(emp.getEname());
}
}
/*
* left join
*/
@Test
public void test2() {
Session session = HibernateSessionFactory.getSession();
String hql = "from Emp e left join fetch e.dept";
// 获取Query对象
Query query = session.createQuery(hql);
List<Emp> list = query.list();
for (Emp emp : list) {
System.out.println(emp.getEname() + " " + emp.getDept().getDname());
}
// System.out.println(list.size());
}
/*
* 子查询- 查询工资高于平均工资的员工
*/
@Test
public void test3() {
Session session = HibernateSessionFactory.getSession();
String hql = "from Emp e where e.sal>(select avg(sal) from Emp)";
Query query = session.createQuery(hql);
List<Emp> list = query.list();
for (Emp emp : list) {
System.out.println(emp.getEname() + " " + emp.getSal());
}
}
/*
* 子查询- 查询所有员工工资都小于5000的部门
*/
@Test
public void test4() {
Session session = HibernateSessionFactory.getSession();
String hql = "from Dept d where 5000>all(select e.sal from d.emps e) and d.emps.size>0";
Query query = session.createQuery(hql);
List<Dept> list = query.list();
for (Dept dept : list) {
System.out.println(dept.getDname() + " " + dept.getDeptno());
}
}
/*
* 子查询- 查询至少有一个员工工资低于5000的部门
*/
@Test
public void test5() {
Session session = HibernateSessionFactory.getSession();
String hql = "from Dept d where 5000>any(select e.sal from d.emps e)";
Query query = session.createQuery(hql);
List<Dept> list = query.list();
for (Dept dept : list) {
System.out.println(dept.getDname() + " " + dept.getDeptno());
}
}
/*
* 子查询- 查询有员工工资正好等于5000的部门 (方法一)
*/
@Test
public void test6() {
Session session = HibernateSessionFactory.getSession();
String hql = "from Dept d where 5000=some(select e.sal from d.emps e)";
Query query = session.createQuery(hql);
List<Dept> list = query.list();
for (Dept dept : list) {
System.out.println(dept.getDname() + " " + dept.getDeptno());
}
}
/*
* 子查询- 查询有员工工资正好等于5000的部门 (方法二)
*/
@Test
public void test7() {
Session session = HibernateSessionFactory.getSession();
String hql = "from Dept d where 5000 in(select e.sal from d.emps e)";
Query query = session.createQuery(hql);
List<Dept> list = query.list();
for (Dept dept : list) {
System.out.println(dept.getDname() + " " + dept.getDeptno());
}
}
/*
* 子查询- 查询至少有一位员工的部门
*/
@Test
public void test8() {
Session session = HibernateSessionFactory.getSession();
String hql = "from Dept d where exists (from d.emps)";
Query query = session.createQuery(hql);
List<Dept> list = query.list();
for (Dept dept : list) {
System.out.println(dept.getDname() + " " + dept.getDeptno());
}
}
/*
* 子查询- 查询指定员工的部门
*/
@Test
public void test9() {
Session session = HibernateSessionFactory.getSession();
String hql = "from Dept d where ? in (from d.emps)";
Query query = session.createQuery(hql);
Emp emp = new Emp();
emp.setEmpno(new Short("7788"));
query.setParameter(0, emp);
List<Dept> list = query.list();
for (Dept dept : list) {
System.out.println(dept.getDname() + " " + dept.getDeptno());
}
}
/*
* 子查询- 查询员工个数大于5的部门
*/
@Test
public void test10() {
Session session = HibernateSessionFactory.getSession();
String hql = "from Dept d where d.emps.size>5";
String hql2 = "from Dept d where size(d.emps)>5";
Query query = session.createQuery(hql);
List<Dept> list = query.list();
for (Dept dept : list) {
System.out.println(dept.getDname() + " " + dept.getDeptno());
}
}
}
Hibernate-HQL查询(2)子查询、连接查询
最新推荐文章于 2020-03-29 17:45:00 发布