import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.FileReader;
import java.io.FileWriter;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;/**
 * 
 * 功能:
 * 处理*.sql文件
 * 需求:
 * 修改所有varchar字段的的长度
 * 原长度小于等于10,则加10处理
 * 原长度大于10,则*2处理
 * 目标:
 * 生成符合数据库语法的后缀名位.sql的文本文件
 * 
 * @author andy_zhou.china@outlook.com 
 * 
 */
public class UpdateSqlFile {

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

 BufferedReader br = new BufferedReader(new FileReader("d:\\sql\\test01.sql"));
 BufferedWriter bw = new BufferedWriter(new FileWriter("d:\\sql\\test02.sql"));

 ArrayList<String> list = new ArrayList<String>();
 Map<String, Map<String, Integer>> tableEntry = new HashMap<String, Map<String,Integer>>();
 Map<String, Integer> map = null;

 String line = null;
 StringBuffer strb = null;
 String tableName = null;
 String cloumn = null;
 int len = 0;

 while((line = br.readLine()) != null){
 if(line.contains("CREATE TABLE") && !line.contains("--")){
 strb = new StringBuffer(line + "\r\n");
 } else if(!line.contains(";") && strb != null && !line.contains("--")){
 if(strb != null){
 strb.append(line + "\r\n");
 }
 }else if(line.contains(":") && strb != null){
 strb.append(line+"\r\b");
 list.add(strb.toString());
 strb = null;
 }
 }

 for (int i = 0; i < list.size(); i++) {
 tableName = list.get(i).substring(list.get(i).indexOf("CREATE TABLE"), list.get(i).indexOf("("))
 .replaceAll(" ", "").replaceAll("\"", "");
 String[] info = list.get(i).split("\r\n");
 map = new HashMap<String, Integer>();

 for (int j = 0; j < info.length; j++) {
 info[0] = info[0].substring(info[0].indexOf("("));
 if(info[j].contains("VARCHAR") && info[j].contains("(") && info[j].contains(")")){

 cloumn = info[j].substring(info[j].indexOf("\"") + 1, info[j].lastIndexOf("\""));
 len = Integer.parseInt(info[j].substring(info[j].lastIndexOf("("), info[j].indexOf(")")));

 map.put(cloumn, len);
 }
 }
 tableEntry.put(tableName, map);
 }

 String str = null;
 for (Map.Entry<String, Map<String, Integer>> entryTable : tableEntry.entrySet()) {
 for (Map.Entry<String, Integer> entry : entryTable.getValue().entrySet()) {
 int temp;
 if(entry.getValue() <= 10){
 temp = entry.getValue() + 10;
 }else{
 temp = entry.getValue() * 2;
 }
 str = "alter table " + entryTable.getKey() + " alter " + entry.getKey() + "set data type varchar(" + temp + ");";

 bw.write(str);
 bw.newLine();
 bw.flush();
 }
 }
 bw.close();
 br.close();
 }
}