以员工和部门表为基础的关联表查询
这篇讲员工的查询
先看看数据库的建表

• 部门表

CREATE TABLE tb_dept
(
  d_id       INT AUTO_INCREMENT
    PRIMARY KEY,
  d_name VARCHAR(10) NOT NULL

);

• 员工表

CREATE TABLE tb_emp
(
  e_id       INT AUTO_INCREMENT
    PRIMARY KEY,
  e_name VARCHAR(10) NOT NULL,
  e_job VARCHAR(10),
  e_hiredate DATE,
  e_sal int,
  e_dep_id int NOT NULL ,
  FOREIGN KEY (e_dep_id) REFERENCES tb_dept(d_id)
);

表创建好了 , 数据就自己随便加几条了 !!!

这里用的是spring boot + mybatis

实体类肯定是要先有的

• 部门

public class Dept {

    private int id;

    private String name;

    // ......  Getter and Setter  请自行生成添加上
}

• 员工

public class Emp {

    private int id;

    private String name;

    private String job;

    private Date hiredate;

    private int sal;

    private int depId;

    private Dept dept;


     // ......  Getter and Setter  请自行生成添加上
}

现在先从controller部分看起

• @RestController == @Controller + @ResponseBody

@RestController
public class EmpController {

    @Autowired
    private IEmpService empService;


    @RequestMapping(value = "getEmpById/{id}")
    public ResponseEntity<JsonResult> getEmpById(@PathVariable(value = "id") Integer id){
        JsonResult r = new JsonResult();
        try {
            Emp emp = empService.getEmpById(id);
            r.setResult(emp);
            r.setStatus("ok");
        } catch (Exception e) {
            r.setResult(e.getClass().getName()+":"+e.getMessage());
            r.setStatus("error");
            e.printStackTrace();
        }
        return ResponseEntity.ok(r);
    }
}

接下来是注入的service , 分别为接口和实现类:

• 接口

public interface IEmpService {

    Emp getEmpById(Integer id);
}

• 实现类 : 用的Service注解

@Service
public class EmpServiceImpl implements IEmpService {

    @Autowired
    private EmpMapper empMapper;

    @Override
    public Emp getEmpById(Integer id) {
        return empMapper.getEmpById(id);
    }
}

从服务里面看到有一个EmpMapper, 那下一个就进入Repository

@Repository
public interface EmpMapper {

    Emp getEmpById(Integer id);

}

好了 , 剩下的就是empMapper.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">
<mapper namespace="com.flynn.mapper.EmpMapper">
    <resultMap id="BaseResultMap" type="com.flynn.bean.Emp">
        <id property="id" column="e_id" jdbcType="INTEGER" />
        <result property="name" column="e_name" jdbcType="VARCHAR" />
        <result property="job" column="e_job" jdbcType="VARCHAR" />
        <result property="hiredate" column="e_hiredate" jdbcType="DATE" />
        <result property="sal" column="e_sal" jdbcType="INTEGER" />
        <result property="depId" column="e_dep_id" jdbcType="INTEGER" />
        <!--
        association可以指定联合的JavaBean对象
            property="dept"指定哪个属性是联合的对象
            javaType:指定这个属性对象的类型
        -->
        <association property="dept" javaType="com.flynn.bean.Dept">
            <result property="id" column="d_id"/>
            <result property="name" column="d_name" />
        </association>
    </resultMap>

    <select id="getEmpById" parameterType="int" resultMap="BaseResultMap">
        SELECT
        e.e_id,e.e_name,e.e_job,e.e_hiredate,e.e_sal,e.e_dep_id,d.d_id,d.d_name
        FROM
        tb_emp e
        inner join tb_dept d on e.e_dep_id = d.d_id
    </select>

</mapper>

image.png