多对多Hql如何写

有老师,学生,书，老师和学生是多对多关系，学生和书是多对多关系

Book.hbm.xml student.hbm.xml teacher.hbm.xml

    <class name="Book" table="book">
        <id name="id" column="id">
            <generator class="identity"/>
        </id>
         <property name="name" type="string">
            <column name="name" length="50" not-null="true" />
        </property>
        <set name="students"  table="book_student" >
        <key column="bookId"/>
        <many-to-many column="studentId"
            class="Student"/>
    </set>
        
     <class name="Student" table="student">
        <id name="id" column="id">
            <generator class="identity"/>
        </id>
        
        <property name="name" type="string">
            <column name="name" length="100" not-null="true" />
        </property>
        <set name="teachers"  table="student_teacher">
        <key column="studentId"/>
        <many-to-many column="teacherId"
            class="Teacher"/>
		</set>
      <set name="books"  table="book_student" cascade="all">
        <key column="studentId"/>
        <many-to-many column="bookId"
            class="Book"/>
    </set>
  <class name="Teacher" table="teacher">
        <id name="id" column="id">
            <generator class="identity"/>
        </id>
        
        <property name="name" type="string">
            <column name="name" length="100" not-null="true" />
        </property>
        <set name="students"  table="student_teacher">
        <key column="teacherId"/>
        <many-to-many column="studentId"
            class="Student"/>
    </set>

 

 

然后我存入了一些老师学生和书，注意下面只存入了student，因为Student类级联Books

@Test
	public  void updateTable(){
		SessionFactory sessionFactory = (SessionFactory) ServiceProvinder.getBean("mySessionFactory");
		Session session = sessionFactory.openSession();
		Student student = new Student();
		student.setName("wangchen");
		Set<Book> books = new HashSet<Book>();
		for(int i=0;i<20;i++){
		Book book = new Book();
		book.setName("book"+i*3);
		books.add(book);
		}
		student.setBooks(books);
		Transaction transaction= session.beginTransaction();
		session.save(student);
		transaction.commit();
		session.close();

接下来我先用sql查询：查询教过拥有书名是'book3'的学生的所有老师

mysql> select distinct t.name from teacher t join student_teacher st on t.id =st
.teacherid join student s on st.studentid = s.id join book_student bs on s.id =
bs.studentid join book b on bs.bookid = b.id where b.name='book3';

或者

mysql> select distinct t.name from teacher t where t.id in (select teacherid fro
m student_teacher st where st.studentid in (select id from student s  where s.id
 in (select studentid from book_student bs where bs.bookid in (select id from bo
ok b where b.name='book3') ) ) );

写起来很恶心，那么看看hql是如何写吧

select distinct t from Teacher t join t.students s join s.books b where b.name='book3'

直接用join 集合对象就可以了，hibernate会自动连上他们的表

还要注意的是如果要集合中的元素的属性需要用 s.books b where b.name='book3';

而这个HQL翻译成sql就是咱们上面的第一个自己写的sql

那第二种sql如何用hql写呢

select distinct t from Teacher t,Student s,Book b where s.id in elements(t.students) and b.id in elements (s.books)"

还有一个要注意的就是如果写成

 session.createQuery("  from Teacher t join t.students s join s.books b where b.name='book3'").list();

hql会返回三个对象的集合的(teacher student book都会返回)

所以具体要返回那个要加上select distinct 对象名 from

参考： http://blog.csdn.net/wyxz126/article/details/8607597