Mybatis基础教程(十四)
多对多
在上小节的基础上，测试一个学生对应多个教师，一个教师也可以对应多个学生的多对多关联关系。
项目结构图如下：
students/teachers/students_teachers三表之间的关系如下图：
创建测试数据：
-- ----------------------------
-- Table structure for students
-- ----------------------------
DROP TABLE IF EXISTS `students`;
CREATE TABLE `students` (
`sid` int NOT NULL AUTO_INCREMENT,
`sname` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
`gender` varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
`birthday` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
`major` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
`cid` int NULL DEFAULT NULL,
`class_room_id` int NULL DEFAULT NULL,
PRIMARY KEY (`sid`) USING BTREE,
INDEX `fk_cid`(`cid`) USING BTREE,
INDEX `fk_classroomId`(`class_room_id`) USING BTREE,
CONSTRAINT `fk_cid` FOREIGN KEY (`cid`) REFERENCES `idcard` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `fk_classroomId` FOREIGN KEY (`class_room_id`) REFERENCES `classroom` (`cid`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 10 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of students
-- ----------------------------
INSERT INTO `students` VALUES (1, '张三三', '男', '2000-10-01', '软件工程', 1, 1);
INSERT INTO `students` VALUES (2, '李四四', '女', '2001-11-01', '计算机科学与技术', 2, 1);
INSERT INTO `students` VALUES (3, '王五五', '男', '2002-05-01', '软件工程', 3, 2);
INSERT INTO `students` VALUES (7, '张三', '男', '2000-09-09', '软件工程', 5, 1);
INSERT INTO `students` VALUES (9, '张无忌', '男', '2001-08-08', '软件工程', 7, 6);
-- ----------------------------
-- Table structure for students_teachers
-- ----------------------------
DROP TABLE IF EXISTS `students_teachers`;
CREATE TABLE `students_teachers` (
`id` int NOT NULL AUTO_INCREMENT,
`sid` int NULL DEFAULT NULL,
`tid` int NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
INDEX `fk_sid`(`sid`) USING BTREE,
INDEX `fk_tid`(`tid`) USING BTREE,
CONSTRAINT `fk_sid` FOREIGN KEY (`sid`) REFERENCES `students` (`sid`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `fk_tid` FOREIGN KEY (`tid`) REFERENCES `teachers` (`tid`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of students_teachers
-- ----------------------------
INSERT INTO `students_teachers` VALUES (1, 1, 1);
INSERT INTO `students_teachers` VALUES (2, 9, 1);
INSERT INTO `students_teachers` VALUES (3, 2, 2);
INSERT INTO `students_teachers` VALUES (4, 7, 2);
INSERT INTO `students_teachers` VALUES (5, 3, 3);
INSERT INTO `students_teachers` VALUES (6, 1, 3);
INSERT INTO `students_teachers` VALUES (7, 2, 3);
-- ----------------------------
-- Table structure for teachers
-- ----------------------------
DROP TABLE IF EXISTS `teachers`;
CREATE TABLE `teachers` (
`tid` int NOT NULL AUTO_INCREMENT,
`tname` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
PRIMARY KEY (`tid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of teachers
-- ----------------------------
INSERT INTO `teachers` VALUES (1, '张老师');
INSERT INTO `teachers` VALUES (2, '李老师');
INSERT INTO `teachers` VALUES (3, '陈老师');
创建测试实体类
//Students.java
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Students {
private Integer sid; //学号
private String sname; //姓名
private String gender; //性别
private String birthday; //出生日期
private String major; //专业
private IdCard card; //学生持有身份证
private ClassRoom classRoom; //学生所在班级
private Set<Teachers> teachers;
}
//Teachers.java
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Teachers {
private Integer tid; //教师编号
private String tname; //教师姓名
private Set<Students> stus; //学生的集合
}
//Students_Teachers.java
//学生教师关系中间表
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Students_Teachers {
private Integer id; //主键
private Integer sid; //学生编号
private Integer tid; //教师编号
}
编写映射文件studentsMapper.xml
<?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.mapper.studentsMapper">
<!--查询所有学生-->
<select id="getAllStudents" resultType="Students">
select * from students
</select>
<!--根据学生编号查询学生资料原始版-->
<select id="getStudent" parameterType="int" resultType="Students" resultMap="IdCardWithStudentsResult">
select * from students where sid=#{sid}
</select>
<resultMap id="IdCardWithStudentsResult" type="Students">
<id property="sid" column="sid"></id>
<result property="sname" column="sname"></result>
<result property="gender" column="gender"></result>
<result property="birthday" column="birthday"></result>
<result property="major" column="major"></result>
<!--一对一-->
<association property="card" column="cid" javaType="IdCard" select="com.mapper.idCardMapper.getIdCard"/>
</resultMap>
<!--根据学生编号查询学生资料优化版-->
<select id="getStudentBySid" parameterType="int" resultType="Students" resultMap="IdCardWithStudentsResult2">
select s.*,c.code from students s,idcard c where s.cid = c.id and s.sid=#{sid}
</select>
<resultMap id="IdCardWithStudentsResult2" type="Students">
<id property="sid" column="sid"></id>
<result property="sname" column="sname"></result>
<result property="gender" column="gender"></result>
<result property="birthday" column="birthday"></result>
<result property="major" column="major"></result>
<!--一对一-->
<association property="card" javaType="IdCard">
<id property="id" column="cid"></id>
<result property="code" column="code"></result>
</association>
</resultMap>
<!--根据学生编号查询学生及班级资料-->
<select id="getStudentWithClassRoomBySid" parameterType="int" resultType="Students" resultMap="IdCardWithStudentsResult3">
select s.*,c.code,cr.cname from students s,idcard c,classroom cr where s.cid = c.id and s.class_room_id=cr.cid and s.sid=#{sid}
</select>
<resultMap id="IdCardWithStudentsResult3" type="Students">
<id property="sid" column="sid"></id>
<result property="sname" column="sname"></result>
<result property="gender" column="gender"></result>
<result property="birthday" column="birthday"></result>
<result property="major" column="major"></result>
<!--一对一查询身份证-->
<association property="card" javaType="IdCard">
<id property="id" column="cid"></id>
<result property="code" column="code"></result>
</association>
<!--一对一查询班级-->
<association property="classRoom" javaType="ClassRoom">
<id property="cid" column="class_room_id"></id>
<result property="cname" column="cname"></result>
</association>
</resultMap>
<!--根据学生编号查询学生和教师信息-->
<select id="getStudentsWithTeachersBySid" parameterType="int" resultMap="StudentsWithTeachersResultMap">
select * from students where sid = #{sid}
</select>
<resultMap id="StudentsWithTeachersResultMap" type="Students">
<id property="sid" column="sid"></id>
<result property="sname" column="sname"></result>
<result property="gender" column="gender"></result>
<result property="birthday" column="birthday"></result>
<result property="major" column="major"></result>
<collection property="teachers" column="sid" ofType="Teachers" select="com.mapper.teachersMapper.getTeacherBySid"/>
</resultMap>
<!--根据教师编号在中间表中查询学生集合-->
<select id="getStudentsByTid" parameterType="int" resultType="Students">
select * from students where sid in (
select sid from students_teachers where tid=#{tid}
)
</select>
<!--新增学生-->
<insert id="addStudent" parameterType="Students" useGeneratedKeys="true" keyProperty="sid">
insert into students (sname,gender,birthday,major,cid,class_room_id)
values
(#{sname},#{gender},#{birthday},#{major},#{card.id},#{classRoom.cid})
</insert>
<!--更新学生-->
<update id="updateStudent" parameterType="Students">
update students set sname = #{sname},gender=#{gender},birthday=#{birthday},major=#{major} where sid = #{sid}
</update>
<!--删除学生-->
<delete id="deleteStudents" parameterType="int">
delete from students where sid = #{sid}
</delete>
</mapper>
编写teachersMapper.xml
<?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.mapper.teachersMapper">
<!--查询所有的教师-->
<select id="getAllTeachers" resultType="Teachers">
select * from teachers
</select>
<!--根据教师编号查询某个教师资料-->
<select id="getTeacher" parameterType="int" resultType="Teachers">
select * from teachers where tid=#{tid}
</select>
<!--根据学生编号在中间表中查询教师集合-->
<select id="getTeacherBySid" parameterType="int" resultType="Teachers">
select * from teachers where tid in (
select tid from students_teachers where sid=#{sid}
)
</select>
<!--根据教师编号查询教师和学生资料-->
<select id="getTeachersWithStudentsByTid" parameterType="int" resultMap="TeachersWithStudentsResultMap">
select * from teachers where tid=#{tid}
</select>
<resultMap id="TeachersWithStudentsResultMap" type="Teachers">
<id property="tid" column="tid"/>
<result property="tname" column="tname"/>
<collection property="stus" column="tid" ofType="Students" select="com.mapper.studentsMapper.getStudentsByTid">
</collection>
</resultMap>
<!--新增教师-->
<insert id="addTeacher" parameterType="Teachers" useGeneratedKeys="true" keyProperty="tid">
insert into teachers (tname) values (#{tname})
</insert>
</mapper>
编写studentsTeachersMapper.xml
<?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.mapper.studentsTeachersMapper">
<!--新增学生教师关系-->
<insert id="addStudentsAndTeacher" parameterType="Students_Teachers" useGeneratedKeys="true" keyProperty="id">
insert into students_teachers (sid,tid) values (#{sid},#{tid})
</insert>
</mapper>
在mybatis-config.xml添加映射文件
<?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="db.properties"/>
<!--配置加载策略-->
<settings>
<!--打开延迟加载的开关-->
<setting name="lazyLoadingEnabled" value="true"/>
<!--将积极加载改为按需加载-->
<setting name="aggressiveLazyLoading" value="false"/>
</settings>
<!-- 配置映射类的别名 -->
<typeAliases>
<package name="com.entity"/>
</typeAliases>
<!--数据源配置-->
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${mysql.driver}"/>
<property name="url" value="${mysql.url}"/>
<property name="username" value="${mysql.username}"/>
<property name="password" value="${mysql.password}"/>
</dataSource>
</environment>
</environments>
<!--映射文件-->
<mappers>
<mapper resource="com/mapper/idCardMapper.xml"/>
<mapper resource="com/mapper/studentsMapper.xml"/>
<mapper resource="com/mapper/classRoomMapper.xml"/>
<mapper resource="com/mapper/teachersMapper.xml"/>
<mapper resource="com/mapper/studentsTeachersMapper.xml"/>
</mappers>
</configuration>
编写测试类
TeachersMapperTest.java
public class TeachersMapperTest {
//测试添加教师资料
@Test
public void testAddTeacher(){
SqlSession session = null;
Teachers t1 = new Teachers();
t1.setTname("陈老师");
try {
session = SqlSessionFactoryUtil.openSqlSession();
session.insert("addTeacher",t1);
session.commit();
} catch (Exception ex) {
ex.printStackTrace();
} finally {
if (session != null) {
session.close();
}
}
}
//测试添加教师资料
@Test
public void testQueryTeachersWithSid(){
SqlSession session = null;
try {
session = SqlSessionFactoryUtil.openSqlSession();
List<Teachers> teachersList= session.selectList("getTeacherBySid",1);
System.out.println(teachersList);
} catch (Exception ex) {
ex.printStackTrace();
} finally {
if (session != null) {
session.close();
}
}
}
@Test
public void testQueryTeachersWithStudentsByTid(){
SqlSession session = null;
try {
session = SqlSessionFactoryUtil.openSqlSession();
Teachers teacher= session.selectOne("getTeachersWithStudentsByTid",3);
System.out.println(teacher);
} catch (Exception ex) {
ex.printStackTrace();
} finally {
if (session != null) {
session.close();
}
}
}
}
StudentsTeachersMapperTest.java
public class StudentsTeachersMapperTest {
@Test
public void testAddStudentsTeachersRelationship() {
SqlSession session = null;
Teachers t = null;
Students s = null;
Students_Teachers st = null;
try {
session = SqlSessionFactoryUtil.openSqlSession();
t = session.selectOne("getTeacher",3);
s = session.selectOne("getStudent",2);
st = new Students_Teachers();
st.setSid(s.getSid());
st.setTid(t.getTid());
session.insert("addStudentsAndTeacher", st);
session.commit();
} catch (Exception ex) {
ex.printStackTrace();
} finally {
if (session != null) {
session.close();
}
}
}
}
StudentsMapperTest.java
public class StudentsMapperTest {
@Test
public void testAddStudents(){
SqlSession session = null;
Students s1 = new Students();
s1.setSname("张三");
s1.setGender("男");
s1.setBirthday("2000-09-09");
s1.setMajor("软件工程");
IdCard card1 = new IdCard();
card1.setCode("610103200009093651");
s1.setCard(card1);
try {
session = SqlSessionFactoryUtil.openSqlSession();
session.insert("addIdCard",card1);
session.insert("addStudent",s1);
session.commit();
} catch (Exception ex) {
ex.printStackTrace();
} finally {
if (session != null) {
session.close();
}
}
}
@Test
public void testQueryStudentsBySid(){
SqlSession session = null;
try {
session = SqlSessionFactoryUtil.openSqlSession();
Students s = session.selectOne("getStudent",7);
System.out.println(s);
} catch (Exception ex) {
ex.printStackTrace();
} finally {
if (session != null) {
session.close();
}
}
}
@Test
public void testQueryStudentsBySid2(){
SqlSession session = null;
try {
session = SqlSessionFactoryUtil.openSqlSession();
Students s = session.selectOne("getStudentBySid",7);
System.out.println(s);
} catch (Exception ex) {
ex.printStackTrace();
} finally {
if (session != null) {
session.close();
}
}
}
@Test
public void testQueryStudentsWithClassRoomBySid(){
SqlSession session = null;
try {
session = SqlSessionFactoryUtil.openSqlSession();
Students s = session.selectOne("getStudentWithClassRoomBySid",7);
System.out.println(s);
} catch (Exception ex) {
ex.printStackTrace();
} finally {
if (session != null) {
session.close();
}
}
}
@Test
public void testQueryStudentsWithTeachersBySid(){
SqlSession session = null;
try {
session = SqlSessionFactoryUtil.openSqlSession();
Students s = session.selectOne("getStudentsWithTeachersBySid",3);
System.out.println(s);
} catch (Exception ex) {
ex.printStackTrace();
} finally {
if (session != null) {
session.close();
}
}
}
}