Mybatis复杂结果集映射(详细)-阿里云开发者社区

Mybatis复杂结果集映射(详细)

2023-08-03 62

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

简介： Mybatis复杂结果集映射(详细)

多对一：

多对一的理解：

多个女神对应一个男神
对于女神这边，就是一个多对一的现象，即从女神这边关联一个男神

数据库设计

# 女神表
CREATE TABLE `beauty` (
    // 编号
  `id` int(11) NOT NULL AUTO_INCREMENT,
    // 姓名
  `name` varchar(50) NOT NULL,
    // 生日
  `bornDate` datetime DEFAULT '1987-01-01 00:00:00',
    // 电话
  `phone` varchar(11) NOT NULL,
    // 男神id
  `boyfriendId` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert  into `beauty` values 
(1,'柳岩','1988-02-03 00:00:00','18209876577',5),
(2,'苍老师','1987-12-30 00:00:00','18219876577',5),
(3,'Angelababy','1989-02-03 00:00:00','18209876567',3),
(4,'热巴','1993-02-03 00:00:00','18209876579',2),
(5,'周冬雨','1992-02-03 00:00:00','18209179577',9),
(6,'周芷若','1988-02-03 00:00:00','18209876577',1),
(7,'岳灵珊','1987-12-30 00:00:00','18219876577',9),
(8,'小昭','1989-02-03 00:00:00','18209876567',1),
(9,'双儿','1993-02-03 00:00:00','18209876579',9),
(10,'王语嫣','1992-02-03 00:00:00','18209179577',4),
(11,'夏雪','1993-02-03 00:00:00','18209876579',9),
(12,'赵敏','1992-02-03 00:00:00','18209179577',1),
(13,'紫霞仙子','1999-12-10 00:00:00','17303773603',7),
(14,'嫦娥','1234-02-03 00:00:00','1234',3),
(15,'阳丽','1999-09-21 00:00:00','0987654',5),
(16,'关晓彤','1999-12-10 00:00:00','1234',5),
(17,'张靓颖','1999-01-11 00:00:00','145678',6),
(18,'貂蝉','1978-12-12 00:00:00','098765',1954),
(19,'不知火舞','1999-12-13 00:00:00','2345689',5),
(20,'阿珂','1223-08-09 00:00:00','12456789',5);
#男神表
CREATE TABLE `boys` (
    // 男神id
  `id` int(11) NOT NULL AUTO_INCREMENT,
    // 男神姓名
  `boyName` varchar(20) DEFAULT NULL,
    // 魅力值
  `userCP` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert  into `boys` values 
(1,'张无忌',100),
(2,'鹿晗',800),
(3,'黄晓明',50),
(4,'段誉',300),
(5,'齐菁菁',360),
(6,'刘祥',260),
(7,'牛辉',300),
(8,'武怡',123),
(10,'王一帆',10),
(12,'詹三',100),
(17,'齐元松',180),
(20,'朱辉',246);

编写实体类

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Beauty {
    /**
     * 女神id
     */
    private int id;
    /**
     * 女神姓名
     */
    private String name;
    /**
     * 出生日期
     */
    private Date bornDate;
    /**
     * 手机号
     */
    private String phone;
    /**
     * 对应男神id
     */
    private int boyfriendId;
    /**
     * 对应男神信息
     */
    private Boys boys;
}

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Boys {
    /**
     * 男神id
     */
    private int id;
    /**
     * 男神姓名
     */
    private String boyName;
    /**
     * 魅力值
     */
    private int userCP;
}

编写两个实体类的接口

public interface BeautyMapper {
}

public interface BoysMapper {
}

编写对应的配置文件

BeautyMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mapper.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.lili.dao.BeautyMapper">
</mapper>

BoysMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mapper.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.lili.dao.BoysMapper">
</mapper>

编写需求代码

1.给BeautyMapper接口增加方法

List<Beauty> findBeautyBoy();

2.编写对应的Mapper文件

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mapper.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.lili.dao.BeautyMapper">
    <!--结果集映射-->
    <resultMap id="findBeautyBoyMap" type="com.lili.entity.Beauty" autoMapping="true">
        <!--由于两个表id一样，所以需要用别名来区别-->
        <id property="id" column="b1_id"/>
        <association property="boys" javaType="com.lili.entity.Boys" autoMapping="true">
            <id property="id" column="b2_id"/>
        </association>
    </resultMap>
    <!--查询所有的女神以及她的男神信息（多对一）-->
    <select id="findBeautyBoy" resultMap="findBeautyBoyMap">
        select b1.id as b1_id,
               b1.name,
               b1.bornDate,
               b1.phone,
               b1.boyfriendId,
               b2.id as b2_id,
               b2.boyName,
               b2.userCP
        from beauty b1
                 left join boys b2 on
            b1.boyfriendId = b2.id
    </select>
</mapper>

3.测试

@Test
public void test1() {
    // 利用多对一查询所有女神及女神的男神名字
    try (SqlSession sqlSession = MybatisUtil.getSqlSession()) {
        BeautyMapper mapper = sqlSession.getMapper(BeautyMapper.class);
        List<Beauty> beauty = mapper.findBeautyBoy();
        beauty.forEach(System.out::println);
    } catch (Exception e) {
        e.printStackTrace();
    }
}

一对多：

实体类编写

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Beauty {
    private int id;
    private String name; 
    private Date bornDate;   
    private String phone;
    private int boyfriendId;
}

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Boys {
    private int id;
    private String boyName;
    private int userCP;
    private List<Beauty> beautyList;
}

编写代码

1.给BeautyMapper接口增加方法

List<Boys> getBoyBeauty();

2.编写对应的Mapper文件

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mapper.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.lili.dao.BoysMapper">
    <!--结果集映射-->
    <resultMap id="getBoyBeautyMap" type="com.lili.entity.Boys" autoMapping="true">
        <result property="id" column="b2_id"/>
        <collection property="beautyList" ofType="com.lili.entity.Beauty" autoMapping="true">
            <result property="id" column="b1_id"/>
        </collection>
    </resultMap>
    <!--一对多-->
    <select id="getBoyBeauty" resultMap="getBoyBeautyMap">
        select b1.id as b1_id,
               b1.name,
               b1.bornDate,
               b1.phone,
               b1.boyfriendId,
               b2.id as b2_id,
               b2.boyName,
               b2.userCP
        from beauty b1
                 right join boys b2 on
            b1.boyfriendId = b2.id
    </select>
</mapper>

3.测试

@Test
public void test1() {
 // 利用一对多查询所有男神的女神名字
        try (SqlSession sqlSession = MybatisUtil.getSqlSession()) {          
            BoysMapper mapper = sqlSession.getMapper(BoysMapper.class);          
            List<Boys> boys = mapper.getBoyBeauty();
            boys.forEach(System.out::println);
        } catch (Exception e) {
            e.printStackTrace();
        }
}

小结：

1.关联-association

2.集合-collection

3.所以association是用于一对一和多对一，而collection是用于一对多的关系

4.JavaType和ofType都是用来指定对象类型的

JavaType是用来指定entity中属性的类型
ofType是用来指定list集合中属性的entity类型

Mybatis复杂结果集映射(详细)

多对一：

一对多：

小结：

热门文章

最新文章

相关课程

相关电子书