Mybatis-Plus 实现联表查询

Mybatis-Plus 实现联表查询

mapper

package com.sf.map.entity.mysql.stat;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;

import java.io.Serializable;
import java.time.LocalDateTime;

@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@TableName("t_stat_field_collect")
@ApiModel(value="StatFieldCollect对象", description="外业采集量统计报表")
public class StatFieldCollect implements Serializable {

    private static final long serialVersionUID = 1L;

    @ApiModelProperty(value = "统计日期")
    private String statDate;

    @ApiModelProperty(value = "所属采集项目ID")
    private Integer planProjectId;

    @ApiModelProperty(value = "所属采集计划ID")
    private Integer planCollectId;

    @ApiModelProperty(value = "采集员")
    private String workUser;

    @ApiModelProperty(value = "7_8点采集提交量")
    private Integer c_7_8;

    @ApiModelProperty(value = "8_9点采集提交量")
    private Integer c_8_9;

    @ApiModelProperty(value = "9_10点采集提交量")
    private Integer c_9_10;

    @ApiModelProperty(value = "10_11点采集提交量")
    private Integer c_10_11;

    @ApiModelProperty(value = "11_12点采集提交量")
    private Integer c_11_12;

    @ApiModelProperty(value = "12_13点采集提交量")
    private Integer c_12_13;

    @ApiModelProperty(value = "13_14点采集提交量")
    private Integer c_13_14;

    @ApiModelProperty(value = "14_15点采集提交量")
    private Integer c_14_15;

    @ApiModelProperty(value = "15_16点采集提交量")
    private Integer c_15_16;

    @ApiModelProperty(value = "16_17点采集提交量")
    private Integer c_16_17;

    @ApiModelProperty(value = "17_18点采集提交量")
    private Integer c_17_18;

    @ApiModelProperty(value = "18_19点采集提交量")
    private Integer c_18_19;

    @ApiModelProperty(value = "19_20点采集提交量")
    private Integer c_19_20;

    @ApiModelProperty(value = "其他时间段采集提交量")
    private Integer cOther;

    @ApiModelProperty(value = "采集提交总量")
    private Integer cSum;

    @ApiModelProperty(value = "采集返工提交总量")
    private Integer cSumRework;

    @ApiModelProperty(value = "出勤时长，分钟")
    private Integer workTime;

    @ApiModelProperty(value = "创建时间")
    private LocalDateTime createTime;

    @ApiModelProperty(value = "更新时间")
    private LocalDateTime updateTime;

    @ApiModelProperty(value = "真实姓名")
    private String realname;


    public static final String STAT_DATE = "stat_date";
    public static final String PLAN_PROJECT_ID = "plan_project_id";
    public static final String PLAN_COLLECT_ID = "plan_collect_id";
    public static final String WORK_USER = "work_user";
    public static final String C_7_8 = "c_7_8";
    public static final String C_8_9 = "c_8_9";
    public static final String C_9_10 = "c_9_10";
    public static final String C_10_11 = "c_10_11";
    public static final String C_11_12 = "c_11_12";
    public static final String C_12_13 = "c_12_13";
    public static final String C_13_14 = "c_13_14";
    public static final String C_14_15 = "c_14_15";
    public static final String C_15_16 = "c_15_16";
    public static final String C_16_17 = "c_16_17";
    public static final String C_17_18 = "c_17_18";
    public static final String C_18_19 = "c_18_19";
    public static final String C_19_20 = "c_19_20";
    public static final String C_OTHER = "c_other";
    public static final String C_SUM = "c_sum";
    public static final String C_SUM_REWORK = "c_sum_rework";
    public static final String WORK_TIME = "work_time";
    public static final String CREATE_TIME = "create_time";
    public static final String UPDATE_TIME = "update_time";
    public static final String REALNAME = "realname";
}

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="com.sf.map.mapper.mysql.stat.StatFieldCollectMapper">
    <select id="statFieldCollectInfo" parameterType="map" statementType="CALLABLE">
		{
			call sp_stat_field_collect(
				#{map.i_op_flag			, mode=IN	, jdbcType=NUMERIC},
            	#{map.i_plan_project_id	, mode=IN	, jdbcType=NUMERIC},
            	#{map.i_stat_date	    , mode=IN	, jdbcType=VARCHAR},
            	#{map.o_err_no		    , mode=OUT	, jdbcType=NUMERIC},
            	#{map.o_err_msg		    , mode=OUT	, jdbcType=VARCHAR}
          	)
        }
	</select>
	
	<select id="selectStatListPage" resultType="com.sf.map.entity.mysql.stat.StatFieldCollect">
		select a.stat_date as statDate , a.plan_project_id as planProjectId , a.plan_collect_id as planCollectId ,  a.work_user as workUser
            , a.c_7_8, a.c_8_9, a.c_9_10, a.c_10_11, a.c_11_12, a.c_12_13, a.c_13_14, a.c_14_15, a.c_15_16, a.c_16_17, a.c_17_18, a.c_18_19, a.c_19_20
            , a.c_other, a.c_sum, a.c_sum_rework,  a.work_time, a.create_time, a.update_time, b.realname
		from t_stat_field_collect a
		inner join app_user_info b on b.username = a. work_user
		<where>
			1 = 1
		</where>
	</select>
</mapper>

service

interface

package com.sf.map.service.stat;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.IService;
import com.sf.map.entity.mysql.plan.PlanProject;
import com.sf.map.entity.mysql.stat.StatFieldCollect;
import org.springframework.data.domain.PageRequest;

import java.util.List;
import java.util.Map;


public interface FieldStatCollectService extends IService<StatFieldCollect> {
    void statFieldCollectInfoByProc(Map<String, Object> map);

    Page<StatFieldCollect> selectStatListPage(int pageIndex, int step, StatFieldCollect statFieldCollect);
}

impl

package com.sf.map.service.stat.impl;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.sf.map.entity.mysql.stat.StatFieldCollect;
import com.sf.map.exception.MyException;
import com.sf.map.mapper.mysql.stat.StatFieldCollectMapper;
import com.sf.map.service.stat.FieldStatCollectService;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.annotations.Param;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;
import java.util.Map;

@Service
@Slf4j
public class FieldDailyStatServiceImpl extends ServiceImpl<StatFieldCollectMapper, StatFieldCollect> implements FieldStatCollectService {

    @Autowired(required = false)
    private StatFieldCollectMapper statFieldCollectMapper;

    @Override
    public void statFieldCollectInfoByProc(Map<String, Object> map){
        try {
            statFieldCollectMapper.statFieldCollectInfo(map);
        }catch(Exception e) {
            throw new MyException("外业采集量统计失败！msg=" + e.getMessage());
        }
    }

    @Override
    public Page<StatFieldCollect> selectStatListPage(int pageIndex, int step, StatFieldCollect statFieldCollect) {
        Page page = new Page(pageIndex, step);

        return page.setRecords(statFieldCollectMapper.selectStatListPage(page, statFieldCollect));
    }
}

Controller

package com.sf.map.controller.stat;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.sf.map.entity.mysql.stat.StatFieldCollect;
import com.sf.map.service.stat.FieldStatCollectService;
import io.swagger.annotations.Api;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import java.util.Objects;

@RestController
@RequestMapping("/Stat/FieldCollect")
@Api(tags = "外业采集量统计表")
public class StatFieldCollectController {
    @Autowired
    private FieldStatCollectService fieldStatCollectService;

    @RequestMapping(value = "/list", method = RequestMethod.GET)
    public Page findListByPage(@RequestParam(name = "pageNum", defaultValue = "1") int pageIndex,
                               @RequestParam(name = "pageSize", defaultValue = "20") int step){

        StatFieldCollect statFieldCollect = new StatFieldCollect();
        return fieldStatCollectService.selectStatListPage(pageIndex, step, statFieldCollect);

    }

	private void statFieldCollectByOpFlag(Long projectId, int opFlag){
        log.info("开始外业统计项目opflag-{},id-{}", opFlag, projectId);

        try {
            Date date = new Date();
            String nowDate = DateUtil.formatDate(date, "yyyy-MM-dd");
            // 当前日期减去2个月
            String startDate = DateUtil.formatDate(DateUtil.dateDiffer(date, "M",-2), "yyyy-MM-dd");

            Map<String, Object> map = new HashMap<String, Object>();
            map.put("i_op_flag", opFlag);
            map.put("i_plan_project_id", projectId);
            map.put("i_stat_date", nowDate);
            map.put("o_err_no", 0);
            map.put("o_err_msg", "");
            try {
                fieldStatCollectService.statFieldCollectInfoByProc(map);

                BigDecimal bigDecimal= (BigDecimal)map.get("o_err_no");
                Integer errNo = bigDecimal.intValue();
                if ( !errNo.equals(0) ){
                    String errMsg = (String)map.get("o_err_msg");
                    log.error("外业统计项目["+opFlag+"-"+projectId+"]异常["+errNo.toString()+"]"+errMsg);
                }
            } catch (Exception e) {
                log.error("外业统计项目["+opFlag+"-"+projectId+"]异常", e);
            }
            log.info("结束外业统计项目opflag-{},id-{}", opFlag, projectId);
        } catch (Exception e) {
            log.error("结束外业统计项目["+opFlag+"-"+projectId+"]异常", e);
        }
    }
}