后面加列sqlMain

package sqlite;

import java.io.FileInputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.util.HashMap;
import java.util.Map.Entry;
import java.util.Properties;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class SqliteMain {
	private static HashMap<String, Integer> firstMap = new HashMap<String, Integer>();
	private static HashMap<String, Integer> secondMap = new HashMap<String, Integer>();
	private static HashMap<String, Integer> testIdfirstMap = new HashMap<String, Integer>();
	private static HashMap<String, Integer> testIdsecondMap = new HashMap<String, Integer>();
	/*
	 * static Connection conn = null; static Connection urlOldConn = null;
	 * static Connection urlNewConn = null;
	 */
	static PreparedStatement ppsm = null;
	static CallableStatement callStmt = null;

	public static void main(String[] args) throws ClassNotFoundException,
			SQLException, IOException, ParseException {
		// String urlOld = args[0];
		// String urlNew = args[1];
		Properties prop = new Properties();
		// 需要外部属性配置文件的路径
		FileInputStream inputStream = new FileInputStream("jdbc.properties");
		prop.load(inputStream);

		String urlOld = prop.getProperty("urlOld");
		String urlNew = prop.getProperty("urlNew");
		// 用户名 密码
		/*
		 * String username = prop.getProperty("user"); String password =
		 * prop.getProperty("password");
		 */

		Connection urlOldConn = JDBCUtils.getConnection("urlOld");
		// 有用户名或密码的时候的代码
		// conn = DriverManager.getConnection(urlOld,username,password);
		readFile(firstMap, urlOldConn);

		Connection urlNewConn = JDBCUtils.getConnection("urlNew");
		readFile(secondMap, urlNewConn);

		createnewtable(urlOld, urlNew);
		insert();

		readTestId(testIdfirstMap, urlOldConn);
		readTestId(testIdsecondMap, urlNewConn);
		update();

		System.out.println("Success!!!");

		// readTestId(testIdMap,conn);
	}

	public static HashMap<String, Integer> readFile(
			HashMap<String, Integer> map, Connection conn)
			throws ClassNotFoundException, SQLException {
		Statement statement = conn.createStatement();
		statement.setQueryTimeout(30);
		ResultSet rst = statement
				.executeQuery("SELECT productId,updateRegionId,testId,details FROM failedTestResultTable");
		while (rst.next()) {

			String productId = rst.getString("productId");
			String updateRegionId = rst.getString("updateRegionId");
			String testId = rst.getString("testId");
			String details = rst.getString("details");
			// 空 = ""
			if (productId == null || "".equals(productId)) {
				productId = "";
			}
			if (updateRegionId == null || "".equals(updateRegionId)) {
				updateRegionId = "";
			}
			// 三个主键构成唯一标识符：一条数据：key
			String key = productId + "~" + updateRegionId + "~" + testId;
			// 检测details中有无#Cat?
			int catPos = details.toLowerCase().indexOf("#cat");
			if (catPos != -1) {
				// 保留#cat后面的字符串
				String first = details.substring(catPos + 4, details.length());
				// 寻找#cat后面第一次出现字母的位置
				Pattern pattern = Pattern.compile("[A-Za-z]");
				Matcher matcher = pattern.matcher(first);
				if (matcher.find()) {
					// matcher.start()为#cat后面第一次出现字母的位置，并切割
					String result = first.substring(0, matcher.start());
					// 去掉切割后的空格和英文标点符号
					String catnum = Pattern.compile("[.,\"\\?!:' //s//p{Zs}]")
							.matcher(result).replaceAll("");
					// 加上#Cat?，四个主键组成key
					key = key + "~" + catnum;
				}
			}

			if (map.containsKey(key)) {
				map.put(key, map.get(key) + 1);
			} else {
				map.put(key, 1);
			}

		}

		return map;

	}

	// 插入方法
	public static void insert() throws ClassNotFoundException, SQLException {

		String key, productId, updateRegionId, testId, Cat = "", changeSituation, changePercent, testIdSituation = "";
		Integer oldNum, newNum;
		String sql = "insert into presentation values(?,?,?,?,?,?,?,?,?,?)";

		Connection conn = JDBCUtils.getConnection("urlPre");
		try {
			// 不自动提交
			conn.setAutoCommit(false);
			// 批量提交
			ppsm = conn.prepareStatement(sql);
			String[] keys;
			// 对firstMap遍历
			for (Entry<String, Integer> entry1 : firstMap.entrySet()) {
				key = entry1.getKey();
				// 变动前的数目，即firstMap中的计数
				oldNum = firstMap.get(entry1.getKey());
				// 将key中的值切出来放入变量中插库
				keys = key.split("~");

				productId = keys[0];
				updateRegionId = keys[1];
				testId = keys[2];
				// 有无#Cat?
				if (keys.length >= 4) {
					Cat = keys[3];
				} else {
					Cat = "";
				}
				testIdSituation = "";

				// 净减少
				// 使用map中的containsKey()方法“不包含”解决数据净减少的情况
				if (!secondMap.containsKey(entry1.getKey())) {
					// 1是指sql语句的第一个问号占位符
					ppsm.setString(1, productId);
					ppsm.setString(2, updateRegionId);
					ppsm.setString(3, testId);
					ppsm.setString(4, Cat);
					ppsm.setInt(5, oldNum);
					ppsm.setString(6, "");
					ppsm.setString(7, "删除");
					ppsm.setInt(8, oldNum);
					ppsm.setString(9, "");

					// 添加一次预定义参数
					ppsm.addBatch();
				} else {
					// “包含”则解决同一条数据增加减少多少的情况
					Integer oldNum1 = firstMap.get(key);
					newNum = secondMap.get(key);
					int num = secondMap.get(key) - firstMap.get(key);

					// BigDecimal加减乘除计算
					// 变动率：保留两位小数
					BigDecimal bg = new BigDecimal(Double.valueOf(num) * 100
							/ Double.valueOf(oldNum1)).setScale(2,
							RoundingMode.UP);
					changePercent = bg.abs() + "%";
					// 变动情况
					if (num > 0) {
						changeSituation = "增加";
					} else if (num == 0) {
						changeSituation = "未改变";
						changePercent = "";
					} else {
						changeSituation = "减少";
					}

					ppsm.setString(1, productId);
					ppsm.setString(2, updateRegionId);
					ppsm.setString(3, testId);
					ppsm.setString(4, Cat);
					ppsm.setInt(5, oldNum1);
					ppsm.setInt(6, newNum);
					ppsm.setString(7, changeSituation);
					// 变动数目取绝对值
					ppsm.setInt(8, Math.abs(num));
					ppsm.setString(9, changePercent);

					ppsm.addBatch();
				}
			}
			// 对secondMap遍历
			for (Entry<String, Integer> entry2 : secondMap.entrySet()) {
				key = entry2.getKey();
				keys = key.split("~");
				productId = keys[0];
				updateRegionId = keys[1];
				testId = keys[2];
				if (keys.length >= 4) {
					Cat = keys[3];
				} else {
					Cat = "";
				}
				// 使用map中的containsKey()方法“不包含”解决数据净增加的情况
				if (!firstMap.containsKey(key)) {
					newNum = secondMap.get(key);
					ppsm.setString(1, productId);
					ppsm.setString(2, updateRegionId);
					ppsm.setString(3, testId);
					ppsm.setString(4, Cat);
					ppsm.setString(5, "");
					ppsm.setInt(6, newNum);
					ppsm.setString(7, "新增");
					ppsm.setInt(8, newNum);
					ppsm.setString(9, "");

					ppsm.addBatch();
				}
			}
			// 批量执行上面的语句，全部提交
			// 批量执行预定义SQL，即把以上添加到批处理命令中的所有命令一次过提交给数据库来执行
			ppsm.executeBatch();
			// Commit it 咽下，到肚子(DB)里面
			// 前面设置connection.setautocommit(false);只有程序调用connection.commit()的时候才会将先前执行的语句一起提交到数据库
			conn.commit();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.release(ppsm, conn);

		}

		// JDBCUtils.close(conn, ppsm, null);
	}

	// 创建新表
	public static void createnewtable(String urlOld, String urlNew)
			throws ClassNotFoundException, SQLException, ParseException {
		String oldDateColumnName = formatString(urlOld);
		String newDateColumnName = formatString(urlNew);
		Connection conn = null;
		conn = JDBCUtils.getConnection("urlPre");
		Statement statement = conn.createStatement();
		statement.setQueryTimeout(30);
		try {

			// 创建新表
			statement.executeUpdate("drop table if exists presentation");
			statement
					.executeUpdate("create table presentation (productId string, updateRegionId string, testId string, Cat string,"
							+ oldDateColumnName
							+ " int, "
							+ newDateColumnName
							+ " int, changeSituation string, changeNum int, changePercent string,testIdSituation string)");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.release(statement, conn);

		}

		// JDBCUtils.close(conn, statement, null);

	}

	public static String formatString(String urlName) throws ParseException {

		String newString = urlName.replaceAll("\\.", "_");
		newString = newString.replaceAll("-", "_");
		String date = newString.substring(newString.indexOf("result_"),
				newString.length() - 4);
		return date;
	}

	public static HashMap<String, Integer> readTestId(
			HashMap<String, Integer> map, Connection conn)
			throws ClassNotFoundException, SQLException {

		Statement statement = conn.createStatement();

		ResultSet rst = statement
				.executeQuery("SELECT testId FROM failedTestResultTable");
		while (rst.next()) {
			String testId = rst.getString("testId");
			String key = testId;
			if (map.containsKey(key)) {
				map.put(key, map.get(key) + 1);
			} else {
				map.put(key, 1);
			}
		}

		return map;
	}

	// 插入方法
	public static void update() throws ClassNotFoundException, SQLException {

		String key, testId, changeSituation, testIdSituation = "";
		Integer oldNum, newNum;
		Connection conn = null;
		String sql = "update presentation set testIdSituation= ? where testId = ?";
		conn = JDBCUtils.getConnection("urlPre");
		PreparedStatement ppsm1 = null;
		try {

			// 批量提交
			ppsm1 = conn.prepareStatement(sql);
			// 不自动提交
			conn.setAutoCommit(false);
			String[] keys;
			// 对firstMap遍历
			for (Entry<String, Integer> entry1 : testIdfirstMap.entrySet()) {
				key = entry1.getKey();
				testId = key;
				if (!testIdsecondMap.containsKey(entry1.getKey())) {
					ppsm1.setString(1, testId);
					ppsm1.setString(2, "删除");

					ppsm1.executeUpdate();
				} else {
					int num = testIdsecondMap.get(key)
							- testIdfirstMap.get(key);
					if (num > 0) {
						changeSituation = "增加";
					} else if (num == 0) {
						changeSituation = "未改变";
					} else {
						changeSituation = "减少";
					}
					ppsm1.setString(1, changeSituation);
					ppsm1.setString(2, testId);

					ppsm1.executeUpdate();
				}
			}
			// 对secondMap遍历
			for (Entry<String, Integer> entry2 : testIdsecondMap.entrySet()) {
				key = entry2.getKey();
				testId = key;
				if (!testIdfirstMap.containsKey(key)) {
					ppsm1.setString(1, "新增");
					ppsm1.setString(2, testId);
					// ppsm1.addBatch();
					ppsm1.executeUpdate();
				}
			}

			conn.commit();

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.release(ppsm1, conn);

		}

	}

}