项目目录结构:
一.多对一(多个学生拥有一个老师)
需求:查询所有的学生信息,以及对应的老师信息;
思路:1.查询所有的学生信息 2.根据查询出来的学生的tid,寻找对应的老师;
所以,此时的Student类中包含Teacher类属性,则pojo类字段详情如下:
- 方式一:按照查询嵌套处理(子查询)
StudentMapper.java接口
public interface StudentMapper {
public List<Student> getStudentInfo();
}
StudentMapper.xml
<mapper namespace="com.kuang.dao.StudentMapper">
<!-- 方式一:按照查询嵌套处理(子查询) -->
<select id="getStudentInfo" resultMap="StudentTeacher">
select * from student
</select>
<resultMap type="Student" id="StudentTeacher">
<result property="sid" column="sid" />
<result property="sname" column="sname" />
<!-- 复杂的属性,我们需要单独处理 对象:association 集合:collection -->
<!--association关联属性 property属性名 column在多的一方的表中的列名 javaType属性类型-->
<association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
</resultMap>
<select id="getTeacher" resultType="Teacher">
select * from teacher
where tid = #{tid}
</select>
</mapper>
注意点说明:
- association 是一个复杂类型的关联,使用它来处理关联查询
- 关于association 的 column 属性:写子查询的所需要的参数
这里传递过来的id,只有一个属性的时候,下面可以写任何值(例子如上)
association中column多参数配置时,采用键值对方式(例子如下)
<!--
association中column多参数配置:
column="{key=value,key=value}"
其实就是键值对的形式,key是传给下个sql的取值名称,value是片段一中sql查询的字段名。
-->
<resultMap id="StudentTeacher" type="Student">
<association property="teacher" column="{id=tid,name=tname}" javaType="Teacher" select="getTeacher"/>
</resultMap>
<select id="getTeacher" resultType="teacher">
select * from teacher where id = #{id} and name = #{name}
</select>
SqlMapConfig.xml
<!-- 配置映射文件的位置 -->
<mappers>
<mapper class="com.kuang.dao.StudentMapper"/>
</mappers>
StudentTest.java
@Test
public void test() {
SqlSession sqlSession = MybatisUtils.getSession();
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
List<Student> studentInfo = mapper.getStudentInfo();
for (Student student : studentInfo) {
System.out.println(student);
}
sqlSession.close();
}
-
方式二:按照结果嵌套处理(连表查询)
StudentMapper.java接口
public interface StudentMapper {
public List<Student> getStudentInfo2();
}
StudentMapper.xml
<mapper namespace="com.kuang.dao.StudentMapper">
<!-- 方式二:按照结果嵌套处理(连表查询) -->
<select id="getStudentInfo2" resultMap="StudentTeacher2">
SELECT s.sid,s.sname,t.tname
FROM student s,teacher t
WHERE s.tid = t.tid;
</select>
<resultMap type="student" id="StudentTeacher2">
<result property="sid" column="sid"/>
<result property="sname" column="sname"/>
<association property="teacher" javaType="Teacher">
<result property="tname" column="tname"/>
</association>
</resultMap>
</mapper>
SqlMapConfig.xml
<!-- 配置映射文件的位置 -->
<mappers>
<mapper class="com.kuang.dao.StudentMapper"/>
</mappers>
StudentTest.java
@Test
public void test2() {
SqlSession sqlSession = MybatisUtils.getSession();
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
List<Student> studentInfo2 = mapper.getStudentInfo2();
for (Student student : studentInfo2) {
System.out.println(student);
}
sqlSession.close();
}
二.一对多(一个老师拥有多个学生)
需求:查询某位老师的信息,以及他所有学生的信息;
思路:1.查询老师信息 2.根据查询出来的老师的tid,寻找他所有的学生;
所以,此时的Teacher类中包含Student类属性,则pojo类字段详情如下:
- 方式一:按照查询嵌套处理(子查询)
TeacherMapper.java
public interface TeacherMapper {
//获取指定老师下的所有学生集老师的信息
Teacher getTeacher(int id);
}
TeacherMapper.xml
<mapper namespace="com.kuang.dao.TeacherMapper">
<!-- 方式一:按照查询嵌套处理(子查询) -->
<resultMap type="teacher" id="TeacherResultMap">
<result property="tid" column="tid"/>
<result property="tname" column="tname"/>
<collection property="students" javaType="ArrayList" ofType="student" select="selectByTid" column="tid">
<result property="sid" column="sid"/>
<result property="sname" column="sname"/>
<result property="tid" column="tid"/>
</collection>
</resultMap>
<select id="selectByTid" resultType="student">
select * from student where tid = #{tid}
</select>
<select id="getTeacher" resultMap="TeacherResultMap">
select * from teacher where tid = #{tid}
</select>
</mapper>
SqlMapConfig.xml
<!-- 配置映射文件的位置 -->
<mappers>
<mapper class="com.kuang.dao.TeacherMapper"/>
</mappers>
TeacherTest.java
@Test
public void test() {
SqlSession sqlSession = MybatisUtils.getSession();
TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
Teacher teacher = mapper.getTeacher(1);
System.out.println(teacher);
sqlSession.close();
}
- 方式一:按照查询嵌套处理(子查询)
TeacherMapper.java
public interface TeacherMapper {
//获取指定老师下的所有学生集老师的信息
Teacher getTeacher2(int id);
}
TeacherMapper.xml
<mapper namespace="com.kuang.dao.TeacherMapper">
<!-- 方式二:按结果嵌套查询(连表查询) -->
<select id="getTeacher2" resultMap="TeacherStudent">
SELECT s.sid,s.sname,t.tname,t.tid
FROM student s,teacher t
WHERE s.tid = t.tid and t.tid = #{tid}
</select>
<resultMap type="Teacher" id="TeacherStudent">
<result property="tid" column="tid"/>
<result property="tname" column="tname"/>
<!--
ofType 指定映射到List或者集合中的pojo类型!
也就是集合泛型中的约束类型
-->
<collection property="students" ofType="Student">
<result property="sid" column="sid"/>
<result property="sname" column="sname"/>
<result property="tid" column="tid"/>
</collection>
</resultMap>
</mapper>
SqlMapConfig.xml
<!-- 配置映射文件的位置 -->
<mappers>
<mapper class="com.kuang.dao.TeacherMapper"/>
</mappers>
TeacherTest.java
@Test
public void test2() {
SqlSession sqlSession = MybatisUtils.getSession();
TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
Teacher teacher = mapper.getTeacher2(1);
System.out.println(teacher);
sqlSession.close();
}
SELECT s.sid,s.sname,t.tname,t.tid FROM student s,teacher t WHERE s.tid = t.tid and t.tid = ?