要查询的响应数据List<类>:可以看出是一对多的查询
@Data
@ApiModel("カルテ一览响应参数")
public class BeautyChartResVo {
@ApiModelProperty("カルテ ID")
private Long id;
@ApiModelProperty("ルテ做成者(店员)")
private String staff_name;
@ApiModelProperty("顾客ID")
private Integer customer_id;
@ApiModelProperty("顾客名字")
private String fullname;
@ApiModelProperty("做成日期")
private String date;
@ApiModelProperty("文字内容")
private String content;
@ApiModelProperty("图片信息")
private List<ImageInfo> image;
@ApiModelProperty("材料用品信息")
private List<MaterialInfo> material;
}
图片信息List<类>
@Data
public class ImageInfo {
@ApiModelProperty("图片ID")
private Integer image_id;
@ApiModelProperty("原图URL")
private String image;
@ApiModelProperty("缩略图url")
private String abbreviation_image;
}
材料用品List<类>
@Data
public class MaterialInfo {
@ApiModelProperty("材料用品id")
private Integer material_id;
@ApiModelProperty("材料名")
private String material_name;
}
controller和service都是正常写,主要在于xml中SQL
先写个要查询的响应数据的映射
<resultMap id="BeautyChartMap" type="cn.repeatlink.module.beautychart.vo.beautyChart.BeautyChartResVo">
<result property="id" column="id"/>
<result property="content" column="content"/>
<result property="customer_id" column="customer_id"/>
<result property="date" column="date"/>
<result property="fullname" column="fullname"/>
<result property="staff_name" column="staff_name"/>
<collection property="image" ofType="cn.repeatlink.module.beautychart.vo.beautyChart.ImageInfo" column="id" javaType="java.util.ArrayList" select="getBeautyChartImage">
<id property="image_id" column="image_id"/>
<result property="image" column="image"/>
<result property="abbreviation_image" column="abbreviation_image"/>
</collection>
<collection property="material" ofType="cn.repeatlink.module.beautychart.vo.beautyChart.MaterialInfo" column="id" javaType="java.util.ArrayList" select="getBeautyChartMaterial">
<id property="material_id" column="material_id"/>
<result property="material_name" column="material_name"/>
</collection>
</resultMap>
然后是图片的映射
<resultMap id="ImageMap" type="cn.repeatlink.module.beautychart.vo.beautyChart.ImageInfo">
<id property="image_id" column="image_id"/>
<result property="image" column="image"/>
<result property="abbreviation_image" column="abbreviation_image"/>
</resultMap>
材料用品的映射
<resultMap id="materialMap" type="cn.repeatlink.module.beautychart.vo.beautyChart.MaterialInfo">
<id property="material_id" column="material_id"/>
<result property="material_name" column="material_name"/>
</resultMap>
接下来是主查询的SQL 以及子查询
<select id="getBeautyChartList" resultMap="BeautyChartMap">
select
sbc.id,
ss.`name` as staff_name,
sbc.customer_id,
c.fullname,
DATE_FORMAT(sbc.create_time, "%Y-%m-%d") as date,
sbc.content
from
sapp_beauty_chart sbc,
customer c,
staff ss
where
sbc.customer_id = c.id
and sbc.staff_id = ss.staffid
and sbc.customer_id = #{customerId}
and sbc.category_id = #{categoryId}
and sbc.create_time >= #{startDate}
and sbc.create_time <= #{endDate}
and sbc.`status` = 1
order by id
</select>
图片子查询的SQL
<select id="getBeautyChartImage" parameterType="long" resultMap="ImageMap">
SELECT
si.id as image_id,
si.image,
si.abbreviation_image
FROM
sapp_image si,
sapp_rel_beauty_chart_image ri
WHERE
si.id = ri.image_id
AND si.`status` = 1
AND ri.`status` = 1
AND ri.beauty_chart_id = #{id}
</select>
材料用品子查询的SQL
<select id="getBeautyChartMaterial" resultMap="materialMap">
SELECT
sm.id AS material_id,
sm.`name` AS material_name
FROM
sapp_material sm,
sapp_rel_beauty_chart_material ri
WHERE
sm.id = ri.material_id
AND sm.`status` = 1
AND ri.`status` = 1
AND ri.beauty_chart_id = #{id}
</select>
结束over。