mybatis多表查询(两表)例子

需求:前台展示的数据需要两张表一个是巡诊记录表一个是老人信息表,但是框架本身自动生成方法都是单表查询,所以需要自己重新写.

前台代码

//请求
import axios from "axios";
export default {
    findListAllByConditions(data) {
        return axios.post('api/XXX/findListAllByConditions', data)
    },
}
//模糊查询方法
findListAllByConditions() {
  let current = this.current;
  let pageSize = this.pageSize;
  let keyword = this.keyword;
  let req = {};
  if (keyword !== undefined && keyword !== "" && keyword !== null) {
    req.keyword = keyword;
  }
  req.current = current;
  req.pageSize = pageSize;
  let that = this
  findListAllByConditions(req).then((response) => {
    this.totalSize = response.data.content.total
    let content = response.data.content.list
    let dataSet = []
    let i = 1
    content.forEach(x => {
      dataSet.push({
        index: (i++) + (that.current - 1) * that.pageSize,
        key: x.id,
		key: x.name,
      })
    })
    this.data = dataSet
    console.log(response)
  }).catch((response) => {
    console.log(response);
  })
},

VO部分就是你前台需要回填哪些参数就写哪些

package xxx.dailyDiagnosis;
import lombok.Data;
import java.io.Serializable;
/**
 * @author wym
 */
@Data
public class DailyDiagnosisVO implements Serializable {
    private Integer id;
    private String name;
}

DTO部分是你传给后端用来查询的条件参数

package xxx.dailyDiagnosis;
import lombok.Data;
import java.io.Serializable;
/**
 * @author wym
 */
@Data
public class DailyDiagnosisDTO implements Serializable {
    private String keyword;
    private Integer current;
    private Integer pageSize;
}

controller

package xxx.dailyDiagnosis;
import com.github.pagehelper.PageInfo;
import xxx.commons.entity.ResultEntity;
import xxx.dto.dailyDiagnosis.DailyDiagnosisDTO;
import xxx.service.dailyDiagnosis.DailyDiagnosisService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;

@RequestMapping("api/xxx")
@Api(tags = "xxx")
@Controller
@Slf4j
public class DailyDiagnosisController {
    @Autowired
    private DailyDiagnosisService dailyDiagnosisService;

    @ApiOperation("自定义查询列表(老人信息表+日常巡视表)")
    @RequestMapping(value = "findListAllByConditions", method = RequestMethod.POST)
    @ResponseBody
    public ResultEntity findListAllByConditions(@RequestBody DailyDiagnosisDTO searchDTO) {
        PageInfo result = dailyDiagnosisService.findListAllByConditions(searchDTO);
        return ResultEntity.findOk(result);
    }
}

service实现类

package xxx.service.impl.dailyDiagnosis;
import cn.hutool.core.util.ObjectUtil;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import xxx.constant.BaseConstant;
import xxx.dto.dailyDiagnosis.DailyDiagnosisDTO;
import xxx.entity.dailyDiagnosis.DailyDiagnosisEntity;
import xxx.mapper.dailyDiagnosis.DailyDiagnosisMapper;
import xxx.service.dailyDiagnosis.DailyDiagnosisService;
import java.util.List;
import xxx.vo.dailyDiagnosis.DailyDiagnosisVO;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
@Slf4j
public class DailyDiagnosisServiceImpl extends ServiceImpl<DailyDiagnosisMapper, DailyDiagnosisEntity> implements DailyDiagnosisService {
    @Autowired
    private DailyDiagnosisMapper dailyDiagnosisMapper;

    @Override
    public PageInfo findListAllByConditions(DailyDiagnosisDTO searchDTO) {
        int currentPage = ObjectUtil.isNotEmpty(searchDTO.getCurrent()) ? searchDTO.getCurrent() : BaseConstant.DEFAULT_PAGE_INDEX;
        int pageSize = ObjectUtil.isNotEmpty(searchDTO.getPageSize()) ? searchDTO.getPageSize() : BaseConstant.MAX_PAGE_SIZE;
        PageHelper.startPage(currentPage, pageSize);
        List<DailyDiagnosisVO> dailyDiagnosisList = dailyDiagnosisMapper.findDailyDiagnosisList(searchDTO);
        PageInfo pageInfo = new PageInfo<>(dailyDiagnosisList);
        return pageInfo;
    }
}

mapper

package xxx.mapper.dailyDiagnosis;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import xxx.dto.dailyDiagnosis.DailyDiagnosisDTO;
import xxx.entity.dailyDiagnosis.DailyDiagnosisEntity;
import xxx.vo.dailyDiagnosis.DailyDiagnosisVO;
import java.util.List;

public interface DailyDiagnosisMapper extends BaseMapper<DailyDiagnosisEntity> {
    List<DailyDiagnosisVO> findDailyDiagnosisList(DailyDiagnosisDTO searchDTO);
}

mappers 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="xxx.mapper.dailyDiagnosis.DailyDiagnosisMapper">
    <cache/>
    <resultMap id="BaseResultMap" type="xxx.entity.dailyDiagnosis.DailyDiagnosisEntity">
        <id column="id" jdbcType="INTEGER" property="id"/>
        <result column="oid" jdbcType="INTEGER" property="oid"/>
        <result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>
        <result column="update_time" jdbcType="TIMESTAMP" property="updateTime"/>
    </resultMap>

    <select id="findDailyDiagnosisList" resultType="xxx.vo.dailyDiagnosis.DailyDiagnosisVO">
        select a.id, b.name
        from b_daily_diagnosis as a, b_old_info as b
        where a.is_deleted =0 and a.oid = b.id
        <if test="keyword != null and keyword !='' ">
            AND ((a.doctor_name LIKE CONCAT(CONCAT('%',#{keyword},'%')))
            OR (b.name LIKE CONCAT(CONCAT('%',#{keyword},'%')))
        </if>
        ORDER BY a.create_time DESC
    </select>
</mapper>

思路就是这样,代码部分稍微省略了一点点,之前我看百度有的文章说应该把两个实体类合并成一个新的实体类作为resultType的返回类型,但是使用VO更简单,只需要关注前端需要哪些参数就行.(新手理解)