Mybatis入门之 resultMap的映射关系

当查询的数据表的字段名与pojo类的属性名一致时，可以用resultType，但sql查询到的字段与pojo的属性名不一致时，则需要使用resultMap将字段名和属性名对应起来。
resultType直接表示返回类型，
resultMap：对外部resultMap的引用，二者不能同时存在。

前期准备，一个employee表，项目的目录结构与之前写的两篇文章一致。

CREATE TABLE employee(
 id int(11) NOT NULL auto_increment,
 user_name VARCHAR(50) NOT NULL,
 age int(6) NOT NULL,
 email VARCHAR(50) NOT NULL,
 PRIMARY KEY(id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

@Data
public class Employee {
    private Integer id;
    private String userName;
    private Integer age;
    private String email;  
}

单表查询

EmployeeMapper接口：

public Employee getEmpById(Integer id);

EmployeeMapper.xml映射文件：

<mapper namespace="com.diligentkong.mybatis.dao.EmployeeMapper">

    <!--resultMap 自定义某个javaBean的封装规则， type：自定义规则的java类型 id“唯一标识，方便引用-->
    <resultMap type="com.diligentkong.mybatis.bean.Employee" id="simpleEmp">
       <!-- 指定主键列的封装规则：与下面<result>标签的区别是可以用于提升性能，所以这里用<id>标签来定义主键的映射关系
            column：数据库中对应的字段名
            property：对应JavaBean 的属性名
       -->
        <id column="id" property="id"></id>
       <!-- 定义普通列封装规则-->
        <result column="user_name" property="userName"></result>
        <!-- 其他不指定的列会自动封装：我们只要写resultMap就把全部的映射规则都写上。 -->
        <result column="age" property="age"></result>
        <result column="email" property="email"></result>
    </resultMap>

    <select id="getEmpById" resultMap="simpleEmp" >
        select * from employee where id=#{id};
    </select>


</mapper>

测试结果：

 public SqlSessionFactory getSqlSeeionFactory() throws IOException {
        String resource = "config/mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        return new SqlSessionFactoryBuilder().build(inputStream);

    }

    @Test
    public void myTest() throws IOException {
        // 1.获取sqlSessionFactory对象
        SqlSessionFactory sqlSessionFactory = getSqlSeeionFactory();
        // 2、获取sqlSession对象
        SqlSession openSession = sqlSessionFactory.openSession();

        try {
            // 3、获取接口的实现类对象
            //会为接口自动的创建一个代理对象，代理对象去执行增删改查方法

            EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
            Employee employee = mapper.getEmpById(1);
            System.out.println(employee);

            openSession.commit();
        } finally {
            openSession.close();
        }
    }

}

结果：Employee(id=1, userName=张三, age=20, email=zhangsan.163com)

association 用于一对一，一对多

关联查询：association 用于一对一，一对多的情况
现在我有这样的需求，一个Employee 员工对应一个dept部门，而这个dept部门也有员工（employee） 信息
新建一个dept表，

CREATE TABLE dept(
id int(11) NOT NULL auto_increment,
dept_name VARCHAR(50) NOT NULL,
PRIMARY KEY(id) 
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO dept VALUES(1,"研发部");
INSERT INTO dept VALUES(2,"测试部");
INSERT INTO dept VALUES(3,"市场部");

在employee表中添加dept_id字段，建立外键关系。

alter table employee add column d_id INT(11)
ALTER table employee ADD constraint fk_emp_dept FOREIGN KEY(dept_id) REFERENCES dept(id);
INSERT INTO employee VALUES(1,"张三",20,"zhangsan.163com",1);
INSERT INTO employee VALUES(2,"李四",21,"lisi.163com",1);
INSERT INTO employee VALUES(3,"王五",22,"wangwu.163com",2);
INSERT INTO employee VALUES(4,"赵六",23,"zhaoliu.163com",3);
INSERT INTO employee VALUES(5,"kong",20,"kong.163com",3);

新建Department 类

@Data
public class Department {
    private Integer id;
    private String deptName;
   
}

Employee类中新增dept属性

@Data
public class Employee {
    private Integer id;
    private String userName;
    private Integer age;
    private String email;
    private Department dept;

}

public Employee getEmpAndDept(Integer id);

EmployeeMapper接口：

public Employee getEmpAndDept(Integer id);

EmployeeMapper.xml映射文件：

 <resultMap id="MyDeptEmp" type="com.diligentkong.mybatis.bean.Employee">
      <id column="id" property="id"></id>
      <result column="user_name" property="userName"></result>
      <result column="age" property="age"></result>
      <result column="email" property="email"></result>

      <!--  association可以指定联合的javaBean对象
property="dept"：指定哪个属性是联合的对象
javaType:指定这个属性对象的类型[不能省略]
-->
      <association property="dept" javaType="com.diligentkong.mybatis.bean.Department">
          <id column="id" property="id"/>
          <result column="dept_name" property="departmentName"/>
      </association>

  </resultMap>
  <select id="getEmpAndDept" resultMap="MyDeptEmp">
      select e.id id, e.user_name user_name, e.age age, e.email email,e.dept_id deptid,
      d.id id,d.dept_name dept_name from employee e,dept d where e.dept_id=d.id AND e.id= #{id}
  </select>

测试结果：

Employee employee = mapper.getEmpAndDept(1);
System.out.println(employee);
结果：
Employee(id=1, userName=张三, age=20, email=zhangsan.163com, dept=Department(id=1, departmentName=研发部))

collection 用于 一对一 和 一对多

查询部门信息，并查询部门中包含的员工信息，修改pojo

@Data
public class Department {
    private Integer id;
    private String departmentName;
    private List<Employee> emps;

}

DepartmentMapper接口：

public Department getDeptById(Integer id);

DepartmentMapper.xml映射文件

<mapper namespace="com.diligentkong.mybatis.dao.DepartmentMapper">

    <resultMap id="MyDept" type="com.diligentkong.mybatis.bean.Department">
        <id column="did" property="id"></id>
        <result column="dept_name" property="departmentName"></result>
        <!--
           collection定义关联集合类型的属性的封装规则
           ofType:指定集合里面元素的类型
       -->
        <collection property="emps" ofType="com.diligentkong.mybatis.bean.Employee">
            <!-- 定义这个集合中元素的封装规则 -->
            <id column="id" property="id"/>
            <result column="user_name" property="userName"/>
            <result column="age" property="age"/>
            <result column="email" property="email"/>
        </collection>
    </resultMap>


    <select id="getDeptById" resultMap="MyDept">
		SELECT d.id did,d.dept_name dept_name,
				e.id eid,e.user_name user_name,e.age age,e.email email
		FROM dept d
		LEFT JOIN employee e
		ON d.id=e.dept_id
		WHERE d.id=#{id}
	</select>
</mapper>

测试结果：

 DepartmentMapper dept = openSession.getMapper(DepartmentMapper.class);
 Department department = dept.getDeptById(1);
 System.out.println(department);
 结果：
 Department(id=1, departmentName=研发部, emps=[Employee(id=null, userName=张三, age=20, email=zhangsan.163com, dept=null), Employee(id=null, userName=李四, age=21, email=lisi.163com, dept=null)])