1）数据库准备
进入数据库，创建ssm_crud数据库，并按如下要求创建两个表
tbl_dept（部门信息）
tbl_emp（员工信息）
然后设置外键（tbl_emp的d_id 对应tbl_dept的dept_id）
2）新增sql查询方法
点击进入EmployeeMapper.xml。可以看到已经有了很多自动生成的sql方法，但是却没有能够一次性把 员工 和 对应的部门 一起查出来的sql语句（即关联查询）。因此我们要新添加两个方法，能够关联查询
<sql id="WithDept_Column_List">
e.emp_id,e.emp_name,e.gender,e.email,e.d_id,d.dept_id,d.dept_name
</sql>
<resultMap type="com.atguigu.crud.bean.Employee" id="WithDeptResultMap">
<id column="emp_id" jdbcType="INTEGER" property="empId" />
<result column="emp_name" jdbcType="VARCHAR" property="empName" />
<result column="gender" jdbcType="CHAR" property="gender" />
<result column="email" jdbcType="VARCHAR" property="email" />
<result column="d_id" jdbcType="INTEGER" property="dId" />
<association property="department" javaType="com.atguigu.crud.bean.Department">
<id column="dept_id" property="deptId" />
<result column="dept_name" property="deptName" />
</association>
</resultMap>
<select id="selectByExampleWithDept" resultMap="WithDeptResultMap">
select
<if test="distinct">
distinct
</if>
<include refid="WithDept_Column_List" />
FROM tbl_emp e
LEFT JOIN tbl_dept d ON e.d_id=d.dept_id
<if test="_parameter != null">
<include refid="Example_Where_Clause" />
</if>
<if test="orderByClause != null">
order by ${orderByClause}
</if>
</select>
<select id="selectByPrimaryKeyWithDept" resultMap="WithDeptResultMap">
select
<include refid="WithDept_Column_List" />
FROM tbl_emp e
LEFT JOIN tbl_dept d ON e.d_id=d.dept_id
where emp_id =
#{empId,jdbcType=INTEGER}
</select>
3）修改对应的dao和实体类
3.1）Department.java（添加重构即可）
public Department() {
super();
// TODO Auto-generated constructor stub
}
public Department(Integer deptId, String deptName) {
super();
this.deptId = deptId;
this.deptName = deptName;
}
3.2）Employee.java（添加重构方法，并添加Department属性）
//添加对应的员工部门，并添加对应的getter setter
//希望查询员工的同时，部门信息也是查询好的
private Department department;
public Department getDepartment() {
return department;
}
public void setDepartment(Department department) {
this.department = department;
}
public Employee() {
super();
// TODO Auto-generated constructor stub
}
public Employee(Integer empId, String empName, String gender, String email, Integer dId) {
super();
this.empId = empId;
this.empName = empName;
this.gender = gender;
this.email = email;
this.dId = dId;
}
3.3）EmployeeMapper.java（在dao层添加刚刚新建的方法）
//新增两个查询方法，用于关联查询时候将两个表所有信息都表示出来
List<Employee> selectByExampleWithDept(EmployeeExample example);
Employee selectByPrimaryKeyWithDept(Integer empId);
4）测试
在“com.atguigu.crud.test”下添加java类，代码如下
package com.atguigu.crud.test;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import com.atguigu.crud.bean.Department;
import com.atguigu.crud.dao.DepartmentMapper;
import com.atguigu.crud.dao.EmployeeMapper;
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations={"classpath:applicationContext.xml"})
public class MapperTest {
@Autowired
DepartmentMapper departmentMapper;
@Autowired
EmployeeMapper employeeMapper;
@Autowired
SqlSession sqlSession;
@Test
public void test(){
//由于id是自增，所以设置为null让它自增即可
departmentMapper.insertSelective(new Department(null,"游戏部"));
}
}
作者：匆匆四月
来源：CSDN
原文：https://blog.csdn.net/zyf2333/article/details/77855980
版权声明：本文为博主原创文章，转载请附上博文链接！
运行结果：
此处还有个问题，我们想给tbl_emp添加大量数据怎么办？
如下：
@Test
public void test(){
departmentMapper.insertSelective(new Department(null,"游戏部"));
//添加大量随机用户
EmployeeMapper mapper=sqlSession.getMapper(EmployeeMapper.class);
for(int i=0;i<100;i++){
String name=UUID.randomUUID().toString().substring(0, 5)+i;
mapper.insertSelective(new Employee(null,name,"M",name+"@aiguigu.com",1));
}
System.out.println("完成");
}
