问题背景
主要用到了association
和collection
的关联的嵌套 Select 查询
最终返回VO实体
@Data
@ApiModel(description = "智能名片 模板数据")
public class BizCardTemplateVO {
@ApiModelProperty("模板id")
private Integer id;
@ApiModelProperty("模板名称")
private String templateName;
@ApiModelProperty("员工信息")
private BizCardEmpInfoVO empVO;
}
内嵌实体
@Data
@ApiModel("模板-员工信息")
public class BizCardEmpInfoVO {
@ApiModelProperty("1启用 0隐藏")
private boolean status;
@ApiModelProperty("员工信息")
private List<EmpInfo> empInfos;
@Data
@ApiModel("员工信息")
public static class EmpInfo {
@ApiModelProperty("id")
private Integer id;
@ApiModelProperty("标签内容")
private String label;
@ApiModelProperty("1选中 0未选中")
private boolean selected;
@ApiModelProperty("属性名称,用于h5判断字段使用")
private String fieldName;
@ApiModelProperty("用于h5端信息展示")
private String fieldValue;
}
}
主表结构
CREATE TABLE `operate_workchat_biz_card_template` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`corp_id` int(11) DEFAULT NULL COMMENT 'corp.id',
`template_name` varchar(64) DEFAULT NULL COMMENT '模板名称',
`emp_template_id` int(10) DEFAULT NULL COMMENT 'operate_workchat_biz_card_emp_template.id',
`biz_template_id` int(10) DEFAULT NULL COMMENT 'operate_workchat_biz_card_template.id'
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COMMENT='智能名片模板';
从表结构
CREATE TABLE `operate_workchat_biz_card_emp_template` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`corp_id` int(11) DEFAULT NULL COMMENT 'corp.id',
`pid` varchar(64) DEFAULT NULL COMMENT '父id',
`label` varchar(64) DEFAULT NULL COMMENT '标签内容',
`selected` tinyint(1) DEFAULT '0' COMMENT '是否选中',
`field_name` varchar(16) DEFAULT NULL COMMENT '属性名称,用于h5判断字段使用',
`field_value` varchar(32) DEFAULT NULL COMMENT '用于h5端信息展示',
`status` tinyint(1) DEFAULT NULL COMMENT '1启用 0隐藏,这个字段pid=0的能用得着',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COMMENT='智能名片模板-员工信息模板';
期待返回结果
{
"id": 1,
"templateName": "默认模板",
"empVO": {
"status": true,
"empInfos": [
{
"id": 2,
"label": "头像",
"selected": true,
"fieldName": "avatar",
"fieldValue": null
}
]
}
}
我想通过select嵌套查询返回期待结果,就有了以下xml内容
<select id="getTemplateData" resultMap="templateDataResultMap">
select id, template_name, emp_template_id
from operate_workchat_biz_card_template
where id = #{templateId} and corp_id = #{corpId}
</select>
<select id="selectEmp" resultMap="empResult" >
select id as emp_id,pid,label,selected,field_name,field_value,status from operate_workchat_biz_card_emp_template where id = #{id}
</select>
<select id="selectEmpList" resultType="cn.zalldigital.data.scrm.web.workchat.pojo.vo.BizCardEmpInfoVO$EmpInfo">
select id,label,selected,field_name,field_value from operate_workchat_biz_card_emp_template where pid = #{id}
</select>
<resultMap id="empResult" type="cn.zalldigital.data.scrm.web.workchat.pojo.vo.BizCardEmpInfoVO">
<result column="status" property="status"/>
</resultMap>
<resultMap id="templateDataResultMap" type="cn.zalldigital.data.scrm.web.workchat.pojo.vo.BizCardTemplateVO">
<id column="id" property="id"/>
<result column="template_name" property="templateName"/>
<association property="empVO" column="emp_template_id" select="selectEmp" javaType="cn.zalldigital.data.scrm.web.workchat.pojo.vo.BizCardEmpInfoVO" >
<result column="status" property="status"/>
<collection property="empInfos" column="emp_id" select="selectEmpList" ofType="cn.zalldigital.data.scrm.web.workchat.pojo.vo.BizCardEmpInfoVO$EmpInfo">
<id column="id" property="id"/>
<result column="label" property="label"/>
<result column="selected" property="selected"/>
<result column="field_name" property="fieldName"/>
<result column="field_value" property="fieldValue"/>
</collection>
</association>
</resultMap>
当时想法很简单,想通过selectEmp
获取到status
和emp_id
,然后将emp_id
通过colum
传递给selectEmpList
进而获取到一个集合。但是操作了很久collection
标签的内容都不会执行。。
实际返回结果
{
"id": 1,
"templateName": "默认模板",
"empVO": {
"status": true,
"empInfos": null
}
}
解决方法
因为我这里使用到了association
的关联的嵌套 Select 查询,所以association
的结果都要放到对应的select的结果集中取处理!!!
避免association
嵌套collection
,将collection
的处理直接放到selectEmp
的结果集中去处理
<select id="getTemplateData" resultMap="templateDataResultMap">
select id, template_name, emp_template_id
from operate_workchat_biz_card_template
where id = #{templateId} and corp_id = #{corpId}
</select>
<select id="selectEmp" resultMap="empResult" >
select id as emp_id,pid,label,selected,field_name,field_value,status from operate_workchat_biz_card_emp_template where id = #{id}
</select>
<select id="selectEmpList" resultType="cn.zalldigital.data.scrm.web.workchat.pojo.vo.BizCardEmpInfoVO$EmpInfo">
select id,label,selected,field_name,field_value from operate_workchat_biz_card_emp_template where pid = #{id}
</select>
<resultMap id="empResult" type="cn.zalldigital.data.scrm.web.workchat.pojo.vo.BizCardEmpInfoVO">
<result column="status" property="status"/>
<collection property="empInfos" column="emp_id" select="selectEmpList" ofType="cn.zalldigital.data.scrm.web.workchat.pojo.vo.BizCardEmpInfoVO$EmpInfo">
<id column="id" property="id"/>
<result column="label" property="label"/>
<result column="selected" property="selected"/>
<result column="field_name" property="fieldName"/>
<result column="field_value" property="fieldValue"/>
</collection>
</resultMap>
<resultMap id="templateDataResultMap" type="cn.zalldigital.data.scrm.web.workchat.pojo.vo.BizCardTemplateVO">
<id column="id" property="id"/>
<result column="template_name" property="templateName"/>
<association property="empVO" column="emp_template_id" select="selectEmp" javaType="cn.zalldigital.data.scrm.web.workchat.pojo.vo.BizCardEmpInfoVO"/>
</resultMap>