使用mybatis-plus的ew.customSqlSegment

• service

        案例1：

         案例2： 

        完整源码：

package com.zygh.investigation.control.service.control;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.date.DateField;
import cn.hutool.core.date.DateTime;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.map.MapUtil;
import cn.hutool.core.util.NumberUtil;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.github.yulichang.toolkit.JoinWrappers;
import com.github.yulichang.wrapper.MPJLambdaWrapper;
import com.zygh.investigation.control.common.constant.IcsConstants;
import com.zygh.investigation.control.common.enums.CaseCarStatusEnum;
import com.zygh.investigation.control.common.enums.CaseConutolStatusEnum;
import com.zygh.investigation.control.common.enums.CaseStatusEnum;
import com.zygh.investigation.control.common.pojo.CommonResult;
import com.zygh.investigation.control.dal.dataobject.dict.DictDataDO;
import com.zygh.investigation.control.dal.mapper.dict.DictDataMapper;
import com.zygh.investigation.control.dao.control.ControlCarMapper;
import com.zygh.investigation.control.dao.control.ControlCaseMapper;
import com.zygh.investigation.control.dao.sso.DeptMapper;
import com.zygh.investigation.control.datapermission.api.DeptDataPermissionRespDTO;
import com.zygh.investigation.control.dto.count.CaseCountAnalysisDTO;
import com.zygh.investigation.control.dto.count.CaseCountCheckpointDTO;
import com.zygh.investigation.control.dto.count.CaseCountHandleDTO;
import com.zygh.investigation.control.dto.count.CaseCountHistoryDTO;
import com.zygh.investigation.control.dto.count.CaseCountImageTypeDTO;
import com.zygh.investigation.control.dto.count.CaseCountMomDTO;
import com.zygh.investigation.control.dto.count.CaseCountStatusDTO;
import com.zygh.investigation.control.dto.count.CaseCountTeamDTO;
import com.zygh.investigation.control.dto.count.CaseCountTypeDTO;
import com.zygh.investigation.control.dto.count.TotalAndSeize;
import com.zygh.investigation.control.entity.control.ControlCar;
import com.zygh.investigation.control.entity.control.ControlCase;
import com.zygh.investigation.control.entity.control.ControlCheckpoint;
import com.zygh.investigation.control.entity.control.WarnManage;
import com.zygh.investigation.control.entity.sso.Dept;
import com.zygh.investigation.control.service.sso.PermissionService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.Comparator;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.Set;
import java.util.stream.Collectors;
import java.util.stream.IntStream;

/**
 * @Description 案件统计服务
 * @Author wpz
 * @Date 2024/4/20 10:41
 */
@Slf4j
@Service
public class CaseCountService {


    @Resource
    private ControlCaseMapper controlCaseMapper;

    @Resource
    private DictDataMapper dictDataMapper;

    @Resource
    private IControlCaseService iControlCaseService;

    @Resource
    private PermissionService permissionService;

    @Resource
    private DeptMapper deptMapper;

    @Resource
    private ControlCarMapper controlCarMapper;

    @Resource
    private IWarnManageService iWarnManageService;

    @Resource
    private CheckpointService checkpointService;


    /**
     * 各区域支队部门id
     *
     */
    private static final List<String> DEPT_CODE_LIST = Collections.unmodifiableList(Arrays.asList(
            "410100271800",
            "410100271900",
            "410100272000",
            "410100272100",
            "410100272200",
            "410100272300",
            "410100272800",
            "410100272900"
    ));


   
    /**
     * 各区域案件统计
     *
     * @param dimension 维度
     * @param type      类型
     * @return 各区域案件统计
     */
    public Map<String, Double> region(String dimension, String type) {

        DateTime startTime = null;
        //当前时间
        DateTime endTime = DateUtil.date();
        if (IcsConstants.M.equals(dimension)) {
            startTime = DateUtil.beginOfMonth(DateUtil.date());
        }
        if (IcsConstants.Y.equals(dimension)) {
            startTime = DateUtil.beginOfYear(DateUtil.date());
        }

        if (Objects.isNull(startTime)) {
            return MapUtil.newHashMap();
        }

        //案件数量
        if (IcsConstants.CASE_TOTAL.equals(type)) {
            return extracted(startTime, endTime, false);
        }

        //案件完成率
        if (IcsConstants.RATE.equals(type)) {
            Map<String, Double> totalMap = extracted(startTime, endTime, false);
            Map<String, Double> completeMap = extracted(startTime, endTime, true);
            Map<String, Double> res = MapUtil.newHashMap();

            totalMap.forEach((k, v) -> {
                if (v > 0) {
                    res.put(k, NumberUtil.round(completeMap.get(k) / v * IcsConstants.INT_100, IcsConstants.INT_2).doubleValue());
                } else {
                    res.put(k, IcsConstants.DOUBLE_0);
                }
            });

            return res;
        }

        //布控车辆数据
        if (IcsConstants.CAR_TOTAL.equals(type)) {
            Map<String, Double> res = MapUtil.newHashMap();
            for (String deptId : DEPT_CODE_LIST) {
                //获取部门id
                Set<String> deptCodeList = permissionService.queryCodeSet(deptId);
                MPJLambdaWrapper<ControlCar> wrapper = JoinWrappers.lambda(ControlCar.class);
                wrapper.between(ControlCase::getGmtCreate, startTime, endTime)
                        .eq(ControlCase::getStatus, CaseStatusEnum.PASS.getStatus())
                        .ne("t1", ControlCar::getStatus, CaseCarStatusEnum.CANCEL.getStatus())
                        .in(ControlCase::getDeptId, deptCodeList);
                long count = controlCaseMapper.countCar(wrapper);
                res.put(deptId, (double) count);
            }
            return res;
        }

        return MapUtil.newHashMap();
    }


    /**
     * 案件统计
     *
     * @param startTime 开始时间
     * @param endTime   结束时间
     * @param flag      标识
     * @return 案件统计
     */
    private Map<String, Double> extracted(DateTime startTime, DateTime endTime, boolean flag) {

        Map<String, Double> res = MapUtil.newHashMap();
        for (String deptId : DEPT_CODE_LIST) {
            //获取部门id
            Set<String> deptCodeList = permissionService.queryCodeSet(deptId);
            LambdaQueryWrapper<ControlCase> wrapper = new LambdaQueryWrapper<>();
            wrapper.between(ControlCase::getGmtCreate, startTime, endTime)
                    .in(ControlCase::getDeptId, deptCodeList)
                    .eq(ControlCase::getStatus, CaseStatusEnum.PASS.getStatus())
                    .eq(flag, ControlCase::getControlStatus, CaseConutolStatusEnum.COMPLETE.getStatus());
            long count = controlCaseMapper.countCase(wrapper);

            res.put(deptId, (double) count);
        }

        return res;
    }

 
}

• mapper

package com.zygh.investigation.control.dao.control;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.github.yulichang.base.MPJBaseMapper;
import com.zygh.investigation.control.datapermission.core.annotation.DataPermission;
import com.zygh.investigation.control.entity.control.ControlCar;
import com.zygh.investigation.control.entity.control.ControlCase;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

/**
 * <p>
 * Mapper 接口
 * </p>
 *
 * @author liubh
 * @since 2024-03-04
 */
public interface ControlCaseMapper extends MPJBaseMapper<ControlCase> {


    /**
     * 布控案件统计
     *
     * @param wrapper 构造条件
     * @return 统计
     */
    @DataPermission(enable = false)
    @Select("select count(id) from control_case  ${ew.customSqlSegment} ")
    long countCase(@Param(Constants.WRAPPER) Wrapper<ControlCase> wrapper);

    /**
     * 布控车辆统计
     *
     * @param wrapper 构造条件
     * @return 统计
     */
    @DataPermission(enable = false)
    @Select("select count(t1.id) from control_car t1 inner join control_case t on t1.case_id = t.id  ${ew.customSqlSegment} ")
    long countCar(@Param(Constants.WRAPPER) Wrapper<ControlCar> wrapper);
}

MybatisPlus_${ew.sqlSelect}，${ew.sqlSet}，${ew.sqlSegment}，${ew.customSqlSegment}的使用

说明：

• ${ew.sqlSelect}                    拼接select SQL主体
• ${ew.sqlSet}                        拼接update SQL主体
• ${ew.sqlSegment}               拼接where后的语句
• ${ew.customSqlSegment}   拼接where后的语句（包括where。需注意在动态SQL中勿处于<where></where>标签内）

Mapper接口

@Mapper
@Repository
public interface UserMapper extends BaseMapper<User> {
    List<User> queryAll(@Param("tableName") String tableName,@Param(Constants.WRAPPER) Wrapper wrapper);
    
    boolean updateById(@Param("tableName") String tableName,@Param("id") int id,@Param(Constants.WRAPPER) Wrapper wrapper);//若变量名为ew则无需注解
}

XML

    <select id="queryAll" resultType="cn.alan.mybatis.POJO.User">
        select ${ew.sqlSelect} from ${tableName} ${ew.customSqlSegment};
    </select>
 
    <update id="updateById">
        update ${tableName} set ${ew.sqlSet} ${ew.customSqlSegment};
    </update>

Controller

    @Autowired
    UserServiceImpl userService;
 
    @RequestMapping("/query")
    public List<User> queryAll(){
        QueryWrapper<User> wrapper = new QueryWrapper<>();
 
        wrapper.select("*").eq("age","10");
 
        return  userService.queryAll("user",wrapper);
 
        //return userService.queryAll("user", Wrappers.query().select("*").eq("age","10"));
    }
 
    @RequestMapping("/update")
    public boolean upDateById(){
        UpdateWrapper<User> wrapper = new UpdateWrapper<>();
 
        wrapper.set("name","5").eq("id","5");
 
        return userService.updateById("user",5,wrapper);
 
        //return userService.updateById("user",5,Wrappers.update().set("name","5").eq("id",5));
    }

注：关于wrapper的写法都是正确的，但后者需要注意使用的是静态类Wrappers

等效select SQL：select * from user where age = 10；

等效update SQL：update user set name = 5 where id = 5；