当前位置：网站首页>建表到页面完整实例演示—联表查询
建表到页面完整实例演示—联表查询
2022-04-23 05:42:00 【ShuangTwo】
建表
设计两张表，分别是员工表和部门表，员工表有部门表的id字段
emp:
+--------+----------+---------+
| emp_id | emp_name | dept_id |
+--------+----------+---------+
| 1 | 战三 | 1 |
| 2 | 李思 | 2 |
| 3 | 王武 | 1 |
| 4 | 刘二 | 3 |
+--------+----------+---------+
dept:
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 1 | 开发部 |
| 2 | 运维部 |
| 3 | 人事部 |
+---------+-----------+
导包、配置
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.5.1</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
</dependencies>
spring.application.name=inner-join
spring.thymeleaf.cache=true
spring.thymeleaf.check-template=true
spring.thymeleaf.check-template-location=true
spring.thymeleaf.content-type=text/html
spring.thymeleaf.enabled=true
spring.thymeleaf.encoding=UTF-8
spring.thymeleaf.excluded-view-names=
spring.thymeleaf.mode=HTML5
spring.thymeleaf.prefix=classpath:/templates/
spring.thymeleaf.suffix=.html
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.name=defaultDataSource
spring.datasource.url=jdbc:mysql://localhost:3306/test?serverTimezone=UTC
spring.datasource.username=root
spring.datasource.password=123456
server.port=800
实体类
重点：private Dept dept;，在emp实体类引入dept属性
package com.java.web.domain;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import java.io.Serializable;
import java.util.Objects;
import lombok.Data;
/** * * @TableName emp */
@TableName(value ="emp")
@Data
public class Emp implements Serializable {
/** * 编号 */
@TableId(type = IdType.AUTO)
private Integer empId;
/** * 员工姓名 */
private String empName;
/** * 所属部门 */
private Dept dept;
@TableField(exist = false)
private static final long serialVersionUID = 1L;
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (o == null || getClass() != o.getClass()) return false;
Emp emp = (Emp) o;
return Objects.equals(empId, emp.empId) && Objects.equals(empName, emp.empName) && Objects.equals(dept, emp.dept);
}
@Override
public int hashCode() {
return Objects.hash(empId, empName, dept);
}
@Override
public String toString() {
return "Emp{" +
"empId=" + empId +
", empName='" + empName + '\'' +
", dept=" + dept +
'}';
}
}
package com.java.web.domain;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import java.io.Serializable;
import lombok.Data;
/** * * @TableName dept */
@TableName(value ="dept")
@Data
public class Dept implements Serializable {
/** * 部门id */
@TableId(type = IdType.AUTO)
private Integer deptId;
/** * 部门名称 */
private String deptName;
@TableField(exist = false)
private static final long serialVersionUID = 1L;
@Override
public boolean equals(Object that) {
if (this == that) {
return true;
}
if (that == null) {
return false;
}
if (getClass() != that.getClass()) {
return false;
}
Dept other = (Dept) that;
return (this.getDeptId() == null ? other.getDeptId() == null : this.getDeptId().equals(other.getDeptId()))
&& (this.getDeptName() == null ? other.getDeptName() == null : this.getDeptName().equals(other.getDeptName()));
}
@Override
public int hashCode() {
final int prime = 31;
int result = 1;
result = prime * result + ((getDeptId() == null) ? 0 : getDeptId().hashCode());
result = prime * result + ((getDeptName() == null) ? 0 : getDeptName().hashCode());
return result;
}
@Override
public String toString() {
StringBuilder sb = new StringBuilder();
sb.append(getClass().getSimpleName());
sb.append(" [");
sb.append("Hash = ").append(hashCode());
sb.append(", deptId=").append(deptId);
sb.append(", deptName=").append(deptName);
sb.append(", serialVersionUID=").append(serialVersionUID);
sb.append("]");
return sb.toString();
}
}
Controller控制层
package com.java.web.controller;
import com.java.web.domain.Dept;
import com.java.web.service.DeptService;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import javax.annotation.Resource;
import java.util.List;
@Controller
@RequestMapping("/dept")
public class DeptController {
@Resource
private DeptService deptService;
@GetMapping("/list")
public String list(Model model) {
List<Dept> list = deptService.list();
model.addAttribute("deptList", list);
return "dept";
}
}
package com.java.web.controller;
import com.java.web.domain.Emp;
import com.java.web.service.EmpService;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import javax.annotation.Resource;
import java.util.List;
@Controller
@RequestMapping("/emp")
public class EmpController {
@Resource
private EmpService empService;
@GetMapping("/list")
public String list(Model model) {
List<Emp> list = empService.empAndDept();
model.addAttribute("empList", list);
return "emp";
}
}
service层
package com.java.web.service;
import com.java.web.domain.Emp;
import com.baomidou.mybatisplus.extension.service.IService;
import java.util.List;
/** * @author H * @description 针对表【emp】的数据库操作Service * @createDate 2022-04-22 15:00:53 */
public interface EmpService extends IService<Emp> {
List<Emp> empAndDept();
}
package com.java.web.service;
import com.java.web.domain.Dept;
import com.baomidou.mybatisplus.extension.service.IService;
/** * @author H * @description 针对表【dept】的数据库操作Service * @createDate 2022-04-22 15:00:53 */
public interface DeptService extends IService<Dept> {
}
service.Impl实现层
package com.java.web.service.impl;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.java.web.domain.Emp;
import com.java.web.service.EmpService;
import com.java.web.mapper.EmpMapper;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import java.util.List;
/** * @author H * @description 针对表【emp】的数据库操作Service实现 * @createDate 2022-04-22 15:00:53 */
@Service
public class EmpServiceImpl extends ServiceImpl<EmpMapper, Emp>
implements EmpService{
@Resource
private EmpMapper empMapper;
@Override
public List<Emp> empAndDept() {
return empMapper.empAndDept();
}
}
package com.java.web.service.impl;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.java.web.domain.Dept;
import com.java.web.service.DeptService;
import com.java.web.mapper.DeptMapper;
import org.springframework.stereotype.Service;
/** * @author H * @description 针对表【dept】的数据库操作Service实现 * @createDate 2022-04-22 15:00:53 */
@Service
public class DeptServiceImpl extends ServiceImpl<DeptMapper, Dept>
implements DeptService{
}
mapper接口
package com.java.web.mapper;
import com.java.web.domain.Dept;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
/** * @author H * @description 针对表【dept】的数据库操作Mapper * @createDate 2022-04-22 15:00:53 * @Entity com.java.web.domain.Dept */
public interface DeptMapper extends BaseMapper<Dept> {
}
package com.java.web.mapper;
import com.java.web.domain.Emp;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import java.util.List;
/** * @author H * @description 针对表【emp】的数据库操作Mapper * @createDate 2022-04-22 15:00:53 * @Entity com.java.web.domain.Emp */
public interface EmpMapper extends BaseMapper<Emp> {
List<Emp> empAndDept();
}
mapper.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.java.web.mapper.DeptMapper">
<resultMap id="BaseResultMap" type="com.java.web.domain.Dept">
<id property="deptId" column="dept_id" jdbcType="INTEGER"/>
<result property="deptName" column="dept_name" jdbcType="VARCHAR"/>
</resultMap>
<sql id="Base_Column_List">
dept_id,dept_name
</sql>
</mapper>
<?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.java.web.mapper.EmpMapper">
<resultMap id="BaseResultMap" type="com.java.web.domain.Emp">
<id property="empId" column="emp_id" jdbcType="INTEGER"/>
<result property="empName" column="emp_name" jdbcType="VARCHAR"/>
<association property="dept" javaType="com.java.web.domain.Dept">
<id property="deptId" column="dept_id"/>
<result property="deptName" column="dept_name"/>
</association>
</resultMap>
<sql id="Base_Column_List">
emp_id,emp_name,dept
</sql>
<select id="empAndDept" resultMap="BaseResultMap">
select emp_id, emp_name, dept.dept_name
from emp inner join dept
on emp.dept_id=dept.dept_id
</select>
</mapper>
html视图展示
<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
<meta charset="UTF-8">
<title>部门表</title>
</head>
<body>
<table border="1">
<tr>
<th>部门ID</th>
<th>部门名称</th>
</tr>
<tr th:each="dept:${deptList}">
<td th:text="${dept.getDeptId()}"></td>
<td th:text="${dept.getDeptName()}"></td>
</tr>
</table>
</body>
</html>
<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
<meta charset="UTF-8">
<title>员工表</title>
</head>
<body>
<table border="1">
<tr>
<th>员工ID</th>
<th>员工姓名</th>
<th>所属部门</th>
</tr>
<tr th:each="emp:${empList}">
<td th:text="${emp.getEmpId()}"></td>
<td th:text="${emp.getEmpName()}"></td>
<td th:text="${emp.dept.getDeptName()}"></td>
</tr>
</table>
</body>
</html>
总结
- 在emp的实体类中定义dept表，不是定义deptId属性
- 在empMapper.xml中自定义查询返回值类型，通过association引入dept，然后编写联表查询的SQL语句（可以在Navicat中先运行测试）
<resultMap id="BaseResultMap" type="com.java.web.domain.Emp">
<id property="empId" column="emp_id" jdbcType="INTEGER"/>
<result property="empName" column="emp_name" jdbcType="VARCHAR"/>
<association property="dept" javaType="com.java.web.domain.Dept">
<id property="deptId" column="dept_id"/>
<result property="deptName" column="dept_name"/>
</association>
</resultMap>
<select id="empAndDept" resultMap="BaseResultMap">
select emp_id, emp_name, dept.dept_name
from emp inner join dept
on emp.dept_id=dept.dept_id
</select>
- 通过thymeleaf渲染时，就可以通过emp实体类中的dept获取部门名称：
<td th:text="${emp.dept.getDeptName()}"></td>
版权声明
本文为[ShuangTwo]所创，转载请带上原文链接，感谢
https://blog.csdn.net/weixin_43356538/article/details/124349897
边栏推荐
- 转置卷积（Transposed Convolution）
- Duplicate key update in MySQL
- 多线程与高并发（3）——synchronized原理
- DWSurvey是一个开源的调查问卷系统。解决无法运行问题，修改bug。
- Strategies to improve Facebook's touch rate and interaction rate | intelligent customer service helps you grasp users' hearts
- 一文读懂当前常用的加密技术体系（对称、非对称、信息摘要、数字签名、数字证书、公钥体系）
- Shell instruction learning 1
- deep learning object detection
- Golang通过exec模块实现Ping连通性检测案例
- Pytorch deep learning practice_ 11 convolutional neural network
猜你喜欢
多线程与高并发（1）——线程的基本知识（实现，常用方法，状态）
Excel sets row and column colors according to cell contents
Common protocols of OSI layer
Establish excel bookkeeping book through setting context menu
多线程与高并发（3）——synchronized原理
mysql sql优化之Explain
Hongji micro classroom | cyclone RPA's "flexible digital employee" actuator
Transposed convolution
Interview Basics
JVM系列（3）——内存分配与回收策略
随机推荐
MySQL transaction
No.1.＃_ 6 Navicat shortcuts
手动删除eureka上已经注册的服务
Usage and difference of shellexecute, shellexecuteex and winexec in QT
Golang通过exec模块实现Ping连通性检测案例
Jiugong magic square - the 8th Lanqiao provincial competition - group C (DFS and comparison of all magic square types)
类的加载与ClassLoader的理解
PreparedStatement防止SQL注入
Dwsurvey is an open source questionnaire system. Solve the problem that cannot be run and modify the bug.
C3P0数据库连接池使用
创建线程的三种方式
基于ssm 包包商城系统
2 - software design principles
mysql实现主从复制/主从同步
DBCP使用
多线程与高并发（2）——synchronized用法详解
SQL statement simple optimization
freemark中插入图片
What financial products will benefit during May Day?
C language - Spoof shutdown applet