【Springboot+JPA】存储过程Procedure使用及踩坑记录

存储过程实现

1.表结构

本次存储过程实现功能包含两个表：
1.process_source_report 主表（简称A），记录上报数据
2.process_source_report_video 从表（简称B），记录数据关联的视频信息

/*DDL 信息*/------------CREATE TABLE `process_source_report` (`process_report_id` bigint NOT NULL AUTO_INCREMENT,`tag` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,`alarm_level` int DEFAULT NULL,`alarm_text` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,`channel_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,`confirm_time` datetime(6) DEFAULT NULL,`create_time` datetime(6) DEFAULT NULL,`image_file_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,`process_id` bigint DEFAULT NULL,`process_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,`source_id` bigint DEFAULT NULL,`source_index` int DEFAULT NULL,`update_time` datetime(6) DEFAULT NULL,`user_state` int DEFAULT '0',`video_file_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,`report_status` int DEFAULT '0',`relative_box` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,`timestamp` bigint DEFAULT NULL,PRIMARY KEY (`process_report_id`),UNIQUE KEY `UK4c7m1ir5j0uj9p4s4hyqiuh1n` (`tag`),KEY `IDXrp57fkk73cmvrp2c80xqskng1` (`user_state`),KEY `IDX7wphi9do4thvn7xqqs6f8lo7j` (`alarm_level`),KEY `IDXr9mb5lpy1fliw44nurlrn0bc9` (`process_id`),KEY `IDXanidygk5t2fmxpvutc7n28fup` (`source_id`),KEY `IDXldvlk8jobglhs5ytbhiks3sll` (`source_index`)
) ENGINE=InnoDB AUTO_INCREMENT=9452 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci/*DDL 信息*/------------CREATE TABLE `process_source_report_video` (`channel_area_report_video_id` bigint NOT NULL AUTO_INCREMENT,`channel_id` bigint DEFAULT NULL,`create_time` datetime(6) DEFAULT NULL,`pipeline_id` bigint DEFAULT NULL,`status` int DEFAULT NULL,`tag` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,`timestamp_end` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,`timestamp_end_by_date` datetime(6) DEFAULT NULL,`timestamp_start` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,`timestamp_start_by_date` datetime(6) DEFAULT NULL,`update_time` datetime(6) DEFAULT NULL,`upload_status` int DEFAULT NULL,`upload_status_by_date` datetime(6) DEFAULT NULL,`video_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,`video_path` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,PRIMARY KEY (`channel_area_report_video_id`),KEY `IDX165djkpsa9bp364p0qod0efrq` (`tag`)
) ENGINE=InnoDB AUTO_INCREMENT=11221 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

2.上报数据分页查询

功能描述：表A通过多个字段进行并行分页筛选查询

2.1先查出总条数

2.1.1 创建存储过程

DROP PROCEDURE IF EXISTS channelReportPagesCount;
DELIMITER $$CREATEPROCEDURE channelReportPagesCount(IN p_page INT,IN p_pageSize INT,IN p_sourceIds VARCHAR(255),IN p_processIds VARCHAR(255),IN p_alarmStates VARCHAR(255),IN p_alarmTypes VARCHAR(255),IN p_pickDate VARCHAR(20),IN p_keywords VARCHAR(255),IN p_isAdmin BOOLEAN,IN p_allIsNull BOOLEAN)BEGIN# 声明变量DECLARE OFFSET INT DEFAULT (p_page - 1) * p_pageSize;		DECLARE countQuery TEXT;DECLARE whereQueryJoin TEXT;DECLARE total_count LONG DEFAULT 0;# 计数查询语句SET countQuery = 'SELECT COUNT(process_report_id) into @total_count FROM process_source_report';# Where条件语句SET whereQueryJoin = ' where 1=1 ';-- Java判断是否页面传入参数都为空，如果页面参数都为空说明是在进行新告警刷新，不需要进入页面参数判断条件，节省时间IF p_allIsNull = FALSE THEN-- sourceIds 查询IF p_sourceIds IS NOT NULL AND p_sourceIds <> '' THENSET whereQueryJoin = CONCAT(whereQueryJoin, ' AND source_id IN (', p_sourceIds, ') ');END IF;-- processIds 查询IF p_processIds IS NOT NULL AND p_processIds <> '' THENSET whereQueryJoin = CONCAT(whereQueryJoin, ' AND process_id IN (', p_processIds, ') ');END IF;-- alarmStates 查询IF p_alarmStates IS NOT NULL AND p_alarmStates <> '' THENSET whereQueryJoin = CONCAT(whereQueryJoin, ' AND user_state IN (', p_alarmStates, ') ');END IF;-- alarmTypes OR 查询IF p_alarmTypes IS NOT NULL AND p_alarmTypes <> '' THENSET whereQueryJoin = CONCAT(whereQueryJoin, ' AND (alarm_text LIKE "%', REPLACE(p_alarmTypes, ',', '%" OR alarm_text LIKE "%'), '%") ');END IF;-- keywords OR 查询IF p_keywords IS NOT NULL AND p_keywords <> '' THENSET whereQueryJoin = CONCAT(whereQueryJoin, ' AND (alarm_text LIKE "%', REPLACE(p_keywords, ',', '%" OR alarm_text LIKE "%'), '%") ');END IF;-- pickDate 日期查询IF p_pickDate IS NOT NULL AND p_pickDate <> '' THENSET whereQueryJoin = CONCAT(whereQueryJoin, ' AND create_time >= "', p_pickDate, ' 00:00:00" AND create_time <= "', p_pickDate, ' 23:59:59" ');END IF;END IF;-- 管理员查看所有，非管理员只看等级为2或4的IF p_isAdmin = FALSE THENSET whereQueryJoin = CONCAT(whereQueryJoin, ' AND (alarm_level = 2 OR alarm_level = 4) ');END IF;-- SELECT whereQueryJoin;# 拼接所有WhereSET countQuery = CONCAT(countQuery, whereQueryJoin);SET @countQuery = countQuery;PREPARE stmtCount FROM @countQuery;  -- 加上 @ 符号EXECUTE stmtCount;DEALLOCATE PREPARE stmtCount;SET totalCount = (SELECT @total_count);END$$
DELIMITER ;CALL channelReportPagesCount(1,        -- 页码，比如 130,    -- 每页的大小，比如 10'',   -- 源 ID，逗号分隔的字符串，例如 '1,2,3''',  -- 进程 ID，逗号分隔的字符串，例如 '4,5''', -- 告警状态，逗号分隔的字符串，例如 '1,2''',  -- 告警类型，逗号分隔的字符串，例如 'type1,type2''',    -- 日期，例如 '2024-01-01''',    -- 关键字，逗号分隔的字符串，例如 'keyword1,keyword2'FALSE,         -- 是否为管理员，布尔值，TRUE 或 FALSETRUE
);

2.1.2 实体类声明存储过程

@NamedStoredProcedureQuery(name = "ProcessSourceReport.pagesTotalCount",procedureName = "channelReportPagesCount",parameters = {@StoredProcedureParameter(mode = ParameterMode.IN, name = "pageNumber", type = Integer.class),@StoredProcedureParameter(mode = ParameterMode.IN, name = "pageSize", type = Integer.class),@StoredProcedureParameter(mode = ParameterMode.IN, name = "sourceIds", type = String.class),@StoredProcedureParameter(mode = ParameterMode.IN, name = "processIds", type = String.class),@StoredProcedureParameter(mode = ParameterMode.IN, name = "alarmStates", type = String.class),@StoredProcedureParameter(mode = ParameterMode.IN, name = "alarmTypes", type = String.class),@StoredProcedureParameter(mode = ParameterMode.IN, name = "pickDate", type = String.class),@StoredProcedureParameter(mode = ParameterMode.IN, name = "keywords", type = String.class),@StoredProcedureParameter(mode = ParameterMode.IN, name = "isAdmin", type = Boolean.class),@StoredProcedureParameter(mode = ParameterMode.IN, name = "paramAllIsNull", type = Boolean.class)})

2.1.3 仓库方法绑定存储过程

@Procedure(name = "ProcessSourceReport.pagesTotalCount")BigInteger channelReportPagesCount(@Param("pageNumber") int pageNumber,@Param("pageSize") int pageSize,@Param("sourceIds") String sourceIds,@Param("processIds") String processIds,@Param("alarmStates") String alarmStates,@Param("alarmTypes") String alarmTypes,@Param("pickDate") String pickDate,@Param("keywords") String keywords,@Param("isAdmin") boolean isAdmin,@Param("paramAllIsNull") boolean paramAllIsNull);

2.1.4 服务调用存储过程

 boolean paramAllIsNull = StringUtils.isBlank(sourceIds) && StringUtils.isBlank(processIds) && StringUtils.isBlank(alarmStates)&& StringUtils.isBlank(alarmTypes) && StringUtils.isBlank(keywords) && StringUtils.isBlank(pickDate);
BigInteger pagesTotalCount = processSourceReportRepo.channelReportPagesCount(pageable.getPageNumber() + 1, pageable.getPageSize(), sourceIds, processIds,alarmStates, alarmTypes, pickDate, keywords, isAdmin, paramAllIsNull);

2.2返回分页数据

2.2.1 创建存储过程

DROP PROCEDURE IF EXISTS channelReportPages;
DELIMITER $$CREATE PROCEDURE channelReportPages(IN p_page INT,IN p_pageSize INT,IN p_sourceIds VARCHAR(255),IN p_processIds VARCHAR(255),IN p_alarmStates VARCHAR(255),IN p_alarmTypes VARCHAR(255),IN p_pickDate VARCHAR(20),IN p_keywords VARCHAR(255),IN p_isAdmin BOOLEAN,IN p_allIsNull BOOLEAN -- 所有页面查询参数是否都为空)BEGIN# 声明变量DECLARE OFFSET INT DEFAULT (p_page - 1) * p_pageSize;DECLARE sqlQuery TEXT;DECLARE whereQueryJoin TEXT;# 基本查询语句SET sqlQuery = 'SELECT process_report_id  ,a.process_id ,a.tag,process_name ,source_id ,source_index ,channel_name ,alarm_text ,alarm_level ,user_state ,image_file_name ,a.create_time ,a.update_time ,video_file_name,(select upload_status from process_source_report_video b where b.tag = a.tag) upload_statusFROM process_source_report a  ';# Where条件语句SET whereQueryJoin = ' where 1=1 ';-- Java判断是否页面传入参数都为空，如果页面参数都为空说明是在进行新告警刷新，不需要进入页面参数判断条件，节省时间IF p_allIsNull = FALSE THEN-- sourceIds 查询IF p_sourceIds IS NOT NULL AND p_sourceIds <> '' THENSET whereQueryJoin = CONCAT(whereQueryJoin, ' AND source_id IN (', p_sourceIds, ') ');END IF;-- processIds 查询IF p_processIds IS NOT NULL AND p_processIds <> '' THENSET whereQueryJoin = CONCAT(whereQueryJoin, ' AND process_id IN (', p_processIds, ') ');END IF;-- alarmStates 查询IF p_alarmStates IS NOT NULL AND p_alarmStates <> '' THENSET whereQueryJoin = CONCAT(whereQueryJoin, ' AND user_state IN (', p_alarmStates, ') ');END IF;-- alarmTypes OR 查询IF p_alarmTypes IS NOT NULL AND p_alarmTypes <> '' THENSET whereQueryJoin = CONCAT(whereQueryJoin, ' AND (alarm_text LIKE "%', REPLACE(p_alarmTypes, ',', '%" OR alarm_text LIKE "%'), '%") ');END IF;-- keywords OR 查询IF p_keywords IS NOT NULL AND p_keywords <> '' THENSET whereQueryJoin = CONCAT(whereQueryJoin, ' AND (alarm_text LIKE "%', REPLACE(p_keywords, ',', '%" OR alarm_text LIKE "%'), '%") ');END IF;-- pickDate 日期查询IF p_pickDate IS NOT NULL AND p_pickDate <> '' THENSET whereQueryJoin = CONCAT(whereQueryJoin, ' AND create_time >= "', p_pickDate, ' 00:00:00" AND create_time <= "', p_pickDate, ' 23:59:59" ');END IF;END IF;-- 管理员查看所有，非管理员只看等级为2或4的IF p_isAdmin = FALSE THENSET whereQueryJoin = CONCAT(whereQueryJoin, ' AND (alarm_level = 2 OR alarm_level = 4) ');END IF;-- SELECT whereQueryJoin;# 拼接whereSET sqlQuery = CONCAT(sqlQuery, whereQueryJoin);-- select sqlQuery;-- 倒序SET sqlQuery = CONCAT(sqlQuery, ' ORDER BY process_report_id DESC');-- 分页SET sqlQuery = CONCAT(sqlQuery, ' LIMIT ', OFFSET, ', ', p_pageSize);-- select countQuery;SET @sqlQuery=sqlQuery;-- 执行SQLPREPARE stmt FROM @sqlQuery;  -- 加上 @ 符号EXECUTE stmt;DEALLOCATE PREPARE stmt;		END$$
DELIMITER ;CALL channelReportPages(1,        -- 页码，比如 130,    -- 每页的大小，比如 10'',   -- 源 ID，逗号分隔的字符串，例如 '1,2,3''',  -- 进程 ID，逗号分隔的字符串，例如 '4,5''', -- 告警状态，逗号分隔的字符串，例如 '1,2''',  -- 告警类型，逗号分隔的字符串，例如 'type1,type2''',    -- 日期，例如 '2024-01-01''',    -- 关键字，逗号分隔的字符串，例如 'keyword1,keyword2'FALSE,         -- 是否为管理员，布尔值，TRUE 或 FALSETRUE
);

2.2.2 实体类声明存储过程

@NamedStoredProcedureQuery(name = "ProcessSourceReport.pages",procedureName = "channelReportPages",parameters = {@StoredProcedureParameter(mode = ParameterMode.IN, name = "pageNumber", type = Integer.class),@StoredProcedureParameter(mode = ParameterMode.IN, name = "pageSize", type = Integer.class),@StoredProcedureParameter(mode = ParameterMode.IN, name = "sourceIds", type = String.class),@StoredProcedureParameter(mode = ParameterMode.IN, name = "processIds", type = String.class),@StoredProcedureParameter(mode = ParameterMode.IN, name = "alarmStates", type = String.class),@StoredProcedureParameter(mode = ParameterMode.IN, name = "alarmTypes", type = String.class),@StoredProcedureParameter(mode = ParameterMode.IN, name = "pickDate", type = String.class),@StoredProcedureParameter(mode = ParameterMode.IN, name = "keywords", type = String.class),@StoredProcedureParameter(mode = ParameterMode.IN, name = "isAdmin", type = Boolean.class),@StoredProcedureParameter(mode = ParameterMode.IN, name = "paramAllIsNull", type = Boolean.class)})

2.2.3 仓库方法绑定存储过程

@Procedure(name = "ProcessSourceReport.pages")List<Object[]> channelReportPages(@Param("pageNumber") int pageNumber,@Param("pageSize") int pageSize,@Param("sourceIds") String sourceIds,@Param("processIds") String processIds,@Param("alarmStates") String alarmStates,@Param("alarmTypes") String alarmTypes,@Param("pickDate") String pickDate,@Param("keywords") String keywords,@Param("isAdmin") boolean isAdmin,@Param("paramAllIsNull") boolean paramAllIsNull);

2.2.4 服务调用存储过程

new PageImpl<>(channelReportPages(pageable.getPageNumber() + 1, pageable.getPageSize(), sourceIds, processIds,alarmStates, alarmTypes, pickDate, keywords, isAdmin, paramAllIsNull).stream().map(result -> {return new StreamReportBO(((BigInteger) result[0]).longValue(), // processReportId, bigint((BigInteger) result[1]).longValue(), // processId, bigint(String) result[2],                // tag(String) result[3],                // processName((BigInteger) result[4]).longValue(), // channelId, bigint((Integer) result[5]), // channelIndex, int(String) result[6],                // channelName(String) result[7],                // alarmText((Integer) result[8]),  // alarmLevel, int((Integer) result[9]),  // userState, int(String) result[10],               // imageFileName(Date) result[11],                 // createTime(Date) result[12],                 // updateTime(String) result[13], // roiFileName(Integer) result[14]);// uploadStatus}).toList(), pageable, pagesTotalCount.longValue());

3.更新视频状态

3.1.1 创建存储过程

DROP PROCEDURE IF EXISTS videoUploadEnd;
DELIMITER $$CREATEPROCEDURE videoUploadEnd(IN p_video_name VARCHAR(256))BEGINDECLARE EXIT HANDLER FOR SQLEXCEPTIONBEGIN-- 错误处理ROLLBACK;END;START TRANSACTION;-- 更新主表中的状态UPDATE process_source_report_videoSET upload_status = 1, upload_status_by_date = NOW()WHERE video_name = p_video_name; -- 根据实际需要更新的条件-- 返回关联表中的数据SELECT  process_id, process_report_id, alarm_level, image_file_name, video_file_name, tag, alarm_textFROM process_source_report WHERE video_file_name = p_video_name; -- 只返回刚更新的相关数据COMMIT;END$$
DELIMITER ;

3.1.2 调用

// repo 层@Modifying
@Procedure(name = "ProcessSourceReportVideo.videoUploadEnd")
List<Object[]> VideoUploadEnd(String videoName);// Service层
public List<VideoUploadEndVO> updateVideoUploadStatusAndReturnReportData(String videoName) {List<Object[]> objects = processSourceReportVideoRepo.VideoUploadEnd(videoName);if (null == objects || objects.isEmpty()) {return null;}return objects.stream().map(item ->new VideoUploadEndVO(((BigInteger) item[0]).longValue(), ((BigInteger) item[1]).longValue(), (Integer) item[2], (String) item[3], (String) item[4], (String) item[5], (String) item[6])).toList();}

4.设置上报数据过期

4.1.1创建存储过程

DROP PROCEDURE IF EXISTS setReportDataExpire;
DELIMITER $$CREATEPROCEDURE setReportDataExpire()BEGINDECLARE EXIT HANDLER FOR SQLEXCEPTIONBEGIN-- 错误处理ROLLBACK;END;-- 设置共用变量SET @expiration_time = SUBDATE(NOW(), INTERVAL 10 MINUTE);SET @nowTime = NOW();START TRANSACTION;-- 更新主表中的状态UPDATE process_source_reportSET user_state = 2, confirm_time = @nowTimeWHERE user_state = 0 AND create_time < @expiration_time; -- 根据实际需要更新的条件-- 更新视频状态，漏网之鱼UPDATE process_source_report_video SET upload_status = 1, upload_status_by_date = NOW() WHERE tag IN (SELECT tagFROM process_source_report WHERE confirm_time = @nowTime);-- 返回关联表中的数据SELECT  process_id, process_report_id, alarm_levelFROM process_source_report WHERE confirm_time = @nowTime; -- 只返回刚更新的相关数据COMMIT;END$$
DELIMITER ;

4.1.2 调用

// repo 层
@Modifying
@Procedure(name = "ProcessSourceReport.setReportDataExpire")
List<Object[]> setReportDataExpireProcedure();// service层
public List<ProcessReportStatusChangeVO> setReportDataExpireProcedure() {List<Object[]> objects = processSourceReportRepo.setReportDataExpireProcedure();if (null == objects || objects.isEmpty()) {return null;}return objects.stream().map(item ->new ProcessReportStatusChangeVO(((BigInteger) item[0]).longValue(), ((BigInteger) item[1]).longValue(), (Integer) item[2])).toList();}

存储过程调用方法

以下两种方法都可以调用上文的存储过程，任选一种，注意区分这里@Query用到的是数据库实际存储过程名称，@Procedure用的则是实体类里面定义的名称

@Query

 @Query(value = "CALL channelReportPagesCount(?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10)", nativeQuery = true)@Query(value = "CALL channelReportPages(?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10)", nativeQuery = true)

@Procedure

如上文2.x.3所示

异常处理

运行时异常：Positional parameter [1] is not registered with this procedure call; nested exception is java.lang.IllegalArgumentException: Positional parameter [1] is not registered with this procedure call
org.springframework.dao.InvalidDataAccessApiUsageException: Positional parameter [1] is not registered with this procedure call; nested exception is java.lang.IllegalArgumentException: Positional parameter [1] is not registered with this procedure call
at org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:374)
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:235)
at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:551)

Caused by: java.lang.IllegalArgumentException: Positional parameter [1] is not registered with this procedure call
at org.hibernate.query.procedure.internal.ProcedureParameterMetadata.getQueryParameter(ProcedureParameterMetadata.java:141)

出现以上异常需要着重检查
@StoredProcedureParameter(mode = ParameterMode.IN, name = “pageNumber”, type = Integer.class)这里的name和repo方法里面写的一致，如果还是报错，一定要在repo方法参数名前加上@Param(“”)注解