前言:之前详解过通过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.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("<httpSample")==true||line.contains("<sample t=")==true) { 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<InterfaceNameArray.size();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<String> 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数据展示:
详情表:
统计表: