[Java知识库]建表到页面完整实例演示—联表查询

建表

设计两张表，分别是员工表和部门表，员工表有部门表的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>

总结

1. 在emp的实体类中定义dept表，不是定义deptId属性
2. 在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>

3. 通过thymeleaf渲染时，就可以通过emp实体类中的dept获取部门名称：<td th:text="${emp.dept.getDeptName()}"></td>

阅读世界，共赴山海

423全民读书节，邀你共读