MyBatis-Plus-Join联表查询

MyBatis-Plus-Join官网

1：添加依赖

<dependency>
    <groupId>com.github.yulichang</groupId>
    <artifactId>mybatis-plus-join-boot-starter</artifactId>
    <version>1.5.3</version>
</dependency>

<!-- 对于的mybaits-plus版本 -->
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.5.9</version>
</dependency>

2：原来写法的Mapper类，继承BaseMapper（其他全部不用修改）

@Mapper
public interface UserMapper extends BaseMapper<User> {
}

2-1：修改为的继承MPJBaseMapper

@Mapper
public interface UserMapper extends MPJBaseMapper<User> {
}

3：例子

MPJLambdaWrapper<DoorControllerSettingsDoorInfoDO> doorControllerSettingsDoorInfoDOMPJLambdaWrapper = new MPJLambdaWrapper<DoorControllerSettingsDoorInfoDO>()
        .selectAll()
        .leftJoin(DoorControllerSettingsDO.class, DoorControllerSettingsDO::getControllerNumber, DoorControllerSettingsDO::getControllerNumber);
doorControllerSettingsMapper.selectJoinList(DoorControllerSettingsDO2.class, doorControllerSettingsDO2MPJLambdaWrapper);

4：对于selectCollection和selectAssociation的使用

4-1：User 实体类

@Data
@TableName("user")
public class User {
    @TableId
    private Long id;
    private String username;
    private String password;
    private Integer age;
    private String email;
}

4-2：Address 实体类

@Data
@TableName("address")
public class Address {
    @TableId
    private Long id;
    private Long userId;  // 外键，关联user.id
    private String province;
    private String city;
    private String district;
    private String detail;
}

4-3：UserDTO 数据传输对象

@Data
public class UserDTO {
    private Long id;
    private String username;
    private String password;
    private Integer age;
    private String email;
    // 使用List接收一对多关系
    private List<Address> addressList;
}

4-4：userMapper代码查询

MPJLambdaWrapper<User> wrapper = new MPJLambdaWrapper<User>()
                .selectAll(User.class)
                //全部映射 不用考虑字段名重复问题(比如 id), 会对重复列自动添加别名
                .selectCollection(Address.class, UserDTO::getAddressList)
                .leftJoin(Address.class, Address::getUserId, User::getId);
List<UserDTO> dtoList= userMapper.selectJoinList(UserDTO.class, wrapper);

4-5：生成的SQL语句，基于MPJLambdaWrapper代码，MyBatis-Plus-Join会生成如下SQL：

SELECT 
    user.id AS user_id,
    user.username AS user_username,
    user.password AS user_password,
    user.age AS user_age,
    user.email AS user_email,
    address.id AS address_id,
    address.user_id AS address_user_id,
    address.province AS address_province,
    address.city AS address_city,
    address.district AS address_district,
    address.detail AS address_detail
FROM 
    user
LEFT JOIN 
    address ON address.user_id = user.id

4-6：执行结果生成sql处理，MyBatis-Plus-Join会自动处理结果集，将数据映射为：

	List<UserDTO> dtoList = [
    {
        "id": 1,
        "username": "张三",
        "password": "123456",
        "age": 25,
        "email": "zhangsan@example.com",
        "addressList": [
            {
                "id": 1,
                "userId": 1,
                "province": "北京",
                "city": "北京市",
                "district": "海淀区",
                "detail": "中关村大街1号"
            },
            {
                "id": 2,
                "userId": 1,
                "province": "上海",
                "city": "上海市",
                "district": "浦东新区",
                "detail": "张江高科技园区"
            }
        ]
    },
]

注意

//使用如下方法
JoinWrappers.lambda(DoorControllerSettingsDoorInfoDO.class)

//替换
new MPJLambdaWrapper<DoorControllerSettingsDoorInfoDO>()


//使用如下方法
Wrappers.lambda(DoorControllerSettingsDoorInfoDO.class)

//替换
new LambdaWrapper<DoorControllerSettingsDoorInfoDO>()