2018-12-10 ssm练手（CRUD） 2、修改sql

1）数据库准备
进入数据库，创建ssm_crud数据库，并按如下要求创建两个表

tbl_dept（部门信息）

image.png

tbl_emp（员工信息）

image.png
然后设置外键（tbl_emp的d_id 对应tbl_dept的dept_id）
image.png

2）新增sql查询方法
点击进入EmployeeMapper.xml。可以看到已经有了很多自动生成的sql方法，但是却没有能够一次性把 员工 和 对应的部门 一起查出来的sql语句（即关联查询）。因此我们要新添加两个方法，能够关联查询

image.png

<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（添加重构即可）

image.png
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属性）

image.png

//添加对应的员工部门，并添加对应的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层添加刚刚新建的方法）

image.png

//新增两个查询方法，用于关联查询时候将两个表所有信息都表示出来
List<Employee> selectByExampleWithDept(EmployeeExample example);

Employee selectByPrimaryKeyWithDept(Integer empId);

4）测试
在“com.atguigu.crud.test”下添加java类，代码如下

image.png

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
版权声明：本文为博主原创文章，转载请附上博文链接！
运行结果：

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

image.png
image.png