Java工具类拼接SQL;MessageFormat.format拼接SQL;

 场景描述:

       最近领导要求将某些数据录入到某个表里,但是这些数据太多了,如果是手动敲SQL语句能累死,那不符合我的脾气,所以自己写了个工具类,类生成SQL语句。第一次使用了字符串拼接,但是这种方式可读性非常差,稍有不注意就会少个东西或者多个东西,偶然看到一篇文章的时候,有个MessageFormat.format方法,出于好奇百度了一下才知道它的用处,然后想到了我这里能用上就做了尝试,用了之后才发现它的便利。

       将要录入的内容修改为指定格式,并保存在txt文件中, 类似于这样的记录还有几百行,格式如下:

0=普通,1=秘密,2=机密,3=绝密		PRIVACY_LEVEL
1=身份证,2=护照,3=港澳通行证,4=台湾通行证,5=军官证		ID_CARD_TYPE
.......

使用MessageFormat.format方法前:

       编写自己的工具类,读取文本并拼接SQL

package common;

import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.InputStreamReader;

public class InsertSQLUtil {
	public static void main(String[] args)  {
		BufferedReader br;
		try {
			br = new BufferedReader (new InputStreamReader(new FileInputStream("C:\\Users\\50209\\Desktop\\Java工具类读取文件\\4.txt")));
	        String str = null;
	        StringBuffer sb = new StringBuffer();
	        //id
	        int id;
			while (br.ready()) {
				//初始化id
				id=1;
				str = br.readLine().trim();
				//若为空行,跳过操作
				if(str==null||str.length()<=0) {
					continue;
				}
				//通过空格分割
				String splitByBlankStrs[] = str.split("\\s+");
				//通过逗号分割
				String strs[] = splitByBlankStrs[0].split(",");
				for(String s:strs) {
					sb.append("insert into pub_dictionary (ID, TYPE, CODE, NAME, DOMAIN_ID, IS_BASE, STATE, PARENT_ID, INPUTCODE1, INPUTCODE2, STANDARD_CODE, STANDARD_NAME, IS_DEFAULT, ORD, VERSION, REMARK, CREATE_EMPID, CREATE_TIME, MODIFY_EMPID, MODIFY_TIME)");
					sb.append("\r\n"); 
					//通过等号分割
					String strs2[] = s.split("=");
					//id,type,code,name
					sb.append("values ("+id+", '"+splitByBlankStrs[1].trim()+"', '"+strs2[0]+"', '"+strs2[1]+"',");
					sb.append("'**', 1, 1, 0, ");
					//辅码1
					sb.append("OF_GET_SRM('"+strs2[1]+"',8,0), ");
					//辅码2
					sb.append("OF_GET_SRM('"+strs2[1]+"',8,1), ");
					sb.append("null, null, 0, ");
					//排序码判断是否为数字,若为数字存数字,若不为数字则不存
					if(strs2[0].matches("^[0-9]*$")) {
						//排序码
						sb.append(strs2[0]+", ");
					}else {
						//排序码
						sb.append("null, ");
					}
					sb.append("'V1.0', null, 999, sysdate, null, null);");
					sb.append("\r\n"); 
					id++;
				}
				sb.append("\r\n"); 
	        }
			System.out.println(sb);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

输出结果:

insert into pub_dictionary (ID, TYPE, CODE, NAME, DOMAIN_ID, IS_BASE, STATE, PARENT_ID, INPUTCODE1, INPUTCODE2, STANDARD_CODE, STANDARD_NAME, IS_DEFAULT, ORD, VERSION, REMARK, CREATE_EMPID, CREATE_TIME, MODIFY_EMPID, MODIFY_TIME)
values (1, 'PRIVACY_LEVEL', '0', '普通','**', 1, 1, 0, OF_GET_SRM('普通',8,0), OF_GET_SRM('普通',8,1), null, null, 0, 0, 'V1.0', null, 999, sysdate, null, null);
insert into pub_dictionary (ID, TYPE, CODE, NAME, DOMAIN_ID, IS_BASE, STATE, PARENT_ID, INPUTCODE1, INPUTCODE2, STANDARD_CODE, STANDARD_NAME, IS_DEFAULT, ORD, VERSION, REMARK, CREATE_EMPID, CREATE_TIME, MODIFY_EMPID, MODIFY_TIME)
values (2, 'PRIVACY_LEVEL', '1', '秘密','**', 1, 1, 0, OF_GET_SRM('秘密',8,0), OF_GET_SRM('秘密',8,1), null, null, 0, 1, 'V1.0', null, 999, sysdate, null, null);
insert into pub_dictionary (ID, TYPE, CODE, NAME, DOMAIN_ID, IS_BASE, STATE, PARENT_ID, INPUTCODE1, INPUTCODE2, STANDARD_CODE, STANDARD_NAME, IS_DEFAULT, ORD, VERSION, REMARK, CREATE_EMPID, CREATE_TIME, MODIFY_EMPID, MODIFY_TIME)
values (3, 'PRIVACY_LEVEL', '2', '机密','**', 1, 1, 0, OF_GET_SRM('机密',8,0), OF_GET_SRM('机密',8,1), null, null, 0, 2, 'V1.0', null, 999, sysdate, null, null);
insert into pub_dictionary (ID, TYPE, CODE, NAME, DOMAIN_ID, IS_BASE, STATE, PARENT_ID, INPUTCODE1, INPUTCODE2, STANDARD_CODE, STANDARD_NAME, IS_DEFAULT, ORD, VERSION, REMARK, CREATE_EMPID, CREATE_TIME, MODIFY_EMPID, MODIFY_TIME)
values (4, 'PRIVACY_LEVEL', '3', '绝密','**', 1, 1, 0, OF_GET_SRM('绝密',8,0), OF_GET_SRM('绝密',8,1), null, null, 0, 3, 'V1.0', null, 999, sysdate, null, null);

insert into pub_dictionary (ID, TYPE, CODE, NAME, DOMAIN_ID, IS_BASE, STATE, PARENT_ID, INPUTCODE1, INPUTCODE2, STANDARD_CODE, STANDARD_NAME, IS_DEFAULT, ORD, VERSION, REMARK, CREATE_EMPID, CREATE_TIME, MODIFY_EMPID, MODIFY_TIME)
values (1, 'ID_CARD_TYPE', '1', '身份证','**', 1, 1, 0, OF_GET_SRM('身份证',8,0), OF_GET_SRM('身份证',8,1), null, null, 0, 1, 'V1.0', null, 999, sysdate, null, null);
insert into pub_dictionary (ID, TYPE, CODE, NAME, DOMAIN_ID, IS_BASE, STATE, PARENT_ID, INPUTCODE1, INPUTCODE2, STANDARD_CODE, STANDARD_NAME, IS_DEFAULT, ORD, VERSION, REMARK, CREATE_EMPID, CREATE_TIME, MODIFY_EMPID, MODIFY_TIME)
values (2, 'ID_CARD_TYPE', '2', '护照','**', 1, 1, 0, OF_GET_SRM('护照',8,0), OF_GET_SRM('护照',8,1), null, null, 0, 2, 'V1.0', null, 999, sysdate, null, null);
insert into pub_dictionary (ID, TYPE, CODE, NAME, DOMAIN_ID, IS_BASE, STATE, PARENT_ID, INPUTCODE1, INPUTCODE2, STANDARD_CODE, STANDARD_NAME, IS_DEFAULT, ORD, VERSION, REMARK, CREATE_EMPID, CREATE_TIME, MODIFY_EMPID, MODIFY_TIME)
values (3, 'ID_CARD_TYPE', '3', '港澳通行证','**', 1, 1, 0, OF_GET_SRM('港澳通行证',8,0), OF_GET_SRM('港澳通行证',8,1), null, null, 0, 3, 'V1.0', null, 999, sysdate, null, null);
insert into pub_dictionary (ID, TYPE, CODE, NAME, DOMAIN_ID, IS_BASE, STATE, PARENT_ID, INPUTCODE1, INPUTCODE2, STANDARD_CODE, STANDARD_NAME, IS_DEFAULT, ORD, VERSION, REMARK, CREATE_EMPID, CREATE_TIME, MODIFY_EMPID, MODIFY_TIME)
values (4, 'ID_CARD_TYPE', '4', '台湾通行证','**', 1, 1, 0, OF_GET_SRM('台湾通行证',8,0), OF_GET_SRM('台湾通行证',8,1), null, null, 0, 4, 'V1.0', null, 999, sysdate, null, null);
insert into pub_dictionary (ID, TYPE, CODE, NAME, DOMAIN_ID, IS_BASE, STATE, PARENT_ID, INPUTCODE1, INPUTCODE2, STANDARD_CODE, STANDARD_NAME, IS_DEFAULT, ORD, VERSION, REMARK, CREATE_EMPID, CREATE_TIME, MODIFY_EMPID, MODIFY_TIME)
values (5, 'ID_CARD_TYPE', '5', '军官证','**', 1, 1, 0, OF_GET_SRM('军官证',8,0), OF_GET_SRM('军官证',8,1), null, null, 0, 5, 'V1.0', null, 999, sysdate, null, null);
......

使用MessageFormat.format方法后:

package common;

import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.text.MessageFormat;

public class InsertSQLUtil {
	public static void main(String[] args)  {
		BufferedReader br;
		try {
			br = new BufferedReader (new InputStreamReader(new FileInputStream("C:\\Users\\50209\\Desktop\\Java工具类读取文件\\4.txt")));
	        String str = null;
	        StringBuffer sb = new StringBuffer();
	        String sqlStr ="insert into pub_dictionary (ID, TYPE, CODE, NAME, DOMAIN_ID, IS_BASE, STATE, PARENT_ID, INPUTCODE1, INPUTCODE2, STANDARD_CODE, STANDARD_NAME, IS_DEFAULT, ORD, VERSION, REMARK, CREATE_EMPID, CREATE_TIME, MODIFY_EMPID, MODIFY_TIME)\r\n" + 
	        		"values ({0}, ''{1}'', ''{2}'', ''{3}'',''**'', 1, 1, 0, OF_GET_SRM(''{4}'',8,0), OF_GET_SRM(''{5}'',8,1), null, null, 0, {6}, ''V1.0'', null, 999, sysdate, null, null);";
	        //id
	        int id;
	        String type;
	        String code;
	        String name;
	        String ord;
			while (br.ready()) {
				//初始化id
				id=1;
				str = br.readLine().trim();
				//若为空行,跳过操作
				if(str==null||str.length()<=0) {
					continue;
				}
				//通过空格分割
				String splitByBlankStrs[] = str.split("\\s+");
				type = splitByBlankStrs[1];
				//通过逗号分割
				String strs[] = splitByBlankStrs[0].split(",");
				for(String s:strs) {
					//通过等号分割
					String strs2[] = s.split("=");
					code = strs2[0];
					name = strs2[1];
					//排序码判断是否为数字,若为数字存数字,若不为数字则不存
					if(strs2[0].matches("^[0-9]*$")) {
						//排序码
						ord=strs2[0];
					}else {
						ord=null;
					}
					//id,type,code,name
					sb.append(MessageFormat.format(sqlStr,id,type,code,name,name,name,ord));
					sb.append("\r\n"); 
					id++;
				}
				sb.append("\r\n"); 
	        }
			System.out.println(sb);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

       输出结果与上面的输出结果一致。

总结:

       比较之后可以发现,使用该方法的可读性非常好,但是需要注意的一点是,如果字符串需要输出单引号',需要用两个单引号''进行转义

MessageFormat.format方法使用参考文章:https://www.cnblogs.com/xiandedanteng/p/3321993.html

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值