Perfect

package sqlite;

import java.math.BigDecimal;
import java.math.RoundingMode;
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.util.HashMap;
import java.util.Map.Entry;

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 String keyCopy;
	static Connection conn = null;
	static PreparedStatement ppsm = null;
	
	public static void main(String[] args) throws ClassNotFoundException, SQLException {
		Class.forName("org.sqlite.JDBC");
		conn = DriverManager.getConnection("jdbc:sqlite:MIB3_result_2019.08.31_17-09-03.sq3");
        readFile(firstMap,conn);
        conn = DriverManager.getConnection("jdbc:sqlite:MIB3_result_2019.09.17_22-35-36.sq3");
        readFile(secondMap,conn);
		        
		createnewtable();
		insert();
	}
	
	public static HashMap<String, Integer> readFile(HashMap<String, Integer> map,Connection conn) throws ClassNotFoundException, SQLException{
		try {
			Statement statement = conn.createStatement();
			statement.setQueryTimeout(30);
			ResultSet rst = statement.executeQuery("SELECT\r\n" + 
			"\r\n" + 
			"  \"productId\","+ "\r\n" + 
			"  \"updateRegionId\",\r\n" + 
			"  \"testId\",\r\n" + " \r\n" + 
			"  \"details\"\r\n" + 
			"FROM\r\n" + 
			"  \"main\".\"failedTestResultTable\" \r\n" + 
			"ORDER BY testId, case \r\n" + 
			"when details like '%Cat1%' then 1\r\n" + 
			"when details like '%Cat2%' then 2\r\n" + 
			"when details like '%Cat3%' then 3\r\n" + 
			"when details like '%Cat4%' then 4\r\n" + 
			"when details like '%Cat5%' then 5\r\n" + 
			"when details like '%Cat6%' then 6\r\n" + 
			"when details like '%Cat7%' then 7\r\n" + 
			"when details like '%Cat8%' then 8\r\n" + 
			"when details like '%Cat9%' then 9\r\n" + 
			"end");
			
			int i = 1;
			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 = "";
				}
			    
			    String key = productId + "~" + updateRegionId + "~" + testId;   //三个主键构成唯一标识符：一条数据：key
			    //检测details中有无#Cat?
			    int result = details.indexOf("#Cat");                           //details中有没有#Cat
			    if (result != -1) {                                             //有的话，result不等于-1
					char position = details.charAt(result + 4);                 //#Cat的位置是result，加4越过#Cat，?是多少存入position
					String catnum = String.valueOf(position);                   //把position转成需要的类型
					key = key + "~" + catnum;                                   //加上#Cat?，四个主键组成key
				}
			    
			    if (key.equals(keyCopy)) {                                      //新识别的key和上一条识别的keyCopy是否相等
					map.put(key, i);                                            //计数放到firstMap中
				}else {                                                         //不等
					i = 1;                                                      //从1开始计数
					map.put(key, i);                                            //计数放到firstMap中
				}
			    
			    keyCopy = key;                                                  //识别出来的key赋值给keyCopy，再比较
			    i++;                                                            //累加计数
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
		} finally {
			try {
				if(conn != null)
					conn.close();
			} catch(SQLException e) {
				System.err.println(e);
			}
		}
		return map;
	}
	
	//插入方法
public static void insert() throws ClassNotFoundException, SQLException{
        try {
        	String key,productId,updateRegionId,testId,Cat = "",changeSituation,changePercent = "";
        	Integer oldNum,newNum;
        	String sql = "insert into presentation(productId,updateRegionId,testId,Cat,thirty_first_August,seventeenth_September,changeSituation,changeNum,changePercent) values(?,?,?,?,?,?,?,?,?)";
            conn = DriverManager.getConnection("jdbc:sqlite:presentation.sq3");
            conn.setAutoCommit(false);         //不自动提交
            ppsm = conn.prepareStatement(sql); //批量提交
            String[] keys;
         // 对firstMap遍历
    		for (Entry<String, Integer> entry1 : firstMap.entrySet()) {
    			
    			key = entry1.getKey();
    			oldNum = firstMap.get(entry1.getKey());           //变动前的数目，即firstMap中的计数
    			//将key中的值切出来放入变量中插库
    			keys = key.split("~");
    			
    			productId = keys[0];
    			updateRegionId = keys[1];
    			testId = keys[2];
    			//有无#Cat?
    			if (keys.length >= 4) {
					Cat = keys[3];
				}else {
					Cat = "";
				}
    			
    			//净减少
    			// 使用map中的containsKey()方法“不包含”解决数据净减少的情况
    			if (!secondMap.containsKey(entry1.getKey())) {
//    				System.out.println(entry1.getKey() + "减少：" + -firstMap.get(entry1.getKey()));
    				ppsm.setString(1, productId);   //1是指sql语句的第一个问号占位符 
    				ppsm.setString(2, updateRegionId);
    				ppsm.setString(3, testId);
    				ppsm.setString(4, Cat);
    				ppsm.setString(5, "");
    				ppsm.setString(6, "");
    				ppsm.setString(7, "减少");
    				ppsm.setInt(8, oldNum);
    				ppsm.setString(9, "");
    				
    				ppsm.addBatch();    //添加一次预定义参数 
    			} else {
    				// “包含”则解决同一条数据增加减少多少的情况
    				oldNum = firstMap.get(key);
    				newNum = secondMap.get(key);
    				int num = secondMap.get(key) - firstMap.get(key);
//    				System.out.println(entry1.getKey() + "变动" + num);
    				
    				//BigDecimal加减乘除计算
    				//变动率：保留两位小数
    				BigDecimal bg = new BigDecimal(Double.valueOf(num)*100/Double.valueOf(oldNum)).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, oldNum);
                     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)) {
//    				System.out.println(entry2.getKey() + "增加：" + +secondMap.get(entry2.getKey()));
    				newNum = secondMap.get(key);
    				 ppsm.setString(1, productId);
                     ppsm.setString(2, updateRegionId);
                     ppsm.setString(3, testId);
                     ppsm.setString(4, Cat);
                     ppsm.setString(5, "");
                     ppsm.setString(6, "");
                     ppsm.setString(7, "增加");
                     ppsm.setInt(8, newNum);
                     ppsm.setString(9, "");
                     ppsm.addBatch();
    			}
    		}
         //批量执行上面的语句，全部提交
    	    ppsm.executeBatch(); //批量执行预定义SQL，即把以上添加到批处理命令中的所有命令一次过提交给数据库来执行
    	 //Commit it 咽下，到肚子(DB)里面
            conn.commit();//前面设置connection.setautocommit(false);只有程序调用connection.commit()的时候才会将先前执行的语句一起提交到数据库
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
        	if(ppsm != null) {
        		try {
					ppsm.close();
				} catch (SQLException e) {
					e.printStackTrace();
		            throw new RuntimeException(e);
				}
        	}
                if(conn != null) {
                	try {
                		conn.close();
					} catch (SQLException e) {
						e.printStackTrace();
			            throw new RuntimeException(e);
					}
        }
    }
}
	//创建新表
	public static void createnewtable() throws ClassNotFoundException, SQLException{
		Class.forName("org.sqlite.JDBC");
	    Connection conn = null;
	    try {
	        conn = DriverManager.getConnection("jdbc:sqlite:presentation.sq3");
	        Statement statement = conn.createStatement();
	        statement.setQueryTimeout(30);
	        //创建新表
	        statement.executeUpdate("drop table if exists presentation");
	        statement.executeUpdate("create table presentation (productId string, updateRegionId string, testId string, Cat string, thirty_first_August int, seventeenth_September int, changeSituation string, changeNum int, changePercent string)");
	    } catch (SQLException e) {
	        System.err.println(e.getMessage());
	    } finally {
	        try {
	            if(conn != null)
	                conn.close();
	        } catch(SQLException e) {
	            System.err.println(e);
	        }
	    }
	}
	}