mybatis级联操作

忘是亡心i 2022-02-17 19:26 393阅读 0赞

# **一对一关联查询** #

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0ppblhZYW4_size_16_color_FFFFFF_t_70][]

### 1、嵌套结果 ###

使用嵌套结果映射来处理重复的联合结果的子集

![20190420100950192.png][]

1.  resultMap可以通过使用extends实现继承关系，简化很多配置工作量；
2.  关联的表查询的类添加前缀是编程的好习惯；
3.  通过添加完整的命名空间，可以引用其他xml文件的resultMap；

**实例：**

**映射文件：**

<select id="selectUserPosition1" resultMap="userAndPosition1">
    	select
    	    a.id, 
    	    user_name,
    	    real_name,
    	    sex,
    	    mobile,
    	    email,
    	    a.note,
    	    b.id  post_id,
    	    b.post_name,
    	    b.note post_note
    	    from t_user a,
    	    t_position b
    	where a.position_id = b.id
    </select>
    
    <resultMap id="userAndPosition1" extends="BaseResultMap" type="TUser">
    	<association property="position" javaType="TPosition" columnPrefix="post_">
    		<id column="id" property="id"/>
    		<result column="name" property="postName"/>
    		<result column="note" property="note"/>
    	</association>
    </resultMap>
    
    <resultMap id="BaseResultMap" type="TUser">
    
    	<id column="id" property="id" />
    	<result column="user_name" property="userName" />
    	<result column="real_name" property="realName" />
    	<result column="sex" property="sex" />
    	<result column="mobile" property="mobile" />
    	<result column="email" property="email" />
    	<result column="note" property="note" />
    </resultMap>

映射的实体类中有TPosition属性：

public class TUser implements Serializable{
        private Integer id;
    
        private String userName;
    
        private String realName;
    
        private Byte sex;
    
        private String mobile;
    
        private String email;
    
        private String note;
    
        private TPosition position;
        
        private List<TJobHistory> jobs ;
        
        private List<HealthReport> healthReports;
    
        
        private List<TRole> roles;

**测试类：**

@Test
    // 1对1两种关联方式
    public void testOneToOne() {
    	// 2.获取sqlSession
    	SqlSession sqlSession = sqlSessionFactory.openSession();
    	// 3.获取对应mapper
    	TUserMapper mapper = sqlSession.getMapper(TUserMapper.class);
    	// 4.执行查询语句并返回结果
    	// ----------------------
    	List<TUser> list1 = mapper.selectUserPosition1();
    	for (TUser tUser : list1) {
    		System.out.println(tUser);
    	}
    }

### **2、嵌套查询（单表操作，推荐）** ###

通过执行另外一个 SQL 映射语句来返回预期的复杂类型

![20190420103552113.png][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0ppblhZYW4_size_16_color_FFFFFF_t_70 1][]

**xml映射文件：**

<mapper namespace="com.enjoylearning.mybatis.mapper.TUserMapper">
    
    <select id="selectUserPosition2" resultMap="userAndPosition2">
    	select
    	a.id,
    	a.user_name,
    	a.real_name,
    	a.sex,
    	a.mobile,
    	a.position_id
    	from t_user a
    </select>
    
    <resultMap id="userAndPosition2" extends="BaseResultMap" type="TUser">
    	<association property="position" fetchType="lazy"  column="position_id" 
    		     select="com.enjoylearning.mybatis.mapper.TPositionMapper.selectByPrimaryKey" />
    </resultMap>
    
    <resultMap id="BaseResultMap" type="TUser">
    	<id column="id" property="id" />
    	<result column="user_name" property="userName" />
    	<result column="real_name" property="realName" />
    	<result column="sex" property="sex" />
    	<result column="mobile" property="mobile" />
    	<result column="email" property="email" />
    	<result column="note" property="note" />
    </resultMap>

**另一个xml映射文件：**

<mapper namespace="com.enjoylearning.mybatis.mapper.TPositionMapper">
    
    <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
        
        select 
        <include refid="Base_Column_List" />
        from t_position
        where id = #{id,jdbcType=INTEGER}
    </select>
    
    <resultMap id="BaseResultMap" type="com.enjoylearning.mybatis.entity.TPosition">
        
        <id column="id" jdbcType="INTEGER" property="id" />
        <result column="post_name" jdbcType="VARCHAR" property="postName" />
        <result column="note" jdbcType="VARCHAR" property="note" />
    </resultMap>

**测试类：**

@Test
    	// 1对1两种关联方式
    	public void testOneToOne() {
    		// 2.获取sqlSession
    		SqlSession sqlSession = sqlSessionFactory.openSession();
    		// 3.获取对应mapper
    		TUserMapper mapper = sqlSession.getMapper(TUserMapper.class);
    		// 4.执行查询语句并返回结果
    		// ----------------------
    		
    		List<TUser> list2 = mapper.selectUserPosition2();
    		for (TUser tUser : list2) {
    			System.out.println(tUser.getPosition());
    		}
    	}

# **一对多关联查询** #

<resultMap>元素中,包含了一个<collection>子元素,MyBatis就是通过该元素来处理一对多关联关系的

<collection>子元素的属性大部分与<association>元素相同,但其还包含一个特殊属性--ofType

ofType属性与javaType属性对应,它用于指定实体对象中集合类属性所包含的元素类型。

<collection >元素的使用也非常简单,同样可以参考如下两种示例进行配置,具体代码如下:

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0ppblhZYW4_size_16_color_FFFFFF_t_70 2][]

** 实例：**

**映射文件：**

<select id="selectUserJobs1" resultMap="userAndJobs1">
    	select
    	a.id,
    	a.user_name,
    	a.real_name,
    	a.sex,
    	a.mobile,
    	b.comp_name,
    	b.years,
    	b.title
    	from t_user a,
    	t_job_history b
    	where a.id = b.user_id
    </select>
    
    <resultMap id="userAndJobs1" extends="BaseResultMap" type="TUser">
    	<collection property="jobs"
    		ofType="com.enjoylearning.mybatis.entity.TJobHistory" >
    		<result column="comp_name" property="compName" jdbcType="VARCHAR" />
    		<result column="years" property="years" jdbcType="INTEGER" />
    		<result column="title" property="title" jdbcType="VARCHAR" />
    	</collection>
    </resultMap>
    
    <resultMap id="BaseResultMap" type="TUser">
    	<id column="id" property="id" />
    	<result column="user_name" property="userName" />
    	<result column="real_name" property="realName" />
    	<result column="sex" property="sex" />
    	<result column="mobile" property="mobile" />
    	<result column="email" property="email" />
    	<result column="note" property="note" />
    </resultMap>

对应的JavaBean实体类：对应属性为一个List类型

**测试类：**

@Test
    	// 1对多两种关联方式
    	public void testOneToMany() {
    		// 2.获取sqlSession
    		SqlSession sqlSession = sqlSessionFactory.openSession();
    		// 3.获取对应mapper
    		TUserMapper mapper = sqlSession.getMapper(TUserMapper.class);
    		// 4.执行查询语句并返回结果
    		// ----------------------
    		List<TUser> selectUserJobs1 = mapper.selectUserJobs1();
    		List<TUser> selectUserJobs2 = mapper.selectUserJobs2();
    		for (TUser tUser : selectUserJobs1) {
    			System.out.println(tUser);
    		}
    		for (TUser tUser : selectUserJobs2) {
    			System.out.println(tUser.getJobs().size());
    		}
    	}

sql数据库脚本：

创建t_user表
    CREATE TABLE `t_user` (
      `id` int(20) NOT NULL AUTO_INCREMENT,
      `user_name` varchar(60) DEFAULT NULL COMMENT '用户名称',
      `real_name` varchar(60) DEFAULT NULL COMMENT '真实名称',
      `sex` tinyint(3) DEFAULT NULL COMMENT '姓名',
      `mobile` varchar(20) DEFAULT NULL COMMENT '电话',
      `email` varchar(60) DEFAULT NULL COMMENT '邮箱',
      `note` varchar(200) DEFAULT NULL COMMENT '备注',
      `position_id` int(20) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `fk_4` (`position_id`),
      CONSTRAINT `fk_4` FOREIGN KEY (`position_id`) REFERENCES `t_position` (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=127 DEFAULT CHARSET=utf8;
    
    插入
    INSERT INTO `t_user` VALUES ('1', 'lison', '李小宇', '1', '186995587411', 'lison@qq.com', 'lison的备注', '1');
    INSERT INTO `t_user` VALUES ('2', 'james', '陈大雷', '1', '18677885200', 'james@qq.com', 'james的备注', '2');
    INSERT INTO `t_user` VALUES ('3', 'cindy', '王美丽', '2', '18695988747', 'xxoo@163.com', 'cindy\'s note', '1');
    INSERT INTO `t_user` VALUES ('126', 'mark', '毛毛', '1', '18695988747', 'xxoo@163.com', 'mark\'s note', '1');
    
    
    
    
    创建t_job_history表
    CREATE TABLE `t_job_history` (
      `id` int(20) NOT NULL AUTO_INCREMENT,
      `user_id` int(20) DEFAULT NULL,
      `comp_name` varchar(50) DEFAULT NULL,
      `years` int(3) DEFAULT NULL,
      `title` varchar(50) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `fk_5` (`user_id`),
      CONSTRAINT `fk_5` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
    插入
    INSERT INTO `t_job_history` VALUES ('1', '1', '阿里', '2', '程序员');
    INSERT INTO `t_job_history` VALUES ('2', '2', '百度', '4', '项目经理');
    INSERT INTO `t_job_history` VALUES ('3', '2', '腾讯', '1', '程序员');
    INSERT INTO `t_job_history` VALUES ('4', '3', '京东', '1', '测试');
    INSERT INTO `t_job_history` VALUES ('5', '3', '网易', '2', '测试主管');
    INSERT INTO `t_job_history` VALUES ('6', '3', '享学', '1', '讲师');

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0ppblhZYW4_size_16_color_FFFFFF_t_70]: /images/20220217/aea363dd84134c2f807305def775922c.png
[20190420100950192.png]: /images/20220217/9a2c79b0b0a14008a9a5c6f662b1739e.png
[20190420103552113.png]: /images/20220217/e109217f9b834150b857b807a6c2c96e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0ppblhZYW4_size_16_color_FFFFFF_t_70 1]: /images/20220217/68d441fb8aa8407188d0c2a181382dd5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0ppblhZYW4_size_16_color_FFFFFF_t_70 2]: /images/20220217/432ad039021c49199cb09071357925a4.png