前言：之前詳解過通過jmeter生成的csv文件，解析csv存入DB，這個有弊端
第一：需獨立創(chuàng)建一個job
第二：需按照一定規(guī)范輸出
因此，放棄解析csv方式，直接解析自動化生成的原始jtl文件并集成到ant-jmeter中，不在獨立一個job而是跟項目在一起，直接在持續(xù)集成過程中insert DB。
解析jtl文件與前面介紹的javamail思路相同（若想了解 http://www.cnblogs.com/nzg-noway/p/6909821.html ）
insert DB依然是2張表,詳情表和統(tǒng)計表
Step1：具體insert操作就是jdbc鏈接和sql操作。如下：
注意：
1.把jdbc配置項拉取到配置文件，支持靈活可變
2.創(chuàng)建表結(jié)構(gòu)（根據(jù)業(yè)務(wù)創(chuàng)建索引、唯一等）
package org.programmerplanet.ant.taskdefs.jmeter;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.Statement;import java.text.SimpleDateFormat;import java.util.Date;public class InsertDB { public static String currTime(){ //設(shè)置日期格式
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd"); // new Date()為獲取當(dāng)前系統(tǒng)時間
String now = df.format(new Date()); return now;
}
//詳情數(shù)據(jù)
static public void insertDetailDB(String BuildNum,String Module,String InterfaceName,String CaseName,String Result,String databaseName,String userName, String password,String connUrl){ try {
Class.forName("com.mysql.jdbc.Driver");// String databaseName = "AutoResult";
// String userName = "root";
// String password = "trend520";// String connUrl = "jdbc:mysql://10.64.66.227:3306/";
Connection conn = DriverManager.getConnection(connUrl + databaseName, userName, password);
PreparedStatement st = null;
String currTime = InsertDB.currTime();// System.out.println("當(dāng)前詳情入庫時間："+currTime);
Statement stmt = conn.createStatement();
// 創(chuàng)建數(shù)據(jù)庫中的表，
String sql = "create table if NOT EXISTS Auto_Detail"
+ "(id int NOT NULL auto_increment primary key ,"
+ "BuildNum varchar(20) ,"
+ "Module varchar(100),"
+ "InterfaceName varchar(100),"
+ "CaseName varchar(100) ,"
+ "Result varchar(10),"
+ "CreateTime varchar(30) NOT NULL DEFAULT '"+currTime+"',"
+ " UNIQUE INDEX(BuildNum,Module,InterfaceName,CaseName,CreateTime))";// System.out.println("輸出sql創(chuàng)建表語句："+sql);
int result = stmt.executeUpdate(sql); if (result != -1) {
sql = "insert into Auto_Detail(BuildNum,Module,InterfaceName,CaseName,Result,CreateTime) values(?,?,?,?,?,?) "
+ "ON DUPLICATE KEY UPDATE Result=?";
st = conn.prepareStatement(sql);
st.setString(1, BuildNum); //存入過程大寫轉(zhuǎn)小寫
st.setString(2, Module.toLowerCase());
st.setString(3, InterfaceName.toLowerCase());
st.setString(4, CaseName.toLowerCase()); //存入執(zhí)行結(jié)果true或者false轉(zhuǎn)為小寫
st.setString(5, Result.toLowerCase());
st.setString(6, currTime);
st.setString(7, Result.toLowerCase());
st.executeUpdate();
sql = "SELECT * FROM Auto_Detail";
// System.out.println(stmt.executeQuery(sql));
ResultSet rs = stmt.executeQuery(sql);// System.out.println("id\tBuildNum\tModule\tInterfaceName\tCaseName\tResult\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)); }
}
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
//統(tǒng)計數(shù)據(jù)
static public void insertStatisticsDB(String BuildNum,String Module,int CaseNum,int FailNum,String Rate,String databaseName,String userName, String password,String connUrl){ try {
Class.forName("com.mysql.jdbc.Driver");// String databaseName = "AutoResult";
// String userName = "root";
// String password = "trend520";// String connUrl = "jdbc:mysql://10.64.66.227:3306/";
Connection conn = DriverManager.getConnection(connUrl + databaseName, userName, password);
PreparedStatement st = null;
String currTime = InsertDB.currTime();// System.out.println("當(dāng)前統(tǒng)計入庫時間："+currTime);
Statement stmt = conn.createStatement();
// 創(chuàng)建數(shù)據(jù)庫中的表，
String sql = "create table if NOT EXISTS Auto_Statistics"
+ "(id int NOT NULL auto_increment primary key ,"
+ "BuildNum varchar(20) ,"
+ "Module varchar(100),"
+ "CaseNum int,"
+ "FailNum int ,"
+ "Rate varchar(20),"
+ "CreateTime varchar(30) NOT NULL DEFAULT '"+currTime+"',"
+ " UNIQUE INDEX(BuildNum,Module,CreateTime))";// System.out.println("輸出sql創(chuàng)建表語句："+sql);
int result = stmt.executeUpdate(sql); if (result != -1) {
sql = "insert into Auto_Statistics(BuildNum,Module,CaseNum,FailNum,Rate,CreateTime) values(?,?,?,?,?,?) "
+ "ON DUPLICATE KEY UPDATE CaseNum=?,FailNum=?,Rate=?";// System.out.println("查看統(tǒng)計sql："+sql);
st = conn.prepareStatement(sql);
st.setString(1, BuildNum); //存入過程大寫轉(zhuǎn)小寫
st.setString(2, Module.toLowerCase());
st.setInt(3, CaseNum);
st.setInt(4, FailNum);
st.setString(5, Rate);
st.setString(6, currTime);
st.setInt(7, CaseNum);
st.setInt(8, FailNum);
st.setString(9, Rate);
st.executeUpdate();
sql = "SELECT * FROM Auto_Statistics";
// System.out.println(stmt.executeQuery(sql));
ResultSet rs = stmt.executeQuery(sql);// System.out.println("id\tBuildNum\tModule\tCaseNum\tFailNum\tRate\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)); }
}
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public static void main(String[] args){
insertDetailDB("9.8.1001", "test", "aaa", "bbb", "true","AutoResult","root","trend520","jdbc:mysql://10.64.66.227:3306/");
}
}
step2：拆分和獲取insert DB的參數(shù)
注意：
1.命名規(guī)范（滿足module和interface，由于統(tǒng)計jdbc請求因此，module和table也可以）
2.參數(shù)拆分和獲取依據(jù)
3.數(shù)據(jù)統(tǒng)計計算
4.由于業(yè)務(wù)方面buildNum需要，因此參數(shù)值獲取時需要把buildNum丟掉
package org.programmerplanet.ant.taskdefs.jmeter;import java.io.BufferedReader;import java.io.File;import java.io.FileInputStream;import java.io.IOException;import java.io.InputStreamReader;import javax.mail.MessagingException;import java.io.UnsupportedEncodingException;import java.text.DecimalFormat;import java.util.ArrayList;import java.util.HashMap;import java.util.Iterator;import java.util.List;import org.apache.tools.ant.BuildException;import org.apache.tools.ant.Task;public class DBTask extends Task{ private String resultLog=null;
File resultLogFile;//
private String databaseName=null; private String userName=null; private String password=null; private String connUrl=null; /* String databaseName = "AutoResult";
String userName = "root";
String password = "xxxxxx";
String connUrl = "jdbc:mysql://10.64.66.227:3306/";*/
//jtl文件
public void setResultLog(String resultLog) { this.resultLog = resultLog;
} public String getResultLog() { return resultLog;
}
public void setdatabaseName(String databaseName) { this.databaseName = databaseName;
} public String getdatabaseName() { return databaseName;
} public void setuserName(String userName) { this.userName = userName;
} public String getuserName() { return userName;
} public void setpassword(String password) { this.password = password;
} public String getpassword() { return password;
} public void setconnUrl(String connUrl) { this.connUrl = connUrl;
} public String getconnUrl() { return connUrl;
}
/**
* @see org.apache.tools.ant.Task#execute()
* task執(zhí)行的入口 */
public void execute() throws BuildException {
System.out.println("開始執(zhí)行插入數(shù)據(jù)庫task");
resultLogFile = new File(System.getProperty("user.dir")+resultLog); if (resultLogFile.exists()){
System.out.println("開始解析數(shù)據(jù)插入DBresultLog"); try {
analyseResultLog();
} catch (MessagingException e) {
e.printStackTrace();
}
}else{System.out.println("resultLog不存在，請檢查！");}
}
private class Entity { int successNum=0; int failNum=0;
}
private void analyseResultLog() throws BuildException, MessagingException {
String fullTitile= null;
String BuildNum= null;
String Module= null;
String InterfaceName= null;
String InterfaceName_1= null;
String CaseName= null;
String Result= null;
String rate= null; int CaseTotalNum=0; int FailNumber=0;
List<String> ModuleArray = new ArrayList<String>();
List<String> InterfaceNameArray = new ArrayList<String>();
List<String> CaseNameArray = new ArrayList<String>();
List<String> ResultArray = new ArrayList<String>();
HashMap<String,Entity> data = new HashMap<String,Entity>();
DecimalFormat df = new DecimalFormat("0.00http://www.cnblogs.com/nzg-noway/p/7095585.html
延伸閱讀
- .NET C#到Java沒那么難，DB篇 2017-07-25
- DB數(shù)據(jù)導(dǎo)出工具分享 2017-07-21
- jmeter 實現(xiàn)DB數(shù)據(jù)與接口數(shù)據(jù)的匹配校驗 2017-06-21
- Linux平臺 Oracle 10gR2（10.2.0.5）RAC安裝 Part3：db安裝和升級 2017-01-04
- DB監(jiān)控-Riak集群監(jiān)控 2016-11-07
.NET C#到Java沒那么難，DB篇