Mybatis的多表查询使用

表设计，User（用户表），UserRelation（用户关系表），Authority（用户权限表），UserInfo（用户信息表）

设计实体类

@Data
@AllArgsConstructor
@NoArgsConstructor
@ApiModel("用户实体类")
//使用者实体类
public class User {
   

    @ApiModelProperty("userId")
    private String userId;
    @ApiModelProperty("用户名")
    private String username;
    @ApiModelProperty("密码")
    private String password;

    @ApiModelProperty("备用字段")
    private String beiyong1;
    @ApiModelProperty("备用字段")
    private String beiyong2;
    @ApiModelProperty("备用字段")
    private String beiyong3;

    //这个好像不符合设计逻辑
    @ApiModelProperty("user连接关系表字段")
    private User_relation userRelation;

    @ApiModelProperty("用户权限表")
    private Authority authority;

    @ApiModelProperty("用户信息表")
    private UserInfo userInfo;

}

@Data
@AllArgsConstructor
@NoArgsConstructor
@ApiModel("用户联系中间实体类")
public class User_relation {
   

    @ApiModelProperty("中间表id")
    private int urId;
    @ApiModelProperty("连接user表字段")
    private String userId;
    @ApiModelProperty("连接authority表字段")
    private String authorityId;
    @ApiModelProperty("连接user")
    private String uInfoId;

    @ApiModelProperty("用户权限表")
    private Authority authority;

    @ApiModelProperty("用户信息表")
    private UserInfo userInfo;

}

@Data
@AllArgsConstructor
@NoArgsConstructor
@ApiModel("用户权限实体类")
public class Authority {
   

    @ApiModelProperty("权限id")
    private String authorityId;
    @ApiModelProperty("权限名")
    private String authorityName;
    @ApiModelProperty("角色名")
    private String roleName;

}

@Data
@AllArgsConstructor
@NoArgsConstructor
//注册页面需要的数据
@ApiModel("用户信息实体类")
public class UserInfo {
   
    @ApiModelProperty("uInfoId")
    private String uInfoId;
    @ApiModelProperty("邮箱")
    private String email;
    @ApiModelProperty("电话号码")
    private int phonoNumber;
    @ApiModelProperty("昵称")
    private String nackName;
}

Mapper接口的编写

@Repository
@Mapper
//user用户查询
//与resources里面的mappers的UserMapper.xml绑定

public interface UserMapper {
   

    //增加--@Insert("INSERT INTO spring.user (username,password) VALUES (#{one},#{two})")

    public int userAdd(@Param("userId") String userId,@Param("username") String user, @Param("password") String password);


    //删除,删除全部数据--@Delete("DELETE FROM spring.user WHERE username = #{one}")

    public int userDelete(@Param("username") String user);


    //修改--@Update("UPDATE spring.user SET password = #{two} WHERE username = #{one}")

    public int userUpdate(@Param("username") String user, @Param("login.password") String password);


    //查询全部--@Select("SELECT * FROM spring.user")

    public List<User> userQueAll();


    //查询一个数据--@Select("SELECT * FROM spring.user where username=#{one}")

    public User userQueOne(@Param("username") String user);


    //查询用户表和权限表的字段

    public List<User> userAuthor();

    public List<User> userAuthorThree();

    public User userInfo();

    public User userInfoThree();
}

XML编写

这是两表查询–具体看sql语句–下面两个返回的结果是一样的–但是数组的关系不一样

    <!--连表查询-按照结果嵌套查询-->
<!--    两表联查-只能查询到需要的数据，其他的数据不显示-->
<!--    查询用户和权限-->
    <select id="userAuthor" resultMap="UserAuthor">
        select u.username,u.`password`,a.authorityName
        from user u,authority a,user_relation ur
        where u.userId=ur.userId
          and a.authorityId=ur.authorityId
    </select>
    <resultMap id="UserAuthor" type="User">
        <result column="userId" property="userId"></result>
        <result column="username" property="username"></result>
        <result column="password" property