mybatis配置一对多查询关系

1，部门bean

package com.hsk.mybatis.bean;

import java.util.List;

public class DeptBean {

	private String deptCode;
	private String deptName;
	private String cmpyCode;
	private List<UserBean> userList;
	public List<UserBean> getUserList() {
		return userList;
	}
	public void setUserList(List<UserBean> userList) {
		this.userList = userList;
	}
	public String getDeptCode() {
		return deptCode;
	}
	public void setDeptCode(String deptCode) {
		this.deptCode = deptCode;
	}
	public String getDeptName() {
		return deptName;
	}
	public void setDeptName(String deptName) {
		this.deptName = deptName;
	}
	public String getCmpyCode() {
		return cmpyCode;
	}
	public void setCmpyCode(String cmpyCode) {
		this.cmpyCode = cmpyCode;
	}
	@Override
	public String toString() {
		// TODO Auto-generated method stub
		return deptName+"["+deptCode+"]"+cmpyCode+"["+userList.size()+"]";
	}
}

2，用户bean

package com.hsk.mybatis.bean;

/**
 * 用户bean
 * @author huangshikai
 *
 */
public class UserBean {

	private String userCode;
	private String userName;
	private String deptCode;
	private String cmpyCode;
	private DeptBean deptBean;
	public DeptBean getDeptBean() {
		return deptBean;
	}
	public void setDeptBean(DeptBean deptBean) {
		this.deptBean = deptBean;
	}
	public String getCmpyCode() {
		return cmpyCode;
	}
	public void setCmpyCode(String cmpyCode) {
		this.cmpyCode = cmpyCode;
	}
	public String getUserCode() {
		return userCode;
	}
	public void setUserCode(String userCode) {
		this.userCode = userCode;
	}
	public String getUserName() {
		return userName;
	}
	public void setUserName(String userName) {
		this.userName = userName;
	}
	public String getDeptCode() {
		return deptCode;
	}
	public void setDeptCode(String deptCode) {
		this.deptCode = deptCode;
	}
	@Override
	public String toString() {
		return "UserBean [userCode=" + userCode + ", userName=" + userName + ", deptCode=" + deptCode + "]";
	}
}

3，deptMapper.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">
<!-- namespace命名空间对sql分类化管理，隔离不同表的sql语句 -->
<mapper namespace="com.hsk.mybatis.dao.DeptDao">

	<!-- 配置结果集字段和javabean字段对应关系 -->
	<resultMap id="deptMap" type="com.hsk.mybatis.bean.DeptBean">
        <id property="deptCode" column="DEPT_CODE" />
        <result property="deptName" column="DEPT_NAME"/>
        <result property="cmpyCode" column="CMPY_CODE"/>
        <!-- 集合的column应为deptbean传入的关联查询字段 (关联查询配置一对多)-->
        <collection property="userList" ofType="com.hsk.mybatis.bean.UserBean"
        	javaType="java.util.List" column="DEPT_CODE">
        	<id property="userCode" column="USER_CODE"></id>
        	<result property="deptCode" column="DEPTCODE"/><!-- 连接查询的字段别名 -->
        	<result property="cmpyCode" column="CMPYCODE"/>
        	<result property="userName" column="USER_NAME"/>
        </collection>
    </resultMap>
    
    <!-- 配置结果集字段和javabean字段对应关系 -->
    <!-- 集合的column应为deptbean传入的关联查询字段 (子查询配置一对多)-->
    
	<resultMap id="deptMapSonQuery" type="com.hsk.mybatis.bean.DeptBean">
        <id property="deptCode" column="DEPT_CODE" />
        <result property="deptName" column="DEPT_NAME"/>
        <result property="cmpyCode" column="CMPY_CODE"/>
        
        <collection property="userList" ofType="com.hsk.mybatis.bean.UserBean"
        	javaType="java.util.List" column="DEPT_CODE" select="com.hsk.mybatis.dao.DeptDao.getUsersByCode">
        </collection>
    </resultMap>
    
    
	<!--
	 统一命名空间下id唯一 
	parameterType输入参数类型可以是java简单的数据类型也可以是java对象，这里指定string
	#{}表示占位符
	#{userCode}其中userCode为输入参数，参数名称为userCode
	resultMap指定sql输出结果所映射的java对象，注意resultMap要配置java对象属性与数据库字段对应关系
	resultType指定sql输出结果单条记录所映射的java对象，注意resultType配置的java类的属性需要与数据库字段名称一致
	-->
   <select id="getDeptByCode" parameterType="String" resultMap="deptMap">
       SELECT a.DEPT_CODE,DEPT_NAME,a.CMPY_CODE,
       b.dept_code DEPTCODE,b.USER_NAME,b.CMPY_CODE CMPYCODE,
       b.USER_CODE FROM SY_ORG_DEPT a
       left join SY_ORG_USER b on a.dept_code=b.dept_code
        WHERE a.DEPT_CODE=#{deptCode}
   </select>
   
   <!-- 子查询的一对多查询 -->
   <select id="getDeptUsers" parameterType="String" resultMap="deptMapSonQuery">
   	SELECT DEPT_CODE,DEPT_NAME,CMPY_CODE FROM SY_ORG_DEPT
   	WHERE DEPT_CODE=#{deptCode}
   </select>
   
   <!-- 注意子查询的返回结果应为resultMap而不是resultType否则返回的结果集的对象都为NULL -->
   <select id="getUsersByCode" parameterType="String" resultMap="com.hsk.mybatis.mapper.UserMapper.userMap">
   	SELECT USER_CODE,DEPT_CODE,CMPY_CODE,USER_NAME FROM SY_ORG_USER
   	WHERE DEPT_CODE=#{deptCode}
   </select>
   
   <!-- 
   	根据用户名称查找对应的用户集合
   	${}表示拼接sql串，将传参不加修饰的拼接在sql中,value表示简单类型的传参值
   	${}会引起sql注入，如传入 ' OR 1=1 OR '
   	#{}占位符，{}中可以写value或者其他值。根据传参类型修饰传参后拼接在sql中，如传入整型结果就是a=1,字符串就是a='1'
   	${value}:接收输入的传参内容，如果是简单的传参类型则${}中只能用value
   	#{}，${}通过OGNL读取对象的属性值，通过  属性.属性.属性.....方式获取对象属性值
   	一般不推荐使用${}方式获取属性值
      注意：#{}会将传参拼接程指定的sql语法如a=#{name}会被转换成a='zhangda'
      ${}会将传参原封不动的塞进sql语句如a=${name}会被转换成a=zhangda
    -->
   <select id="getDeptByName" parameterType="String" resultType="com.hsk.mybatis.bean.DeptBean">
   		SELECT DEPT_NAME,DEPT_CODE,CMPY_CODE FROM SY_ORG_DEPT WHERE DEPT_NAME LIKE '%${_parameter}%'
   </select>
   
   <!-- 
   parameterType为pojo（java对象）类型参数
   #{}占位符中指定pojo属性，接收到pojo对象属性值后mybatis通过OGNL获取对象属性值
    -->
   <insert id="addDept" parameterType="com.hsk.mybatis.bean.DeptBean">
   	    insert into sy_org_dept (dept_code,dept_name,CMPY_CODE) values (#{deptCode},#{deptName},#{cmpyCode})
   </insert>
   
   <delete id="updateDept" parameterType="com.hsk.mybatis.bean.DeptBean">
   	   update sy_org_dept set dept_name=#{deptName} where dept_code=#{deptCode}
   </delete>
</mapper>