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