当前位置：网站首页>建表到页面完整实例演示—联表查询
建表到页面完整实例演示—联表查询
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
边栏推荐
- QT compressed folder
- Utf8 to STD: string and STD: string to utf8
- Generation of straightening body in 3D slicer
- Deconstruction function of ES6
- MySQL realizes master-slave replication / master-slave synchronization
- Frequently asked interview questions - 3 (operating system)
- opensips（1）——安装opensips详细流程
- Mysql 查询使用\G，列转行
- Golang implements Ping connectivity detection case through exec module
- SQL语句简单优化
猜你喜欢
C, class library
OSI层常用协议
Batch import of orange single micro service
Pol / select / EPO
‘EddiesObservations‘ object has no attribute ‘filled‘
Hongji cyclone RPA provides technical support for Guojin securities and realizes process automation in more than 200 business scenarios
第36期《AtCoder Beginner Contest 248 打比赛总结》
Find the number of "blocks" in the matrix (BFS)
Establish excel bookkeeping book through setting context menu
Transposed convolution
随机推荐
Strategies to improve Facebook's touch rate and interaction rate | intelligent customer service helps you grasp users' hearts
Isosceles triangle - the 9th Lanqiao provincial competition - group C
The QT debug version runs normally and the release version runs crash
合约锁仓漏洞
Range of numbers (dichotomous classic template topic)
opensips（1）——安装opensips详细流程
STL function library
MySQL realizes master-slave replication / master-slave synchronization
50 SQL exercises, answers and detailed analysis
Getting started with JDBC \ getting a database connection \ using Preparedstatement
XXL job pit guide XXL RPC remoting error (connect timed out)
‘EddiesObservations‘ object has no attribute ‘filled‘
Differences between sea level anatomy and sea surface height anatomy
AcWing 1096. Detailed notes of Dungeon Master (3D BFS) code
Pol / select / EPO
Parameter analysis of open3d material setting
MySQL事务
Flutter nouvelle génération de rendu graphique Impeller
windows连接mysql出现ERROR 2003 (HY000): Can‘t connect to MySQL server on ‘localhost‘ (10061)
Record a project experience and technologies encountered in the project