ssm练手（CRUD） 2、修改sql方法，并利用JUnit结合数据库进行测试

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 -->
    <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,"游戏部"));
    }
}

运行结果：

此处还有个问题，我们想给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("完成");
    }