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="password"></result>
        <association property="authority" javaType="Authority">
            <result column="authorityId" property="authorityId"></result>
            <result column="authorityName" property="authorityName"></result>
            <result column="roleName" property="roleName"></result>
        </association>
    </resultMap>

结果为

User(userId=null, username=111, password=222, beiyong1=null, beiyong2=null, beiyong3=null, userRelation=null, authority=Authority(authorityId=null, authorityName=ROLE_ADMIN, roleName=null), userInfo=null)

分段如下

User(userId=null, 
username=111, 
password=222, 
beiyong1=null, 
beiyong2=null, 
beiyong3=null, 
userRelation=null, 
authority=Authority(authorityId=null, authorityName=ROLE_ADMIN, roleName=null), 
userInfo=null)

<!--    三表联查-->
    <select id="userAuthorThree" parameterType="String" resultMap="userAuthorMap">
            select u.username,u.`password`,a.authorityName,ur.userId,ur.authorityId
            from user u,authority a,user_relation ur
            where u.userId=ur.userId
            and a.authorityId=ur.authorityId
    </select>
    <resultMap id="userAuthorMap" type="User">
        <!--property表示com.domain.Student的字段,coloum为表中的字段，进行配置映射-->
        <!--主键字段-->
        <id column="userId" property="userId"></id>
        <!--非主键字段-->
        <result column="username" property="username"></result>
        <result column="password" property="password"></result>
        <result column="beiyong1" property="beiyong1"></result>
        <result column="beiyong2" property="beiyong2"></result>
        <result column="beiyong3" property="beiyong3"></result>
        <association property="userRelation" javaType="User_relation">
            <result column="urId" property="urId"></result>
            <result column="userId" property="userId"></result>
            <result column="authorityId" property="authorityId"></result>
            <result column="uInfoId" property="uInfoId"></result>
            <association property="authority" javaType="Authority">
                <result column="authorityId" property="authorityId"></result>
                <result column="authorityName" property="authorityName"></result>
                <result column="roleName" property="roleName"></result>
            </association>
        </association>
    </resultMap>

上下两种是一样的，上一个是三层循环的嵌套，下面是两层的表查询–查询结果一样，查询结构不一样

<!--    三表联查-非嵌套式-->
    <select id="userAuthorThree" parameterType="String" resultMap="userAuthorMap">
            select u.username,u.`password`,a.authorityName,ur.userId,ur.authorityId
            from user u,authority a,user_relation ur
            where u.userId=ur.userId
            and a.authorityId=ur.authorityId
    </select>
    <resultMap id="userAuthorMap" type="User">
        <!--property表示com.domain.Student的字段,coloum为表中的字段，进行配置映射-->
        <!--主键字段-->
        <id column="userId" property="userId"></id>
        <!--非主键字段-->
        <result column="username" property="username"></result>
        <result column="password" property="password"></result>
        <result column="beiyong1" property="beiyong1"></result>
        <result column="beiyong2" property="beiyong2"></result>
        <result column="beiyong3" property="beiyong3"></result>
        <association property="userRelation" javaType="User_relation">
            <result column="urId" property="urId"></result>
            <result column="userId" property="userId"></result>
            <result column="authorityId" property="authorityId"></result>
            <result column="uInfoId" property="uInfoId"></result>
        </association>
        <association property="authority" javaType="Authority">
            <result column="authorityId" property="authorityId"></result>
            <result column="authorityName" property="authorityName"></result>
            <result column="roleName" property="roleName"></result>
        </association>
    </resultMap>

结果为

User(userId=1111, username=阿大撒, password=阿斯顿撒, beiyong1=null, beiyong2=null, beiyong3=null, userRelation=User_relation(urId=0, userId=1111, authorityId=002, uInfoId=null, authority=Authority(authorityId=002, authorityName=ROLE_USER, roleName=null), userInfo=null), authority=null, userInfo=null)

我分段一下–对应User表的字段

User(userId=1111, 
username=阿大撒, 
password=阿斯顿撒, 
beiyong1=null, 
beiyong2=null, 
beiyong3=null, 
userRelation=User_relation(urId=0, userId=1111, authorityId=002, uInfoId=null, authority=Authority(authorityId=002, authorityName=ROLE_USER, roleName=null), userInfo=null), 
authority=null, 
userInfo=null)

上下对比对比

User(userId=f34c822e, username=111, password=222, beiyong1=null, beiyong2=null, beiyong3=null, userRelation=User_relation(urId=0, userId=f34c822e, authorityId=001, uInfoId=null, authority=null, userInfo=null), authority=Authority(authorityId=001, authorityName=ROLE_ADMIN, roleName=null), userInfo=null)

分段

User(userId=f34c822e, 
username=111, 
password=222, 
beiyong1=null, 
beiyong2=null, 
beiyong3=null, 
userRelation=User_relation(urId=0, userId=f34c822e, authorityId=001, uInfoId=null, authority=null, userInfo=null), 
authority=Authority(authorityId=001, authorityName=ROLE_ADMIN, roleName=null), 
userInfo=null)

下面两个返回的结果是一样的–但是数组的关系不一样

<!--    用户信息两表联查-->
    <select id="userInfo" resultMap="uUserInfo">
        select u.username,u.`password`,uf.email,uf.nackName,uf.phonoNumber
        from user u,userinfo uf,user_relation ur
        where u.userId=ur.userId
          and uf.uInfoId=ur.uInfoId
    </select>
    <resultMap id="uUserInfo" type="User">
        <result column="userId" property="userId"></result>
        <result column="username" property="username"></result>
        <result column="password" property="password"></result>
        <association property="userInfo" javaType="UserInfo">
            <result column="uInfoId" property="uInfoId"></result>
            <result column="email" property="email"></result>
            <result column="phonoNumber" property="phonoNumber"></result>
            <result column="nackName" property="nackName"></result>
        </association>
    </resultMap>

结果为

User(userId=null, username=111, password=222, beiyong1=null, beiyong2=null, beiyong3=null, userRelation=null, authority=null, userInfo=UserInfo(uInfoId=null, email=13123, phonoNumber=1111, nackName=12312))

分段

User(userId=null, 
username=111, 
password=222, 
beiyong1=null, 
beiyong2=null, 
beiyong3=null, 
userRelation=null, 
authority=null, 
userInfo=UserInfo(uInfoId=null, email=13123, phonoNumber=1111, nackName=12312))

    <!--    用户信息三表联查-->
    <select id="userInfoThree" parameterType="String" resultMap="uUserInfoMap">
        select u.username,u.`password`,uf.email,uf.nackName,uf.phonoNumber
        from user u,userinfo uf,user_relation ur
        where u.userId=ur.userId
          and uf.uInfoId=ur.uInfoId
    </select>
    <resultMap id="uUserInfoMap" type="User">
        <!--property表示com.domain.Student的字段,coloum为表中的字段，进行配置映射-->
        <!--主键字段-->
        <id column="userId" property="userId"></id>
        <!--非主键字段-->
        <result column="username" property="username"></result>
        <result column="password" property="password"></result>
        <association property="userRelation" javaType="User_relation">
            <result column="urId" property="urId"></result>
            <result column="userId" property="userId"></result>
            <result column="authorityId" property="authorityId"></result>
            <result column="uInfoId" property="uInfoId"></result>
            <association property="userInfo" javaType="UserInfo">
                <result column="uInfoId" property="uInfoId"></result>
                <result column="email" property="email"></result>
                <result column="nackName" property="nackName"></result>
                <result column="phonoNumber" property="phonoNumber"></result>
            </association>
        </association>
    </resultMap>

结果为

User(userId=null, username=111, password=222, beiyong1=null, beiyong2=null, beiyong3=null, userRelation=User_relation(urId=0, userId=null, authorityId=null, uInfoId=null, authority=null, userInfo=UserInfo(uInfoId=null, email=13123, phonoNumber=1111, nackName=12312)), authority=null, userInfo=null)

分段后

User(userId=null, 
username=111, 
password=222, 
beiyong1=null, 
beiyong2=null, 
beiyong3=null, 
userRelation=User_relation(urId=0, userId=null, authorityId=null, uInfoId=null, authority=null, userInfo=UserInfo(uInfoId=null, email=13123, phonoNumber=1111, nackName=12312)), 
authority=null, 
userInfo=null)

输入值的多表查询方法

<!--    用户权限表，查单条数据-->
    <select id="userAuthorOne" parameterType="String" resultMap="UserAuthorO">
        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
          and u.username = #{username}
    </select>
    <resultMap id="UserAuthorO" type="User">
        <result column="userId" property="userId"></result>
        <result column="username" property="username"></result>
        <result column="password" property="password"></result>
        <association property="authority" javaType="Authority">
            <result column="authorityId" property="authorityId"></result>
            <result column="authorityName" property="authorityName"></result>
            <result column="roleName" property="roleName"></result>
        </association>
    </resultMap>

都与User实体类对应

多表查询结构解析一

两表查询

三表嵌套查询