当前位置：网站首页>Complete example demonstration of creating table to page - joint table query
Complete example demonstration of creating table to page - joint table query
2022-04-23 05:57:00 【ShuangTwo】
Build table
Design two tables , They are employee table and department table , Employee table has department table id Field
emp:
+--------+----------+---------+
| emp_id | emp_name | dept_id |
+--------+----------+---------+
| 1 | War three | 1 |
| 2 | Lee thought | 2 |
| 3 | Wang Wu | 1 |
| 4 | Liu er | 3 |
+--------+----------+---------+
dept:
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 1 | Development Department |
| 2 | Operation and maintenance department |
| 3 | The personnel department |
+---------+-----------+
Guide pack 、 To configure
<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
Entity class
a key ：private Dept dept;, stay emp Entity class introduction dept attribute
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 {
/** * Number */
@TableId(type = IdType.AUTO)
private Integer empId;
/** * Employee name */
private String empName;
/** * Department */
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 {
/** * department id */
@TableId(type = IdType.AUTO)
private Integer deptId;
/** * Department name */
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 Control layer
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 layer
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 For table 【emp】 Database operation 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 For table 【dept】 Database operation Service * @createDate 2022-04-22 15:00:53 */
public interface DeptService extends IService<Dept> {
}
service.Impl Implementation layer
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 For table 【emp】 Database operation Service Realization * @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 For table 【dept】 Database operation Service Realization * @createDate 2022-04-22 15:00:53 */
@Service
public class DeptServiceImpl extends ServiceImpl<DeptMapper, Dept>
implements DeptService{
}
mapper Interface
package com.java.web.mapper;
import com.java.web.domain.Dept;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
/** * @author H * @description For table 【dept】 Database operation 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 For table 【emp】 Database operation 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 Interface implementation layer
<?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 View display
<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
<meta charset="UTF-8">
<title> Departmental table </title>
</head>
<body>
<table border="1">
<tr>
<th> department ID</th>
<th> Department name </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> The employee table </title>
</head>
<body>
<table border="1">
<tr>
<th> staff ID</th>
<th> Employee name </th>
<th> Department </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>
summary
- stay emp Defined in the entity class of dept surface , It's not a definition deptId attribute
- stay empMapper.xml Custom query return value type in , adopt association introduce dept, Then write the of join table query SQL sentence （ Can be in Navicat Run the test first ）
<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>
- adopt thymeleaf When rendering , You can go through emp In entity class dept Get department name ：
<td th:text="${emp.dept.getDeptName()}"></td>
版权声明
本文为[ShuangTwo]所创，转载请带上原文链接，感谢
https://yzsam.com/2022/04/202204230541367631.html
边栏推荐
- 在Jupyter notebook中用matplotlib.pyplot出现服务器挂掉、崩溃的问题
- Excel obtains the difference data of two columns of data
- Pytorch learning record (7): skills in processing data and training models
- Fundamentals of digital image processing (Gonzalez) I
- 去噪论文——[Noise2Void,CVPR19]Noise2Void-Learning Denoising from Single Noisy Images
- PyTorch笔记——观察DataLoader＆用torch构建LeNet处理CIFAR-10完整代码
- SQL基础：初识数据库与SQL-安装与基本介绍等—阿里云天池
- 框架解析2.源码-登录认证
- Pytorch学习记录（十一）：数据增强、torchvision.transforms各函数讲解
- Pytorch——数据加载和处理
猜你喜欢
建表到页面完整实例演示—联表查询
PyQy5学习（二）：QMainWindow+QWidget+QLabel
给yarn配置国内镜像加速器
Ora: 28547 connection to server failed probable Oracle net admin error
Get the value of state in effects in DVA
Pytorch learning record (III): structure of neural network + using sequential and module to define the model
mysql sql优化之Explain
Navicate连接oracle(11g)时ORA:28547 Connection to server failed probable Oeacle Net admin error
深度学习基础——简单了解meta learning(来自李宏毅课程笔记)
Pytorch学习记录（十）：数据预处理+Batch Normalization批处理（BN）
随机推荐
框架解析2.源码-登录认证
The attendance client date of K / 3 wise system can only be selected to 2019
Get the value of state in effects in DVA
Manually delete registered services on Eureka
Solution record of slow access speed of SMB service in redhat6
一文读懂当前常用的加密技术体系（对称、非对称、信息摘要、数字签名、数字证书、公钥体系）
Pytoch learning record (x): data preprocessing + batch normalization (BN)
io. lettuce. core. RedisCommandExecutionException: ERR wrong number of arguments for ‘auth‘ command
域内用户访问域外samba服务器用户名密码错误
Understand the current commonly used encryption technology system (symmetric, asymmetric, information abstract, digital signature, digital certificate, public key system)
深入源码分析Servlet第一个程序
EditorConfig
interviewter：介绍一下MySQL日期函数
ValueError: loaded state dict contains a parameter group that doesn‘t match the size of optimizer‘s
Pytorch——数据加载和处理
RedHat realizes keyword search in specific text types under the directory and keyword search under VIM mode
框架解析1.系统架构简介
数字图像处理基础（冈萨雷斯）一
The difference between cookie and session
编写一个自己的 RedisTemplate