mybatis实战教程三：多对多关联

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `gender` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `student` VALUES (‘1‘, ‘刘德华‘, ‘55‘, ‘0‘);
INSERT INTO `student` VALUES (‘2‘, ‘张惠妹‘, ‘49‘, ‘1‘);
INSERT INTO `student` VALUES (‘3‘, ‘谢霆锋‘, ‘35‘, ‘0‘);
INSERT INTO `student` VALUES (‘4‘, ‘王菲‘, ‘47‘, ‘1‘);
INSERT INTO `student` VALUES (‘5‘, ‘汪峰‘, ‘48‘, ‘0‘);
INSERT INTO `student` VALUES (‘6‘, ‘章子怡‘, ‘36‘, ‘1‘);

DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `gender` varchar(255) DEFAULT NULL,
  `subject` varchar(255) DEFAULT NULL,
  `degree` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `teacher` VALUES (‘1‘, ‘王晶‘, ‘0‘, ‘CHINESE‘, ‘大专‘);
INSERT INTO `teacher` VALUES (‘2‘, ‘冯小刚‘, ‘0‘, ‘ENGLISH‘, ‘本科‘);
INSERT INTO `teacher` VALUES (‘3‘, ‘吴京‘, ‘0‘, ‘MATHEMATICS‘, ‘大专‘);
INSERT INTO `teacher` VALUES (‘4‘, ‘王倦‘, ‘1‘, ‘MATHEMATICS‘, ‘研究生‘);

DROP TABLE IF EXISTS `stu_teach_rel`;
CREATE TABLE `stu_teach_rel` (
  `id` int(11) NOT NULL,
  `stu_id` int(11) NOT NULL,
  `teach_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `stu_teach_rel` VALUES (‘1‘, ‘1‘, ‘1‘);
INSERT INTO `stu_teach_rel` VALUES (‘2‘, ‘2‘, ‘1‘);
INSERT INTO `stu_teach_rel` VALUES (‘3‘, ‘1‘, ‘2‘);
INSERT INTO `stu_teach_rel` VALUES (‘4‘, ‘2‘, ‘2‘);
INSERT INTO `stu_teach_rel` VALUES (‘5‘, ‘2‘, ‘3‘);
INSERT INTO `stu_teach_rel` VALUES (‘6‘, ‘3‘, ‘1‘);
INSERT INTO `stu_teach_rel` VALUES (‘7‘, ‘3‘, ‘2‘);
INSERT INTO `stu_teach_rel` VALUES (‘8‘, ‘1‘, ‘3‘);
INSERT INTO `stu_teach_rel` VALUES (‘9‘, ‘4‘, ‘1‘);
INSERT INTO `stu_teach_rel` VALUES (‘10‘, ‘4‘, ‘2‘);
INSERT INTO `stu_teach_rel` VALUES (‘11‘, ‘5‘, ‘3‘);
INSERT INTO `stu_teach_rel` VALUES (‘12‘, ‘5‘, ‘4‘);
INSERT INTO `stu_teach_rel` VALUES (‘13‘, ‘6‘, ‘1‘);
INSERT INTO `stu_teach_rel` VALUES (‘14‘, ‘6‘, ‘3‘);

package com.yihaomen.mybatis.model;
import com.yihaomen.mybatis.enums.Gender;
import java.util.List;

public class Student {
    private String id;
    private String name;
    private int age;
    private Gender gender;
    private List<Teacher> teachers;

    setters&getters
    
}

package com.yihaomen.mybatis.model;
import com.yihaomen.mybatis.enums.Gender;
import com.yihaomen.mybatis.enums.Subject;
import java.util.List;

public class Teacher {
    private int id;
    private String name;
    private Gender gender;
    private Subject subject;
    private String degree;
    private List<Student> students;

    setters&getters
}

package com.yihaomen.mybatis.dao;
import com.yihaomen.mybatis.model.Student;
import org.springframework.stereotype.Repository;
import java.util.List;

@Repository
public interface StudentMapper {
    List<Student> selectStudents();
}

<?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.yihaomen.mybatis.dao.StudentMapper">
    <resultMap id="studentMap" type="Student">
        <id property="id" column="id" />
        <result property="name" column="name" />
        <result property="age" column="age" />
        <result property="gender" column="gender" typeHandler="org.apache.ibatis.type.EnumOrdinalTypeHandler" />
    </resultMap>
    <resultMap id="collectionMap" type="Student" extends="studentMap">
        <collection property="teachers" ofType="Teacher">
            <id property="id" column="teach_id" />
            <result property="name" column="tname"/>
            <result property="gender" column="tgender" typeHandler="org.apache.ibatis.type.EnumOrdinalTypeHandler"/>
            <result property="subject" column="tsubject" typeHandler="org.apache.ibatis.type.EnumTypeHandler"/>
            <result property="degree" column="tdegree" javaType="string" jdbcType="VARCHAR"/>
        </collection>
    </resultMap>
    <select id="selectStudents" resultMap="collectionMap">
        SELECT
            s.id, s.name, s.gender, t.id teach_id, t.name tname, t.gender tgender, t.subject tsubject, t.degree tdegree
        FROM
            student s
        LEFT JOIN
            stu_teach_rel str
        ON
            s.id = str.stu_id
        LEFT JOIN
            teacher t
        ON
            t.id = str.teach_id
    </select>
</mapper>

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <properties resource="jdbc.properties"/>
    <typeAliases>
        <typeAlias alias="Student" type="com.yihaomen.mybatis.model.Student" />
        <typeAlias alias="Teacher" type="com.yihaomen.mybatis.model.Teacher" />
    </typeAliases>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}" />
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="com/yihaomen/mybatis/model/Student.xml"/>
    </mappers>
</configuration>

package com.yihaomen.service.student;
import com.yihaomen.mybatis.dao.StudentMapper;
import com.yihaomen.mybatis.model.Student;
import com.yihaomen.mybatis.model.Teacher;
import com.yihaomen.service.BaseTest;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import java.util.List;

public class TestStudent extends BaseTest {
    public static void testStuTeachRela() {
        SqlSessionFactory sqlSessionFactory = getSession();
        SqlSession session = sqlSessionFactory.openSession();
        StudentMapper mapper = session.getMapper(StudentMapper.class);
        List<Student> list = mapper.selectStudents();

        for(Student s : list) {
            System.out.println("------------------");
            System.out.println(s.getName() + "," + s.getAge() + "," + s.getGender());
            for(Teacher t : s.getTeachers()) {
                System.out.println(t.getName() + "," + t.getGender() + "," + t.getSubject());
            }
        }
    }

    public static void main(String[] args) {
        testStuTeachRela();
    }
}