一、java 实体描述
/**
* <p>
* 家庭表
* </p>
*
* @author lohas
*/
@Data
@EqualsAndHashCode(callSuper = true)
@TableName("t_family_info")
@ApiModel(value = "FamilyInfoEntity对象", description = "家庭表")
public class FamilyInfoEntity extends Model {
private static final long serialVersionUID = 1L;
@ApiModelProperty(value = "主键")
@TableId(value = "id", type = IdType.INPUT)
private String id;
@ApiModelProperty(value = "家庭名称")
private String familyName;
@ApiModelProperty(value = "家庭头像url")
private String headUrl;
@ApiModelProperty(value = "国家")
private String country;
@ApiModelProperty(value = "城市")
private String city;
@ApiModelProperty(value = "地址")
private String address;
@ApiModelProperty(value = "经度")
private BigDecimal longitude;
@ApiModelProperty(value = "纬度")
private BigDecimal latitude;
@ApiModelProperty(value = "租户ID")
private String tenantId;
@ApiModelProperty(value = "是否删除(0:正常;低于0的数字:已删除)")
private String deleteFlag;
@ApiModelProperty(value = "创建人ID")
private String creatorId;
@ApiModelProperty(value = "创建时间")
@JsonDeserialize(using = LocalDateTimeSerializerConfig.LocalDateTimeDeserializer.class)
@JsonSerialize(using = LocalDateTimeSerializerConfig.LocalDateTimeSerializer.class)
private LocalDateTime createTime;
@ApiModelProperty(value = "更新人ID")
private String updateId;
@ApiModelProperty(value = "更新时间")
@JsonDeserialize(using = LocalDateTimeSerializerConfig.LocalDateTimeDeserializer.class)
@JsonSerialize(using = LocalDateTimeSerializerConfig.LocalDateTimeSerializer.class)
private LocalDateTime updateTime;
}
/**
* @author lohas
* @description
*/
@Data
@ApiModel(value = "RoomDeviceInfoVo对象", description = "房间设备信息实体")
public class RoomDeviceInfoVo implements Serializable {
@ApiModelProperty(value = "房间id")
private String roomId;
@ApiModelProperty(value = "房间名称")
private String roomName;
@ApiModelProperty(value = "设备数量")
private Integer deviceNum;
@ApiModelProperty(value = "设备信息列表")
private List<String> deviceIds;
}
/**
* @author lohas
* @description
*/
@Data
@ApiModel(value = "FamilyRoomInfoVo对象", description = "家庭房间信息对象实例")
public class FamilyRoomInfoVo extends FamilyInfoEntity {
@ApiModelProperty(value = "房间设备信息列表")
private List<RoomDeviceInfoVo> roomDeviceInfoVoList;
}
二、mybatis 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.lohas.mapper.FamilyInfoMapper">
<select id="getFamilyRoomInfoVoByUserId" resultMap="familyMap">
SELECT *
FROM (
SELECT tfi.*,
trf."id" AS r_room_id, --要加上r因为columnPrefix="r_"(如果字段唯一,可以不加)
trf.room_name AS r_room_name, --要加上r因为columnPrefix="r_"
trd.device_id AS r_d_device_id --要加上r因为columnPrefix="r_",加上d因为columnPrefix="d_"
FROM t_user_family tuf
LEFT JOIN t_family_info tfi ON tfi.ID = tuf.family_id
LEFT JOIN t_family_room tfr ON tuf.family_id = tfr.family_id
LEFT JOIN t_room_info trf ON tfr.room_id = trf.ID
LEFT JOIN t_room_device trd ON tfr.room_id = trd.room_id
WHERE tuf.user_id = #{userId}
ORDER BY tfi.create_time DESC
) AS fr,
(
SELECT trf."id" AS r_room_id, --要加上r因为columnPrefix="r_"
COUNT(trd.device_id) AS r_device_num --要加上r因为columnPrefix="r_"
FROM t_user_family tuf
LEFT JOIN t_family_info tfi ON tfi.ID = tuf.family_id
LEFT JOIN t_family_room tfr ON tuf.family_id = tfr.family_id
LEFT JOIN t_room_info trf ON tfr.room_id = trf.
ID
LEFT JOIN t_room_device trd ON tfr.room_id = trd.room_id
WHERE tuf.user_id = #{userId}
GROUP BY trf."id"
) AS rd
WHERE fr.r_room_id = rd.r_room_id
</select>
<resultMap id="familyMap" type="com.lohas.vo.FamilyRoomInfoVo">
<!-- 一层: 家庭信息-->
<id column="id" jdbcType="VARCHAR" property="id"/>
<result column="family_name" jdbcType="VARCHAR" property="familyName"/>
<result column="head_url" jdbcType="VARCHAR" property="headUrl"/>
<result column="country" jdbcType="VARCHAR" property="country"/>
<result column="city" jdbcType="VARCHAR" property="city"/>
<result column="address" jdbcType="VARCHAR" property="address"/>
<result column="longitude" jdbcType="NUMERIC" property="longitude"/>
<result column="latitude" jdbcType="NUMERIC" property="latitude"/>
<result column="delete_flag" jdbcType="CHAR" property="deleteFlag"/>
<result column="creator_id" jdbcType="VARCHAR" property="creatorId"/>
<result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>
<result column="update_id" jdbcType="VARCHAR" property="updateId"/>
<result column="update_time" jdbcType="TIMESTAMP" property="updateTime"/>
<result column="tenant_id" jdbcType="VARCHAR" property="tenantId"/>
<!-- 如果字段唯一,可以不加columnPrefix="r_"-->
<collection property="roomDeviceInfoVoList" columnPrefix="r_" resultMap="roomMap"/>
</resultMap>
<resultMap id="roomMap" type="com.lohas.vo.RoomDeviceInfoVo">
<!-- 二层: 房间信息-->
<result column="room_id" jdbcType="VARCHAR" property="roomId"/>
<result column="room_name" jdbcType="VARCHAR" property="roomName"/>
<result column="device_num" jdbcType="VARCHAR" property="deviceNum"/>
<!-- 如果字段唯一,可以不加columnPrefix="d_"-->
<collection property="deviceIds" columnPrefix="d_" resultMap="deviceMap"/>
</resultMap>
<resultMap id="deviceMap" type="string">
<!-- 三层: 设备信息-->
<result column="device_id" jdbcType="VARCHAR"/>
</resultMap>
</mapper>
三、调用方法
public interface FamilyInfoMapper extends BaseMapper<FamilyInfoEntity> {
List<FamilyRoomInfoVo> getFamilyRoomInfoVoByUserId(@Param("userId") String userId);
}
四、注意总结
如果你的字段唯一,columnPrefix="r_" 和 columnPrefix="d_",可以不加,如果你加上了columnPrefix记得在字段上拼接上,否则查询不出数据,拼接要有前后顺序拼接例如:
<collection property="roomDeviceInfoVoList" columnPrefix="r_" resultMap="roomMap"/>
<collection property="deviceIds" columnPrefix="d_" resultMap="deviceMap"/>
r_d_device_id(要加上r因为roomMap columnPrefix="r_",加上d因为deviceMap columnPrefix="d_")