Multi-to-Multi-association query SQL statements

1. The object associations of student, score, and coure are defined as follows:

Bytes ------------------------------------------------------------------------------------------------

Student:

@ Entity
@ Table (name ="Tb_student")
Public ClassStudentImplementsSerializable {

.....
PrivateSet <course> courses =NewHashset <course> ();
PrivateSet <score> scores =NewHashset <score> ();

.....

@ manytomany
@ jointable (name = " tb_student_course ",
joincolumns = @ joincolumn (name = " student_id ", referencedcolumnname = " id "),
inversejoincolumns = @ joincolumn (name = " course_id ", referencedcolumnname = " id ")
)
Public set getcourses () {
return courses;
}< br> Public void setcourses (set courses) {
This . courses = courses;
}

@ Onetoworkflow (mappedby ="Student")
PublicSet <score> getscores (){
ReturnScores;
}
Public VoidSetscores (set <score> scores ){
This. Scores = scores;
}
}

Bytes -----------------------------------------------------------------------------------------------

Score:

 @ entity @ table (name = " tb_score ")  Public   class  score  implements  serializable {@ manytoone @ joincolumn (name ="  course_id  ")  Public  course getcourse () { return  course ;}  Public   void  setcourse (Course course) { This . course = course ;}@ manytoone @ joincolumn (name = " student_id ")  Public  Student getstudent () { return  Student ;}< span style =" color: # 0000ff "> Public   void  setstudent (Student) { This . student = student ;}}

Bytes -------------------------------------------------------------------------------------------------
 

Course entity has no association annotation.

Bytes --------------------------------------------------------------------------------------------------------------

Student and course are many-to-many, one-way, student can access course

Student and score are one-to-many, two-way

The score and course are many-to-one and one-way. The score can be accessed by course.

Bytes ---------------------------------------------------------------------------------------------------------------

Find all scores of student 1 in all courses:

 
PublicList <score> findscorebystudentid (integer ID) {list <score> ls = em. createquery ("Select score from student s join S. Scores score where S. ID =: ID")
 

 
. Setparameter ("ID", ID)
 

. Getresultlist ();ReturnLs ;}
 

 

Client:

 
Scoredao = (scoredao) cxt. Lookup ("Scoredaobean/remote"); List <score> scores1 = scoredao. findscorebystudentid (NewINTEGER (1); system. Out. println ("= Query the scores of all subjects of student 1");For(Score S: scores1) {system. Out. println (S. getcourse (). getname () +"--"+ S. getscore ());}
 

Result output:

 
= Query the scores of all subjects of student 1 course1 -- 99w.course2 -- 98.0
 

 

SQL output:

22:21:07, 765 info [stdout] hibernate: Select scores1 _. ID as id19 _, scores1 _. course_id as course4_19 _, scores1 _. student_id as student3_19 _, scores1 _. score as score19 _ from tb_student student0 _ inner join tb_score scores1 _ on student0 _. id = scores1 _. student_id where student0 _. id =? 22:21:07, 765 info [stdout] hibernate: Select course0 _. ID as id18_0 _, course0 _. name as name18_0 _, course0 _. description as descript3_18_0 _, course0 _. optional as optional18_0 _, course0 _. teacher as teacher18_0 _ from tb_course course0 _ Where course0 _. id =? 22:21:07, 765 info [stdout] hibernate: Select student0 _. ID as id20_1 _, student0 _. name as name20_1 _, student0 _. description as descript3_20_1 _, student0 _. class_id as class9_20_1 _, student0 _. temporary as temporary20_1 _, student0 _. age as age20_1 _, student0 _. sex as sex20_1 _, student0 _. birthday as birthday20_1 _, student0 _. createdate as createdate20_1 _, classeo1 _. ID as id17_0 _, classeo1 _. classname as Class Name17_0 _ from tb_student student0 _ left Outer Join tb_class classeo1 _ on student0 _. class_id = classeo1 _. ID where student0 _. ID =? 22:21:07, 781 info [stdout] hibernate: Select course0 _. ID as id18_0 _, course0 _. name as name18_0 _, course0 _. description as descript3_18_0 _, course0 _. optional as optional18_0 _, course0 _. teacher as teacher18_0 _ from tb_course course0 _ Where course0 _. id =?

 

Timely loading by default ??? (Isn't loading delayed for the set by default ?),Jpql can also change the query to the following:

 
PublicList <score> findscorebystudentid (integer ID) {list <score> ls = em. createquery ("Select S. Scores from student s where S. ID =: ID")
 

 
. Setparameter ("ID", ID)
 

 
. Getresultlist ();ReturnLs ;}
 

 
Result output:
 

 
= Query the scores of all subjects of student 1 course1 -- 99w.course2 -- 98.0
 

 

 

The output SQL statement is as follows:

22:36:55, 546 info [stdout] hibernate: Select scores1 _. ID as id19 _, scores1 _. course_id as course4_19 _, scores1 _. student_id as student3_19 _, scores1 _. score as score19 _ from tb_student student0 _ inner join tb_score scores1 _ on student0 _. id = scores1 _. student_id where student0 _. id =? 22:36:55, 546 info [stdout] hibernate: Select course0 _. ID as id18_0 _, course0 _. name as name18_0 _, course0 _. description as descript3_18_0 _, course0 _. optional as optional18_0 _, course0 _. teacher as teacher18_0 _ from tb_course course0 _ Where course0 _. id =? 22:36:55, 546 info [stdout] hibernate: Select student0 _. ID as id20_1 _, student0 _. name as name20_1 _, student0 _. description as descript3_20_1 _, student0 _. class_id as class9_20_1 _, student0 _. temporary as temporary20_1 _, student0 _. age as age20_1 _, student0 _. sex as sex20_1 _, student0 _. birthday as birthday20_1 _, student0 _. createdate as createdate20_1 _, classeo1 _. ID as id17_0 _, classeo1 _. classname as Class Name17_0 _ from tb_student student0 _ left Outer Join tb_class classeo1 _ on student0 _. class_id = classeo1 _. ID where student0 _. ID =? 22:36:55, 562 info [stdout] hibernate: Select course0 _. ID as id18_0 _, course0 _. name as name18_0 _, course0 _. description as descript3_18_0 _, course0 _. optional as optional18_0 _, course0 _. teacher as teacher18_0 _ from tb_course course0 _ Where course0 _. id =?