package com.edu.bean;
import java.util.List;
public class School {
private Integer id;
private String name;
private List<Student> students;
public School() {
super();
}
public School(Integer id, String name) {
super();
this.id = id;
this.name = name;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public List<Student> getStudents() {
return students;
}
public void setStudents(List<Student> students) {
this.students = students;
}
@Override
public String toString() {
return "School [id=" + id + ", name=" + name + ", students=" + students + "]";
}
}
package com.edu.bean;
import java.util.List;
public class Student {
private Integer id;
private String name;
private List<Teacher> teachers;
private School school;
public Student() {
super();
}
public Student(Integer id, String name) {
super();
this.id = id;
this.name = name;
}
public Student(Integer id, String name, School school) {
super();
this.id = id;
this.name = name;
this.school = school;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public List<Teacher> getTeachers() {
return teachers;
}
public void setTeachers(List<Teacher> teachers) {
this.teachers = teachers;
}
public School getSchool() {
return school;
}
public void setSchool(School school) {
this.school = school;
}
@Override
public String toString() {
return "Student [id=" + id + ", name=" + name + ", teachers=" + teachers + ", school=" + school + "]";
}
}
- 2.学生Dao查询时,同时查询其所在的学校:
从学生角度看,属于1对1,可以直接使用association
package com.edu.dao;
import com.edu.bean.Student;
public interface StudentDao {
public Student getStudentSchoolById(Integer id);
}
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.edu.dao.StudentDao">
<select id="getStudentSchoolById" resultMap="st2Map">
select st.id tid,st.name tname,sc.id cid,sc.name cname from t_schools sc left join t_students st on sc.id = st.schoolId where st.id = #{id}
</select>
<!-- 使用[级联属性]封装联合查询出的结果 -->
<resultMap type="com.edu.bean.Student" id="studentSchoolMap">
<id property="id" column="tid"/>
<result property="name" column="tname"/>
<result property="school.id" column="cid"/>
<result property="school.name" column="cname"/>
</resultMap>
<!-- 使用association封装联合查询出的结果 -->
<resultMap type="com.edu.bean.Student" id="st2Map">
<id property="id" column="tid"/>
<result property="name" column="tname"/>
<!-- javaType 指定封装的类型 -->
<association property="school" javaType="com.edu.bean.School">
<id property="id" column="cid"/>
<result property="name" column="cname"/>
</association>
</resultMap>
<!-- 1对n 或 n对1 : 外键一定放在n的一端 -->
<!-- n对n : 使用中间表存储对应关系 -->
</mapper>
@Test
public void TestJoinSelect01() {
SqlSession sqlSession = factory.openSession();
StudentDao studentDao = sqlSession.getMapper(StudentDao.class);
Student student = studentDao.getStudentSchoolById(1);
System.out.println(student);
}
- 3.学校Dao查询时,同时查询所有校内学生
从学校角度看,属于1对多
package com.edu.dao;
import com.edu.bean.School;
public interface SchoolDao {
/**
* 查学校的时候,将所有学生查出来
* @param id
* @return
*/
public School getSchoolById(Integer id);
}
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.edu.dao.SchoolDao">
<select id="getSchoolById" resultMap="scMap">
select sc.id schoolid,sc.name schoolname,st.id studentid,st.name studentname from
t_schools sc left join t_students st on st.schoolID = sc.id where sc.id = #{id};
</select>
<resultMap type="com.edu.bean.School" id="scMap">
<id property="id" column="schoolid"/>
<result property="name" column="schoolname"/>
<!-- ofType指定集合元素类型 -->
<collection property="students" ofType="com.edu.bean.Student">
<id property="id" column="studentid"/>
<result property="name" column="studentname"/>
<association property="school" javaType="com.edu.bean.School">
<id property="id" column="schoolid"/>
<result property="name" column="schoolname"/>
</association>
</collection>
</resultMap>
</mapper>
@Test
public void TestJointSelect02() {
SqlSession sqlSession = factory.openSession();
SchoolDao schoolDao = sqlSession.getMapper(SchoolDao.class);
School school = schoolDao.getSchoolById(1);
System.out.println(school);
}