批量导出部分Sql两种便捷方法

接到一个任务，说是要将测试环境的插入语句全部导出来，在生产环境执行，由于有一个麻烦点是，每次导入只是一部分语句，所以想到两个方案来做这件事，故此记录下来。

1.用存储过程+游标的方式+字符串拼接方式。

部分代码如下:

DELIMITER $$

USE `interfacepf`$$

DROP PROCEDURE IF EXISTS `myproc5`$$

CREATE DEFINER=`root`@`%` PROCEDURE `myproc5`(IN pid INT)
BEGIN
	DECLARE done BOOLEAN DEFAULT TRUE;
	#bs_interface参数
	DECLARE cur_INTF_ID VARCHAR(50);
	DECLARE cur_ROUTE_RULE_ID VARCHAR(50);
	DECLARE cur_BIZ_SYS_ID VARCHAR(50);
	DECLARE cur_INTF_NM VARCHAR(50);
	DECLARE cur_INTF_CLA_NM VARCHAR(50);
	DECLARE cur_OUT_SYSTEM_TYPE VARCHAR(50);
	DECLARE cur_INTF_TYPE_CD VARCHAR(50);
	DECLARE cur_BACK_CHNL_ID VARCHAR(50);
	DECLARE cur_INTF_PATH VARCHAR(50);
	DECLARE cur_INTF_BIZ_CODE VARCHAR(50);
	DECLARE cur_RSTFL_REQST_MTHD_NM VARCHAR(50);
	DECLARE cur_MSG_FMT_TYPE_CD VARCHAR(50);
	DECLARE cur_AGRMT_ENT_ID VARCHAR(50);
	DECLARE cur_INTF_DESC VARCHAR(50);
	DECLARE cur_INTF_BIZ_TYPE_ID VARCHAR(50);
	DECLARE cur_EFF_TIME VARCHAR(50);
	DECLARE cur_INVLD_TIME VARCHAR(50);
	DECLARE cur_SYS_STS_CD VARCHAR(50);
	DECLARE cur_CRT_TIME VARCHAR(50);
	DECLARE cur_OP_TIME VARCHAR(50);
	
	
	#bs_interface表游标
	DECLARE curr_bs_interface CURSOR FOR SELECT *FROM bs_interface WHERE INTF_ID IN(pid ,(SELECT MAPNG_INTF_ID FROM bs_interface_mapping WHERE INTF_ID=pid));
	
	#游标跳出的条件done=False
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = FALSE;
	
	#1:执行bs_interface表游标
	SET @result='--------------------------------bs_interface表---------------------------------------';
	OPEN curr_bs_interface;
		WHILE done DO
			FETCH curr_bs_interface INTO cur_INTF_ID,cur_ROUTE_RULE_ID ,cur_BIZ_SYS_ID ,cur_INTF_NM ,cur_INTF_CLA_NM ,cur_OUT_SYSTEM_TYPE ,cur_INTF_TYPE_CD ,cur_BACK_CHNL_ID ,cur_INTF_PATH ,cur_INTF_BIZ_CODE ,cur_RSTFL_REQST_MTHD_NM ,cur_MSG_FMT_TYPE_CD ,cur_AGRMT_ENT_ID ,cur_INTF_DESC ,cur_INTF_BIZ_TYPE_ID ,cur_EFF_TIME ,cur_INVLD_TIME ,cur_SYS_STS_CD ,cur_CRT_TIME ,cur_OP_TIME; 
			IF done=TRUE THEN
				SET @temp=CONCAT('INSERT  INTO bs_interface(INTF_ID,ROUTE_RULE_ID,BIZ_SYS_ID,INTF_NM,INTF_CLA_NM,OUT_SYSTEM_TYPE,INTF_TYPE_CD,BACK_CHNL_ID,INTF_PATH,INTF_BIZ_CODE,RSTFL_REQST_MTHD_NM,MSG_FMT_TYPE_CD,AGRMT_ENT_ID,INTF_DESC,INTF_BIZ_TYPE_ID,EFF_TIME,INVLD_TIME,SYS_STS_CD,CRT_TIME,OP_TIME) VALUES (',
				cur_INTF_ID,',',cur_ROUTE_RULE_ID,',',cur_BIZ_SYS_ID,',',cur_INTF_NM,',',cur_INTF_CLA_NM,',',cur_OUT_SYSTEM_TYPE,',',cur_INTF_TYPE_CD,',',cur_BACK_CHNL_ID,',',cur_INTF_PATH,',',cur_INTF_BIZ_CODE,',',cur_RSTFL_REQST_MTHD_NM,',',cur_MSG_FMT_TYPE_CD,',',cur_AGRMT_ENT_ID,',',cur_INTF_DESC,',',cur_INTF_BIZ_TYPE_ID,',',cur_EFF_TIME,',',cur_INVLD_TIME,',',cur_SYS_STS_CD,',',cur_CRT_TIME,',',cur_OP_TIME,')','\n\t');				
				SET @result=CONCAT(@result,'\n',@temp);
				
			END IF;
		END WHILE;
	CLOSE curr_bs_interface;
	SELECT @result;
	
	
    END$$

DELIMITER ;

2.用Java文件导出

代码片段如下:

while (ret.next()) {
						for (int i = 1; i < ret.getMetaData().getColumnCount() + 1; i++) {
							if (1 == i) {
								bs_interface_delete_mapping = bs_interface_delete_mapping.replaceAll("<" + i + ">",
										ret.getString(i));
							
							}
							bs_interface_mapping = bs_interface_mapping.replaceAll("<" + i + ">",
									ret.getString(i));
						}

						bs_interface_list.add(getInterfaceSql1(ret.getString(1)));
						bs_interface_delete_list.add(getInterfaceSql(ret.getString(1)));

						bs_interface_list.add(getInterfaceSql1(ret.getString(2)));
						bs_interface_delete_list.add(getInterfaceSql(ret.getString(2)));
						
						
						bs_interface_param_and_mapping_list = getInterfaceParamAndMapping(ret.getString(1),
								ret.getString(2));
						bs_interface_param_and_mapping_delete_list = getInterfaceParamAndMapping1(ret.getString(1),
								ret.getString(2));
					}

当然最后还是以选择第二种方式导出的。