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);
	        }
	    }
	}
	}

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值