SpringBoot整合Mybatis-Plus----多表关联查询（使用注解）

• 一、创建两张表
• 二、编写实体类
• 三、使用 @One 注解实现一对一关联
• 四、使用 @Many 注解实现一对一关联
• 五、Controller层
• 六、测试

继续在 SpringBoot整合Mybatis-Plus 基础上修改项目

用户表（User）、区域表（Area），其中用户表里通过 area_id 字段关联区域表的 id 主键

User

package org.spring.springboot.entity;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import lombok.Data;

@Data
public class User {
    //指定主键使用数据库ID自增策略
    @TableId(type = IdType.AUTO)
    private Integer id;
    private String userName;
    private String passWord;
    private Integer areaId;
    //由于 area_name 不是 User 数据库表里的字段，因此需要添加 @TableField 注解，并将 exist 属性设置为 false。
    @TableField(exist = false)
    private String areaName;
    //假设我们需要查询一个区域及其下面的所有用户，首先对 Area 实体类稍作修改，增加 users 集合属性
    @TableField(exist = false)
    private Area area;
}

Area

package org.spring.springboot.entity;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import lombok.Data;
import java.util.List;

@Data
public class Area {
    //指定主键使用数据库ID自增策略
    @TableId(type = IdType.AUTO)
    private Integer id;
    private String areaName;

    @TableField(exist = false)
    private List users;
}

UserMapper

package org.spring.springboot.mapper;

import com.baomidou.mybatisplus.core.mapper.baseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.spring.springboot.entity.User;
import org.springframework.stereotype.Repository;

@Mapper
@Repository
public interface UserMapper extends baseMapper {
    @Select("select user.* ,area.area_name from user,area "+
            " where user.area_id = area.id and user.id = #{id}"
            )
    User getUserById(int id);

    @Select("SELECT * FROM user WHERe area_id = #{areaId}")
    User selectByAreaId(int areaId);
}

UserMapperOne

package org.spring.springboot.mapper;

import com.baomidou.mybatisplus.core.mapper.baseMapper;
import org.apache.ibatis.annotations.*;
import org.spring.springboot.entity.User;
import org.springframework.stereotype.Repository;

@Mapper
@Repository
public interface UserMapperOne extends baseMapper {
    //@Results 注解来映射查询结果集到实体类属性
    
    @Results({
            @Result(column = "area_id", property = "areaId"),
            @Result(column = "area_id", property = "area",
                    one = @One(select = "org.spring.springboot.mapper.AreaMapper.selectById"))
    })
    @Select("SELECT * FROM user WHERe id = #{id}")
    User getUserById(int id);
}

AreaMapper

package org.spring.springboot.mapper;

import com.baomidou.mybatisplus.core.mapper.baseMapper;
import org.apache.ibatis.annotations.*;
import org.spring.springboot.entity.Area;
import org.springframework.stereotype.Repository;

@Mapper
@Repository
public interface AreaMapper extends baseMapper {
    
    @Results({
            @Result(column = "id", property = "id"),
            @Result(column = "id", property = "users",
                    many = @Many(select = "org.spring.springboot.mapper.UserMapper.selectByAreaId"))
    })
    @Select("SELECT * FROM area WHERe id = #{id}")
    Area getAreaById(int id);
}

SelectController

package org.spring.springboot.controller;

import org.spring.springboot.entity.Area;
import org.spring.springboot.entity.User;
import org.spring.springboot.mapper.AreaMapper;
import org.spring.springboot.mapper.UserMapper;
import org.spring.springboot.mapper.UserMapperOne;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
@RequestMapping("/api")
public class SelectController {
    @Autowired
    UserMapper userMapper;
    @Autowired
    UserMapperOne userMapperOne;
    @Autowired
    AreaMapper areaMapper;

    @RequestMapping("/selectUser")
    public User test(){
        User user = userMapper.getUserById(1);
        return user;
    }

    @RequestMapping("/selectUserOne")
    public User testOne(){
        User user = userMapperOne.getUserById(1);
        return user;
    }

    @RequestMapping("/selectUserMany")
    public Area testMany(){
        Area area = areaMapper.getAreaById(1);
        return area;
    }
}

输入http://localhost:8086/api/selectUser

输入http://localhost:8086/api/selectUserOne

输入http://localhost:8086/api/selectUserMany

整体项目结构

参考文章
SpringBoot - MyBatis-Plus使用详解7（Mapper的CRUD接口4：多表关联查询）