以员工和部门表为基础的关联表查询
这篇讲员工的查询
先看看数据库的建表
- 部门表
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
