Mybatis基础教程(十二)
一对一
关联关系是面向对象分析、面向对象设计最终的思想,Mybatis完全可以理解这种关联关系，如果关系得当，Mybatis的关联映射将可以大大简化持久层数据的访问。关联关系大致可以分为以下情况:
- 一对一，比如一个学生只能有一张身份证，而一张身份证只能属于一学生；
- 一对多，比如一个班级有多个学生，而一个学生只能属于一个班级；
- 多对多，比如一个教师可以教多个学生，而一个学生也可以被多个老师教。
当然，还有更复杂的关系，比如在购物系统中，一个用户可以有多个订单，而一个订单只能属于一个用户，再加上商品的关系就是一对多夹杂多对多的关系，但是万变都不离其宗，都是由以上三种关系组合而成。
下面就围绕这几种关系来看看Mybatis是怎么处理的。
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');
-- ----------------------------
-- 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,
PRIMARY KEY (`sid`) USING BTREE,
INDEX `fk_cid`(`cid`) USING BTREE,
CONSTRAINT `fk_cid` FOREIGN KEY (`cid`) REFERENCES `idcard` (`id`) 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);
INSERT INTO `students` VALUES (2, '李四四', '女', '2001-11-01', '计算机科学与技术', 2);
INSERT INTO `students` VALUES (3, '王五五', '男', '2002-05-01', '软件工程', 3);
创建测试实体类：
//IdCard.java
@Data
@AllArgsConstructor
@NoArgsConstructor
public class IdCard {
private Integer id; //编号
private String code; //身份证号码
private Students stu; //身份证对应的学生对象
}
//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; //学生持有身份证
}
编写映射文件idCardMapper.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.idCardMapper">
<!--查询所有的身份证-->
<select id="getAllIdCards" resultType="IdCard">
select * from idcard
</select>
<!--根据用户编号查询某个身份证资料-->
<select id="getIdCard" parameterType="int" resultType="IdCard">
select * from idcard where id=#{id}
</select>
<!--新增身份证-->
<insert id="addIdCard" parameterType="IdCard" useGeneratedKeys="true" keyProperty="id">
insert into idcard (code) values (#{code})
</insert>
<!--根据身份证编号查询身份证信息和对应的学生信息-->
<select id="getIdCardWithStudentById" parameterType="int" resultMap="IdCardWithStudentsResult3">
select s.*,c.id,c.code from students s,idcard c where s.cid = c.id and c.id = #{id}
</select>
<resultMap id="IdCardWithStudentsResult3" type="IdCard">
<id property="id" column="id"></id>
<result property="code" column="code"></result>
<association property="stu" javaType="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>
<!--也可以不使用association,直接使用result-->
<!--
<result property="stu.sname" column="sname"/>
<result property="stu.gender" column="gender"/>
<result property="stu.birthday" column="birthday"/>
<result property="stu.major" column="major"/>
-->
</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>
<!--新增学生-->
<insert id="addStudent" parameterType="Students" useGeneratedKeys="true" keyProperty="sid">
insert into students (sname,gender,birthday,major,cid) values (#{sname},#{gender},#{birthday},#{major},#{card.id})
</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"/>
</mappers>
</configuration>
编写测试类
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();
}
}
}
}
IdCardMapperTest.java
@Test
public void testQueryIdCardWithStudentById(){
SqlSession session = null;
IdCard card = null;
try {
session = SqlSessionFactoryUtil.openSqlSession();
card = session.selectOne("getIdCardWithStudentById", 4);
System.out.println(card);
} catch (Exception ex) {
ex.printStackTrace();
} finally {
try {
if (session != null) {
session.close();
session = null;
}
} catch (Exception ex) {
ex.printStackTrace();
}
}
}