Mybatis一对一一对多嵌套查询

系统管理员 2022-12-01 15:53 227阅读 0赞

1.模拟数据, 用户表user   用户儿子表user\_son(一对多)   用户类型表user\_type (一对一)

DROP TABLE IF EXISTS `user`;
    CREATE TABLE `user` (
      `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
      `name` varchar(11) DEFAULT NULL COMMENT '名字',
      `password` varchar(11) DEFAULT NULL COMMENT '密码',
      `age` varchar(1) DEFAULT NULL COMMENT '年龄',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户';
    
    insert  into `user`(`id`,`name`,`password`,`age`) values 
    (1,'张三','123','1'),(2,'李四','456','2'),(3,'王五','789','2'),
    (4,'马六','321','3'),(5,'老气','654','3'),(6,'沙巴','987','4');
    
    
    DROP TABLE IF EXISTS `user_son`;
    CREATE TABLE `user_son` (
      `id` bigint(1) NOT NULL AUTO_INCREMENT COMMENT 'id',
      `p_id` bigint(11) DEFAULT NULL COMMENT '父id',
      `username` varchar(11) DEFAULT NULL COMMENT '用户名',
      `detail` varchar(11) DEFAULT NULL COMMENT '详情',
      `age` varchar(1) DEFAULT NULL COMMENT '年龄',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COMMENT='用户儿子';
    
    insert  into `user_son`(`id`,`p_id`,`username`,`detail`,`age`) values 
    (1,1,'张三1','张三好吧','1'),(2,1,'张三2','张三1','2'),(3,1,'张三3','张三3','3'),
    (4,1,'张三4','张三4','4'),(5,2,'李四1','李四1','1'),(6,2,'李四2','李四2','2'),
    (7,2,'李四3','李四3','3'),(8,3,'王五1','王五1','1'),(9,3,'王五2','王五2','2'),
    (10,4,'马六1','马六1','1'),(11,4,'马六2','马六2','2'),(12,4,'马六3','马六3','3'),
    (13,5,'老气1','老气1','1'),(14,5,'老气2','老气2','2'),(15,6,'傻吧','傻吧','1');
    
    
    DROP TABLE IF EXISTS `user_type`;
    CREATE TABLE `user_type` (
      `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
      `user_id` bigint(11) DEFAULT NULL COMMENT 'userId',
      `name` varchar(11) DEFAULT NULL COMMENT '名字',
      `type` varchar(1) DEFAULT NULL COMMENT '类型',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COMMENT='用户类型';
    
    insert  into `user_type`(`id`,`user_id`,`name`,`type`) values 
    (1,1,'张三类型','1'),(2,2,'李四类型','2'),(3,3,'王五类型','3'),
    (4,4,'马六类型','4'),(5,5,'老气类型','5'),(6,6,'傻吧类型','6');

2.配置实体接参数

@AllArgsConstructor
    @NoArgsConstructor
    @Data
    @ApiModel(description = "用户表")
    public class User implements Serializable {
        @ApiModelProperty(value = "id" ,example = "2")
        private Long id;
    
        @ApiModelProperty(value = "名字" ,example = "名字")
        private String name;
    
        @ApiModelProperty(value = "密码" ,example = "密码")
        private String password;
    
        @ApiModelProperty(value = "年龄" ,example = "年龄")
        private String age;
    
        @ApiModelProperty(value = "用户儿子信息")
        private List<UserSon> userSonList;
    
        @ApiModelProperty(value = "用户类型信息")
        private UserType userType;
       
    }

3.模拟查询用户表.并查出多个用户儿子,和一对一的用户类型信息

@Api(tags = "用户",description = "User")
    @RestController
    @RequestMapping("/manager/user")
    public class UserController {
    
        @Autowired
        private UserMapper userMapper;
        
        @CrossOrigin
        @ApiOperation("条件分页查询用户列表")
        @GetMapping("/findList")
        public Object findList(QueryParam param){
            return userMapper.findList(param);
        }
    
    }

4.查询sql

<select id="findList" resultMap="UserMap">
            select  * from `user`
        </select>
        
        <resultMap type="com.zhang.pair.model.User" id="UserMap" autoMapping="true">
            <id column="id" jdbcType="BIGINT" property="id"/>
            <result column="name" jdbcType="VARCHAR" property="name"/>
            <result column="password" jdbcType="VARCHAR" property="password"/>
            <result column="age" jdbcType="VARCHAR" property="age"/>
            
            <association property="userType" javaType="com.zhang.pair.model.UserType"
                         select="findUserType" column="id"/>
    
            
            <collection property="userSonList" ofType="com.zhang.pair.model.UserSon"
                        select="findUserSon"
                        column="id"/>
        </resultMap>
    
        
        <select id="findUserType" resultType="com.zhang.pair.model.UserType">
      	select * from user_type where user_id = #{id}
        </select>
    
        
        <select id="findUserSon" resultType="com.zhang.pair.model.UserSon">
      	select * from user_son where p_id = #{id}
        </select>

5.查询Sql对应关系详解

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nc2hlbmdxaWFuZzE2OA_size_16_color_FFFFFF_t_70][]

6.使用 resultType 接参数,需要配置

#开启驼峰命名  譬如数据库create_time 自动映射pojo属性createTime
    mybatis.configuration.map-underscore-to-camel-case=true

7.返回结果

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nc2hlbmdxaWFuZzE2OA_size_16_color_FFFFFF_t_70 1][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nc2hlbmdxaWFuZzE2OA_size_16_color_FFFFFF_t_70]: /images/20221123/06d036fa5557492ca3a15ed0f9ec750d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nc2hlbmdxaWFuZzE2OA_size_16_color_FFFFFF_t_70 1]: /images/20221123/53f661bfa39148f98280bd269e17baae.png