代码备忘-一个简单SQL转换程序

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
/*
* convert only for specified SQLs
*/
public class SqlUitl {

public static void main(String[]args) throws IOException{

File file = new File("D:\\inputSG.SQL");
InputStreamReader isr = new InputStreamReader(new FileInputStream(file) ,"UTF-8");
// System.out.println(isr.getEncoding());
BufferedReader br= new BufferedReader(isr);

//FileWriter fw = new FileWriter("D:\\convertedfile.SQL");
OutputStreamWriter osw = new OutputStreamWriter(new FileOutputStream("D:\\outputSG.SQL"), "UTF-8");
BufferedWriter bw = new BufferedWriter(osw);

String convertedSQL = null;
String line = br.readLine();
int delNum = 0;
int insNum = 0;
while(line != null)
{
if(line.trim().toLowerCase().indexOf("delete from")>=0)
{
delNum++;
System.out.println("delNum : "+delNum);
convertedSQL = getConvertedInsertSQL(line);
}
else if(line.trim().toLowerCase().indexOf("insert into")>=0)
{
insNum++;
System.out.println("insNum : "+insNum);
convertedSQL = getConvertedDeleteSQL(line);
}
else
{
line = br.readLine();
bw.newLine();
continue;
}

System.out.println(line);

bw.write(convertedSQL);
bw.newLine();
line = br.readLine();
}
System.out.println("delNum : "+delNum+" insNum : "+insNum);

isr.close();
bw.flush();
bw.close();
br.close();
bw.close();


// String input = "delete from table1 where bc='SIN' and locale ='zh_CN' and code = '660' and description = 'No Description Found' and effective_from = to_date('01/01/1900', 'mm/dd/yyyy') and effective_to = to_date('12/31/9999', 'mm/dd/yyyy');";
// String input = "insert into table2 values ('SIN','AOU','zh_CN', '货银对付 - 转出', to_date('01/01/1900', 'mm/dd/yyyy'), to_date('12/31/9999', 'mm/dd/yyyy'));";
// System.out.println(getConvertedDeleteSQL(input));
}

public static String getConvertedDeleteSQL(String OrignalSQL)
{
String[] temp = new String[12];
temp = OrignalSQL.split(", |values \\(|\\);|,");
String bc = temp[1];
String locale = temp[3];
String code = temp[2];
String description = temp[4];
String effective_from = temp[5]+", "+temp[6];
String effective_to =temp[7]+", "+temp[8];

for(String str : temp)
{
System.out.println(str);
}
StringBuilder sb = new StringBuilder("delete from chd_l_sec_trans_type where ");

sb.append("bc=").append(bc)
.append(" and locale =").append(locale)
.append(" and code = ").append(code)
.append(" and description = ").append(description)
.append(" and effective_from = ").append(effective_from)
.append(" and effective_to = ").append(effective_to)
.append(";");

return sb.toString();
}

private static String getValue(String input,String regex)
{
Pattern pattern = Pattern.compile(regex);
Matcher matcher = pattern.matcher(input);
String value = null;
while (matcher.find()) {
// System.out.println(matcher.group(0));
// System.out.println(matcher.group(1));
value = matcher.group(1);
}
return value;
}

public static String getConvertedInsertSQL(String OrignalSQL)
{
StringBuilder sb = new StringBuilder("insert into chd_l_sec_trans_type values (");
String regex_bc = "bc=(\'(.*?)\')";
String bc = getValue(OrignalSQL,regex_bc);

String regex_locale = "locale =(\'(.*?)\')";
String locale = getValue(OrignalSQL,regex_locale);

String regex_code = "code = (\'(.*?)\')";
String code = getValue(OrignalSQL,regex_code);

String regex_description = "description = (\'(.*?)\')";
String description = getValue(OrignalSQL,regex_description);

String regex_effective_from = "effective_from = (to_date\\((.*?)\\))";
String effective_from = getValue(OrignalSQL,regex_effective_from);

String regex_effective_to = "effective_to = (to_date\\((.*?)\\))";
String effective_to = getValue(OrignalSQL,regex_effective_to);

sb.append(bc).append(", ").append(code).append(", ").append(locale).append(", ").append(description).append(", ").append(effective_from).append(", ").append(effective_to).append(");");
return sb.toString();
}

}[code="java"][/code]

小程序主要加强了3方面知识的应用
1.I/O流,许久没用I/O流的API,此过程中遇到拉从文本文件读去字符出现乱码的问题,在构造InputStreamReader和OutputStreamReader时,一致采用"UTF-8"编码方式;
2.正则表达式的应用,从字符串提取特定格式的内容,用正则无疑是很好大选择,"[]",".*","|","?",(具体参阅JDK API)还有"()"主要用于辅助Matcher的group()方法,从匹配的结果中获得某个区域的value.
3.while循环内if,else的嵌套,以及continue的作用范围, 在此例中,如果进入else块执行continue时,将跳到前面的while语句(结束本次循环,后门的语句将被忽略)进入新的循环,处理不当就会进入死循环,下次要注意这一点.