SSM

SSM

前言

在做一个项目需要实现三表联合查询，在这个问题卡了一些时间，故在此做个总结

需求

查询所有员工相对应的部门和职位

分析：

公司里的部门与员工的关系，是一对多的关系
职位与员工的关系，也是一对多的关系
不论是一对多，还是多对一，在“多“”的一方表中，都有一个与“一”的一方表主键对应的字段，例如这里的员工表里有部门的dept_id值和职位的job_id值

一、数据库表

以下三张图分别是员工表、部门表、职位表

二、编写实体类

1. 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 : im();}public String getCardId() {return cardId;}public void setCardId(String cardId) {this.cardId = cardId == null ? null : im();}public String getAddress() {return address;}public void setAddress(String address) {this.address = address == null ? null : im();}public String getPostCode() {return postCode;}public void setPostCode(String postCode) {this.postCode = postCode == null ? null : im();}public String getTel() {return tel;}public void setTel(String tel) {l = tel == null ? null : im();}public String getPhone() {return phone;}public void setPhone(String phone) {this.phone = phone == null ? null : im();}public String getQqNum() {return qqNum;}public void setQqNum(String qqNum) {this.qqNum = qqNum == null ? null : im();}public String getEmail() {return email;}public void setEmail(String email) {ail = email == null ? null : im();}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 : im();}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 : im();}public String getEducation() {return education;}public void setEducation(String education) {this.education = education == null ? null : im();}public String getSpeciality() {return speciality;}public void setSpeciality(String speciality) {this.speciality = speciality == null ? null : im();}public String getHobby() {return hobby;}public void setHobby(String hobby) {this.hobby = hobby == null ? null : im();}public String getRemark() {return remark;}public void setRemark(String remark) {ark = remark == null ? null : im();}public String getCreateDate() {return createDate;}public void setCreateDate(String createDate) {ateDate = 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;}@Overridepublic 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 + "]";}}

3. Dept实体类

package com.wyu.pojo;import java.util.List;public class Dept {private Integer id;private String name;private String remark;//private List<Employee> 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 : im();}public String getRemark() {return remark;}public void setRemark(String remark) {ark = remark == null ? null : im();}@Overridepublic String toString() {return "Dept [id=" + id + ", name=" + name + ", remark=" + remark + "]";}}

4. Job实体类

package com.wyu.pojo;import java.util.List;public class Job {private Integer id;private String name;private String remark;//private List<Employee> 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 : im();}public String getRemark() {return remark;}public void setRemark(String remark) {ark = remark == null ? null : im();}@Overridepublic 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<Employee> selectAll();}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis//DTD Mapper 3.0//EN" ".dtd" ><mapper namespace="com.wyu.mapper.EmployeeMapper" ><!--映射的内容放在最顶部--><!-- type :映射的类型，可以是完整的包名+类名，也可省略包路径。 --><!-- id:  就是给这个被配置了字段的 pojo 起个别名，是要写到sql语句的resultType里的，名字随意； --><resultMap id="DeptMap" type="com.wyu.pojo.Dept" ><!-- property 表示com.wyu.pojo.Dept中定义的属性名； column 表示Dept数据库表中的列名 --><id column="ID" property="id" jdbcType="INTEGER" /><!-- 这里要注意，因三个表的名称列名均为name，故后面三表联合查询会出错 --><!-- 我在下面的sql语句将Dept和Job里的name列名分别改为deptName和jobName，所以这里的column也要相应的修改，否则会出错 --><!-- 如果你的和我不同，则还是按照原样，列名是什么就写什么 --><result column="deptName" property="name" jdbcType="VARCHAR" /></resultMap><resultMap id="JobMap" type="com.wyu.pojo.Job" ><id column="ID" property="id" jdbcType="INTEGER" /><result column="jobName" property="name" jdbcType="VARCHAR" /></resultMap><resultMap id="empMap" type="com.wyu.pojo.Employee"><id column="id" property="id" jdbcType="INTEGER" /><result column="dept_id" property="deptId" jdbcType="INTEGER" /><result column="job_id" property="jobId" jdbcType="INTEGER" /><result column="name" property="name" jdbcType="VARCHAR" /><result column="card_id" property="cardId" jdbcType="VARCHAR" /><result column="address" property="address" jdbcType="VARCHAR" /><result column="post_code" property="postCode" jdbcType="VARCHAR" /><result column="tel" property="tel" jdbcType="VARCHAR" /><result column="phone" property="phone" jdbcType="VARCHAR" /><result column="qq_num" property="qqNum" jdbcType="VARCHAR" /><result column="email" property="email" jdbcType="VARCHAR" /><result column="sex" property="sex" jdbcType="INTEGER" /><result column="party" property="party" jdbcType="VARCHAR" /><result column="birthday" property="birthday" jdbcType="TIMESTAMP" /><result column="race" property="race" jdbcType="VARCHAR" /><result column="education" property="education" jdbcType="VARCHAR" /><result column="speciality" property="speciality" jdbcType="VARCHAR" /><result column="hobby" property="hobby" jdbcType="VARCHAR" /><result column="remark" property="remark" jdbcType="VARCHAR" /><result column="create_date" property="createDate" jdbcType="TIMESTAMP" /><!-- association这里只专门做一对一关联； property表示是com.wyu.pojo.Employee中的属性名称； javaType表示该属性是什么类型对象； --><!-- resultMap将查询结果映射为复杂类型的pojo，DeptMap和jobMap上边已定义 --><association property="dept" javaType="com.wyu.pojo.Dept" resultMap="DeptMap" /><association property="job" javaType="com.wyu.pojo.Job" resultMap="JobMap" /></resultMap><!--  多表联合查询 --><select id="selectAll" resultMap="empMap" >select e.*, d.name as deptName, j.name as jobNamefrom employee e left join job jon e.job_id = j.IDleft join dept don e.dept_id = d.ID</select></mapper>

三、测试

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;@Service
public class EmployeeServiceImpl implements EmployeeService {@Autowiredprivate EmployeeMapper employeeMapper;@Overridepublic List<Employee> findEmp() {List<Employee> 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, 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 ller;/*import……*/@Controller
public class EmployeeController {@RequestMapping(value = "/findEmployees", produces = "application/json;charset=utf8")@ResponseBodypublic String findEmployees(HttpServletRequest request, int page, int rows) {Page p = PageHelper.startPage(page, rows);List<Employee> list = employeeService.findEmp();list.forEach(li->System.out.println(li));PageInfo info = new PageInfo<>(p.getResult());long total = Total();System.out.println("total="+total);String jsonlist = JSONString(list);String json = "{"total" : " + total + ","rows" : " + jsonlist + "} ";System.out.println("json="+json);return json;}
}

五、前台显示

前台框架采用的是easyui，信息显示用的是datagrid
前台接收控制台传送到前台的数据是嵌套的json，故可使用formatter获取

<table id="dg" style="width:100%;height:550px;"></table>  <script type="text/javascript">	
$(function() {		
//调用获取数据方法
getData("${tPath}/findEmployees");    
});
function getData(url) {$('#dg').datagrid({    url: url,title: "员工列表",border: false,toolbar: "#tb",rownumbers: true,pagination: true,pageSize: "10",pageList: [10,15,20],columns:[[    {field:'id',title:'选择',value: 'number',checkbox: true,width:50},    {field:'name',title:'姓名',width:80},    {field:'deptName',title:'所属部门',width:80, formatter:function(value, row, index){return row.dept.name}},{field:'jobName',title:'职位',width:80, formatter:function(value, row, index){return row.job.name}},{field:'sex',title:'性别',width:80,formatter:function(value, row, index) {if(value == '1') {return "男";} else {return "女";}}},{field:'party',title:'面貌',width:80},{field:'race',title:'民族',width:80},{field:'education',title:'学历',width:80},{field:'speciality',title:'专业',width:80},{field:'birthday',title:'出生日期',width:200},{field:'phone',title:'手机号码',width:150},{field:'tel',title:'电话',width:150},{field:'qqNum',title:'qq号',width:150},{field:'email',title:'邮箱',width:150},{field:'cardId',title:'银行卡号',width:200},{field:'address',title:'住址',width:250},{field:'postCode',title:'邮政编码',width:80},{field:'createDate',title:'注册时间',width:200},{field:'hobby',title:'爱好',width:150},{field:'remark',title:'备注',width:150}]]});  			}
</script>

效果如图

学习自：.html