MyBatis框架-关联映射

MyBatis关联映射-一对一

1.1 实体关系

实体–数据实体，实体关系指的就是数据与数据之间的关系

例如：订单和商品，用户和角色

实体关系分为以下四种：

1. **一对一关联：**用户表和用户详情表

• 数据表关系：

  • 主键关联：用户表主键和用户详情表的主键相同时，表示是匹配的数据

  • 唯一外键关联：用户表和详情表使用外键关联，但需要将外键设置为唯一键

2. 一对多关联or多对一关联：

   • 一对多：班级对学生

   • 多对一：学生对班级

数据表关系：

• 在多的一段，添加外键和一的一段进行关联

3. 多对多关联：
   • 用户和角色、订单和商品

数据表关系：建立第三张关系表，添加两个外键分别和两张表主键进行关联

用户表	用户角色表	角色表
user_id	外键FK: uid=user_id ；rid=role_id	role_id

1.2 一对一关联

实例：用户表user–用户详情表 userDetail

1.2.1 创建数据表

-- 用户信息表
CREATE TABLE user(user_id int PRIMARY KEY auto_increment,user_name VARCHAR(20) NOT NULL UNIQUE,user_pwd VARCHAR(20) NOT NULL,user_realname VARCHAR(20) NOT NULL,user_img VARCHAR(100) NOT NULL
);-- 用户详情表
CREATE TABLE userDetail(detail_id INT PRIMARY KEY auto_increment,user_addr	VARCHAR(50) NOT NULL,user_tel CHAR(11) NOT NULL,user_desc VARCHAR(200),uid INT NOT null UNIQUE
);

1.2.2 创建工具类

MyBatisUtils

package com.feng.utils;import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;import java.io.IOException;
import java.io.InputStream;/*** @program: mybatis_project2* @description: MyBatis工具类* @author: FF* @create: 2024-11-28 18:52**/
public class MyBatisUtils {private static SqlSessionFactory sqlSessionFactory;//为SqlSession加锁private static final ThreadLocal<SqlSession> sqlSessionlock = new ThreadLocal<>();static {InputStream inputStream = null;try {//构建sqlSession工厂inputStream = Resources.getResourceAsStream("mybatis-config.xml");SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();sqlSessionFactory = builder.build(inputStream);inputStream.close();} catch (IOException e) {throw new RuntimeException(e);}}/*** @Description: 通过sqlSessionFactory，获取sqlSession对象（private不对外）* @Param: [isAutoCommit]* @return: org.apache.ibatis.session.SqlSession* @Author: FF* @Date: 2024/11/27*/private static SqlSession getSqlSession(boolean isAutoCommit) {SqlSession sqlSession = sqlSessionlock.get();if (sqlSession == null) {sqlSession = sqlSessionFactory.openSession(isAutoCommit);sqlSessionlock.set(sqlSession);}return sqlSession;}/*** @Description: 获取sqlSession对象，默认手动提交事务（public对外）* @Param: []* @return: org.apache.ibatis.session.SqlSession* @Author: FF* @Date: 2024/11/27*/public static SqlSession getSqlSession() {return getSqlSession(false);}/*** @Description: 提供getMapper方法，获取mapper代理对象，默认自动提交事务* @Param: [c]* @return: T* @Author: FF* @Date: 2024/11/27*/public static <T extends Object> T getMapper(Class<T> c) {return getSqlSession(true).getMapper(c);}
}

1.2.3 创建实体类

package com.feng.pojo;import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;/*** @program: mybatis_project2* @description: 用户实体类* @author: FF* @create: 2024-11-28 18:56**/
@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class User {private int userID;private String userName;private String password;private String realName;private String img;private UserDetail userDetail;
}

package com.feng.pojo;import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;/*** @program: mybatis_project2* @description: 用户详情实体类* @author: FF* @create: 2024-11-28 18:57**/
@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class UserDetail {public int detailId;public String userAddr;public String userTel;public String userDesc;public int uid;
}

1.2.4 创建Dao接口

package com.feng.dao;import com.feng.pojo.User;import java.util.List;public interface UserDao {public int addUser(User user);public List<User> selectAllUser();public User selectUserByUserName(String userName);public User selectUserByRealName(String realName);
}

package com.feng.dao;import com.feng.pojo.UserDetail;public interface UserDetailDao {public int insertUserDetail(UserDetail userDetail);public UserDetail selectUserDetailByUid(int Uid);}

1.2.5 创建MyBatis主配置文件

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="jdbc.properties"/><typeAliases><typeAlias type="com.feng.pojo.User" alias="User"/><typeAlias type="com.feng.pojo.UserDetail" alias="UserDetail"/></typeAliases><environments default="mysql"><environment id="mysql"><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="mappers/userMapper.xml"/><mapper resource="mappers/userDetailMapper.xml"/></mappers>
</configuration>

1.2.6 创建jdbc.properties

mysql_driver=com.mysql.jdbc.Driver
mysql_url=jdbc:mysql://localhost:3306/db_test?characterEncoding=utf-8
mysql_username=root
mysql_password=root

1.2.7 创建Dao映射文件

useMapper.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.feng.dao.UserDao"><resultMap id="User" type="User"><id column="user_id" property="userID"/><result column="user_name" property="userName"/><result column="user_pwd" property="password"/><result column="user_realname" property="realName"/><result column="user_img" property="img"/><!--子查询，通过主表userID关联到子表uid，查询出子表信息，赋值给User.userDetail--><association property="userDetail" select="com.feng.dao.UserDetailDao.selectUserDetailByUid" column="user_id"/><!--<result column="detail_id" property="userDetail.detailId"/><result column="user_addr" property="userDetail.userAddr"/><result column="user_tel" property="userDetail.userTel"/><result column="user_desc" property="userDetail.userDesc"/><result column="uid" property="userDetail.uid"/>--></resultMap><insert id="addUser" useGeneratedKeys="true" keyProperty="userID">insert into user(user_name,user_pwd,user_realname,user_img)values (#{userName},#{password},#{realName},#{img})</insert><select id="selectAllUser" parameterType="int" resultMap="User">SELECT *FROM user</select><select id="selectUserByUserName" resultMap="User">select * from user u inner join userDetail d on u.user_id = d.detail_id where user_name = #{userName}</select><select id="selectUserByRealName" resultMap="User" >select * from user where user_realname = #{realName}</select>
</mapper>

userDetailMapper.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.feng.dao.UserDetailDao"><resultMap id="UserDetail" type="UserDetail"><id column="detail_id" property="detailId"/><result column="user_addr" property="userAddr"/><result column="user_tel" property="userTel"/><result column="user_desc" property="userDesc"/><result column="uid" property="uid"/></resultMap><insert id="insertUserDetail">insert into userDetail(user_addr,user_tel,user_desc,uid)values (#{userAddr},#{userTel},#{userDesc},#{uid})</insert><select id="getById" >SELECT *FROM userDetailWHERE id = #{id}</select><select id="selectUserDetailByUid" resultMap="UserDetail">select * from userDetail where uid = #{Uid}</select>
</mapper>

1.2.8 创建测试类

package com.feng.dao;import com.feng.pojo.User;
import com.feng.pojo.UserDetail;
import com.feng.utils.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;import java.util.List;public class UserDaoTest {@Testpublic void selectAllUser() {UserDao mapper = MyBatisUtils.getMapper(UserDao.class);List<User> list = mapper.selectAllUser();for (User user : list) {System.out.println(user);}}@Testpublic void addUser() {SqlSession sqlSession = MyBatisUtils.getSqlSession();try {UserDao mapper = sqlSession.getMapper(UserDao.class);User user = new User();user.setUserName("admin2");user.setPassword("123456");user.setRealName("关羽");user.setImg("123");int i = mapper.addUser(user);System.out.println(i);System.out.println("userID" + user.getUserID());int uid = user.getUserID();UserDetail userDetail = new UserDetail(0, "北京市延庆区", "134113311", "test", uid);UserDetailDao userDetailDao = sqlSession.getMapper(UserDetailDao.class);userDetailDao.insertUserDetail(userDetail);sqlSession.commit();}catch (Exception e){e.printStackTrace();sqlSession.rollback();}}@Testpublic void selectUserByUserName() {UserDao mapper = MyBatisUtils.getMapper(UserDao.class);User user = mapper.selectUserByUserName("admin");System.out.println(user);}@Testpublic void selectUserByRealName() {User user = MyBatisUtils.getMapper(UserDao.class).selectUserByRealName("夏侯惇");System.out.println(user);}
}

package com.feng.dao;import com.feng.pojo.UserDetail;
import com.feng.utils.MyBatisUtils;
import org.junit.Test;import static org.junit.Assert.*;public class UserDetailDaoTest {@Testpublic void insertUserDetail() {UserDetailDao userDetailDao = MyBatisUtils.getMapper(UserDetailDao.class);int i = userDetailDao.insertUserDetail(new UserDetail(0,"北京市昌平区","13566666","test",2));System.out.println(i);}@Testpublic void selectUserDetailByUid() {UserDetailDao userDetailDao = MyBatisUtils.getMapper(UserDetailDao.class);UserDetail userDetail = userDetailDao.selectUserDetailByUid(2);System.out.println(userDetail);}
}

1.3 一对多关联

1.3.1 创建数据表

班级对学生，一个多级对多个学生关联

-- 创建班级表
CREATE TABLE classes(cid int PRIMARY KEY auto_increment,cname VARCHAR(30) NOT NULL UNIQUE,cdesc VARCHAR(100)
);-- 创建学生表
CREATE TABLE student(sid int PRIMARY KEY auto_increment,sname VARCHAR(20) NOT NULL,sage INT NOT NULL,scid int NOT NULL
);

1.3.2 创建实体类

package com.feng.pojo;import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;/*** @program: mybatis_project* @description: 班级表实体类* @author: FF* @create: 2024-11-29 15:52**/
@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class Clazz {private int cid;private String cname;private String cdesc;
}

package com.feng.pojo;import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;/*** @program: mybatis_project* @description: 学生表实体类* @author: FF* @create: 2024-11-29 15:54**/
@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class Student2 {private int sid;private String sname;private int sage;private int scid;
}

1.3.3 关联查询

当查询一个班级时，要关联查询出这个班级下的所有学生

连接查询

classMapper.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.feng.dao.ClassDao"><resultMap id="classMap" type="class"><id column="cid" property="cid"/><result column="cname" property="cname"/><result column="cdesc" property="cdesc"/><!--Clazz对象的student2List是个List集合，需要使用collection--><!--Collection标签的ofType属性声明集合中元素的类型--><collection property="student2List" ofType="Student2"><result column="sid" property="sid"/><result column="sname" property="sname"/><result column="sage" property="sage"/></collection></resultMap><select id="queryClassInfo" resultMap="classMap">select * from classes c INNER JOIN student s on c.cid = s.scid where c.cid = #{cid}</select>
</mapper>

子查询

classMapper.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.feng.dao.ClassDao"><resultMap id="classMap" type="Clazz"><id column="cid" property="cid"/><result column="cname" property="cname"/><result column="cdesc" property="cdesc"/><!--连接查询--><!-- <collection property="studentList" ofType="Student"><result column="sid" property="sid"/><result column="sname" property="sname"/><result column="sage" property="sage"/></collection>--><!--子查询--><collection property="studentList" select="com.feng.dao.StudentDao.getStudentByScid" column="cid"><id column="sid" property="sid"/><result column="sname" property="sid"/><result column="sage" property="sage"/><result column="scid" property="scid"/></collection></resultMap><!--select * from classes c inner join student s on c.cid = s.scid where c.cid = #{cid} --><select id="queryClassInfo" resultMap="classMap">select * from classes c where c.cid = #{cid}</select></mapper>

1.4 多对一

通过学生id查询学生所在班级

连接查询

StudentMapper.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.feng.dao.Student2Dao"><resultMap id="student2Map" type="Student2"><id column="sid" property="sid"/><result column="sname" property="sname"/><result column="sage" property="sage"/><result column="scid" property="scid"/><result column="cid" property="clazz.cid"/><result column="cname" property="clazz.cname"/><result column="cdesc" property="clazz.cdesc"/></resultMap><select id="queryStudent2" resultMap="student2Map">select * from student s INNER JOIN classes c ON s.scid = c.cid where s.sid = #{sid}</select></mapper>

子查询

studentMapper.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.feng.dao.Student2Dao"><resultMap id="student2Map" type="Student2"><id column="sid" property="sid"/><result column="sname" property="sname"/><result column="sage" property="sage"/><result column="scid" property="scid"/>
<!--        <result column="cid" property="clazz.cid"/>-->
<!--        <result column="cname" property="clazz.cname"/>-->
<!--        <result column="cdesc" property="clazz.cdesc"/>--><!--子查询--><association property="clazz" select="com.feng.dao.ClassDao.queryClassInfo" column="scid"/></resultMap><select id="queryStudent2" resultMap="student2Map">select * from student s where s.sid = #{sid}</select></mapper>

1.4 多对多关联

1.4.1 数据准备

-- 课程表
CREATE TABLE courses(course_id int PRIMARY KEY auto_increment,course_name VARCHAR(50) not null 
);-- 选课信息表/成绩表
CREATE TABLE greades(sid char(5) not NULL,course_id INT NOT NULL,score INT NOT NULL
);

1.4.2 关联查询

查询学生时，同时查询出学生选择的课程

• 连接查询

Student实体类

package com.feng.pojo;import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;import java.util.List;/*** @program: mybatis_project* @description: 学生表实体类* @author: FF* @create: 2024-11-29 15:54**/
@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class Student {private int sid;private String sname;private int sage;private int scid;private List<Courses> coursesList;
}

StudentMapper.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.feng.dao.StudentDao"><resultMap id="StudentMap" type="Student"><id column="sid" property="sid"/><result column="sname" property="sname"/><result column="sage" property="sage"/><result column="scid" property="scid"/><collection property="coursesList" ofType="Courses"><result property="courseId" column="course_id"/><result property="courseName" column="course_name"/></collection></resultMap><select id="getStudentBySid" resultMap="StudentMap">select s.sid,s.sname,c.course_id,c.course_name from student s INNER JOIN greades g INNER JOIN courses cON s.sid = g.sid AND g.course_id = c.course_id where s.sid = #{sid};</select>
</mapper>

• 子查询

  StudentMapper.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.feng.dao.StudentDao"><resultMap id="StudentMap" type="Student"><id column="sid" property="sid"/><result column="sname" property="sname"/><result column="sage" property="sage"/><result column="scid" property="scid"/><!--连接查询--><!--<collection property="coursesList" ofType="Courses"><result property="courseId" column="course_id"/><result property="courseName" column="course_name"/></collection>--><!--子查询--><collection property="coursesList" select="com.feng.dao.CoursesDao.getCourses" column="sid"><id property="courseId" column="course_id"/><result property="courseName" column="course_name"/></collection></resultMap><select id="getStudentBySid" resultMap="StudentMap">select s.sid,s.sname,c.course_id,c.course_name from student s INNER JOIN greades g INNER JOIN courses cON s.sid = g.sid AND g.course_id = c.course_id where s.sid = #{sid};</select>
  </mapper>