mybatis-plus三表查询

MyBatis-Plus的三表查询可以通过使用MyBatis的XML映射文件或者注解方式来实现。以下是一个使用XML映射文件实现的三表查询示例：

1. 创建实体类

假设我们有三个实体类：User、Role和UserRole，分别对应数据库中的三张表。

User实体类：

public class User {
    private Long id;
    private String username;
    private String password;
    private String email;
    // getter和setter方法
}

Role实体类：

public class Role {
    private Long id;
    private String roleName;
    private String description;
    // getter和setter方法
}

UserRole实体类：

public class UserRole {
    private Long id;
    private Long userId;
    private Long roleId;
    // getter和setter方法
}

2. 创建Mapper接口

创建三个Mapper接口：UserMapper、RoleMapper和UserRoleMapper，分别对应User、Role和UserRole实体类。

UserMapper接口：

public interface UserMapper extends BaseMapper<User> {}

RoleMapper接口：

public interface RoleMapper extends BaseMapper<Role> {}

UserRoleMapper接口：

public interface UserRoleMapper extends BaseMapper<UserRole> {}

3. 创建XML映射文件

在MyBatis的XML映射文件中定义三个SQL语句，分别查询用户、角色和用户角色关联信息。

UserMapper.xml文件：

<mapper namespace="com.example.mapper.UserMapper">
    <resultMap id="userRoleMap" type="com.example.entity.User">
        <id column="id" property="id" />
        <result column="username" property="username" />
        <result column="password" property="password" />
        <result column="email" property="email" />
        <collection property="roles" ofType="com.example.entity.Role">
            <id column="role_id" property="id" />
            <result column="role_name" property="roleName" />
            <result column="description" property="description" />
        </collection>
    </resultMap>

    <select id="getUserWithRolesById" resultMap="userRoleMap">
        select u.id, u.username, u.password, u.email, r.id as role_id, r.role_name, r.description
        from user u
        left join user_role ur on u.id = ur.user_id
        left join role r on ur.role_id = r.id
        where u.id = #{id}
    </select>
</mapper>

RoleMapper.xml文件：

<mapper namespace="com.example.mapper.RoleMapper">
    <select id="getRoleWithUsersById" resultType="com.example.entity.Role">
        select r.id, r.role_name, r.description, u.id as user_id, u.username, u.password, u.email
        from role r
        left join user_role ur on r.id = ur.role_id
        left join user u on ur.user_id = u.id
        where r.id = #{id}
    </select>
</mapper>

UserRoleMapper.xml文件：

<mapper namespace="com.example.mapper.UserRoleMapper">
    <select id="getUserRoleByUserId" resultType="com.example.entity.UserRole">
        select id, user_id, role_id from user_role where user_id = #{userId}
    </select>
</mapper>

4. 使用三表查询

在Service层或Controller层中调用Mapper接口中定义的方法即可使用三表查询功能。

UserService类：

@Service
public class UserService {
    @Autowired
    private UserMapper userMapper;
    @Autowired
    private UserRoleMapper userRoleMapper;
    @Autowired
    private RoleMapper roleMapper;

    public User getUserWithRolesById(Long id) {
        User user = userMapper.getUserWithRolesById(id);
        List<UserRole> userRoles = userRoleMapper.getUserRoleByUserId(user.getId());
        for (UserRole userRole : userRoles) {
            Role role = roleMapper.getRoleWithUsersById(userRole.getRoleId());
            user.getRoles().add(role);
        }
        return user;
    }
}

RoleService类：

@Service
public class RoleService {
    @Autowired
    private RoleMapper roleMapper;
    @Autowired
    private UserRoleMapper userRoleMapper;
    @Autowired
    private UserMapper userMapper;

    public Role getRoleWithUsersById(Long id) {
        Role role = roleMapper.getRoleWithUsersById(id);
        List<UserRole> userRoles = userRoleMapper.getUserRoleByRoleId(role.getId());
        for (UserRole userRole : userRoles) {
            User user = userMapper.getUserWithRolesById(userRole.getUserId());
            role.getUsers().add(user);
        }
        return role;
    }
}

注：以上示例中的XML映射文件中的SQL语句仅供参考，实际使用时需要根据业务需求自行修改。