作者:中青33期_840 | 来源:互联网 | 2023-08-19 20:45
一个老师教许多学生,一个学生被许多老师教,一个学生有好多书,同一种书被许多同学拥有.
要查询教拥有书"a"的学生的老师!
HQL如何写呀?如何取值?
class
Teacher{
String id;
String name;
Set students;
}
class
Student{
String id;
String name;
Set teachers;
Set books;
}
class
Book{
String id;
String name;
Set students;
}
<
class
name
="net.fengrun.lzk.stu.Book"
table
="books"
>
<
id
name
="id"
type
="integer"
column
="id"
>
<
generator
class
="native"
/>
id
>
<
property
name
="name"
/>
<
set
name
="students"
table
="student_book"
inverse
="true"
cascade
="none"
lazy
="true"
>
<
key
column
="bookid"
/>
<
many-to-many
column
="studentid"
class
="net.fengrun.lzk.stu.Student"
/>
set
>
class
>
<
class
name
="net.fengrun.lzk.stu.Student"
table
="students"
>
<
id
name
="id"
type
="integer"
column
="id"
>
<
generator
class
="native"
/>
id
>
<
property
name
="name"
/>
<
set
name
="books"
table
="student_book"
inverse
="false"
cascade
="none"
lazy
="true"
>
<
key
column
="studentid"
/>
<
many-to-many
column
="bookid"
class
="net.fengrun.lzk.stu.Book"
/>
set
>
<
set
name
="teachers"
table
="teacher_student"
inverse
="false"
cascade
="none"
lazy
="true"
>
<
key
column
="studentid"
/>
<
many-to-many
column
="teacherid"
class
="net.fengrun.lzk.stu.Teacher"
/>
set
>
class
>
<
class
name
="net.fengrun.lzk.stu.Teacher"
table
="teachers"
>
<
id
name
="id"
type
="integer"
column
="id"
>
<
generator
class
="native"
/>
id
>
<
property
name
="name"
/>
<
set
name
="students"
table
="teacher_student"
inverse
="true"
cascade
="delete-orphan"
lazy
="true"
>
<
key
column
="teacherid"
/>
<
many-to-many
column
="studentid"
class
="net.fengrun.lzk.stu.Student"
/>
set
>
class
>
Hql语句:
SELECT t FROM Teacher t join t.students s join s.books b where b.name = 'a'
解释:t.students s中s并不是集合的意思,而是t的students对象的表别名,join t.students s这个hql,hibernate会翻译成两个表的内连接关系
错误写法:
SELECT t FROM teacher t where t.students.books.name = 'a'
其实道理是很显然的,t.students是一个Set,那么这个Set怎么可能有books这样的属性呢?同理books.name也不对,所以使用表间连接并给出别名t.students s,此时的对象s才有books属性,所以可以写s.books b,最后使用b.name限制查询b.name = 'a'.
另外一种写法:
SELECT t FROM Teacher t,Student s,Book b where s.id in elements(t.students) and b.id in elements(s.books)
这种方法没有出错!不过这种方式要用子查询! 但是MySql并不支持子查询,为了通用性,选择第一种做法。
未完,待续...