优化

liusuyi

2025-12-28 7e4432176e90f5710e108037e473afc106229af8

优化

 ard-work/src/main/java/com/ard/work/mapper/ArdMediaPicMapper.java

@@ -1,6 +1,7 @@
package com.ard.work.mapper;

import com.ard.work.domain.entity.ArdMediaPic;
import com.ard.work.domain.entity.ArdMediaVideo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

@@ -13,7 +14,10 @@
     * 查询图片列表（条件查询）
     */
    List<ArdMediaPic> selectPicList(ArdMediaPic query);

    /**
     * 查询图片列表中本图为null的数据
     */
    List<ArdMediaPic> selectPicsWithNullMedia();
    /**
     * 根据任务ID查询图片
     */
@@ -25,6 +29,11 @@
    int insertPic(ArdMediaPic pic);

    /**
     * 更新图片
     */
    int updatePic(ArdMediaPic pic);

    /**
     * 批量新增图片
     */
    int insertPicList(@Param("list") List<ArdMediaPic> list);

 ard-work/src/main/java/com/ard/work/mapper/ArdMediaVideoMapper.java

@@ -15,6 +15,12 @@
    List<ArdMediaVideo> selectArdMediaVideoList(ArdMediaVideo query);

    /**
     * 查询视频列表中本地视频和缩略图为null的数据
     */
    List<ArdMediaVideo> selectVideosWithNullMedia();

    List<ArdMediaVideo> selectVideosWithThumbnailNull();
    /**
     * 根据任务ID查询视频
     */
    List<ArdMediaVideo> selectArdMediaVideoByTaskId(@Param("taskId") Integer taskId);
@@ -25,6 +31,11 @@
    int insertArdMediaVideo(ArdMediaVideo video);

    /**
     * 更新视频
     */
    int updateArdMediaVideo(ArdMediaVideo video);

    /**
     * 根据ID删除视频
     */
    int deleteArdMediaVideoById(@Param("id") String id);

 ard-work/src/main/java/com/ard/work/service/IArdMediaService.java

@@ -16,7 +16,7 @@
    List<ArdMediaVideo> selectArdMediaVideoList(ArdMediaVideo ardMediaVideo);
    void insertPhotoToDatabase(ArdFlightTask flightTask, String serverId, String boxSn);
    void insertVideoToDatabase(ArdFlightTask flightTask, String serverId, String boxSn);
    void updatePhotoToDatabase(ArdFlightTask flightTask, String serverId, String boxSn);
    void updateVideoToDatabase(ArdFlightTask flightTask, String serverId, String boxSn);
    void syncPicToDatabase(ArdFlightTask flightTask, String serverId, String boxSn);
    void syncVideoToDatabase(ArdFlightTask flightTask, String serverId, String boxSn);

}

 ard-work/src/main/java/com/ard/work/service/impl/ArdFlightTaskPartServiceImpl.java

@@ -1,6 +1,7 @@
package com.ard.work.service.impl;

import cn.hutool.core.util.SerializeUtil;
import com.ard.common.utils.DateUtils;
import com.ard.work.domain.cache.UavStateCache;
import com.ard.work.domain.cache.UavTelemetryDataCache;
import com.ard.work.domain.dto.UavFlightStateDTO;
@@ -50,16 +51,17 @@
    public void readTelemetryTask() {
        long now = System.currentTimeMillis();
        UavTelemetryDataCache.forEach((boxSn, telemetryData) -> {

            // 1️⃣ 从状态缓存里取当前状态
            UavState uavState = UavStateCache.get(boxSn);
            if (uavState == null) {
                log.warn("状态不存在，不记录");
                return; // 状态不存在，不记录
            }

            // 2️⃣ 使用遥测自身时间戳判断是否还活着
            long dataTs = telemetryData.getTimestamp(); // 设备时间 / 服务器接收时间
            if (now - dataTs > 3000) {
                log.warn("超过 3 秒没有新遥测 → 认为已离线或冻结，不记录：{}", new Date(dataTs));
                return; // 超过 3 秒没有新遥测 → 认为已离线或冻结，不记录
            }

@@ -71,9 +73,10 @@
                flushRemaining(boxSn);
            }
            lastFlightStatusMap.put(boxSn, currentStatus);

            log.warn("设备 {} 当前状态为：{}", boxSn, currentStatus);
            // 只有飞行中才添加缓存
            if (currentStatus == 2) {
                log.warn("设备 {} 当前状态为飞行中，添加遥测数据到缓存", boxSn);
                UavFlightStateDTO dto = new UavFlightStateDTO(uavState, telemetryData, dataTs);
                addTelemetryData(dto);
            }

 ard-work/src/main/java/com/ard/work/service/impl/ArdFlightTaskServiceImpl.java

@@ -81,7 +81,7 @@
        // 创建查询条件 BoxHistoryDTO，并设置 startTime 作为过滤条件
        BoxHistoryDTO boxHistoryDTO = BoxHistoryDTO.builder()
                .pageIndex(1)
                .pageSize(100)
                .pageSize(20)
                .startTime(DateUtils.parseDateToStr(DateUtils.YYYY_MM_DD, startTime))
                .build();

@@ -102,7 +102,7 @@
        ArdFlightTask ardFlightTask = ardFlightTaskMapper.getArdFlightTaskById(boxHistoryReceiver.getTaskId());
        if (ardFlightTask == null) {
            // 如果不存在，则插入新记录
            log.debug("insert task {}", boxHistoryReceiver.getTaskId());
            log.info("insert task {}", boxHistoryReceiver.getTaskId());
            Date endTime = null;
            if (boxHistoryReceiver.getEndTime() != null) {
                endTime = DateUtils.dateTime(DateUtils.YYYY_MM_DD_HH_MM_SS, boxHistoryReceiver.getEndTime());
@@ -144,13 +144,12 @@
            }
        } else {
            // 如果存在，则更新记录
            log.debug("Updating task {}", boxHistoryReceiver.getTaskId());
            log.debug("sync task {}", boxHistoryReceiver.getTaskId());
            updateFlightTask(ardFlightTask, boxHistoryReceiver);
            // 增量更新视频
            ardMediaService.updateVideoToDatabase(ardFlightTask, serverId, boxHistoryReceiver.getBoxSn());
            // 增量更新图片
            ardMediaService.updatePhotoToDatabase(ardFlightTask, serverId, boxHistoryReceiver.getBoxSn());

            // 同步任务图片
            ardMediaService.syncPicToDatabase(ardFlightTask, serverId, boxHistoryReceiver.getBoxSn());
            // 同步任务视频
            ardMediaService.syncVideoToDatabase(ardFlightTask, serverId, boxHistoryReceiver.getBoxSn());
        }
    }

@@ -163,6 +162,10 @@
            Date newEnd = DateUtils.dateTime(DateUtils.YYYY_MM_DD_HH_MM_SS, remote.getEndTime());
            if (local.getEndTime() == null || !local.getEndTime().equals(newEnd)) {
                local.setEndTime(newEnd);
                local.setAveSpeed(remote.getAveSpeed());
                local.setMaxSpeed(remote.getMaxSpeed());
                local.setFlightDuration(remote.getFlightDuration());
                local.setFlightMileage(remote.getFlightMileage());
                needUpdate = true;
            }
        }

 ard-work/src/main/java/com/ard/work/service/impl/ArdMediaServiceImpl.java

@@ -18,7 +18,7 @@
import com.ard.work.service.IArdMediaService;
import com.ard.work.util.osSelect;
import lombok.extern.slf4j.Slf4j;
import org.springframework.scheduling.annotation.Async;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;
import org.springframework.stereotype.Service;

@@ -27,18 +27,20 @@
import javax.net.ssl.SSLContext;
import javax.net.ssl.TrustManager;
import javax.net.ssl.X509TrustManager;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.*;
import java.net.HttpURLConnection;
import java.net.URL;
import java.net.URLConnection;
import java.nio.file.Files;
import java.nio.file.Path;
import java.security.cert.X509Certificate;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Set;
import java.util.concurrent.CompletableFuture;
import java.util.concurrent.ConcurrentHashMap;
import java.util.concurrent.TimeUnit;
import java.util.stream.Collectors;

/**
@@ -62,6 +64,9 @@
    private ArdFlightTaskMapper ardFlightTaskMapper;
    @Resource
    private ThreadPoolTaskExecutor threadPoolTaskExecutor;

    private final Set<Long> uploadingVideos = ConcurrentHashMap.newKeySet();
    private final Set<Long> uploadingPics = ConcurrentHashMap.newKeySet();

    // 调用接口获取架次图片
    private List<BoxPhotosReceiver> getBoxPhotos(String serverId, BoxPhotosDTO boxPhotosDTO) {
@@ -114,9 +119,8 @@
        }
    }


    @Override
    public void updatePhotoToDatabase(ArdFlightTask flightTask, String serverId, String boxSn) {
    public void syncPicToDatabase(ArdFlightTask flightTask, String serverId, String boxSn) {

        // 获取本地数据库中已存在的图片记录
        List<ArdMediaPic> existingPhotos = ardMediaPicMapper.selectPicByTaskId(flightTask.getTaskId());
@@ -151,9 +155,8 @@
        }
    }


    @Override
    public void updateVideoToDatabase(ArdFlightTask flightTask, String serverId, String boxSn) {
    public void syncVideoToDatabase(ArdFlightTask flightTask, String serverId, String boxSn) {

        // 获取本地数据库中已存在的视频记录
        List<ArdMediaVideo> existingVideos = ardMediaVideoMapper.selectArdMediaVideoByTaskId(flightTask.getTaskId());
@@ -193,173 +196,147 @@

    private void addPhotos(ArdFlightTask flightTask, List<BoxPhotosReceiver> photoList) {
        log.warn("开始添加图片");
        String boxSn = flightTask.getBoxSn();
        for (BoxPhotosReceiver photo : photoList) {
            CompletableFuture.runAsync(() -> {
                ArdMediaPic ardMedia = ArdMediaPic.builder()
                        .id(photo.getPhotoId())
                        .taskId(flightTask.getTaskId())
                        .fileSize(photo.getFileSize())
                        .fileMd5(photo.getFileMd5())
                        .sourceUrl(photo.getImgUrl())
                        .longitude(photo.getLng())
                        .latitude(photo.getLat())
                        .height(photo.getHeight())
                        .captureTime(DateUtils.parseDate(photo.getTimeStamp()))
                        .build();

                String url = "";
                try (InputStream inputStream = openUrlInputStream(photo.getImgUrl(), 5000, 10000)) {
                    if (inputStream != null) {
                        url = ossServiceContext.upload(
                                "pic",
                                boxSn + "/" + ardMedia.getId() + ".jpg",
                                inputStream,
                                MimeTypeUtils.IMAGE_JPG
                        );
                    }
                } catch (Exception e) {
                    log.error("图片上传失败 taskId={}, photoId={}", flightTask.getTaskId(), photo.getPhotoId(), e);
                }

                ardMedia.setMediaUrl(url);
                ardMedia.setCreateTime(new Date());
                ardMediaPicMapper.insertPic(ardMedia);

            }, threadPoolTaskExecutor); // 使用自定义线程池
            ArdMediaPic ardMedia = ArdMediaPic.builder()
                    .id(photo.getPhotoId())
                    .taskId(flightTask.getTaskId())
                    .fileSize(photo.getFileSize())
                    .fileMd5(photo.getFileMd5())
                    .sourceUrl(photo.getImgUrl())
                    .longitude(photo.getLng())
                    .latitude(photo.getLat())
                    .height(photo.getHeight())
                    .captureTime(DateUtils.parseDate(photo.getTimeStamp()))
                    .build();
            ardMedia.setCreateTime(new Date());
            ardMediaPicMapper.insertPic(ardMedia);
        }
    }

    private void addVideos(ArdFlightTask flightTask, List<BoxVideosReceiver> videoList) {
        String boxSn = flightTask.getBoxSn();

        for (BoxVideosReceiver video : videoList) {
            CompletableFuture.runAsync(() -> {
                ArdMediaVideo ardMedia = ArdMediaVideo.builder()
                        .id(video.getId())
                        .name(video.getName())
                        .taskId(flightTask.getTaskId())
                        .fileSize(getFileSizeByUrl(video.getImgUrl()))
                        .sourceUrl(video.getImgUrl())
                        .startTime(DateUtils.parseDate(video.getStartTime()))
                        .endTime(DateUtils.parseDate(video.getEndTime()))
                        .spanSeconds(Integer.parseInt(video.getSpanSeconds()))
                        .build();

                // 视频上传
                String videoUrl = null;
                try (InputStream videoStream = openUrlInputStream(ardMedia.getSourceUrl(), 5000, 10000)) {
                    if (videoStream != null) {
                        videoUrl = ossServiceContext.upload("record",
                                boxSn + "/" + ardMedia.getId() + ".mp4",
                                videoStream,
                                MimeTypeUtils.VIDEO_MP4);
                    }
                } catch (Exception e) {
                    log.error("视频上传失败 taskId={}, videoId={}", flightTask.getTaskId(), video.getId(), e);
                }

                // 缩略图上传（失败不影响视频入库）
                String thumbUrl = null;
                try (ByteArrayInputStream thumbStream = generateThumbnail(ardMedia.getSourceUrl(), 1, 200)) {
                    if (thumbStream != null) {
                        thumbUrl = ossServiceContext.upload("record",
                                boxSn + "/" + ardMedia.getId() + ".jpg",
                                thumbStream,
                                MimeTypeUtils.IMAGE_JPEG);
                    }
                } catch (Exception e) {
                    log.warn("缩略图生成或上传失败 taskId={}, videoId={}", flightTask.getTaskId(), video.getId(), e);
                }

                // 入库（即使缩略图为空也入库）
                ardMedia.setMediaUrl(videoUrl);
                ardMedia.setThumbnailUrl(thumbUrl);
                ardMedia.setCreateTime(new Date());
                ardMediaVideoMapper.insertArdMediaVideo(ardMedia);

            }, threadPoolTaskExecutor);
            ArdMediaVideo ardMedia = ArdMediaVideo.builder()
                    .id(video.getId())
                    .name(video.getName())
                    .taskId(flightTask.getTaskId())
//                    .fileSize(getFileSizeByUrl(video.getImgUrl()))
                    .sourceUrl(video.getImgUrl())
                    .startTime(DateUtils.parseDate(video.getStartTime()))
                    .endTime(DateUtils.parseDate(video.getEndTime()))
                    .spanSeconds(Integer.parseInt(video.getSpanSeconds()))
                    .build();
            ardMedia.setCreateTime(new Date());
            ardMediaVideoMapper.insertArdMediaVideo(ardMedia);
        }
    }

    // 获取文件大小
    private long getFileSizeByUrl(String fileUrl) {
        try {
            URL url = new URL(fileUrl);
            HttpURLConnection conn = (HttpURLConnection) url.openConnection();
            conn.setRequestMethod("HEAD");
            conn.connect();
            return conn.getContentLengthLong(); // 返回字节大小
    /**
     * 处理视频缩略图
     */
    private void processVideoThumbnail(ArdMediaVideo video) {
        // 生成缩略图
        try (ByteArrayInputStream in = generateThumbnail(video.getSourceUrl())) {
            String thumbUrl = ossServiceContext.upload(
                    "record",
                    video.getTaskId() + "/" + video.getId() + ".jpg",
                    in,
                    MimeTypeUtils.IMAGE_JPEG
            );
            video.setThumbnailUrl(thumbUrl);
        } catch (Exception e) {
            return -1;
            log.warn("视频缩略图生成或上传失败 taskId={}, videoId={}", video.getTaskId(), video.getId(), e);
        }
        // 获取文件大小
        try {
            long videoSize = getFileSizeFromUrl(video.getSourceUrl());
            video.setFileSize(videoSize);
        } catch (Exception e) {
            log.warn("获取视频大小失败 url={}", video.getSourceUrl(), e);
        }
        // 更新数据库
        video.setUpdateTime(new Date());
        ardMediaVideoMapper.updateArdMediaVideo(video);
    }

    // 生成视频缩略图
    public ByteArrayInputStream generateThumbnail(String videoFile, int skipSeconds, int targetWidth) {
        String ffmpegPath = "";
        if (osSelect.isWindows()) {
            ffmpegPath = "ffmpeg";
        } else if (osSelect.isLinux()) {
            ffmpegPath = "/usr/local/ffmpeg/bin/ffmpeg";
        }
    public long getFileSizeFromUrl(String fileUrl) throws IOException {
        URL url = new URL(fileUrl);
        HttpURLConnection conn = (HttpURLConnection) url.openConnection();
        conn.setRequestMethod("HEAD");
        conn.setConnectTimeout(5000);
        conn.setReadTimeout(5000);
        conn.connect();
        long size = conn.getContentLengthLong(); // 返回字节数
        conn.disconnect();
        return size;
    }


    private ByteArrayInputStream generateThumbnail(String videoFile) {

        String ffmpeg = osSelect.isWindows()
                ? "ffmpeg"
                : "/usr/local/ffmpeg/bin/ffmpeg";

        Path tempImg = null;
        try {
            List<String> command = new ArrayList<>();
            command.add(ffmpegPath);
            tempImg = Files.createTempFile("thumb_", ".jpg");

            command.add("-timeout");
            command.add("3000000");
            command.add("-rw_timeout");
            command.add("3000000");
            List<String> cmd = new ArrayList<>();
            cmd.add(ffmpeg);
            cmd.add("-y");
            cmd.add("-loglevel");
            cmd.add("error");
            cmd.add("-i");
            cmd.add(videoFile);
            cmd.add("-ss");
            cmd.add(String.valueOf(1));
            cmd.add("-vframes");
            cmd.add("1");
            cmd.add("-vf");
            cmd.add("scale=" + 200 + ":-1");
            cmd.add(tempImg.toAbsolutePath().toString());

            command.add("-ss");
            command.add(String.format("00:00:%02d", skipSeconds));
            command.add("-i");
            command.add(videoFile);
            command.add("-vframes");
            command.add("1");
            log.info("ffmpeg cmd: {}", String.join(" ", cmd));

            // 等比缩放
            command.add("-vf");
            command.add("scale=" + targetWidth + ":-1");
            Process process = new ProcessBuilder(cmd).start();

            // **确保输出 JPEG**
            command.add("-f");
            command.add("mjpeg");      // 关键点：输出格式固定为 JPEG
            command.add("-vcodec");
            command.add("mjpeg");
            drain(process.getErrorStream());

            // 输出到管道
            command.add("pipe:1");
            boolean finished = process.waitFor(15, TimeUnit.SECONDS); // 超时保护
            if (!finished) {
                process.destroyForcibly();
                throw new RuntimeException("ffmpeg timeout");
            }

            // ⭐ 打印最终命令（可复制）
            log.info("ffmpeg command: {}", String.join(" ", command));
            if (process.exitValue() != 0) {
                throw new RuntimeException("ffmpeg exit=" + process.exitValue());
            }

            ProcessBuilder pb = new ProcessBuilder(command);
            pb.redirectErrorStream(false);
            Process process = pb.start();
            byte[] data = Files.readAllBytes(tempImg);
            return new ByteArrayInputStream(data);

            // 读取 FFmpeg 输出（JPEG 数据）到内存
            ByteArrayOutputStream baos = new ByteArrayOutputStream();
            try (InputStream is = process.getInputStream()) {
                byte[] buffer = new byte[8192];
                int len;
                while ((len = is.read(buffer)) != -1) {
                    baos.write(buffer, 0, len);
        } catch (Exception e) {
            throw new RuntimeException("生成缩略图失败", e);
        } finally {
            if (tempImg != null) {
                try {
                    Files.deleteIfExists(tempImg);
                } catch (IOException ignore) {
                }
            }

            int exitCode = process.waitFor();
            if (exitCode != 0) {
                throw new RuntimeException("FFmpeg 执行失败，退出码: " + exitCode);
            }

            return new ByteArrayInputStream(baos.toByteArray());

        } catch (IOException | InterruptedException e) {
            throw new RuntimeException("FFmpeg 生成缩略图失败", e);
        }
    }

    private static void drain(InputStream is) {
        new Thread(() -> {
            try (BufferedReader br = new BufferedReader(new InputStreamReader(is))) {
                while (br.readLine() != null) {
                }
            } catch (IOException ignore) {
            }
        }, "ffmpeg-stderr").start();
    }


    /**
     * 将 URL 转成 InputStream
@@ -408,4 +385,72 @@
            return null;
        }
    }

    @Scheduled(fixedDelay = 10000) // 每10秒执行一次
    public void syncVideoMediaUrl() {
        List<ArdMediaVideo> videos = ardMediaVideoMapper.selectVideosWithNullMedia();
        for (ArdMediaVideo video : videos) {
            if (!uploadingVideos.add(video.getId())) {
                continue;
            }
            CompletableFuture.runAsync(() -> {
                try {
                    // 上传视频到 OSS
                    try (InputStream is = openUrlInputStream(video.getSourceUrl(), 5000, 10000)) {
                        if (is != null) {
                            String mediaUrl = ossServiceContext.upload(
                                    "record",
                                    video.getTaskId() + "/" + video.getId() + ".mp4",
                                    is,
                                    MimeTypeUtils.VIDEO_MP4
                            );
                            log.info("upload mediaUrl={}", mediaUrl);
                            video.setMediaUrl(mediaUrl);
                        }
                    }
                    // 生成缩略图
                    processVideoThumbnail(video);

                    // 更新数据库
                    video.setUpdateTime(new Date());
                    ardMediaVideoMapper.updateArdMediaVideo(video);
                } catch (Exception e) {
                    log.error("生成 mediaUrl/缩略图失败 videoId={}", video.getId(), e);
                } finally {
                    uploadingVideos.remove(video.getId());
                }
            }, threadPoolTaskExecutor);
        }
    }

    @Scheduled(fixedDelay = 10000) // 每10秒执行一次
    public void syncPicMediaUrl() {
        List<ArdMediaPic> pics = ardMediaPicMapper.selectPicsWithNullMedia();
        for (ArdMediaPic pic : pics) {
            if (!uploadingPics.add(pic.getId())) {
                continue;
            }
            CompletableFuture.runAsync(() -> {
                try (InputStream inputStream = openUrlInputStream(pic.getSourceUrl(), 5000, 10000)) {
                    if (inputStream != null) {
                        String url = ossServiceContext.upload(
                                "pic",
                                pic.getTaskId() + "/" + pic.getId() + ".jpg",
                                inputStream,
                                MimeTypeUtils.IMAGE_JPG
                        );
                        pic.setMediaUrl(url);
                        pic.setUpdateTime(new Date());
                        ardMediaPicMapper.updatePic(pic);
                    }
                } catch (Exception e) {
                    log.error("图片上传失败 taskId={}, photoId={}", pic.getTaskId(), pic.getId(), e);
                } finally {
                    uploadingPics.remove(pic.getId());
                }

            }, threadPoolTaskExecutor); // 使用自定义线程池
        }
    }

}

 ard-work/src/main/java/com/ard/work/service/impl/TyServiceImpl.java

@@ -978,7 +978,7 @@
                // 处理无人机遥测消息
                UavTelemetryDataCmd uavTelemetryDataCmd = (UavTelemetryDataCmd) tyCommand;
                TelemetryData telemetryData = uavTelemetryDataCmd.getTelemetryData();
                //telemetryData.setTimestamp(System.currentTimeMillis());//由于六合提供的状态时间戳不准，先以接收状态作为时间戳
                telemetryData.setTimestamp(System.currentTimeMillis());//由于六合提供的状态时间戳不准，先以接收状态作为时间戳
                UavTelemetryDataCache.put(telemetryData);
                //logPrint("无人机遥测", uavTelemetryDataCmd.getTelemetryData().getBoxSn());
                //WebSocketUtil.sendMessageAll(uavTelemetryDataCmd.getTelemetryData());

 ard-work/src/main/resources/mapper/ArdMediaPicMapper.xml

@@ -32,7 +32,28 @@
        </where>
        ORDER BY capture_time DESC
    </select>

    <!-- 查询 media_url为 NULL 的视频 -->
    <select id="selectPicsWithNullMedia" resultMap="ArdMediaPicResult">
        SELECT
            id,
            task_id,
            file_size,
            file_md5,
            media_url,
            source_url,
            longitude,
            latitude,
            height,
            capture_time,
            create_time,
            update_time
        FROM
            ard_media_pic
        WHERE
            media_url IS NULL
        ORDER BY
            capture_time ASC
    </select>
    <!-- 根据任务ID查询图片 -->
    <select id="selectPicByTaskId" resultMap="ArdMediaPicResult">
        SELECT * FROM ard_media_pic WHERE task_id = #{taskId}
@@ -40,7 +61,7 @@
    </select>

    <!-- 插入图片 -->
    <insert id="insertPic" parameterType="com.ard.work.domain.entity.ArdMediaPic">
    <insert id="insertPic" parameterType="ArdMediaPic">
        INSERT INTO ard_media_pic (
            id, task_id, file_size, file_md5,
            media_url, source_url,
@@ -53,6 +74,14 @@
                     #{captureTime}, #{createTime}
                 )
    </insert>
    <!-- 更新图片 -->
    <update id="updatePic" parameterType="ArdMediaPic">
        UPDATE ard_media_pic
        SET
            media_url   = #{mediaUrl},
            update_time = #{updateTime}
        WHERE id = #{id} AND media_url IS NULL
    </update>

    <!-- 批量插入图片 -->
    <insert id="insertPicList">

 ard-work/src/main/resources/mapper/ArdMediaVideoMapper.xml

@@ -37,6 +37,24 @@
        ORDER BY start_time DESC
    </select>

    <!-- 查询 media_url 或 thumbnail_url 为 NULL 的视频 -->
    <select id="selectVideosWithNullMedia" resultMap="ArdMediaVideoResult">
        SELECT
        id, name, file_size, media_url, source_url, thumbnail_url,
        start_time, end_time, span_seconds, task_id,
        create_time, update_time
        FROM ard_media_video where media_url IS NULL
        ORDER BY start_time ASC
    </select>
    <select id="selectVideosWithThumbnailNull" resultMap="ArdMediaVideoResult">
        SELECT
            id, name, file_size, media_url, source_url, thumbnail_url,
            start_time, end_time, span_seconds, task_id,
            create_time, update_time
        FROM ard_media_video where thumbnail_url IS NULL
        ORDER BY start_time ASC
    </select>

    <!-- 根据ID查询 -->
    <select id="selectArdMediaVideoById" resultMap="ArdMediaVideoResult">
        SELECT