该系列为imooc Java数据库开发与实战应用中MyBatis课程笔记,跟随课程加入自己见解,同时也为项目中碰到一些问题做了解答
本章节是mybatis,使用多表关联查询,实现一个用户可以拥有多个地址
一、创建用户地址表
USE mydb;
CREATE TABLE address(
id int auto_increment primary key comment '主键',
userid int comment '外键:关联用户表主键',
defaultAddr boolean comment '是否默认地址',
nation varchar(20) comment '国家',
province varchar(20) comment '省',
city varchar(20) comment '市',
country varchar(20) comment '县',
street varchar(100) comment '街道',
remark text comment '备注'
)CHARSET "UTF8";
ALTER TABLE address ADD foreign key(userid) REFERENCES users(id);
INSERT INTO address(userid,defaultAddr,nation,province,city,country,street,remark)
VALUES(1,1,"中国","河南","郑州","金水区","二环路","星辰大海111号"),
(1,0,"中国","贵州","贵阳","小河区","长江路","大宝剑洗浴"),
(1,0,"中国","广东","广州","萝岗区","不受力路","技师66号");
二、创建地址实体类
com.damu.entity包下>新建Address
package com.damu.entity;
public class Address {
private Integer id;//编号
private Users user;//所属用户
private Boolean defaultAddr;//是否默认地址
private String nation;//国家
private String province;//省
private String city;//市
private String country;//县
private String street;//街道
private String remark;//描述
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public Users getUser() {
return user;
}
public void setUser(Users user) {
this.user = user;
}
public Boolean getDefaultAddr() {
return defaultAddr;
}
public void setDefaultAddr(Boolean defaultAddr) {
this.defaultAddr = defaultAddr;
}
public String getNation() {
return nation;
}
public void setNation(String nation) {
this.nation = nation;
}
public String getProvince() {
return province;
}
public void setProvince(String province) {
this.province = province;
}
public String getCity() {
return city;
}
public void setCity(String city) {
this.city = city;
}
public String getCountry() {
return country;
}
public void setCountry(String country) {
this.country = country;
}
public String getStreet() {
return street;
}
public void setStreet(String street) {
this.street = street;
}
public String getRemark() {
return remark;
}
public void setRemark(String remark) {
this.remark = remark;
}
}
三、在用户实体类增加地址字段
修改Users表,在表中新增地址字段,因为一个用户可拥有多个地址,故使用List,同时提供get/set方法
private List<Address>addresses; //用户地址信息列表
public List<Address> getAddresses() {
return addresses;
}
public void setAddresses(List<Address> addresses) {
this.addresses = addresses;
}
四、配置mapper映射文件
使用collection对集合进行映射
<select id="findUsers" resultMap="forUsers">
select * from users
<if test="id !=null">
where id =#{id}
</if>
</select>
<!-- 自定义映射关系集合:主要包含对于一些自定义操作的配置,如不一致的属性和字段 -->
<resultMap id="forUsers" type="com.damu.entity.Users">
<!-- 将Users的id属性与数据库中users表的id主键绑定 -->
<id property="id" column="id"></id>
<!-- result配置,主要配置普通属性,column表示配置的是数据库字段名称 property配置的是实体类的属性名称 -->
<result column="username" property="name"></result>
<!--
查询字段property(主表的实体类变量)为Users实体类变量addresses,
column表示根据users表中主键id进行查询(指根据主表数据库中的字段进行关联查询),
存放的数据类型ofType(副表实体类完整类名)为Address,
select为配置的外部查询语句id-->
<collection property="addresses" column="id" ofType="com.damu.entity.Address" select="getAddress"></collection>
</resultMap>
<!--
select id=外部查询语句id
resultType=副表实体类完整类名
-->
<select id="getAddress" resultType="com.damu.entity.Address">
select * from address where userid =#{id}
</select>
五、改造信息展示页detail.jsp
在detail.jsp底部增加列表
<div class="row">
<table class="table" table-striped>
<tr>
<th>地址编号</th>
<th>国家</th>
<th>省</th>
<th>市</th>
<th>县</th>
<th>街道</th>
<th>详细地址</th>
<th>是否默认</th>
</tr>
<c:forEach var="addr" items="${user.addresses}">
<tr>
<td>${addr.id}</td>
<td>${addr.nation}</td>
<td>${addr.province}</td>
<td>${addr.city}</td>
<td>${addr.country}</td>
<td>${addr.street}</td>
<td>${addr.remark}</td>
<c:if test="${addr.defaultAddr ==true}">
<td>默认地址</td>
</c:if>
<c:if test="${addr.defaultAddr ==false}">
<td>-----</td>
</c:if>
</tr>
</c:forEach>
</table>
</div>
项目启动后,可在用户详情页正常查看地址数据