Mybatis---多表联合查询（2）

Mybatis---多表联合查询（2）

一、多对多

什么是多对多？举个栗子：一个学生选修多个课程，一个课程被多个学生选修，它们之间需要中间表进行关联，称为多对多

多对多其实就是一对多、一对一的组合

数据库准备

CREATETABLE`student`(`sid`int(11)NOTNULLAUTO_INCREMENT,`sname`varchar(20)CHARACTERSET utf8 COLLATE utf8_general_ci NULLDEFAULTNULL,PRIMARYKEY(`sid`)USINGBTREE)ENGINE=InnoDBAUTO_INCREMENT=4CHARACTERSET= utf8 COLLATE= utf8_general_ci ROW_FORMAT = Dynamic;-- ------------------------------ Records of student-- ----------------------------INSERTINTO`student`VALUES(1,'梦琪');INSERTINTO`student`VALUES(2,'初夏');INSERTINTO`student`VALUES(3,'忆柳');CREATETABLE`course`(`cid`int(11)NOTNULLAUTO_INCREMENT,`cname`varchar(20)CHARACTERSET utf8 COLLATE utf8_general_ci NULLDEFAULTNULL,PRIMARYKEY(`cid`)USINGBTREE)ENGINE=InnoDBAUTO_INCREMENT=4CHARACTERSET= utf8 COLLATE= utf8_general_ci ROW_FORMAT = Dynamic;-- ------------------------------ Records of course-- ----------------------------INSERTINTO`course`VALUES(1,'JAVA');INSERTINTO`course`VALUES(2,'HTML');INSERTINTO`course`VALUES(3,'DATABASE');CREATETABLE`stu_cou`(`scid`int(11)NOTNULLAUTO_INCREMENT,`cid`int(11)NULLDEFAULTNULL,`sid`int(11)NULLDEFAULTNULL,PRIMARYKEY(`scid`)USINGBTREE,INDEX`scs`(`sid`)USINGBTREE,INDEX`scc`(`cid`)USINGBTREE,CONSTRAINT`stu_cou_ibfk_1`FOREIGNKEY(`cid`)REFERENCES`course`(`cid`)ONDELETERESTRICTONUPDATERESTRICT,CONSTRAINT`stu_cou_ibfk_2`FOREIGNKEY(`sid`)REFERENCES`student`(`sid`)ONDELETERESTRICTONUPDATERESTRICT)ENGINE=InnoDBAUTO_INCREMENT=8CHARACTERSET= utf8 COLLATE= utf8_general_ci ROW_FORMAT = Dynamic;-- ------------------------------ Records of stu_cou-- ----------------------------INSERTINTO`stu_cou`VALUES(1,1,1);INSERTINTO`stu_cou`VALUES(2,1,2);INSERTINTO`stu_cou`VALUES(3,1,3);INSERTINTO`stu_cou`VALUES(4,2,1);INSERTINTO`stu_cou`VALUES(5,2,2);INSERTINTO`stu_cou`VALUES(6,2,3);INSERTINTO`stu_cou`VALUES(7,3,1);

实体类

这里的实体类，需要加构造、还有get、set和重写toString的（偷个懒）

Student

publicclassStudent{privateInteger sid;privateString sname;}

Stu_cou

publicclassStuCou{privateInteger scid;privateInteger cid;privateInteger sid;}

Course

publicclassCourse{privateInteger cid;privateString cname;}

StudentMapper

publicinterfaceStudentMapper{//根据学生姓名找学生信息publicStudentfindByName(String name);}

StudentMapper.xml

<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPEmapperPUBLIC"-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mappernamespace="com.ujiuye.mapper.StudentMapper"><resultMapid="resultMap1"type="com.ujiuye.entity.Student"autoMapping="true"><idcolumn="sid"property="sid"></id><!-- 学生 → 中间表 一对多的关系 通过一个学生id 可以查询出多个选修记录 --><collectionproperty="stuCouList"column="sid"ofType="stuCou"select="com.ujiuye.mapper.StuCouMapper.findBySid"></collection></resultMap><selectid="findByName"resultMap="resultMap1">
            select * from student where sname = #{name}
    </select></mapper>

StuCouMapper

publicinterfaceStuCouMapper{publicList<StuCou>findBySid(int sid);}

StuCouMapper.xml

<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPEmapperPUBLIC"-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mappernamespace="com.ujiuye.mapper.StuCouMapper"><resultMapid="resultMap1"type="stuCou"autoMapping="true"><idcolumn="scid"property="scid"></id><resultproperty="cid"column="cid"></result><associationproperty="course"column="cid"javaType="course"select="com.ujiuye.mapper.CourseMapper.findByCid"></association></resultMap><selectid="findBySid"resultMap="resultMap1">
            select * from stu_cou where sid = #{sid}
    </select></mapper>

CourseMapper

publicinterfaceCourseMapper{//根据课程id查询课程信息publicCoursefindByCid(int cid);}

CourseMapper.xml

<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPEmapperPUBLIC"-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mappernamespace="com.ujiuye.mapper.CourseMapper"><selectid="findByCid"resultType="com.ujiuye.entity.Course">
            select * from course where cid = #{cid}
    </select></mapper>

测试

publicclassDemo{@Testpublicvoidtest()throwsIOException{InputStream is =Resources.getResourceAsStream("mybatis-config.xml");SqlSessionFactory sessionFactory =newSqlSessionFactoryBuilder().build(is);SqlSession sqlSession = sessionFactory.openSession();StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);Student student = studentMapper.findByName("梦琪");List<StuCou> stuCouList = student.getStuCouList();for(StuCou stuCou : stuCouList){System.out.println(stuCou.getCid());Course course = stuCou.getCourse();System.out.println(course.getCname());}
        sqlSession.close();}}