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>