一套完整的基于Mabatis的多表关联分页查询脚手架

最新推荐文章于 2024-03-21 10:45:28 发布

计忆芳华

最新推荐文章于 2024-03-21 10:45:28 发布

阅读量594

点赞数 1

分类专栏：毕业设计

本文链接：https://blog.csdn.net/qq_43925089/article/details/117619595

版权

毕业设计专栏收录该内容

8 篇文章 2 订阅

订阅专栏

问题提出

在这里插入图片描述
三张表
学院表（college）,专业表（major）,证书表（certificate）
证书表外键专业表，专业表外键学院表
需要查询出

certificate.id,
certificate.certificatename,
certificate.rank,
certificate.image,
certificate.number,
certificate.create_time,
certificate.major_id,
certificate.college_id,
major.majorname,
college.collegename

问题解决

sql书写

select certificate.id,
certificate.certificatename,
certificate.rank,
certificate.image,
certificate.number,
certificate.create_time,
certificate.major_id,
certificate.college_id,
major.majorname,
college.collegename
FROM certificate
left join  major on certificate.major_id=major.id
left join college on major.college_id=college.id

mapper.xml

    <select id="getCertificateMajorCollege" resultType="com.example.api.entity.Certificate">
        select certificate.id,certificate.certificatename,certificate.rank,certificate.image,certificate.number,certificate.create_time,certificate.update_time
        ,major.majorname,college.collegename
        FROM certificate
        left join  major on certificate.major_id=major.id
        left join college on certificate.college_id=college.id
        <if test="ew.emptyOfWhere == false">
            ${ew.customSqlSegment}
        </if>
    </select>

条件查询配置

        <if test="ew.emptyOfWhere == false">
            ${ew.customSqlSegment}
        </if>

CertificateMapper.java

public interface CertificateMapper extends BaseMapper<Certificate> {
    IPage<Certificate> getCertificateMajorCollege(IPage<Certificate> page, @Param(Constants.WRAPPER) QueryWrapper<Certificate> wrapper);
}

ICertificateService

public interface ICertificateService extends IService<Certificate> {
    IPage<Certificate> getCertificateMajorCollege(IPage<Certificate> page, @Param(Constants.WRAPPER) QueryWrapper<Certificate> wrapper);
}

CertificateServiceImpl

@Service
public class CertificateServiceImpl extends ServiceImpl<CertificateMapper, Certificate> implements ICertificateService {
    @Autowired
    private CertificateMapper certificateMapper;

    @Override
    public IPage<Certificate> getCertificateMajorCollege(IPage<Certificate> page, QueryWrapper<Certificate> wrapper) {
        IPage<Certificate> list = certificateMapper.getCertificateMajorCollege(page,wrapper);
        return list;
    }
}

AdminCertificateController

@PostMapping("/getCertificatesList")
    @ApiOperation("分页获取所有专业接口")
    public ResponseEntity<JWTToken> getCertificatesList(
            @ApiParam("专业查询信息")
            @RequestBody(required = true)
                    CertificateReq certificateReq ){
        try {
            IPage<Certificate> page = new Page<>(certificateReq.getPageNo(), certificateReq.getPageSize(),true);
            QueryWrapper<Certificate> wrapperCertificate = new QueryWrapper<>();
            System.out.println(certificateReq);
            wrapperCertificate.orderByDesc("create_time");
            if(!certificateReq.getCertificatename().equals("0")||!certificateReq.getCertificatename().equals("")){
                wrapperCertificate.like("certificatename",certificateReq.getCertificatename());
            }
            if(certificateReq.getCollegeId()!=null){
                if(certificateReq.getCollegeId()!=0){
                    wrapperCertificate.eq("certificate.college_id",certificateReq.getCollegeId());
                }
            }
            if(certificateReq.getMajorId()!=null){
                if(certificateReq.getMajorId()!=0){
                    wrapperCertificate.eq("certificate.major_id",certificateReq.getMajorId());
                }
            }
            if(certificateReq.getRank()!=null){
                if(certificateReq.getRank()!=0){
                    wrapperCertificate.eq("rank",certificateReq.getRank());
                }
            }
//            page= certificateService.page(page,wrapperCertificate);
            page= certificateService.getCertificateMajorCollege(page,wrapperCertificate);
            return new ResponseEntity<>(new JWTToken(page), HttpStatus.OK);
        } catch (Exception e) {
            log.error(e.getMessage(),e);
            throw new CustomException("",7001,"获取College数据异常");
        }
    }

测试

测试数据

{
  "pageNo": 1,
  "pageSize": 2,
  "certificatename": ""
}

返回结果

{
  "code": 200,
  "msg": "success",
  "data": {
    "records": [
      {
        "id": 13,
        "certificatename": "Web前端开发-初级",
        "collegename": "计算机学院",
        "majorname": "软件工程",
        "rank": 1,
        "image": "http://localhost:3298/upload/f7074348-126d-45e5-87ed-b509bda389ed.jpg",
        "number": 2,
        "updateTime": "2021-05-11T02:36:29",
        "createTime": "2021-04-16T13:54:27",
        "collegeIdAndMajorId": []
      },
      {
        "id": 12,
        "certificatename": "网络安全评估-初级",
        "collegename": "计算机学院",
        "majorname": "网络与信息安全",
        "rank": 1,
        "image": "http://localhost:3298/upload/d945d55c-fa38-456e-8746-2901a2e79051.jpg",
        "number": 2,
        "updateTime": "2021-05-14T06:08:15",
        "createTime": "2021-04-16T13:39:52",
        "collegeIdAndMajorId": []
      }
    ],
    "total": 5,
    "size": 2,
    "current": 1,
    "orders": [],
    "optimizeCountSql": true,
    "hitCount": false,
    "searchCount": true,
    "pages": 3
  }
}