mybatis多对多关联查询
时间: 2023-07-22 15:25:52 浏览: 77
MyBatis多对多关联查询可以通过中间表来实现。
首先,需要在MyBatis的mapper.xml中定义两个查询语句,分别查询两个表的数据。比如,查询学生表和课程表:
```
<!-- 查询学生表 -->
<select id="getStudentById" parameterType="int" resultType="Student">
select * from student where id = #{id}
</select>
<!-- 查询课程表 -->
<select id="getCourseById" parameterType="int" resultType="Course">
select * from course where id = #{id}
</select>
```
然后,在中间表的mapper.xml中定义一个查询语句,查询中间表的数据。比如,查询学生选课表:
```
<!-- 查询学生选课表 -->
<select id="getStudentCourse" parameterType="int" resultMap="studentCourseMap">
select s.id as student_id, s.name as student_name, c.id as course_id, c.name as course_name
from student s
inner join student_course sc on s.id = sc.student_id
inner join course c on c.id = sc.course_id
where s.id = #{id}
</select>
<!-- 定义resultMap -->
<resultMap id="studentCourseMap" type="HashMap">
<id column="student_id" property="student.id"/>
<result column="student_name" property="student.name"/>
<id column="course_id" property="course.id"/>
<result column="course_name" property="course.name"/>
</resultMap>
```
最后,在Java代码中调用这三个查询语句,将结果拼接起来即可:
```
Student student = sqlSession.selectOne("getStudentById", studentId);
List<Course> courses = new ArrayList<>();
List<Map<String, Object>> studentCourses = sqlSession.selectList("getStudentCourse", studentId);
for (Map<String, Object> studentCourse : studentCourses) {
Course course = new Course();
course.setId((Integer) studentCourse.get("course_id"));
course.setName((String) studentCourse.get("course_name"));
courses.add(course);
}
student.setCourses(courses);
```
上面的代码中,先通过getStudentById查询出学生信息,然后通过getStudentCourse查询出学生已选的课程信息,最后将学生信息和课程信息拼接起来。这样就完成了MyBatis多对多关联查询。
阅读全文