点位导入优化

xiangpei

2024-08-22 60c2c06c064008586dece2762fcaeb90b8d739be

点位导入优化

 ycl-pojo/src/main/java/com/ycl/platform/domain/excel/PointExport.java

@@ -4,6 +4,7 @@
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentStyle;
import lombok.Data;

import java.util.Date;
@@ -18,27 +19,28 @@
public class PointExport {

    @ColumnWidth(50)
    @ExcelProperty("点位名称")
    @ExcelProperty("点位名称(可修改)")

    private String pointName;

    @ColumnWidth(30)
    @ExcelProperty("国标码")
    @ExcelProperty("国标码(不可修改)")
    private String serialNumber;

    @ColumnWidth(16)
    @ExcelProperty("点位IP")
    @ColumnWidth(30)
    @ExcelProperty("点位IP(可修改)")
    private String pointIP;

    @ColumnWidth(40)
    @ExcelProperty("当前运维单位")
    @ExcelProperty("当前运维单位(导入界面可改)")
    private String unitName;

    @ColumnWidth(16)
    @ColumnWidth(40)
    @DateTimeFormat("yyyy-MM-dd")
    @ExcelProperty("运维开始时间")
    private Date startTime;

    @ColumnWidth(16)
    @ColumnWidth(40)
    @DateTimeFormat("yyyy-MM-dd")
    @ExcelProperty("运维结束时间")
    private Date endTime;
@@ -46,19 +48,20 @@
    @ExcelIgnore
    private Boolean provinceTag;

    @ExcelProperty("是否省厅标签")
    @ColumnWidth(20)
    @ExcelProperty("是否省厅标签(可修改)")
    private String provinceTagString;

    @ExcelIgnore
    private Boolean importantTag;

    @ExcelProperty("是否重点点位")
    private String importantTagString;
//    @ExcelProperty("是否重点点位")
//    private String importantTagString;

    @ExcelIgnore
    private Boolean importantCommandImageTag;

    @ColumnWidth(16)
    @ExcelProperty("是否重点指挥图像")
    @ColumnWidth(20)
    @ExcelProperty("是否重点指挥图像(可修改)")
    private String importantCommandImageTagString;
}

 ycl-server/src/main/java/com/ycl/platform/controller/YwPointController.java

@@ -7,10 +7,13 @@
import jakarta.validation.constraints.NotBlank;
import org.springframework.format.annotation.DateTimeFormat;
import org.springframework.security.access.prepost.PreAuthorize;
import org.springframework.util.StringUtils;
import org.springframework.validation.annotation.Validated;
import lombok.RequiredArgsConstructor;

import java.io.IOException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import org.springframework.validation.annotation.Validated;
@@ -115,15 +118,26 @@
        ywPointService.export(query, response);
    }

    @PostMapping("/import/{unitId}")
    @PostMapping("/import")
    @ApiOperation(value = "导入数据", notes = "导入数据")
    public Result importData(MultipartFile file,
                             @PathVariable("unitId") Integer unitId,
                             @DateTimeFormat(pattern = "yyyy-MM-dd") Date startTime,
                             @DateTimeFormat(pattern = "yyyy-MM-dd") Date endTime,
                             Boolean provinceTag,
                             Boolean importantCommandImageTag) throws IOException {
        return ywPointService.importData(file, unitId, startTime, endTime, provinceTag, importantCommandImageTag);
                             Integer unitId,
                             String startTime,
                             String endTime) throws IOException, ParseException {
        Date start = null;
        Date end = null;
        if (-1 == unitId) {
            unitId = null;
        }
        if (StringUtils.hasText(startTime)) {
            SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
            start = format.parse(startTime);
        }
        if (StringUtils.hasText(startTime)) {
            SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
            end = format.parse(endTime);
        }
        return ywPointService.importData(file, unitId, start, end);
    }

}

 ycl-server/src/main/java/com/ycl/platform/mapper/YwPointMapper.java

@@ -51,4 +51,11 @@
    List<PointExport> export(@Param("query") YwPointQuery query);

    void deleteAll();

    /**
     * 更新点位信息
     *
     * @param point
     */
    int updatePoint(@Param("point") YwPoint point);
}

 ycl-server/src/main/java/com/ycl/platform/service/YwPointService.java

@@ -121,5 +121,5 @@
     * @param unitId 运维单位id
     * @return
     */
    Result importData(MultipartFile file, Integer unitId, Date startTime, Date endTime, Boolean provinceTag, Boolean importantCommandImageTag) throws IOException;
    Result importData(MultipartFile file, Integer unitId, Date startTime, Date endTime) throws IOException;
}

 ycl-server/src/main/java/com/ycl/platform/service/impl/YwPointServiceImpl.java

@@ -23,11 +23,13 @@
import com.ycl.system.Result;
import com.ycl.system.mapper.SysDeptMapper;
import com.ycl.system.page.PageUtil;
import com.ycl.thread.PointImportCallable;
import com.ycl.utils.DateUtils;
import com.ycl.utils.SecurityUtils;
import jakarta.servlet.http.HttpServletResponse;
import lombok.RequiredArgsConstructor;
import org.springframework.beans.BeanUtils;
import org.springframework.scheduling.annotation.Async;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.Assert;
@@ -36,9 +38,8 @@
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.util.Date;
import java.util.List;
import java.util.Objects;
import java.util.*;
import java.util.concurrent.*;
import java.util.function.Consumer;
import java.util.stream.Collectors;

@@ -55,6 +56,14 @@
    private final SysDeptMapper sysDeptMapper;
    private final YwUnitService unitService;
    private final YwPeopleMapper ywPeopleMapper;

    private static final ExecutorService executorService = new ThreadPoolExecutor(8,
            24,
            5000,
            TimeUnit.SECONDS,
            new ArrayBlockingQueue<>(10),
            new ThreadPoolExecutor.CallerRunsPolicy()
    );

    /**
     * 添加
@@ -238,16 +247,21 @@
    public void export(YwPointQuery query, HttpServletResponse response) throws IOException {
        // 导出数据
        List<PointExport> exportData = baseMapper.export(query);

        EasyExcel.write(response.getOutputStream(), PointExport.class)
                .sheet("点位更换运维单位")
                .doWrite(exportData);
    }

    @Override
    public Result importData(MultipartFile file, Integer unitId, Date startTime, Date endTime, Boolean provinceTag, Boolean importantCommandImageTag) throws IOException {
    public Result importData(MultipartFile file, Integer unitId, Date startTime, Date endTime) throws IOException {
        Consumer<List<PointExport>> consumer = (dataList) -> {
            this.updatePoint(dataList, unitId, startTime, endTime, provinceTag, importantCommandImageTag);
            try {
                this.updatePoint(dataList, unitId, startTime, endTime);
            } catch (ExecutionException e) {
                e.printStackTrace();
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
        };
        EasyExcel.read(file.getInputStream(), PointExport.class , new CurrencyDataListener(consumer)).headRowNumber(1).doReadAll();
        return Result.ok();
@@ -259,19 +273,72 @@
     * @param dataList
     * @param unitId
     */
    private void updatePoint(List<PointExport> dataList, Integer unitId, Date startTime, Date endTime, Boolean provinceTag, Boolean importantCommandImageTag) {
    @Transactional(rollbackFor = Exception.class)
    public void updatePoint(List<PointExport> dataList, Integer unitId, Date startTime, Date endTime) throws ExecutionException, InterruptedException {
        if (CollectionUtils.isEmpty(dataList)) {
            throw new RuntimeException("导入数据不能为空");
        }
        List<String> pointList = dataList.stream().map(PointExport::getSerialNumber).collect(Collectors.toList());
        new LambdaUpdateChainWrapper<>(baseMapper)
                .in(YwPoint::getSerialNumber, pointList)
                .set(YwPoint::getUnitId, unitId)
                .set(YwPoint::getStartTime, startTime)
                .set(YwPoint::getEndTime, endTime)
                .set(YwPoint::getProvinceTag, provinceTag)
                .set(YwPoint::getImportantCommandImageTag, importantCommandImageTag)
                .update();
        List<YwPoint> pointList = dataList.stream().map(item -> {
            YwPoint point = new YwPoint();
            point.setImportantCommandImageTag("是".equals(item.getImportantCommandImageTagString()));
            point.setProvinceTag("是".equals(item.getProvinceTagString()));
            if (Objects.nonNull(unitId)) {
                point.setUnitId(Long.valueOf(unitId));
            }
            point.setStartTime(startTime);
            point.setEndTime(endTime);
            point.setPointName(item.getPointName());
            point.setSerialNumber(item.getSerialNumber());
            return point;
        }).collect(Collectors.toList());
        this.waitAllFinishAndGetResult(pointList);
//        for (PointExport pointExport : dataList) {
//            YwPoint point = new YwPoint();
//            point.setImportantCommandImageTag("是".equals(pointExport.getImportantCommandImageTagString()));
//            point.setProvinceTag("是".equals(pointExport.getProvinceTagString()));
//            point.setUnitId(Long.valueOf(unitId));
//            point.setStartTime(startTime);
//            point.setEndTime(endTime);
//            point.setPointName(pointExport.getPointName());
//            point.setSerialNumber(pointExport.getSerialNumber());
//            this.baseMapper.updatePoint(point);
//        }
    }

    public void waitAllFinishAndGetResult(List<YwPoint> dataList) throws InterruptedException, ExecutionException {
        List<FutureTask<Boolean>> resultList = new ArrayList<>(512);
        List<Boolean> data = new ArrayList<>(512);
        int start = 0;
        Date startTime = new Date();
        while (true) {
            if (dataList.size() < start + 50) {
                List<YwPoint> list = dataList.subList(start, dataList.size() - 1);
                Callable<Boolean> callable = new PointImportCallable(list, this.baseMapper);
                FutureTask<Boolean> futureTask = new FutureTask(callable);
                Thread thread = new Thread(futureTask);
                thread.start();
                // 不能直接调用Future的get方法，否则就变成串行执行了，失去多线程意义
                resultList.add(futureTask);
                break;
            } else {
                List<YwPoint> list = dataList.subList(start, start + 50);
                Callable<Boolean> callable = new PointImportCallable(list, this.baseMapper);
                FutureTask<Boolean> futureTask = new FutureTask(callable);
                Thread thread = new Thread(futureTask);
                thread.start();
                // 不能直接调用Future的get方法，否则就变成串行执行了，失去多线程意义
                resultList.add(futureTask);
                start += 50;
            }
        }
        for (FutureTask<Boolean> futureTask : resultList) {
            data.add(futureTask.get());
        }
        Date endTime = new Date();
        log.error("总共耗时：" + (endTime.getTime() - startTime.getTime()) / 1000);
        if (data.stream().allMatch(item -> item == Boolean.TRUE)) {
            System.out.println("执行成功");
        }
    }

}

 ycl-server/src/main/java/com/ycl/thread/PointImportCallable.java

New file
@@ -0,0 +1,59 @@
package com.ycl.thread;

import com.baomidou.mybatisplus.extension.conditions.query.LambdaQueryChainWrapper;
import com.baomidou.mybatisplus.extension.conditions.update.LambdaUpdateChainWrapper;
import com.ycl.platform.domain.entity.YwPoint;
import com.ycl.platform.mapper.YwPointMapper;

import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.Callable;

/**
 * @author：xp
 * @date：2024/8/22 14:24
 */
public class PointImportCallable implements Callable<Boolean> {

    private List<YwPoint> updateList;
    private YwPointMapper ywPointMapper;
    private List<YwPoint> failedList = new ArrayList<>();

    private Integer maxFailedTimes = 5;

    public PointImportCallable(List<YwPoint> updateList, YwPointMapper ywPointMapper) {
        this.updateList = updateList;
        this.ywPointMapper = ywPointMapper;
    }

    @Override
    public Boolean call() throws Exception {
        this.updateFailed(updateList);
        return this.failedList.size() < 1;
    }

    private void updateFailed(List<YwPoint> list) {
        this.maxFailedTimes -= 1;
        if (this.maxFailedTimes < 0) {
            return;
        }
        for (YwPoint ywPoint : list) {
            boolean update = new LambdaUpdateChainWrapper<>(ywPointMapper)
                    .eq(YwPoint::getSerialNumber, ywPoint.getSerialNumber())
                    .set(YwPoint::getProvinceTag, ywPoint.getProvinceTag())
                    .set(YwPoint::getImportantCommandImageTag, ywPoint.getImportantCommandImageTag())
                    .set(YwPoint::getUnitId, ywPoint.getUnitId())
                    .set(YwPoint::getStartTime, ywPoint.getStartTime())
                    .set(YwPoint::getEndTime, ywPoint.getEndTime())
                    .update();
            if (! update) {
                failedList.add(ywPoint);
            } else if (failedList.contains(ywPoint)) {
                failedList.remove(ywPoint);
            }
        }
        if (failedList.size() > 0) {
            this.updateFailed(failedList);
        }
    }
}

 ycl-server/src/main/resources/mapper/zgyw/YwPointMapper.xml

@@ -89,9 +89,9 @@
        yu.unit_name,
        yp.start_time,
        yp.end_time,
        CASE WHEN yp.province_tag = 0 THEN '' ELSE '省厅点位' END AS provinceTagString,
        CASE WHEN yp.important_tag = 0 THEN '' ELSE '重点点位' END AS importantTagString,
        CASE WHEN yp.important_command_image_tag = 0 THEN '' ELSE '重点指挥图像' END AS importantCommandImageTagString
        CASE WHEN yp.province_tag = 0 THEN '否' ELSE '是' END AS provinceTagString,
        CASE WHEN yp.important_tag = 0 THEN '否' ELSE '是' END AS importantTagString,
        CASE WHEN yp.important_command_image_tag = 0 THEN '否' ELSE '是' END AS importantCommandImageTagString
        FROM
        t_yw_point yp
        INNER JOIN t_monitor m ON yp.serial_number = m.serial_number
@@ -101,9 +101,23 @@
                AND m.name like concat('%', #{query.pointName} ,'%')
            </if>
        </where>
        ORDER BY yu.unit_name
    </select>

    <delete id="deleteAll">
        delete from t_yw_point
    </delete>

    <update id="updatePoint">
        update t_yw_point
        <set>
            <if test="point.pointName != null and point.pointName != ''">point_name = #{point.pointName},</if>
            unit_id = #{point.unitId},
            start_time = #{point.startTime},
            end_time = #{point.endTime},
            province_tag = #{point.provinceTag},
            important_command_image_tag = #{point.importantCommandImageTag},
        </set>
            WHERE serial_number = #{point.serialNumber}
    </update>
</mapper>