mybatis 一对一和一对多的association collection

比如有个医药代表，需要查询其管理的医院、医院里对应医药代表的医生数以及医生的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]]]

 

转载于:https://my.oschina.net/imlim/blog/1940524