建表到页面完整实例演示—联表查询

建表

设计两张表，分别是员工表和部门表，员工表有部门表的id字段

emp: 
+--------+----------+---------+
| emp_id | emp_name | dept_id |
+--------+----------+---------+
|      1 | 战三     |       1 |
|      2 | 李思     |       2 |
|      3 | 王武     |       1 |
|      4 | 刘二     |       3 |
+--------+----------+---------+

dept:
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|       1 | 开发部    |
|       2 | 运维部    |
|       3 | 人事部    |
+---------+-----------+

导包、配置


    
        org.springframework.boot
        spring-boot-starter-thymeleaf
    
    
        org.springframework.boot
        spring-boot-starter-web
    
    
        com.baomidou
        mybatis-plus-boot-starter
        3.5.1
    
    
        org.projectlombok
        lombok
    
    
        mysql
        mysql-connector-java
        runtime
    
    
        org.springframework.boot
        spring-boot-starter-test
        test
        
            
                org.junit.vintage
                junit-vintage-engine

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(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(value ="dept")
@Data
public class Dept implements Serializable {
    
    @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 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 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;


public interface EmpService extends IService {

    List empAndDept();
}

package com.java.web.service;

import com.java.web.domain.Dept;
import com.baomidou.mybatisplus.extension.service.IService;


public interface DeptService extends IService {

}

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;


@Service
public class EmpServiceImpl extends ServiceImpl
    implements EmpService{

    @Resource
    private EmpMapper empMapper;

    @Override
    public List 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;


@Service
public class DeptServiceImpl extends ServiceImpl
    implements DeptService{

}

mapper接口

package com.java.web.mapper;

import com.java.web.domain.Dept;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;


public interface DeptMapper extends BaseMapper {

}

package com.java.web.mapper;

import com.java.web.domain.Emp;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;

import java.util.List;


public interface EmpMapper extends BaseMapper {

    List empAndDept();

}

mapper.xml接口实现层




    
    
        
        
    
    
    
        dept_id,dept_name




    
    
        
        
        
            
            
        
    
    
    
        emp_id,emp_name,dept
    
    
    
        select emp_id, emp_name, dept.dept_name
        from emp inner join dept
        on emp.dept_id=dept.dept_id

html视图展示

总结

在emp的实体类中定义dept表，不是定义deptId属性
在empMapper.xml中自定义查询返回值类型，通过association引入dept，然后编写联表查询的SQL语句（可以在Navicat中先运行测试）

通过thymeleaf渲染时，就可以通过emp实体类中的dept获取部门名称：

建表到页面完整实例演示—联表查询

Java相关栏目本月热门文章