关联查询


mapper接口:

public interface MyAddressMapper {

/**
* 关联查询我的所有地址
* @param phone 用户手机号
* @return 查询结果
*/
MyAddress selectAddress(String phone);

}

dto:
@Data
@Component
public class MyAddress {

private int id;
private String password;
private String phone;
private String username;
private String bornTime;
private String sex;
private String picture;
private List<Address> addresses;


}

controller:

/*------------------- 获取用户+地址信息 ---------------*/
@GetMapping("/selectMap")
public RespResult<MyAddress> selectMap(String phone){

MyAddress myAddress=myAddressMapper.selectAddress(phone);
return returnSuccess(myAddress);
}

}


mappe.xml:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.jj.springshop.mapper.MyAddressMapper">

<sql id="showAddress">
select member.id,member.phone,member.username,member.born_time,member.sex,member.picture,
address.id as aId,address.phone_key,address.phone,address.username,address.province,address.city,
address.region,address.status
</sql>

<resultMap id="addressMap" type="com.jj.springshop.pojo.dto.MyAddress">
<id property="id" column="id"/>
<result property="password" column="password"/>
<result property="phone" column="phone"/>
<result property="username" column="username"/>
<result property="bornTime" column="born_time"/>
<result property="sex" column="sex"/>
<result property="picture" column="picture"/>

<collection property="addresses" ofType="com.jj.springshop.pojo.entity.Address" javaType="java.util.List">
<id property="id" column="aId"/>
<result property="phoneKey" column="phone_key"/>
<result property="username" column="username"/>
<result property="phone" column="phone"/>
<result property="province" column="province"/>
<result property="city" column="city"/>
<result property="region" column="region"/>
<result property="status" column="status"/>
</collection>

</resultMap>

<select id="selectAddress" resultMap="addressMap">
<include refid="showAddress"/>
from member left join address on member.phone=address.phone_key
where member.phone=#{phone}
</select>

</mapper>
posted @ 2021-10-14 21:47  吴大叔  阅读(26)  评论(0编辑  收藏  举报