MarinaDB 存储过程批量写入表

思路：java程序端传递固定字符隔开的json 字符串，Sql 分割字符串，得到单个json对象，解析json字段，循环写入数据库表：

实现过程如下：

CREATE DEFINER=`root`@`%` PROCEDURE `proc_InsertParamsSource`(
	IN `dataString` LONGTEXT
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT '批量写入数据源'
BEGIN

	DECLARE startIndex INT;	
	DECLARE splitChar CHAR;
	DECLARE item TEXT;
	DECLARE loopC INT;/*循环次数*/
	DECLARE loopIndex INT DEFAULT 0;/*当前循环索引 防止死循环*/
	DECLARE myDataString LONGTEXT;

	
	SET splitChar='@';	/* 分割符*/
	SET loopC = (length(dataString) - length(replace(dataString,splitChar,'')))/LENGTH(splitChar);
	SET myDataString = dataString;
	
	lable1:loop		
		SET startIndex = LOCATE(splitChar,myDataString); /* 起始位置 */		
		if (startIndex = 0) then		
			leave lable1;
		end if;	
		
		if (loopIndex > loopC) then /* 双保险，防止死循环 */		
			leave lable1;
		end if;
		SET loopIndex = loopIndex + 1;
		SET item = TRIM(SUBSTRING_INDEX(myDataString,splitChar,1));
		
		SET myDataString = SUBSTRING(myDataString,startIndex+1);/* 新的文本*/
		
		SET @id = JSON_UNQUOTE(JSON_EXTRACT(item,'$."id"'));
		SET @deviceCode = JSON_UNQUOTE(JSON_EXTRACT(item,'$."deviceCode"'));
		SET @tagCode = JSON_UNQUOTE(JSON_EXTRACT(item,'$."tagCode"'));
		SET @dataType  = JSON_UNQUOTE(JSON_EXTRACT(item,'$."dataType"'));
		SET @valueText  = JSON_UNQUOTE(JSON_EXTRACT(item,'$."value"'));
		SET @timestampText  = JSON_UNQUOTE(JSON_EXTRACT(item,'$."timestamp"'));
		SET @quality  = JSON_UNQUOTE(JSON_EXTRACT(item,'$."quality"'));
		
	
		if (ISNULL(@Id)) then
			ITERATE lable1;
		END if;	
			
		INSERT INTO t_params_source
		(
			`id`,
			`deviceCode`,
			`tagCode`,
			`dataType`,
			`value`,
			`timestamp`,
			`quality`
		)
		VALUES
		(			 
			 @id,
			 @deviceCode,
			 @tagCode,
			 @dataType,
			 @valueText,
			 @timestampText,
			 @quality			 
		 );
		 
		SET @id = null;
		SET @deviceCode = null;
		SET @tagCode = null;
		SET @dataType = NULL;
		SET @valueText  = null;
		SET @timestampText  = null;
		SET @quality  = null;
		
	end loop lable1;
	COMMIT;
END

测试存储过程：

select JSON_OBJECT('id',id,
'deviceCode',deviceCode,'tagCode',tagCode,'dataType',dataType,
'value',`value`,
'timestamp',`timestamp`,
'quality',quality) 
FROM t_params_source limit 10;

DROP PROCEDURE if EXISTS proc_TestScript;
DELIMITER ;;
CREATE PROCEDURE proc_TestScript()
BEGIN
CALL proc_DeviceParamsSource('{"id": 406853288, "deviceCode": "A0201010001210120", "tagCode": "02010100010101010056", "dataType": null, "value": "50.0", "timestamp": "2022-05-10 00:10:00", "quality": 192}@');

END;;
CALL proc_TestScript();

 java程序调用存储过程：

void transmitAll(List<ProductQtyDetailSource> list){
		//region 数据存储优化，此处耗时久
		List<String> dataCollection = new ArrayList<>();
		// 步骤1：先分批
		Integer pageSize= 500;//每次导入笔数
		Integer totalSize = list.size();
		final char splitChar = '@';
//			Integer pageTotal = (int)Math.ceil(1.0D * totalSize / pageSize);//总页数
		StringBuilder builder=new StringBuilder();
		for (Integer i=1;i<=totalSize;i++){
			builder.append(JSONObject.toJSONString(list.get(i-1)));
			builder.append(splitChar);
			if(i>1 && i% pageSize == 0){
				dataCollection.add(builder.toString());
				builder= new StringBuilder();
			}
		}
//		if (builder.length() > 0 && builder.charAt(builder.length() - 1) == splitChar) {
//			builder.deleteCharAt(builder.length() -1 );
//		}
		dataCollection.add(builder.toString());
		// 步骤2：逐个分段导入数据库
		for (String item:dataCollection) {
			try{
				//写入数据库
				jdbc.execute("call proc_InsertParamsSource('"+ item +"')");
			}catch (Exception dbException){
				logger.info("proc_InsertParamsSource failed ");
				dbException.printStackTrace();
			}
		}
		//endregion
	}