Mybatis 多对多

多对多：

多对多映射
        对于在mybatis中的多对多的处理,其实我们可以参照一对多来解决
        【注意】在这个例子中有三个字段都是一样的:id,这种情况一定要小心,
        要给列起别名的(上面的一对一和一对多中如果出现这种情况也是一样的处理方式)
        建表语句:
        drop table course;
        drop table student;
        drop table student_course;
        如果需要可以使用 cascade constraints;

        create table course (
          id number primary key,
          course_code varchar2(30) not null,
          course_name varchar2(30) not null 
        );
        create table student (
          id number primary key,
          name varchar2(10) not null,
          gender varchar2(10) ,
          major varchar2(10) ,
          grade varchar2(10) 
        );
        create table student_course (
          id number primary key,
          student_id number references student(id),
          course_id number references course(id)
        );
        
        java代码:
        public class Course {
            private Integer id;
            private String courseCode; // 课程编号
            private String courseName;// 课程名称
            private List<Student> students;// 选课学生
            get/set
        }
        public class Student {
            private Integer id;
            private String name; // 姓名
            private String gender; // 性别
            private String major; // 专业
            private String grade; // 年级
            private List<Course> courses;// 所选的课程
            get/set
        }
        
        Many2ManyMapper.java:
        public interface Many2ManyMapper {
            //插入student数据
            public void insertStudent(Student student);
            //插入course数据
            public void insertCourse(Course course);
            //通过id查询学生
            public Student getStudentById(Integer id);
            //通过id查询课程
            public Course getCourseById(Integer id);
            
            //学生x选课y
            public void studentSelectCourse(Student student, Course course);
            //查询比指定id值小的学生信息
            public List<Student> getStudentByIdOnCondition(Integer id);
            //查询student级联查询出所选的course并且组装成完整的对象
            public Student getStudentByIdWithCourses(Integer id);
        }


        Many2ManyMapper.xml:
        <insert id="insertStudent" parameterType="Student">
            <selectKey keyProperty="id" resultType="int" order="BEFORE">
                select my_seq.nextval from dual
            </selectKey>
            insert into 
                student(id,name,gender,major,grade)
            values
                (#{id},#{name},#{gender},#{major},#{grade})
        </insert>
        
        <insert id="insertCourse" parameterType="Course">
            <selectKey keyProperty="id" resultType="int" order="BEFORE">
                select my_seq.nextval from dual
            </selectKey>
            insert into 
                course(id,course_code,course_name)
            values
                (#{id},#{courseCode},#{courseName})
        </insert>

        <select id="getStudentById" parameterType="int" resultType="Student">
            select id,name,gender,major,grade
            from student
            where id=#{id}
        </select>
        
        <select id="getCourseById" parameterType="int" resultType="Course">
            select id,course_code as courseCode,course_name as courseName
            from course
            where id=#{id}
        </select>

        <!-- param1代表方法中第一个参数 以此类推 -->
        <insert id="studentSelectCourse">
            insert into 
                student_course(id,student_id,course_id)
            values
                (my_seq.nextval,#{param1.id},#{param2.id})
        </insert>
        
        <!-- 如果有特殊符号的话 需要用 <![CDATA[ 特殊符号 ]]>  例如 < & 等等 -->
        <select id="getStudentByIdOnCondition" parameterType="int" resultType="Student">
            select *
            from student
            where id <![CDATA[ < ]]> #{id}
        </select>

        <!-- 
             这里使用了嵌套结果ResultMap的方式进行级联查询 
             当然也可以使用嵌套查询select 
        -->
        <!-- 映射一个基本的Student查询结果 -->
        <resultMap id="StudentResult" type="Student">
            <id property="id" column="id"/>
            <result property="name" column="name"/>
            <result property="gender" column="gender"/>
            <result property="major" column="major"/>
            <result property="grade" column="grade"/>
        </resultMap>
        <!-- 继承上面那个基本的映射,再扩展出级联查询 -->
        <resultMap id="StudentResultWithCourses" type="Student" extends="StudentResult">
            <collection property="courses" resultMap="CourseResult"></collection>
        </resultMap>
        <!-- 这里特别要是的是column="cid" 这是和select语句中的 c.id as cid对应的 一定一定一定要对应起来 -->
        <resultMap id="CourseResult" type="Course">
            <id property="id" column="cid"/>
            <result property="courseCode" column="course_code"/>
            <result property="courseName" column="course_name"/>
        </resultMap>
        <!-- 
            注意:查询语句的中的c.id as cid这个地方,避免名字相同出现查询结果不正确的情况
            同时在id="CourseResult"的resultMap中也有与这里对应的设置要特别特别注意
        -->
        <select id="getStudentByIdWithCourses" parameterType="int" resultMap="StudentResultWithCourses">
            select s.id,s.name,s.gender,s.major,s.grade,c.id as cid,c.course_code,c.course_name,sc.id,sc.student_id,sc.course_id
            from student s,course c,student_course sc
            where 
            s.id=#{id}
            and
            s.id=sc.student_id 
            and 
            sc.course_id=c.id
        </select>
        
        测试代码:
        @Test
        public void test_insertStudent(){
            
            SqlSession session = null;
            try {
                session = MyBatisSqlSessionFactory.openSession();
                
                Many2ManyMapper mapper = session.getMapper(Many2ManyMapper.class);
                    
                mapper.insertStudent(new Student("张三","男","计算机","大四"));
                
                session.commit();
                
            } catch (Exception e) {
                e.printStackTrace();
                session.rollback();
            }finally {
                if(session!=null)session.close();
            }
            
        }
        
        @Test
        public void test_insertCourse(){
            
            SqlSession session = null;
            try {
                session = MyBatisSqlSessionFactory.openSession();
                
                Many2ManyMapper mapper = session.getMapper(Many2ManyMapper.class);
                    
                mapper.insertCourse(new Course("001","corejava"));
                mapper.insertCourse(new Course("002","oracle"));
                
                session.commit();
                
            } catch (Exception e) {
                e.printStackTrace();
                session.rollback();
            }finally {
                if(session!=null)session.close();
            }
            
        }
        
        @Test
        public void test_studentSelectCourse(){
            
            SqlSession session = null;
            try {
                session = MyBatisSqlSessionFactory.openSession();
                
                Many2ManyMapper mapper = session.getMapper(Many2ManyMapper.class);
                    
                Student student = mapper.getStudentById(58);
                Course course = mapper.getCourseById(59);
                
                mapper.studentSelectCourse(student, course);
                
                session.commit();
                
            } catch (Exception e) {
                e.printStackTrace();
                session.rollback();
            }finally {
                if(session!=null)session.close();
            }
            
        }
        
        @Test
        public void test_getStudentByIdOnCondition(){
            
            SqlSession session = null;
            try {
                session = MyBatisSqlSessionFactory.openSession();
                
                Many2ManyMapper mapper = session.getMapper(Many2ManyMapper.class);
                
                List<Student> list = mapper.getStudentByIdOnCondition(100);
                
                for(Student s:list){
                    System.out.println(s);
                }
                
            } catch (Exception e) {
                e.printStackTrace();
            }finally {
                if(session!=null)session.close();
            }
            
        }
        
        @Test
        public void test_getStudentByIdWithCourses(){
            
            SqlSession session = null;
            try {
                session = MyBatisSqlSessionFactory.openSession();
                
                Many2ManyMapper mapper = session.getMapper(Many2ManyMapper.class);
                
                Student student = mapper.getStudentByIdWithCourses(58);
                
                System.out.println(student);
                
            } catch (Exception e) {
                e.printStackTrace();
            }finally {
                if(session!=null)session.close();
            }
            
        }
        
        
        注:这是从student这边出发所做的一些操作,从course一边开始操作是一样的,因为俩者的关系是多对多(对称的).
        同时不论是一对一还是一对多还是多对多,都不能在mybatis中进行级联保存、更新、删除,我们需要使用sql语句控制每一步操作