本文介绍了如何使用Python解析JMeter生成的jtl文件，并将其数据集成到Ant-JMeter任务中，直接在持续集成流程中插入数据库。解析过程类似于解析javamail，主要涉及两个步骤：1. 使用Java JDBC连接和SQL操作插入详情表和统计表。2. 拆分jtl文件内容，获取插入数据库所需的参数，进行数据统计计算。
第一：需独立创建一个job
第二：需按照一定规范输出
因此，放弃解析csv方式，直接解析自动化生成的原始jtl文件并集成到ant-jmeter中，不在独立一个job而是跟项目在一起，直接在持续集成过程中insert DB。
解析jtl文件与前面介绍的javamail思路相同(若想了解 http://www.cnblogs.com/nzg-noway/p/6909821.html )
insert DB依然是2张表,详情表和统计表
Step1：具体insert操作就是jdbc链接和sql操作。如下：
注意：
1.把jdbc配置项拉取到配置文件，支持灵活可变
2.创建表结构(根据业务创建索引、唯一等)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134package 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(){
//设置日期格式
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
// new Date()为获取当前系统时间
String now = df.format(new Date());
return now;
}
//详情数据
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 = "xxxxxx";
// String connUrl = "jdbc:mysql://10.64.66.227:3306/";
Connection conn = DriverManager.getConnection(connUrl + databaseName, userName, password);
PreparedStatement st = null;
String currTime = InsertDB.currTime();
Statement stmt = conn.createStatement();
// 创建数据库中的表，
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创建表语句："+sql);
int result = stmt.executeUpdate(sql);
if (result != -1) {
sql = "insert into Auto_Detail(BuildNum,Module,InterfaceName,CaseName,Result) values(?,?,?,?,?) "
+ "ON DUPLICATE KEY UPDATE Result=?";
st = conn.prepareStatement(sql);
st.setString(1, BuildNum);
//存入过程大写转小写
st.setString(2, Module.toLowerCase());
st.setString(3, InterfaceName.toLowerCase());
st.setString(4, CaseName.toLowerCase());
//存入执行结果true或者false转为小写
st.setString(5, Result.toLowerCase());
st.setString(6, 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();
}
}
//统计数据
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 = "xxxxxx";
// String connUrl = "jdbc:mysql://10.64.66.227:3306/";
Connection conn = DriverManager.getConnection(connUrl + databaseName, userName, password);
PreparedStatement st = null;
String currTime = InsertDB.currTime();
Statement stmt = conn.createStatement();
// 创建数据库中的表，
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创建表语句："+sql);
int result = stmt.executeUpdate(sql);
if (result != -1) {
sql = "insert into Auto_Statistics(BuildNum,Module,CaseNum,FailNum,Rate) values(?,?,?,?,?) "
+ "ON DUPLICATE KEY UPDATE CaseNum=?,FailNum=?,Rate=?";
// System.out.println("查看统计sql："+sql);
st = conn.prepareStatement(sql);
st.setString(1, BuildNum);
//存入过程大写转小写
st.setString(2, Module.toLowerCase());
st.setInt(3, CaseNum);
st.setInt(4, FailNum);
st.setString(5, Rate);
st.setInt(6, CaseNum);
st.setInt(7, FailNum);
st.setString(8, 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","xxxxxx","jdbc:mysql://10.64.66.227:3306/");
}
}
step2：拆分和获取insert DB的参数
注意：
1.命名规范(满足module和interface，由于统计jdbc请求因此，module和table也可以)
2.参数拆分和获取依据
3.数据统计计算
4.由于业务方面buildNum需要，因此参数值获取时需要把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执行的入口 */
public void execute() throws BuildException {
System.out.println("开始执行插入数据库task");
resultLogFile = new File(System.getProperty("user.dir")+resultLog); if (resultLogFile.exists()){
System.out.println("开始解析数据插入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 ModuleArray = new ArrayList();
List InterfaceNameArray = new ArrayList();
List CaseNameArray = new ArrayList();
List ResultArray = new ArrayList();
HashMap data = new HashMap();
DecimalFormat df = new DecimalFormat("0.00");
try {
FileInputStream fis = new FileInputStream(System.getProperty("user.dir")+resultLog);
InputStreamReader isr = new InputStreamReader(fis, "UTF-8");
BufferedReader br = new BufferedReader(isr);
String line = null; while ((line = br.readLine()) != null) { if (line.contains("
fullTitile = line.split("\"")[13];
Result = line.split("\"")[11];
ResultArray.add(Result); //获取构建版本号
if(fullTitile.contains("BuildNum")==true){
BuildNum = fullTitile.substring(fullTitile.toLowerCase().indexOf("module")+6,fullTitile.indexOf("BuildNum")).trim();
} if(fullTitile.toLowerCase().contains("module")==true){
Module = fullTitile.substring(0,fullTitile.toLowerCase().indexOf("module")).trim();
ModuleArray.add(Module); if(fullTitile.toLowerCase().contains("interface")==true){
InterfaceName_1 = fullTitile.substring(fullTitile.toLowerCase().indexOf("module")+6,fullTitile.toLowerCase().indexOf("interface")).trim(); if(InterfaceName_1.contains("BuildNum")==true){
InterfaceName = InterfaceName_1.substring(InterfaceName_1.indexOf("BuildNum")+8);
}else {
InterfaceName = InterfaceName_1;
}// System.out.println("输出接口名称："+InterfaceName);
CaseName = fullTitile.substring(fullTitile.toLowerCase().indexOf("interface")+9).trim();// System.out.println("输出用例名称："+CaseName);
}else if(fullTitile.toLowerCase().contains("table")==true){
InterfaceName = fullTitile.substring(fullTitile.toLowerCase().indexOf("module")+6,fullTitile.indexOf("table")).trim();
CaseName = fullTitile.substring(fullTitile.toLowerCase().indexOf("table")+5).trim();
}
InterfaceNameArray.add(InterfaceName);
CaseNameArray.add(CaseName);
if(data.get(Module) == null){
Entity entity = new Entity(); if (line.indexOf(" s=\"true\"") !=-1) {
entity.successNum = 1;
}else{
entity.failNum = 1;
}
data.put(Module, entity);
}else{
Entity entity = data.get(Module); if (line.indexOf(" s=\"true\"") !=-1) {
entity.successNum += 1;
}else{
entity.failNum += 1;
}
data.put(Module, entity);
}
}
}
}// System.out.println("ModuleArray名称为："+ModuleArray+ModuleArray.size());// System.out.println("InterfaceNameArray名称为："+InterfaceNameArray+InterfaceNameArray.size());// System.out.println("CaseNameArray名称为："+CaseNameArray+CaseNameArray.size());// System.out.println("ResultArray名称为："+ResultArray+ResultArray.size());
System.out.println("构建日期为："+InsertDB.currTime());
System.out.println("构建版本为："+BuildNum); for(int i=0;i
Module=ModuleArray.get(i);
InterfaceName=InterfaceNameArray.get(i);
CaseName=CaseNameArray.get(i);
Result=ResultArray.get(i);
InsertDB.insertDetailDB(BuildNum, Module, InterfaceName, CaseName, Result,databaseName,userName,password,connUrl);
}
System.out.println("场景详情数据插入DB成功");
Iterator iterator = data.keySet().iterator(); while(iterator.hasNext()){
Module = iterator.next();
Entity entity = data.get(Module);
CaseTotalNum =entity.successNum+entity.failNum;
FailNumber = entity.failNum; if(CaseTotalNum == 0){
rate = "0";
}else {
rate = df.format((float)entity.successNum/(float)CaseTotalNum*100);
}// System.out.println("统计参数字段：版本"+BuildNum+"模块："+Module+"用例数："+CaseTotalNum+"失败数："+FailNumber+"通过率："+rate+"%");
InsertDB.insertStatisticsDB(BuildNum, Module, CaseTotalNum,FailNumber, rate+"%", databaseName,userName,password,connUrl);
}
System.out.println("场景统计数据插入DB成功");
br.close();
isr.close();
fis.close();
}catch (IOException e) { throw new BuildException("Could not read jmeter resultLog: " + e.getMessage());
}
} //测试
public static void main(String[] args) throws MessagingException, UnsupportedEncodingException {
DBTask mt = new DBTask();
mt.setResultLog("/InterfaceAutoTestReport.jtl");
mt.execute();
}
}
下面是我的命名规范(大家根据自己不同业务不需求进行优化和获取)
好了，开发到此结束，接下来就是在配置文件中配置jdbc
在target name增加一个insertDB
需要依赖包：mysql-jdbc放到ant的lib目录下
成果展示:
jenkins持续集成日志：
DB数据展示：
详情表：
统计表：
扫一扫
827
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
