比如有个医药代表,需要查询其管理的医院、医院里对应医药代表的医生数以及医生的ID
实体类如下:其他省略
public class AgentVo{
/**
* 医药代表
*/
private String agentId;
/**
* 医院ID
*/
private Integer hospitalId;
/**
* 医院名称
*/
private String hospitalName;
/**
* 医院对应的医生数
*/
private Integer count;
/**
* 医院对应的医生的ID
*/
private List<Integer> doctorIds;
}
mapper.xml文件设置:
<resultMap id="AgentVoMap" type="com.imlim.vo.AgentVo" >
<result column="agent_id" property="agentId" jdbcType="VARCHAR" />
<result column="hospital_id" property="hospitalId" jdbcType="INTEGER" />
<result column="hospital_name" property="hospitalName" jdbcType="VARCHAR" />
<association property="count" column="{agentId=agent_id,hospitalId=hospital_id}" select="selectDoctorCount"></association>
<collection property="doctorIds" column="{agentId=agent_id,hospitalId=hospital_id}" select="selectDoctorIds"></collection>
</resultMap>
<select id="selectDoctorCount" resultType="java.lang.Integer">
select count(1) from t_doctor_relation_agent
where agent_id = #{agentId,jdbcType=VARCHAR}
and hospital_id = #{hospitalId,jdbcType=INTEGER}
</select>
<select id="selectDoctorIds" resultType="java.lang.Integer">
select doctor_id from t_doctor_relation_agent
where agent_id = #{agentId,jdbcType=VARCHAR}
and hospital_id = #{hospitalId,jdbcType=INTEGER}
</select>
<select id="get" resultMap="AgentVoMap">
SELECT
agent.agent_id,
agent.hospital_id,
agent.hospital_name
FROM
t_doctor_relation_agent agent
WHERE agent.agent_id = 15011
GROUP BY
agent.hospital_id
</select>
测试结果:
[AgentVo [agentId=15011, hospitalId=1000, hospitalName=贵阳中医学院第一附属医院, count=1, doctorIds=[572]], AgentVo [agentId=15011, hospitalId=7720, hospitalName=测试一下, count=1, doctorIds=[571]], AgentVo [agentId=15011, hospitalId=7815, hospitalName=中文乱码2, count=2, doctorIds=[605, 606]], AgentVo [agentId=15011, hospitalId=7817, hospitalName=北京儿童医院111, count=2, doctorIds=[696, 47]]]