多对多关联查询sql语句

1.student，score，coure的实体关联定义如下：

------------------------------------------------------------------------------------------------

Student:

@Entity
@Table(name="tb_student")
public class Student implements Serializable {

      .....
private Set<Course> courses=new HashSet<Course>();
private Set<Score> scores=new HashSet<Score>();

.....

@ManyToMany
@JoinTable(name="tb_student_course",
    joinColumns=@JoinColumn(name="student_id",referencedColumnName="id"),
    inverseJoinColumns=@JoinColumn(name="course_id",referencedColumnName="id")
    )
public Set<Course> getCourses() {
return courses;
}
public void setCourses(Set<Course> courses) {
this.courses = courses;
}

@OneToMany(mappedBy="student")
public Set<Score> getScores() {
return scores;
}
public void setScores(Set<Score> scores) {
this.scores = scores;
}
}

-----------------------------------------------------------------------------------------------

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;
	}
	public void setStudent(Student student) {
		this.student = student;
	}
	
}

-------------------------------------------------------------------------------------------------

Course实体无关联注解。

--------------------------------------------------------------------------------------------------------------

student跟course是多对多单向，student可访问course

student跟score是一对多双向

score跟course是多对一单向，score可访问course

---------------------------------------------------------------------------------------------------------------

查找学生1所有课程的所有成绩：

public List<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();
		return ls;
	}

 

客户端：

                       ScoreDAO scoredao=(ScoreDAO)cxt.lookup("ScoreDAOBean/remote");
			List<Score> scores1=scoredao.findScoreByStudentId(new Integer(1));
			System.out.println("==查询学生1的所有科目成绩");
			for(Score s:scores1 ){
				
				System.out.println(s.getCourse().getName()+"--"+s.getScore());
			}

结果输出：

==查询学生1的所有科目成绩
course1--99.0
course2--98.0

 

sql输出：

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 classname17_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=?

 

默认及时加载？？？（不是对集合默认延迟加载吗？），JPQL将查询改为如下也可以：

public List<Score> findScoreByStudentId(Integer id) {
		List<Score> ls=em.createQuery("select s.scores from Student s  where s.id=:id")

                                 .setParameter("id", id)

                                 .getResultList();
		return ls;
	}

结果输出：

==查询学生1的所有科目成绩
course1--99.0
course2--98.0

 

 

输出的sql语句如下：

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 classname17_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=?