前言：之前详解过通过jmeter生成的csv文件，解析csv存入DB，这个有弊端
第一：需独立创建一个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.创建表结构（根据业务创建索引、唯一等）
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(){ //设置日期格式
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 = "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("当前详情入库时间："+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,CreateTime) 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, 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();
}
}
//统计数据
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("当前统计入库时间："+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,CreateTime) 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.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的参数
注意：
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<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 16:10:33.153
- DB数据导出工具分享 2017-07-21 15:49:45.977
- jmeter 实现DB数据与接口数据的匹配校验 2017-06-21 15:20:46.167
- Linux平台 Oracle 10gR2（10.2.0.5）RAC安装 Part3：db安装和升级 2017-01-04 10:45:58.35
- DB监控-Riak集群监控 2016-11-07 10:39:16.03
.NET C#到Java没那么难，DB篇