思路:有3个表 bed(床位表),layer(楼层表),floor(楼宇表), 输入一个床位id,查询出对应的楼层和楼宇的名称

1.建立bed床位表

注意:这里有个layer_id 和floor_id 他们代表楼层的id 和楼宇的id

2.建立楼层和楼宇数据

    2.1layer楼层表数据

    2.2floor楼宇表

3.数据库准备好后,准备对应的java代码

3.1准备bed表的pojo类

package com.yanglao.orders.entity;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import java.time.LocalDateTime;
import com.baomidou.mybatisplus.annotation.TableField;
import java.io.Serializable;
import lombok.Data;
import lombok.EqualsAndHashCode;

/**
 * <p>
 * 
 * </p>
 *
 * @author 施正
 * @since 2021-04-12
 */
@Data
@EqualsAndHashCode(callSuper = false)
public class Bed implements Serializable {

    private static final long serialVersionUID = 1L;

      @TableId(value = "id", type = IdType.AUTO)
    private Integer id;

    @TableField("regionBranch_id")
    private Integer regionbranchId;

    private Integer consultantId;

    private String description;

    private Boolean status;

    @TableField("createTime")
    private LocalDateTime createTime;

    @TableField("updateTime")
    private LocalDateTime updateTime;

    private String name;

    private Integer floorId;

    private Integer layerId;


}

3.2准备mapper映射类 BedLayerFloorMapper,接收一个床位id,注意他是一个接口,还要加上@Mapper注解

package com.yanglao.orders.mapper;

import com.yanglao.orders.entity.BedLayerFloor;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

@Mapper
public interface BedLayerFloorMapper {
    @Select("select * FROM bed  where b.id=#{id}")
    public BedLayerFloor getBedLayerFloor(Long bedid);
}

注意,这里为了调试方便,我刚开始只做了单表的查询

3.3 准备service层接口,注意这里需要增加@Service注解,定义了一个方法

package com.yanglao.orders.service;


import com.yanglao.orders.entity.BedLayerFloor;
import org.springframework.stereotype.Service;

@Service
public interface BedLayerFloorService {
    // 多表查询,获取某个床位的信息和他的楼层楼宇名称
    public BedLayerFloor getBedLayerFloor(Long id);
}

3.4 准备一个BedLayerFloorService的实现类,取名 BedLayerFloorImpl

package com.yanglao.orders.service.impl;

import com.yanglao.orders.entity.BedLayerFloor;
import com.yanglao.orders.mapper.BedLayerFloorMapper;
import com.yanglao.orders.service.BedLayerFloorService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class BedLayerFloorImpl implements BedLayerFloorService {
    @Autowired
    BedLayerFloorMapper bedLayerFloorMapper;
    @Override
    public BedLayerFloor getBedLayerFloor(Long id) {
        return bedLayerFloorMapper.getBedLayerFloor(id);
    }
}

注意,这里的@Service必须加上,我因为忘记增加这个注解,耽误了半个小时的时间

这里把mapper注入进来,直接返回mapper的getBedLayerFloor方法即可, 一会我们要在 这个方法上写多表联合查询的SQL语句

3.5做个controller ,

package com.yanglao.orders.controller;


import com.yanglao.orders.entity.BedLayerFloor;
import com.yanglao.orders.service.BedLayerFloorService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.ResponseBody;

@Controller
public class BedLayerFloorController {
   @Autowired
   BedLayerFloorService bedLayerFloorService;
    @GetMapping("/getBedLayerFloor")
    @ResponseBody
    public BedLayerFloor getBedLayerFloor(Long bedid){
        return bedLayerFloorService.getBedLayerFloor(bedid);
    }
}

把service曾的接口注入进来,调用实现类的方法,这个方法又调用mapper的方法

启动springboot,ok一切正常

这时我们修改mapper的SQL语句如下

package com.yanglao.orders.mapper;

import com.yanglao.orders.entity.BedLayerFloor;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

@Mapper
public interface BedLayerFloorMapper {
    @Select("select b.id,b.regionBranch_id,b.consultant_id,b.description,b.status,b.createTime,b.updateTime,b.name,b.floor_id,b.layer_id,l.name as layerName,f.name as floorName FROM bed as b left join layer as l on b.layer_id=l.id left join floor as f on b.floor_id=f.id where b.id=#{id}")
    public BedLayerFloor getBedLayerFloor(Long bedid);
}

把他修改为多表联合查询

重启,应该也正常了...

这时还有个问题,原先的pojo类bed.java,是床位表的,没有楼层和楼梯与的名称属性

这时候需要增加个多表联合查询后的pojo类 BedLayerFloor.java存放查询结果

package com.yanglao.orders.entity;


import lombok.Data;
import lombok.EqualsAndHashCode;

@Data
@EqualsAndHashCode(callSuper = false)
public class BedLayerFloor extends Bed{
    private String layerName;
    private String floorName;
}

这个表直接继承bed表,增加了楼层和楼宇的名称,以后返回这个类即可