mybatis 多对多代码

MyBatis 多对多关系的实现需要借助中间表，以下是一个简单的示例：

1. 创建数据库表

CREATE TABLE student (
  id INT PRIMARY KEY,
  name VARCHAR(20)
);

CREATE TABLE course (
  id INT PRIMARY KEY,
  name VARCHAR(20)
);

CREATE TABLE student_course (
  student_id INT,
  course_id INT,
  PRIMARY KEY (student_id, course_id),
  FOREIGN KEY (student_id) REFERENCES student(id),
  FOREIGN KEY (course_id) REFERENCES course(id)
);

2. 创建实体类

public class Student {
    private Integer id;
    private String name;
    private List<Course> courses;
    // getter and setter
}

public class Course {
    private Integer id;
    private String name;
    private List<Student> students;
    // getter and setter
}

3. 创建 Mapper 接口

public interface StudentMapper {
    Student selectStudentById(Integer id);
}

public interface CourseMapper {
    Course selectCourseById(Integer id);
}

4. 创建 XML 映射文件

StudentMapper.xml

<mapper namespace="com.example.mapper.StudentMapper">
    <resultMap id="studentMap" type="com.example.entity.Student">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <collection property="courses" ofType="com.example.entity.Course">
            <id property="id" column="course_id"/>
            <result property="name" column="course_name"/>
        </collection>
    </resultMap>
    
    <select id="selectStudentById" resultMap="studentMap">
        SELECT s.id, s.name, c.id AS course_id, c.name AS course_name
        FROM student s
        LEFT JOIN student_course sc ON s.id = sc.student_id
        LEFT JOIN course c ON sc.course_id = c.id
        WHERE s.id = #{id}
    </select>
</mapper>

CourseMapper.xml

<mapper namespace="com.example.mapper.CourseMapper">
    <resultMap id="courseMap" type="com.example.entity.Course">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <collection property="students" ofType="com.example.entity.Student">
            <id property="id" column="student_id"/>
            <result property="name" column="student_name"/>
        </collection>
    </resultMap>
    
    <select id="selectCourseById" resultMap="courseMap">
        SELECT c.id, c.name, s.id AS student_id, s.name AS student_name
        FROM course c
        LEFT JOIN student_course sc ON c.id = sc.course_id
        LEFT JOIN student s ON sc.student_id = s.id
        WHERE c.id = #{id}
    </select>
</mapper>

5. 测试代码

public class Test {
    public static void main(String[] args) {
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("mybatis-config.xml"));
        SqlSession sqlSession = sqlSessionFactory.openSession();
        
        StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
        Student student = studentMapper.selectStudentById(1);
        System.out.println(student.getName());
        for (Course course : student.getCourses()) {
            System.out.println(course.getName());
        }
        
        CourseMapper courseMapper = sqlSession.getMapper(CourseMapper.class);
        Course course = courseMapper.selectCourseById(1);
        System.out.println(course.getName());
        for (Student student : course.getStudents()) {
            System.out.println(student.getName());
        }
        
        sqlSession.close();
    }
}