投稿
  1. 首页
  2. java

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

code • 2022-4-27 • java • 阅读 24

建表到页面完整实例演示—联表查询,第1张

建表

设计两张表，分别是员工表和部门表，员工表有部门表的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.bootgroupId>
        <artifactId>spring-boot-starter-thymeleafartifactId>
    dependency>
    <dependency>
        <groupId>org.springframework.bootgroupId>
        <artifactId>spring-boot-starter-webartifactId>
    dependency>
    <dependency>
        <groupId>com.baomidougroupId>
        <artifactId>mybatis-plus-boot-starterartifactId>
        <version>3.5.1version>
    dependency>
    <dependency>
        <groupId>org.projectlombokgroupId>
        <artifactId>lombokartifactId>
    dependency>
    <dependency>
        <groupId>mysqlgroupId>
        <artifactId>mysql-connector-javaartifactId>
        <scope>runtimescope>
    dependency>
    <dependency>
        <groupId>org.springframework.bootgroupId>
        <artifactId>spring-boot-starter-testartifactId>
        <scope>testscope>
        <exclusions>
            <exclusion>
                <groupId>org.junit.vintagegroupId>
                <artifactId>junit-vintage-engineartifactId>
            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接口实现层 

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>


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>部门IDth>
                <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>员工IDth>
                <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>
  1. 通过thymeleaf渲染时，就可以通过emp实体类中的dept获取部门名称：

欢迎分享，转载请注明来源：内存溢出

原文地址: http://outofmemory.cn/langs/731010.html

赞 (0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
code code 管理员组
0 0
上一篇 2022-04-27
下一篇 2022-04-27

发表评论

登录后才能评论

评论列表（0条）

保存