SSM | MyBatis 多表联合查询

最新推荐文章于 2024-06-04 14:38:15 发布

CaRuJan

最新推荐文章于 2024-06-04 14:38:15 发布

阅读量1.5k

点赞数 3

分类专栏：学习文章标签： easyui spring

本文链接：https://blog.csdn.net/qq_33290233/article/details/104085369

版权

学习专栏收录该内容

37 篇文章 0 订阅

订阅专栏

前言

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

需求

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

分析：

公司里的部门与员工的关系，是一对多的关系
职位与员工的关系，也是一对多的关系
不论是一对多，还是多对一，在“多“”的一方表中，都有一个与“一”的一方表主键对应的字段，例如这里的员工表里有部门的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<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 : 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<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 : 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<Employee> selectAll();

}

<?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.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 jobName
  	from employee e left join job j
		on e.job_id = j.ID
		left join dept d
  	    on 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 {

	@Autowired
	private EmployeeMapper employeeMapper;
	
	@Override
	public 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.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……*/

@Controller
public 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<Employee> 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获取

<table id="dg" style="width:100%;height:550px;"></table>  

<script type="text/javascript">	
$(function() {		
//调用获取数据方法
getData("${pageContext.request.contextPath}/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>

效果如图
在这里插入图片描述

学习自：https://www.cnblogs.com/young-z/p/8001428.html

CaRuJan

关注

3
点赞
踩
12

收藏

觉得还不错? 一键收藏
0
评论
SSM | MyBatis 多表联合查询

前言在做一个项目需要实现三表联合查询，在这个问题卡了一些时间，故在此做个总结需求查询所有员工相对应的部门和职位分析：公司里的部门与员工的关系，是一对多的关系职位与员工的关系，也是一对多的关系不论是一对多，还是多对一，在“多“”的一方表中，都有一个与“一”的一方表主键对应的字段，例如这里的员工表里有部门的dept_id值和职位的job_id值一、数据库表以下三张图分别是员工表、部门...
复制链接

扫一扫