目录
新建数据库
建表时,要较少两个表耦合度,用id将两个表联系起来
create table if not exists mytable
(
userID int primary key auto_increment not null,
clazz varchar(20),
email varchar(20),
username varchar(20) not null,
password varchar(20) not null,
pID int not null
);
insert into mytable value
(1, '2201', '34518868659', 'sean', '123456', 1),
(2, '2202', '34518868659', '李四', '123456', 2),
(3, '2201', '34518868659', '王五', '123456', 1);
create table if not exists permissions
(
pID int primary key auto_increment,
type varchar(50) default 'guest' not null
);
insert into permissions
value (1, 'admin'),
(2, 'guest'),
(3, 'vip');
通过数据库语言查询数据
select m.userID, m.clazz, m.username, m.password, m.pID, p.pID, p.type
from mytable m
left join permissions p on m.pID = p.pID;
通过数据库的左连接查询,查到所有数据,达成显示一对一的效果,现在需要试页面页达到这种直观效果。
新建实体类
@Data
public class Permissions {
private Integer pID;
private String type;
}
@Data
public class UserEntity {
private Integer userID;
private String username;
private String clazz;
private String email;
private String password;
private Integer pID;
//复杂查询 一对一 涵盖其中的一的数据
private Permissions getPermissionsList;
新建v层
因为每次都会写一次Map的返回值判断,所以将判断过程进行封装
@Autowired //从Spring中取出
UserMapper userMapper;
//一对一
@RequestMapping("/getUserAndType")
public ResultMap getUserAndType() {
List<UserEntity> userList = userMapper.getUserAndType();
return ToolUtil.optionReultMap(userList, "查询");
}
新建接口
@Mapper //存进Spring容器中
public interface UserMapper {
//一对一
public List<UserEntity> getUserAndType();
新建配置文件
需要在配置文件中写明查询条件,先查询到所需要的数据
<!-- 由于返回的实体类需要进入复合类中处理数据所以要用 resultMap(结果映射)-->
<!--一对一-->
<select id="getUserAndType" resultMap="getUserAndTypeMap">
select m.userID,
m.clazz,
m.username,
m.password,
m.pID,
p.pID,
p.type
from mytable m
left join permissions p on m.pID = p.pID;
</select>
resultMap标签和association标签
在标签内写映射部分
<!--一对一-->
<select id="getUserAndType" resultMap="getUserAndTypeMap">
select m.userID,
m.clazz,
m.username,
m.password,
m.pID,
p.pID,
p.type
from mytable m
left join permissions p on m.pID = p.pID;
</select>
<resultMap id="getUserAndTypeMap" type="UserEntity">
<id column="userID" property="userID"></id>
<result column="clazz" property="clazz"></result>
<result column="username" property="username"></result>
<result column="password" property="password"></result>
<result column="pID" property="pID"></result>
<association property="getPermissionsList" javaType="Permissions">
<id column="pID" property="pID"></id>
<result column="type" property="type"></result>
</association>
</resultMap>
测试查询结果
三个用户,每个用户的属性,通过id进行连接,显示出想要的效果
{
"code": 200,
"msg": "查询成功",
"result": [
{
"userID": 1,
"username": "sean",
"clazz": "2201",
"email": null,
"password": "123456",
"getBookList": null,
"getPermissionsList": {
"type": "admin",
"pid": 1
},
"pid": 1
},
{
"userID": 2,
"username": "李四",
"clazz": "2202",
"email": null,
"password": "123456",
"getBookList": null,
"getPermissionsList": {
"type": "guest",
"pid": 2
},
"pid": 2
},
{
"userID": 3,
"username": "王五",
"clazz": "2201",
"email": null,
"password": "123456",
"getBookList": null,
"getPermissionsList": {
"type": "vip",
"pid": 3
},
"pid": 3
}
]
}