若依 springboot mybatis-plus mysql 两表联级查询

碳酸水123

已于 2022-10-26 11:38:11 修改

阅读量2.6k

点赞数

文章标签： mysql mybatis spring boot

于 2022-07-27 21:58:45 首次发布

本文链接：https://blog.youkuaiyun.com/weixin_43937851/article/details/126021717

版权

两表联级查询

一、案例描述

数据库中有仪器设备表data_apparatus和仪器设备检校表data_apparatus_check_scheme。
实现联级查询，在获取检校表全部数据的基础上，获取设备表中的某些属性数据，一起传给前端。

二、实现步骤

1.创建vo类

在原有检校表实体类DataApparatusCheckScheme的基础上，创建新的实体类DataApparatusCheckSchemeVo，该实体类中加入了需要查询的设备表的相应属性。

注意：
当前端需要的数据类型与数据库中存储的数据类型不符时，也可以用创建vo类的方法来解决。比如数据库存储的时间数据是Date日期类型的，而前端需要的是String类型的数据，就可以在vo中把时间数据写成String类型，并生成对应的String类型get、set方法。

DataApparatusCheckSchemeVo：

package com.ruoyi.system.domain.vo;

import com.fasterxml.jackson.annotation.JsonFormat;
import com.ruoyi.common.annotation.Excel;
import com.ruoyi.system.domain.DataApparatus;
import org.apache.commons.lang3.builder.ToStringBuilder;
import org.apache.commons.lang3.builder.ToStringStyle;

import java.util.Date;

public class DataApparatusCheckSchemeVo {
    private static final long serialVersionUID = 1L;

    /** 主键 */
    private Long id;

    /** 仪器id */
    @Excel(name = "仪器id")
    private Long apparatusId;

    /** 检/校参量 */
    @Excel(name = "检/校参量")
    private String checkParameter;

    /** 检/校范围 */
    @Excel(name = "检/校范围")
    private String checkRange;

    /** 检/校依据 */
    @Excel(name = "检/校依据")
    private String checkBasis;

    /** 检定单位 */
    @Excel(name = "检定单位")
    private String checkUnit;

    /** 备注 */
    @Excel(name = "备注")
    private String rmk;
/**------------------将数据库中日期型的年份数据，改为前端需要的String类型------------------------------------- */
    /** 年份 */
    @JsonFormat(pattern = "yyyy-MM-dd")
    @Excel(name = "年份", width = 30, dateFormat = "yyyy")
    private String checkYearVo;

    /** 检校日期 */
    @JsonFormat(pattern = "yyyy-MM-dd")
    private Date checkYear;

    /** 检/校结果 (字典）*/
    @Excel(name = "检/校结果")
    private String checkResult;
/**------------------添加需要查询的设备表数据------------------------------------- */
    /** 仪器设备名称 */
    @Excel(name = "仪器设备名称")
    private String apparatusName;

    /** 实验室编号(字典） */
    @Excel(name = "实验室编号(字典）")
    private String labId;

    /** 仪器设备型号 */
    @Excel(name = "仪器设备型号")
    private String apparatusModel;

    /** 出厂编号 */
    @Excel(name = "出厂编号")
    private String factoryNum;


    /** 使用部门(字典） */
    @Excel(name = "使用部门(字典）")
    private String userDepartment;

    /** 检/校周期 */
    @Excel(name = "检/校周期")
    private String checkCycle;

    /** 有效期 */
    @Excel(name = "有效期", width = 30, dateFormat = "yyyy-MM-dd")
    private Date validDate;

    public void setId(Long id)
    {
        this.id = id;
    }

    public Long getId()
    {
        return id;
    }
    public void setApparatusId(Long apparatusId)
    {
        this.apparatusId = apparatusId;
    }

    public Long getApparatusId()
    {
        return apparatusId;
    }
    public void setCheckParameter(String checkParameter)
    {
        this.checkParameter = checkParameter;
    }

    public String getCheckParameter()
    {
        return checkParameter;
    }
    public void setCheckRange(String checkRange)
    {
        this.checkRange = checkRange;
    }

    public String getCheckRange()
    {
        return checkRange;
    }
    public void setCheckBasis(String checkBasis)
    {
        this.checkBasis = checkBasis;
    }

    public String getCheckBasis()
    {
        return checkBasis;
    }
    public void setCheckUnit(String checkUnit)
    {
        this.checkUnit = checkUnit;
    }

    public String getCheckUnit()
    {
        return checkUnit;
    }
    public void setRmk(String rmk)
    {
        this.rmk = rmk;
    }

    public String getRmk()
    {
        return rmk;
    }
    public void setCheckYearVo(String checkYearVo)
    {
        this.checkYearVo = checkYearVo;
    }

    public String getCheckYearVo()
    {
        return checkYearVo;
    }
    public void setCheckResult(String checkResult)
    {
        this.checkResult = checkResult;
    }

    public String getCheckResult()
    {
        return checkResult;
    }

    public void setApparatusName(String apparatusName)
    {
        this.apparatusName = apparatusName;
    }

    public String getApparatusName()
    {
        return apparatusName;
    }

    public Date getCheckYear() {
        return checkYear;
    }

    public void setCheckYear(Date checkYear) {this.checkYear = checkYear;}


    public Date getValidDate() {
        return validDate;
    }

    public String getLabId() {
        return labId;
    }

    public void setLabId(String labId) {
        this.labId = labId;
    }

    public String getApparatusModel() {
        return apparatusModel;
    }

    public void setApparatusModel(String apparatusModel) {
        this.apparatusModel = apparatusModel;
    }

    public String getFactoryNum() {
        return factoryNum;
    }

    public void setFactoryNum(String factoryNum) {
        this.factoryNum = factoryNum;
    }

    public String getUserDepartment() {
        return userDepartment;
    }

    public void setUserDepartment(String userDepartment) {
        this.userDepartment = userDepartment;
    }

    public String getCheckCycle() {
        return checkCycle;
    }

    public void setCheckCycle(String checkCycle) {
        this.checkCycle = checkCycle;
    }
    public void setValidDate(Date validDate) {this.validDate = validDate;}
    @Override
    public String toString() {
        return new ToStringBuilder(this, ToStringStyle.MULTI_LINE_STYLE)
                .append("id", getId())
                .append("apparatusId", getApparatusId())
                .append("checkParameter", getCheckParameter())
                .append("checkRange", getCheckRange())
                .append("checkBasis", getCheckBasis())
                .append("checkUnit", getCheckUnit())
                .append("rmk", getRmk())
                .append("checkYearVo", getCheckYearVo())
                .append("checkResult", getCheckResult())
                .append("apparatusName", getApparatusName())
                .append("checkYear", getCheckYear())
                .append("validDate", getValidDate())
                .append("labId",getLabId())
                .append("apparatusModel",getApparatusModel())
                .append("factoryNum",getFactoryNum())
                .append("userDepartment",getUserDepartment())
                .append("checkCycle",getCheckCycle())
                .toString();
    }
}

2.修改Mapper.xml文件中的查询方法

DataApparatusCheckSchemeMapper.xml：

 <resultMap type="DataApparatusCheckSchemeVo" id="DataApparatusCheckSchemeVoResult">
        <result property="id"    column="id"    />
        <result property="apparatusId"    column="apparatus_id"    />
        <result property="checkParameter"    column="check_parameter"    />
        <result property="checkRange"    column="check_range"    />
        <result property="checkBasis"    column="check_basis"    />
        <result property="checkUnit"    column="check_unit"    />
        <result property="rmk"    column="rmk"    />
        <result property="checkYearVo"    column="check_year1"    />
        <result property="checkResult"    column="check_result"    />
        <result property="apparatusName"    column="apparatus_name"    />
        <result property="checkYear"    column="check_year"    />
        <result property="validDate"    column="valid_date"    />
        <result property="labId"    column="lab_id"    />
        <result property="apparatusModel"    column="apparatus_model"    />
        <result property="factoryNum"    column="factory_num"    />
        <result property="userDepartment"    column="user_department"    />
        <result property="checkCycle"    column="check_cycle"    />
    </resultMap>

    <sql id="selectDataApparatusCheckSchemeVo">
        select data_apparatus_check_scheme.id, apparatus_id, check_parameter, check_range, check_basis,
               check_unit, data_apparatus_check_scheme.rmk, check_year,DATE_FORMAT(check_year,'%Y') as check_year1,
               data_apparatus_check_scheme.check_result,apparatus_name,is_need_check,valid_date,lab_id,apparatus_model,
               factory_num,user_department,check_cycle
        from data_apparatus_check_scheme LEFT JOIN data_apparatus ON data_apparatus_check_scheme.apparatus_id = data_apparatus.id

    </sql>

3.修改其余文件

DataApparatusCheckSchemeMapper.java

    /**
     * 查询仪器校准方案列表
     * 
     * @param dataApparatusCheckSchemeVo 仪器校准方案
     * @return 仪器校准方案集合
     */
    /*public List<DataApparatusCheckScheme> selectDataApparatusCheckSchemeList(DataApparatusCheckScheme dataApparatusCheckScheme);*/
    public List<DataApparatusCheckSchemeVo> selectDataApparatusCheckSchemeList(DataApparatusCheckSchemeVo dataApparatusCheckSchemeVo);

IDataApparatusCheckSchemeService.java

 /**
     * 查询仪器校准方案列表
     * 
     * @param dataApparatusCheckSchemeVo 仪器校准方案
     * @return 仪器校准方案集合
     */
    public List<DataApparatusCheckSchemeVo> selectDataApparatusCheckSchemeList(DataApparatusCheckSchemeVo dataApparatusCheckSchemeVo);

DataApparatusCheckSchemeServiceImpl.java

 /**
     * 查询仪器校准方案列表
     * 
     * @param dataApparatusCheckSchemeVo 仪器校准方案
     * @return 仪器校准方案
     */
    @Override
    public List<DataApparatusCheckSchemeVo> selectDataApparatusCheckSchemeList(DataApparatusCheckSchemeVo dataApparatusCheckSchemeVo)
    {
        return dataApparatusCheckSchemeMapper.selectDataApparatusCheckSchemeList(dataApparatusCheckSchemeVo);
    }

DataApparatusCheckSchemeController.java:

package com.ruoyi.web.controller.system;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.ruoyi.common.config.RuoYiConfig;
import com.ruoyi.common.core.domain.entity.SysDictData;
import com.ruoyi.common.utils.file.FileUtils;
import com.ruoyi.common.utils.file.WordUtil;
import com.ruoyi.system.domain.DataApparatus;
import com.ruoyi.system.domain.TempEntity.TempDataApparatus;
import com.ruoyi.system.domain.TempEntity.TempDataApparatusCheckScheme;
import com.ruoyi.system.domain.vo.DataApparatusCheckSchemeVo;
import com.ruoyi.system.service.ISysDictDataService;
import com.ruoyi.system.service.impl.DataApparatusCheckSchemeServiceImpl;
import org.springframework.security.access.prepost.PreAuthorize;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.PutMapping;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import com.ruoyi.common.annotation.Log;
import com.ruoyi.common.core.controller.BaseController;
import com.ruoyi.common.core.domain.AjaxResult;
import com.ruoyi.common.enums.BusinessType;
import com.ruoyi.system.domain.DataApparatusCheckScheme;
import com.ruoyi.system.service.IDataApparatusCheckSchemeService;
import com.ruoyi.common.utils.poi.ExcelUtil;
import com.ruoyi.common.core.page.TableDataInfo;

import static org.apache.naming.SelectorContext.prefix;

/**
 * 仪器校准方案Controller
 * 
 * @author 1116
 * @date 2022-06-21
 */
@RestController
@RequestMapping("/system/scheme")
public class DataApparatusCheckSchemeController extends BaseController
{
    @Autowired
    private IDataApparatusCheckSchemeService dataApparatusCheckSchemeService;
    @Autowired
    private ISysDictDataService dictDataService;

    /**
     * 查询仪器校准方案列表
     */
    @PreAuthorize("@ss.hasPermi('system:scheme:list')")
    @GetMapping("/list")
    public TableDataInfo list(DataApparatusCheckSchemeVo dataApparatusCheckSchemeVo)
    {
        startPage();
        List<DataApparatusCheckSchemeVo> list = dataApparatusCheckSchemeService.selectDataApparatusCheckSchemeList(dataApparatusCheckSchemeVo);
        return getDataTable(list);
    }
}

scheme.js:

import request from '@/utils/request'

// 查询仪器校准方案列表
export function listScheme(query) {
  return request({
    url: '/system/scheme/list',
    method: 'get',
    params: query
  })
}

总结

Vo类存储需要查询的数据，然后在mapper.xml增加相应的resultmap，修改sql语句。然后修改对应的mapper.java，service，serviceImpl，Controller文件，最后修改前端js文件。