SSM | MyBatis 多表联合查询
本文共 9261 字，大约阅读时间需要 30 分钟。
前言
在做一个项目需要实现三表联合查询，在这个问题卡了一些时间，故在此做个总结
需求
查询所有员工相对应的部门和职位
分析：
公司里的部门与员工的关系，是一对多的关系
职位与员工的关系，也是一对多的关系 不论是一对多，还是多对一，在“多“”的一方表中，都有一个与“一”的一方表主键对应的字段，例如这里的员工表里有部门的dept_id值和职位的job_id值一、数据库表
以下三张图分别是员工表、部门表、职位表
二、编写实体类
- Employee实体类
package com.wyu.pojo;import java.util.Date;import java.util.List;import java.util.Set;import org.springframework.format.annotation.DateTimeFormat;public class Employee { @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss") private Integer id; private Integer deptId; private Integer jobId; private String name; private String cardId; private String address; private String postCode; private String tel; private String phone; private String qqNum; private String email; private Integer sex; private String party; private String birthday; private String race; private String education; private String speciality; private String hobby; private String remark; private String createDate; private Dept dept; //所属的部门，多对一 private Job job; //所属的职位，多对一 public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public Integer getDeptId() { return deptId; } public void setDeptId(Integer deptId) { this.deptId = deptId; } public Integer getJobId() { return jobId; } public void setJobId(Integer jobId) { this.jobId = jobId; } public String getName() { return name; } public void setName(String name) { this.name = name == null ? null : name.trim(); } public String getCardId() { return cardId; } public void setCardId(String cardId) { this.cardId = cardId == null ? null : cardId.trim(); } public String getAddress() { return address; } public void setAddress(String address) { this.address = address == null ? null : address.trim(); } public String getPostCode() { return postCode; } public void setPostCode(String postCode) { this.postCode = postCode == null ? null : postCode.trim(); } public String getTel() { return tel; } public void setTel(String tel) { this.tel = tel == null ? null : tel.trim(); } public String getPhone() { return phone; } public void setPhone(String phone) { this.phone = phone == null ? null : phone.trim(); } public String getQqNum() { return qqNum; } public void setQqNum(String qqNum) { this.qqNum = qqNum == null ? null : qqNum.trim(); } public String getEmail() { return email; } public void setEmail(String email) { this.email = email == null ? null : email.trim(); } public Integer getSex() { return sex; } public void setSex(Integer sex) { this.sex = sex; } public String getParty() { return party; } public void setParty(String party) { this.party = party == null ? null : party.trim(); } public String getBirthday() { return birthday; } public void setBirthday(String birthday) { this.birthday = birthday; } public String getRace() { return race; } public void setRace(String race) { this.race = race == null ? null : race.trim(); } public String getEducation() { return education; } public void setEducation(String education) { this.education = education == null ? null : education.trim(); } public String getSpeciality() { return speciality; } public void setSpeciality(String speciality) { this.speciality = speciality == null ? null : speciality.trim(); } public String getHobby() { return hobby; } public void setHobby(String hobby) { this.hobby = hobby == null ? null : hobby.trim(); } public String getRemark() { return remark; } public void setRemark(String remark) { this.remark = remark == null ? null : remark.trim(); } public String getCreateDate() { return createDate; } public void setCreateDate(String createDate) { this.createDate = createDate; } public Dept getDept() { return dept; } public void setDept(Dept dept) { this.dept = dept; } public Job getJob() { return job; } public void setJob(Job job) { this.job = job; } @Override public String toString() { return "Employee [id=" + id + ", deptId=" + deptId + ", jobId=" + jobId + ", name=" + name + ", cardId=" + cardId + ", address=" + address + ", postCode=" + postCode + ", tel=" + tel + ", phone=" + phone + ", qqNum=" + qqNum + ", email=" + email + ", sex=" + sex + ", party=" + party + ", birthday=" + birthday + ", race=" + race + ", education=" + education + ", speciality=" + speciality + ", hobby=" + hobby + ", remark=" + remark + ", createDate=" + createDate + ", dept=" + dept + ", job=" + job + "]"; } } - Dept实体类
package com.wyu.pojo;import java.util.List;public class Dept { private Integer id; private String name; private String remark; //private List employees; //部门下的员工列表，一对多 public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name == null ? null : name.trim(); } public String getRemark() { return remark; } public void setRemark(String remark) { this.remark = remark == null ? null : remark.trim(); } @Override public String toString() { return "Dept [id=" + id + ", name=" + name + ", remark=" + remark + "]"; } } - Job实体类
package com.wyu.pojo;import java.util.List;public class Job { private Integer id; private String name; private String remark; //private List employees; 一个职位对应多个员工---------一对多 public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name == null ? null : name.trim(); } public String getRemark() { return remark; } public void setRemark(String remark) { this.remark = remark == null ? null : remark.trim(); } @Override public String toString() { return "Job [id=" + id + ", name=" + name + ", remark=" + remark + "]"; } } 二、Mapper
mapper接口及对应sql映射文件
package com.wyu.mapper;import com.wyu.pojo.Employee;import com.wyu.pojo.EmployeeExample;import java.util.List;import org.apache.ibatis.annotations.Param;public interface EmployeeMapper { /** 省略若干代码 **/ List selectAll();} 三、测试
package com.wyu.service;import java.util.List;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Service;import com.wyu.mapper.EmployeeMapper;import com.wyu.pojo.Employee;import com.wyu.pojo.EmployeeExample;@Servicepublic class EmployeeServiceImpl implements EmployeeService { @Autowired private EmployeeMapper employeeMapper; @Override public List findEmp() { List list = employeeMapper.selectAll(); list.forEach(li->System.out.println("原始数据:"+li)); return list; }} 控制台得出的数据，复制一条如下
Employee[id=20201025, deptId=6, jobId=2, name=轻轻, cardId=44, address=深圳, postCode=null, tel=812122423, phone=13714299131, qqNum=83232311, email=83232311@qq.com, sex=2, party=团员, birthday=1994-07-12 00:00:00.0, race=汉, education=硕士, speciality=人力资源, hobby=深圳, remark=, createDate=2020-01-25 22:13:32.0, dept=Dept [id=20201025, name=人事部, remark=null], job=Job [id=20201025, name=会计师, remark=null]]
可以看到以及获得相应员工的所在的部门和职位信息
四、控制台Controller
package com.wyu.controller;/*import……*/@Controllerpublic class EmployeeController { @RequestMapping(value = "/findEmployees", produces = "application/json;charset=utf8") @ResponseBody public String findEmployees(HttpServletRequest request, int page, int rows) { Page p = PageHelper.startPage(page, rows); List list = employeeService.findEmp(); list.forEach(li->System.out.println(li)); PageInfo info = new PageInfo<>(p.getResult()); long total = info.getTotal(); System.out.println("total="+total); String jsonlist = JSON.toJSONString(list); String json = "{\"total\" : " + total + ",\"rows\" : " + jsonlist + "} "; System.out.println("json="+json); return json; }} 五、前台显示
前台框架采用的是easyui，信息显示用的是datagrid
前台接收控制台传送到前台的数据是嵌套的json，故可使用formatter获取效果如图
学习自：https://www.cnblogs.com/young-z/p/8001428.html
你可能感兴趣的文章
mysql 里对root及普通用户赋权及更改密码的一些命令
查看>>
Mysql 重置自增列的开始序号
查看>>
mysql 锁机制 mvcc_Mysql性能优化-事务、锁和MVCC
查看>>
MySQL 错误
查看>>
mysql 随机数 rand使用
查看>>
MySQL 面试题汇总
查看>>
MySQL 面试，必须掌握的 8 大核心点
查看>>
MySQL 高可用性之keepalived+mysql双主
查看>>
MySQL 高性能优化规范建议
查看>>
mysql 默认事务隔离级别下锁分析
查看>>
Mysql--逻辑架构
查看>>
MySql-2019-4-21-复习
查看>>
mysql-5.6.17-win32免安装版配置
查看>>
mysql-5.7.18安装
查看>>
MySQL-Buffer的应用
查看>>
mysql-cluster 安装篇(1)---简介
查看>>
mysql-connector-java.jar乱码,最新版mysql-connector-java-8.0.15.jar，如何愉快的进行JDBC操作...
查看>>
mysql-connector-java各种版本下载地址
查看>>
mysql-EXPLAIN
查看>>
MySQL-Explain的详解
查看>>