mybatis配置多对一或一对一关系

本例以部门用户关系为例说明多对一的关系配置；

第一步，编写部门的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;
	}
}

第二步编写用户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 + "]"+deptBean.toString();
	}
}

 

 第三步编写用户持久层接口及UserDao

package com.hsk.mybatis.dao;

import java.util.List;

import com.hsk.mybatis.bean.UserBean;

/**
 * 用户dao接口，操作用户信息
 * @author huangshikai
 *
 */
public interface UserDao {

	/**
	 * 按用户编码查询用户信息
	 * @param userCode
	 * @return
	 * @throws Exception
	 */
	public UserBean findUserById(String userCode) throws Exception;
	
	public void insertUser(UserBean userBean) throws Exception;
	
	public void deleteUser(String userCode) throws Exception;
	
	public UserBean getUserAndDeptByCode(String userCode);
	
	public List<UserBean> getUsersByDeptCode(String deptCode);
}

第四步编写userMappper.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.UserDao">

	<!-- 配置结果集字段和javabean字段对应关系 -->
	<!-- 配置多对一的人员map方式一 -->
	<resultMap id="userMap" type="com.hsk.mybatis.bean.UserBean">
        <id property="userCode" column="USER_CODE" />
        <result property="userName" column="USER_NAME"/>
        <result property="deptCode" column="DEPT_CODE"/>
        <result property="cmpyCode" column="CMPY_CODE"/>
        <association property="deptBean"  column="DEPT_CODE" 
        javaType="com.hsk.mybatis.bean.DeptBean" 
        resultMap="deptMap"></association>
    </resultMap>
    
    <resultMap type="com.hsk.mybatis.bean.DeptBean" id="deptMap">
    	<id property="deptCode" column="DEPT_CODE"></id>
    	<result property="deptName" column="DEPT_NAME"/>
    	<result property="cmpyCode" column="CMPY_CODE"/>
    </resultMap>
    
    
    <!-- 配置多对一的人员map方式二 -->
    <resultMap type="com.hsk.mybatis.bean.UserBean" id="userAndDeptMap">
    	<id property="userCode" column="USER_CODE"></id>
    	<result property="userName" column="USER_NAME"/>
    	<result property="deptCode" column="DEPT_CODE"/>
    	<result property="cmpyCode" column="CMPY_CODE"/>
    	<association property="deptBean" column="DEPT_CODE" javaType="com.hsk.mybatis.bean.DeptBean">
    		<id property="deptCode" column="DEPT_CODE"/>
    		<result property="deptName" column="DEPT_NAME"/>
    		<result property="cmpyCode" column="CMPY_CODE"/>
    	</association>
    </resultMap>
    
    <select id="getUserAndDeptByCode" parameterType="String" resultMap="userAndDeptMap">
    	SELECT user_code,a.dept_code,a.user_name,a.cmpy_code,
    	b.dept_code,b.dept_name,b.cmpy_code from sy_org_user a
    	left join sy_org_dept b
    	on a.dept_code=b.dept_code
    	where a.user_code=#{userCode}
    </select>
    
    <select id="getUsersByDeptCode" parameterType="String" resultMap="userAndDeptMap">
    SELECT user_code,a.dept_code,a.user_name,a.cmpy_code,
    	b.dept_code,b.dept_name,b.cmpy_code from sy_org_user a
    	left join sy_org_dept b
    	on a.dept_code=b.dept_code
    	where a.dept_code=#{deptCode}
    </select>
    
	<!--
	 统一命名空间下id唯一 
	parameterType输入参数类型可以是java简单的数据类型也可以是java对象，这里指定string
	#{}表示占位符
	#{userCode}其中userCode为输入参数，参数名称为userCode
	resultMap指定sql输出结果所映射的java对象，注意resultMap要配置java对象属性与数据库字段对应关系
	resultType指定sql输出结果单条记录所映射的java对象，注意resultType配置的java类的属性需要与数据库字段名称一致
	-->
   <select id="findUserById" parameterType="String" resultMap="userMap">
       SELECT USER_CODE,USER_NAME,DEPT_CODE FROM SY_ORG_USER WHERE USER_CODE=#{userCode}
   </select>
   
   <!-- 
   	根据用户名称查找对应的用户集合
   	${}表示拼接sql串，将传参不加修饰的拼接在sql中,value表示简单类型的传参值
   	${}会引起sql注入，如传入 ' OR 1=1 OR '
   	#{}占位符，{}中可以写value或者其他值。根据传参类型修饰传参后拼接在sql中，如传入整型结果就是a=1,字符串就是a='1'
   	${value}:接收输入的传参内容，如果是简单的传参类型则${}中只能用value
   	#{}，${}通过OGNL读取对象的属性值，通过  属性.属性.属性.....方式获取对象属性值
   	一般不推荐使用${}方式获取属性值
    -->
   <select id="findUserByName" parameterType="String" resultMap="userMap">
   		SELECT USER_CODE,USER_NAME,DEPT_CODE FROM SY_ORG_USER WHERE USER_NAME LIKE '%${value}%'
   </select>
   
   <!-- 
   parameterType为pojo（java对象）类型参数
   #{}占位符中指定pojo属性，接收到pojo对象属性值后mybatis通过OGNL获取对象属性值
    -->
   <insert id="insertUser" parameterType="com.hsk.mybatis.bean.UserBean">
   	    insert into sy_org_user (user_code,user_name,dept_code,cmpy_code) values (#{userCode},#{userName},#{deptCode},#{cmpyCode})
   </insert>
   
   <delete id="deleteUser" parameterType="String">
   	   delete sy_org_user where user_code=#{userCode}
   </delete>
</mapper>

 第五步mybatis核心配置文件引入userMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">

<configuration>
    <!--环境配置，连接的数据库，这里使用的是MySQL-->
    <environments default="mysql">
        <environment id="mysql">
            <!--指定事务管理的类型，这里简单使用Java的JDBC的提交和回滚设置-->
            <transactionManager type="JDBC"></transactionManager>
            <!--dataSource 指连接源配置，POOLED是JDBC连接对象的数据源连接池的实现-->
            <dataSource type="POOLED">
                <property name="driver" value="oracle.jdbc.driver.OracleDriver"></property>
                <property name="url" value="jdbc:oracle:thin:@localhost:1521/orcl"></property>
                <property name="username" value="SCPT"></property>
                <property name="password" value="SCPT"></property>
            </dataSource>
        </environment>
    </environments>
    
    <mappers>
        <!--这是告诉Mybatis去哪找持久化类的映射文件，对于在src下的文件直接写文件名，
            如果在某包下，则要写明路径,如：com/mybatistest/config/User.xml-->
        <mapper resource="config/sql/user.xml"></mapper>
        <mapper resource="config/mapper/userMapper.xml"></mapper>
        <mapper resource="config/mapper/deptMapper.xml"></mapper>
    </mappers>
</configuration>

第六步单元测试

	@Test
	public void userDaoTest(){
		try {
			Reader reader = Resources.getResourceAsReader("config/mybatis.xml");
			SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);
			SqlSession sqlSession = factory.openSession();
			UserDao userDao = sqlSession.getMapper(UserDao.class);
			UserBean userBean = userDao.getUserAndDeptByCode("25");
			System.out.println(userBean.toString());
			System.out.println("-----获取部门所有人员------");
			List<UserBean> userList = userDao.getUsersByDeptCode("12796");
			for (UserBean userBean2 : userList) {
				System.out.println(userBean2.toString());
			}
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

第七步查看结果