HQL之多表查询（一对多和多对多）

一、一对多

以班级Classes和学生Student为例：

sql语句:

//内链接,两种方式效果一样,查询的是两边都有的数据 SELECT c.,s. FROM classes c,student s WHERE s.cid=c.cid;

SELECT c.cname,s.sname FROM classes c INNER JOIN student s ON s.cid=c.cid;

//左外连接，在内链接基础上，左边表有而右边表没有，两种方式等效； SELECT c.* ,s.* FROM student s LEFT OUTER JOIN classes c ON s.cid=c.cid; SELECT c.* ,s.* FROM student s LEFT JOIN classes c ON s.cid=c.cid;

//右外连接，在内链接基础上，右边有而左边无，两种方式等效； SELECT c.* ,s.* FROM classes c RIGHT OUTER JOIN student s ON s.cid=c.cid; SELECT c.* ,s.* FROM classes c RIGHT JOIN student s ON s.cid=c.cid;

HQL语句：

//查询所有： from Classes c,Student s where c.cid=s.classes.cid； //选择某些属性查询 select c.cname,s.sname from Classes c,Student s where c.cid=s.classes.cid； //选择某些属性，封装为bean查询； select new cn.chen.system.model.ClassVsStudent(c.cname,s.sname) from Classes c,Student s where c.cid=s.cid； //内链接查询，得到的是两个bean from Classes c inner join c.students s； //内敛链接查询，得到的是Classes对象，对象中包含studet集合 from Classes c inner join fetch c.students s；

from Student s inner join fetch s.classes c；

select new cn.chen.system.model.ClassVsStudent(c.cname,s.sname) from Student s inner join s.classes c ；

from Classes c left outer join fetch c.students s；

from Student s left outer join fetch s.classes；

实例代码

/** * 1.一对多 * sql:select c.*,s.* from classes c,student s where c.cid=s.cid; * hql:from Classes c,Student s where c.cid=s.cid，注意与上句的区别； * 得到的list是object[]，数组中的元素是Classes和Student对象； * */ @Test public void testOneToMany_EQ(){ Session session = sessionFactory.openSession(); Query query = session.createQuery( "from Classes c,Student s where c.cid=s.cid"); List list = query.list(); System. out.println(query.list().size()); session.close(); } /** * 2.带属性的查询； * list中装的是object[]; */ @Test public void testOneToMany_EQ_Property(){ Session session = sessionFactory.openSession(); Query query = session.createQuery( "select c.cname,s.sname from Classes c,Student s where c.cid=s.cid"); query.list(); session.close(); } /** * 3.带属性查询，将查询结果封装成一个bean； * 得到的list中装的是classView对象； */ @Test public void testOneToMany_EQ_Property_Constructor(){ Session session = sessionFactory.openSession(); Query query = session.createQuery( "select new cn.chen.system.model.ClassVsStudent(c.cname,s.sname) " + "from Classes c,Student s where c.cid=s.classes.cid"); List list = query.list(); session.close(); } /** * 4.内连接 * 结果与例子1一样； */ @Test public void testOneToMany_InnerJoin_Query(){ Session session = sessionFactory.openSession(); StringBuffer buffer = new StringBuffer(); buffer.append( "from Classes c inner join c.students s"); Query query = session.createQuery(buffer.toString()); query.list(); session.close(); } /** * 5.迫切内连接1：获取所有有学生的班级及班级下的学生； * 要想得到的集合中装的Classes对象，对象中set集合中装student，可以使用迫切内链接。 * */ @Test public void testOneToMany_InnerJoin_Fetch_Query_1(){ Session session = sessionFactory.openSession(); StringBuffer buffer = new StringBuffer(); buffer.append( "from Classes c inner join fetch c.students s"); Query query = session.createQuery(buffer.toString()); List list = query.list(); session.close(); } /** * 6.迫切内连接2 * 从学生端出发； */ @Test public void testOneToMany_InnerJoin_Fetch_Query_2(){ Session session = sessionFactory.openSession(); StringBuffer buffer = new StringBuffer(); buffer.append( "from Student s inner join fetch s.classes c"); Query query = session.createQuery(buffer.toString()); query.list(); session.close(); } /** * 7.迫切内连接3：获取属性，封装结果； * select new cn.chen.system.model.ClassVsStudent(c.cname,s.sname) * from Student s inner join fetch s.classes c； * 上述的 hql语句会报错，因为from后面想要的结构和select想要的结构是冲突的，所以 如果在from后面加fetch,不能写select语句，如果加select，不能写fetch,两者只能选择其一 * */ @Test public void testOneToMany_InnerJoin_Fetch_Query_Property(){ Session session = sessionFactory.openSession(); StringBuffer buffer = new StringBuffer(); //下面的写法不对； // buffer.append("select new cn.chen.system.model.ClassVsStudent(c.cname,s.sname) " + // " from Student s inner join fetch s.classes c"); //不要fetch; buffer.append( "select new cn.chen.system.model.ClassVsStudent(c.cname,s.sname) " + " from Student s inner join s.classes c " ); Query query = session.createQuery(buffer.toString()); List list = query.list(); session.close(); } /** * 8.迫切左外连接 * 从班级出发，得到班级对应的学生 */ @Test public void testOneToMany_LeftJoin_Fetch(){ Session session = sessionFactory.openSession(); StringBuffer buffer = new StringBuffer(); buffer.append( "from Classes c left outer join fetch c.students s"); Query query = session.createQuery(buffer.toString()); List<Classes> list = query.list(); for (Classes classes : list) { System. out.println("classes:" +classes.getCname()); Set<Student> students = classes.getStudents(); for (Student student : students) { System. out.println(" student:" +student.getSname()); } } session.close(); } /** * 9.迫切左外连接2 * 从学生出发，得到对应的班级 */ @Test public void testOneToMany_RightJoin_Fetch(){ Session session = sessionFactory.openSession(); StringBuffer buffer = new StringBuffer(); buffer.append( "from Student s left outer join fetch s.classes "); Query query = session.createQuery(buffer.toString()); List<Student> list = query.list(); for (Student student : list) { System. out.println("student:" +student.getSname()); if (student.getClasses()!=null) { System. out.println(" " +student.getClasses().getCname()); } } session.close(); }

二、 多对多

学生Student和课程Course为例: Student里有装Course的set集合，Course里也有装Student的set集合； 多对多与一对多操作差不多

/** * 1.得到所有的学生以及其对应的课程 * 从学生端出发 * list装的是学生； */ @Test public void testManyToMany_LeftJoin_Fecth(){ Session session = sessionFactory.openSession(); StringBuffer buffer = new StringBuffer(); buffer.append( "from Student s left outer join fetch s.courses"); Query query = session.createQuery(buffer.toString()); List list = query.list(); session.close(); } /** * 2.得到所有的课程及课程下对应的学生； * list装的是课程 */ @Test public void testManyToMany_LeftJoin_Fecth_2(){ Session session = sessionFactory.openSession(); StringBuffer buffer = new StringBuffer(); buffer.append( "from Course c left outer join fetch c.students s"); Query query = session.createQuery(buffer.toString()); query.list(); session.close(); } /** * 3.一对多和多对多的结合 * 得到所有班级下的所有学生以及所有学生下的所有课程； * 从班级出发 */ @Test public void testManyToManyAndOneToMany(){ Session session = sessionFactory.openSession(); StringBuffer buffer = new StringBuffer(); buffer.append( "from Classes c left outer join fetch" + " c.students s left outer join fetch s.courses"); Query query = session.createQuery(buffer.toString()); List<Classes> classeList = query.list(); //去掉集合中的重复元素 Set<Classes> sets = new HashSet<Classes>(classeList); classeList = new ArrayList<Classes>(sets); System. out.println(classeList.size()); for(Classes classes:classeList){//遍历班级 System. out.println(classes.getCname()); Set<Student> students = classes.getStudents();//得到班级下的学生 for(Student student:students){//遍历学生 System. out.println(student.getSname()); Set<Course> courses = student.getCourses(); for(Course course:courses){//遍历学生下的课程 System. out.println(course.getCname()); } } } session.close(); } /** * 从中间表出发，班级有学生，学生修课程，故从学生角度出发进行查询； */ @Test public void testManyToManyAndOneToMany_2(){ Session session = sessionFactory.openSession(); StringBuffer buffer = new StringBuffer(); buffer.append( "from Student s left outer join fetch s.classes c left outer join fetch s.courses cc"); Query query = session.createQuery(buffer.toString()); List<Student> studentList = query.list(); for(Student student:studentList){ System. out.println(student.getSname()); Classes classes = student.getClasses(); System. out.println(classes.getCname()); Set<Course> courses = student.getCourses(); for(Course course:courses){ System. out.println(course.getCname()); } } session.close(); } /*******************************************************************************/ /** * 面向对象的查询 */ @Test public void testQueryCriteria(){ Session session = sessionFactory.openSession(); List<Classes> classesList = session.createCriteria(Classes.class).list() ; System. out.println(classesList.size()); session.close(); } @Test public void testQueryCriteria_Where(){ Session session = sessionFactory.openSession(); Classes classes = (Classes)session.createCriteria(Classes.class).add(Restrictions.eq("cid" , 1L)).uniqueResult(); System. out.println(classes.getCname()); session.close(); } }