jmeter导入DB数据再优化

由于同一个迭代中每天都在执行。之前设计的思路是同个迭代只执行一次插入DB操作！！

因而没有在插入数据前没有做版本、产品类型、页面类型、接口名、接口名是否相等判断操作。

因此，若是这些条件相等，数据不是更新，而是插入，导致一个接口在同一个迭代中出现多次。

 

现如今做了优化，解决上个版本的问题，对版本、产品类型、页面类型、接口名、接口名做联合唯一索引。

当这个key相等时，做更新操作。若key不相等，就做插入操作。

具体改造如下：

详细表：

创建操作：

  String sql = "create table if NOT EXISTS AutoTest_DetailInterface(id int NOT NULL auto_increment primary key ,productType varchar(50) ,pageType varchar(50),interfaceName varchar(50),caseName varchar(50) ,excVersion varchar(50),excTerminal varchar(50) NOT  NULL DEFAULT 'App' ,excResult varchar(50),creatTime timestamp NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE INDEX  ( productType,pageType,interfaceName,caseName,excVersion,excTerminal ) )";  

插入操作:

 if (result != -1) {  
                     sql = "insert into AutoTest_DetailInterface(productType,pageType,interfaceName,caseName,excVersion,excTerminal,excResult) values(?,?,?,?,?,?,?) ON DUPLICATE KEY UPDATE excResult=?,creatTime= CURRENT_TIMESTAMP";
//                     sql = "insert into AutoTest_DetailInterface (permaryTitle,secondaryTitle,excVersion,excTerminal,excResult) values(primaryTitle,secordaryTitle,excVersion,excTerminal,excResult)";
                     st = conn.prepareStatement(sql);
                     st.setString(1, productType);
                    st.setString(2, pageType);
                     st.setString(3, interfaceName);
                     st.setString(4, caseName);
                     st.setString(5, excVersion);
                     //存入终端类型转为小写
                     st.setString(6, excTerminal.toLowerCase());
                     //存入执行结果true或者false转为小写
                     st.setString(7, excResult.toLowerCase());
                     st.setString(8, excResult.toLowerCase());
                     st.executeUpdate();
                     sql = "SELECT * FROM AutoTest_DetailInterface";  
                     System.out.println(stmt.executeQuery(sql));
                     ResultSet rs = stmt.executeQuery(sql);
                     System.out.println("id\tproductType\tpageType\tinterfaceName\tcaseName\tsexcVersion\texcTerminal\texcResult\tcreatTime"); 
                     while (rs.next()) {  
                         System.out.println(rs.getString(1) + "\t" + rs.getString(2) + "\t" + rs.getString(3) + "\t" + rs.getString(4) + "\t" + rs.getString(5) + "\t" + rs.getString(6) + "\t" + rs.getString(7)+ rs.getString(8)+ rs.getString(9));  
                    }  
                 }  

 

统计表：

创建操作：

 String sql = "create table if NOT EXISTS AutoTest_TotalInterface(id int NOT NULL auto_increment primary key ,productType varchar(50) ,pageType varchar(50),interfaceName varchar(50) ,excVersion varchar(50),excTerminal varchar(50) NOT  NULL DEFAULT 'App' ,excRate varchar(50) ,caseTotalNum int,caseSucNum int,creatTime timestamp NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE INDEX  ( productType,pageType,interfaceName,excVersion,excTerminal ) )";

插入操作：

if (result != -1) {  
                    sql = "insert into AutoTest_TotalInterface(productType,pageType,interfaceName,excVersion,excTerminal,excRate,caseTotalNum,caseSucNum) values(?,?,?,?,?,?,?,?) ON DUPLICATE KEY UPDATE excRate=?,caseTotalNum=?,caseSucNum=?,creatTime= CURRENT_TIMESTAMP";
                    st = conn.prepareStatement(sql);
                    st.setString(1, productType);
                    st.setString(2, pageType);
                    st.setString(3, interfaceName);
                    st.setString(4, excVersion);
                    //存入终端类型转为小写
                    st.setString(5, excTerminal.toLowerCase());
                    st.setString(6, excRate);
                    st.setInt(7, caseTotalNum);
                    st.setInt(8, caseSucNum);
                    st.setString(9, excRate);
                    st.setInt(10, caseTotalNum);
                    st.setInt(11, caseSucNum);
                    st.executeUpdate();
                    sql = "SELECT * FROM AutoTest_TotalInterface";  
                    System.out.println(stmt.executeQuery(sql));
                    ResultSet rs = stmt.executeQuery(sql);
                    System.out.println("id\tproductType\tpageType\tinterfaceName\tsexcVersion\texcTerminal\texcRate\tcaseTotalNum\tcaseSucNum\tcreatTime"); 
                    while (rs.next()) {  
                         System.out.println(rs.getString(1) + "\t" + rs.getString(2) + "\t" + rs.getString(3) + "\t" + rs.getString(4) + "\t" + rs.getString(5) + "\t" + rs.getString(6) + "\t" + rs.getString(7)+ "\t" + rs.getString(8) + rs.getString(9) + rs.getString(10));  
                    }  
                }