Mybatis基础教程(十三)
一对多
在上小节的基础上，测试一个班级对应多个学生的一对多关联关系。
项目结构图如下：
创建测试数据：
-- ----------------------------
-- Table structure for classroom
-- ----------------------------
DROP TABLE IF EXISTS `classroom`;
CREATE TABLE `classroom` (
`cid` int NOT NULL AUTO_INCREMENT,
`cname` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
PRIMARY KEY (`cid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of classroom
-- ----------------------------
INSERT INTO `classroom` VALUES (1, '软件工程1班');
INSERT INTO `classroom` VALUES (2, '计算机科学与技术1班');
INSERT INTO `classroom` VALUES (6, '网络工程1班');
-- ----------------------------
-- Table structure for idcard
-- ----------------------------
DROP TABLE IF EXISTS `idcard`;
CREATE TABLE `idcard` (
`id` int NOT NULL AUTO_INCREMENT,
`code` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of idcard
-- ----------------------------
INSERT INTO `idcard` VALUES (1, '610103200010013657');
INSERT INTO `idcard` VALUES (2, '610103200111013689');
INSERT INTO `idcard` VALUES (3, '610103200205013633');
INSERT INTO `idcard` VALUES (5, '610103200009093651');
INSERT INTO `idcard` VALUES (7, '610103200108083659');
-- ----------------------------
-- 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 = 5 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);
创建测试实体类：
//ClassRoom.java
//班级表
@Data
@AllArgsConstructor
@NoArgsConstructor
public class ClassRoom {
private Integer cid;// 班级的编号
private String cname; // 班级的名字
private Set<Students> stus; //学生的集合
}
//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; //学生所在班级对象
}
编写映射文件classRoomMapper.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.classRoomMapper">
<!--新增班级-->
<insert id="addClassRoom" parameterType="ClassRoom" useGeneratedKeys="true" keyProperty="cid">
insert into classroom (cname) values (#{cname})
</insert>
<!--根据班级编号查询班级-->
<select id="getClassRoom" parameterType="int" resultType="ClassRoom">
select * from classroom where cid = #{cid}
</select>
<!--查询班级下的所有学生-->
<select id="getClassRoomWithStudents" parameterType="java.lang.Integer" resultMap="ClassRoomWithStudentsResult">
select c.*,s.sid,s.sname,s.gender,s.birthday,s.major from students s, classroom c
where s.class_room_id = c.cid and c.cid = #{cid}
</select>
<resultMap id="ClassRoomWithStudentsResult" type="ClassRoom">
<id property="cid" column="cid"/>
<result property="cname" column="cname"></result>
<!--一对多关联-->
<collection property="stus" ofType="Students">
<id property="sid" column="sid"/>
<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>
</resultMap>
</mapper>
编写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>
<!--新增学生-->
<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>
在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"/>
</mappers>
</configuration>
编写测试类
//ClassRoomMapperTest.java
public class ClassRoomMapperTest {
//测试添加班级资料
@Test
public void testAddClassRoom(){
SqlSession session = null;
ClassRoom classRoom = new ClassRoom();
classRoom.setCname("网络工程1班");
try {
session = SqlSessionFactoryUtil.openSqlSession();
session.insert("addClassRoom",classRoom);
session.commit();
} catch (Exception ex) {
ex.printStackTrace();
} finally {
if (session != null) {
session.close();
}
}
}
//测试添加学生资料
@Test
public void testAddStudent(){
SqlSession session = null;
Students s1 = new Students();
s1.setSname("张无忌");
s1.setGender("男");
s1.setBirthday("2001-08-08");
s1.setMajor("软件工程");
IdCard card1 = new IdCard();
card1.setCode("610103200108083659");
s1.setCard(card1);
try {
session = SqlSessionFactoryUtil.openSqlSession();
ClassRoom c = session.selectOne("getClassRoom",6);
s1.setClassRoom(c);
session.insert("addIdCard",card1);
session.insert("addStudent",s1);
session.commit();
} catch (Exception ex) {
ex.printStackTrace();
} finally {
if (session != null) {
session.close();
}
}
}
//测试根据班级编号查询班级信息
@Test
public void testQueryClassRoomByCid(){
SqlSession session = null;
try {
session = SqlSessionFactoryUtil.openSqlSession();
ClassRoom c = session.selectOne("getClassRoomWithStudents",1);
System.out.println(c);
} 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();
}
}
}
}