jmeter执行完多个请求后,检查数据库结果是否符合预期,可以用以下方式直接生成比对结果文件,比对excel中用例的预期结果同数据库中的实际结果。
1.相关jar下载
链接: https://pan.baidu.com/s/1RuzmdAMOc_Th1QZ1pQUokQ 提取码: npss
a.下载相关jar包下载加入jmeter\apache-jmeter-3.1\lib\ext下。
b.所有涉及jar包也添加到这里。
2.使用
所有参数换成自己的。一定要加debug Sampler(任何请求都行)。
参数int sheetRes预期结果从0开始计数
import jp.jd.excel.*;
log.info("---------start----------- : "+"\n");
// 数据库连接地址
String url = "jdbc:mysql://1.1.1.224:3306/jp_test?useUnicode=true&characterEncoding=UTF-8";
// 数据库的用户名
String userName = "root";
// 数据库的密码
String password = "133333333";
//执行sql
String sql = "SELECT stunum,recnum,validtime,reason,lastinstancetime,recheckreason FROM `buss_rec_period` ORDER BY recnum ASC;";
String prefile = "E:\\1.测试步骤.xls";
String resfile = "E:\\比对结果\\";
String[] ftitles = "stunum,recnum,validtime,reason,lastinstancetime,recheckreason".split(",");
int sheetRes = 3;
try {
CompareResult.getCompareResult(url,userName,password,sql,prefile,resfile,ftitles,sheetRes);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
log.info("---------end-----------"+"\n");
3.预期结果excel如下:第一行写标题(需与数据库比对字段数量一致),其余写预期比对项。
执行完成后结果文件如下:
4.excel_dz.jar----自定义jar包主要代码实现,也可自定义jar包,生成自己想要的结果比对文件
a.预期结果与数据库字段比对代码
package jp.jd.excel;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import jp.dz.sql.SqlData;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.WriteException;
public class CompareResult {
/**
* @param url 数据库地址
* @param userName 数据库用户名
* @param password 数据库密码
* @param sql 要比对的sql字段查询语句
* @param prefile 预期结果excel文件路径,excel预期文件字段与数据库查询字段保持一致
* @param resfile 比对结果文件写入路径
* @param ftitles 与数据库查询字段保持一致,标题样式
* @param sheetRes 预期结果excel在第几个sheet
* @throws WriteException
* @throws IOException
*/
public static void getCompareResult(String url,String userName,String password,String sql,String prefile,String resfile,String[] ftitles,int sheetRes) throws WriteException, IOException {
JdOutputFile cw = new JdOutputFile();
File file = new File(prefile);
try{
FileInputStream fis = new FileInputStream(file);
StringBuilder sb = new StringBuilder();
jxl.Workbook rwb = Workbook.getWorkbook(fis);
Sheet[] sheet = rwb.getSheets();// 得到多个sheet
Sheet rs = rwb.getSheet(sheetRes); // 第几张sheet
// 获得标题
Cell[] titleCell = rs.getRow(0);
ArrayList arrayListTitles = new ArrayList();
for (int i = 0; i < titleCell.length; i++) {
String title = titleCell[i].getContents().toString();
arrayListTitles.add("预期"+title);
}
for (int i = 0; i < titleCell.length; i++) {
String title = titleCell[i].getContents().toString();
arrayListTitles.add("实际"+title);
}
arrayListTitles.add("结果记录");
//比对结果写入路径
String filepath = cw.cOutputFile(resfile,arrayListTitles);
//获取数据库数据
ArrayList arrayListF = SqlData.Select(url,userName,password,sql,ftitles);
//excel与数据库一行一行比对
for (int j = 1; j < rs.getRows(); j++) {
Cell[] cells = rs.getRow(j);
ArrayList arrayListA = new ArrayList();
for (int i = 0; i < cells.length; i++) {
String cell = cells[i].getContents().toString().trim();
arrayListA.add(cell);
}
//实际结果与逾期比对并输出比对结果
cw.wOutputFile(filepath, arrayListA, (ArrayList)arrayListF.get(j-1));
}
fis.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
b.获取数据库字段相关代码
package jp.dz.sql;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
public class SqlData {
// 查询得到数据库信息
public static ArrayList Select(String URL,String userName,String Password,String sql,String[] ftitles) {
ArrayList arrayLists = new ArrayList();
Connection conn =null;
Statement st = null;
ResultSet set = null;
try {
// 获取连接
conn = DBHerpel.getConnection(URL,userName,Password);
if (conn == null) {return null;}
// 执行sql语句
st = conn.createStatement();
// 返回结果集
set = st.executeQuery(sql);
// 获取数据
while (set.next()) {
ArrayList hs = new ArrayList();
for (int i = 0; i < ftitles.length; i++) {
String temp = set.getString(ftitles[i])+"";
hs.add(temp);
}
arrayLists.add(hs);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
// 释放资源
try {
set.close();
conn.close();
} catch (Exception e2) {
// TODO: handle exception
}
}
return arrayLists;
}
}
数据库连接相关代码
package jp.dz.sql;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DBHerpel {
/**
* * @Description: TODO 获取访问数据库的Connection对象
* @param @return
* @return Connection 连接数据的对象
* @author 情绪i
*/
public static Connection getConnection(String URL,String UserName,String Password) {
try {
Class.forName("com.mysql.jdbc.Driver"); // 加载驱动
System.out.println("加载驱动成功!!!");
} catch (ClassNotFoundException e) {
// TODO: handle exception
e.printStackTrace();
}
try {
//通过DriverManager类的getConenction方法指定三个参数,连接数据库
Connection Conn = DriverManager.getConnection(URL, UserName, Password);
System.out.println("连接数据库成功!!!");
//返回连接对象
return Conn;
} catch (SQLException e) {
// TODO: handle exception
e.printStackTrace();
return null;
}
}
}
c.写入结果文件相关代码
package jp.jd.excel;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import jxl.Cell;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
/*
*导入jxl.jar;
*后续扩充功能,sheet2增加测试报告展现;------待实现;
*/
public class JdOutputFile {
/*
*
* wOutputFile方法写结果文件
*
* wOutputFile(文件路径,学员编号,课程编号,预期总有效时间,预期总需补时间,预期课堂时间,预期需补课堂,预期实车时间,预期需补实车,预期远程时间,预期需补远程,预期里程,预期需补里程,原因)
*
*/
public void wOutputFile(String filepath,ArrayList preResults,ArrayList freResults)
throws IOException,
RowsExceededException, WriteException, BiffException {
File output = new File(filepath);
String result = "";
InputStream instream = new FileInputStream(filepath);
Workbook readwb = Workbook.getWorkbook(instream);
WritableWorkbook wbook = Workbook.createWorkbook(output, readwb); // 根据文件创建一个操作对象
WritableSheet readsheet = wbook.getSheet(0);
// int rsColumns = readsheet.getColumns(); //获取Sheet表中所包含的总列数
int rsRows = readsheet.getRows(); // 获取Sheet表中所包含的总行数
/******************************** 字体样式设置 ****************************/
WritableFont font = new WritableFont(WritableFont.createFont("宋体"), 10,
WritableFont.NO_BOLD);// 字体样式
WritableCellFormat wcf= new WritableCellFormat(font);
/***********************************************************************/
Cell cell1 = readsheet.getCell(0, rsRows);
if (cell1.getContents().equals("")) {
ArrayList arrayList = new ArrayList<>();
int counts = preResults.size();
for (int i = 0; i < counts; i++) {
Label labetest1 = new Label(i, rsRows, (String) preResults.get(i));
Label labetest2 = new Label(counts+i, rsRows, (String) freResults.get(i));
arrayList.add(labetest1);
arrayList.add(labetest2);
}
Boolean isTrue = true;
for (int i = 0; i < counts; i++) {
String p = (String) preResults.get(i);
String f = (String) freResults.get(i);
if(!p.equals(f)) {
isTrue= false;
}
}
//初审判定
String record = "通过";
if (isTrue) {
record = "通过";
wcf.setBackground(Colour.BRIGHT_GREEN); // 通过案例标注绿色
} else {
record = "不通过";
wcf.setBackground(Colour.RED); // 不通过案例标注红色
}
Label labetest3 = new Label(counts*2, rsRows, record, wcf);
arrayList.add(labetest3);
for (int i = 0; i < arrayList.size(); i++) {
WritableCell w = (WritableCell) arrayList.get(i);
readsheet.addCell(w);
}
}
wbook.write();
wbook.close();
}
/*
*
* cOutputFile方法创建输出文件,传入参数为交易类型,如开户等;
*
* cOutputFile方法返回文件路径,作为wOutputFile的入参;
*
*/
public String cOutputFile(String tradeType,ArrayList titles) throws IOException, WriteException {
String temp_str = "";
Date dt = new Date();
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
temp_str = sdf.format(dt); // 获取时间戳
// 相对路径默认为 apache-jmeter-3.1\bin
String filepath = tradeType + "_output_" + "_" + temp_str + ".xls"; // 以时间戳命名结果文件,确保唯一
File output = new File(filepath);
if (!output.isFile()) {
output.createNewFile(); // 如果指定文件不存在,则新建该文件
WritableWorkbook writeBook = Workbook.createWorkbook(output);
WritableSheet Sheet = writeBook.createSheet("输出结果", 0); // createSheet(sheet名称,第几个sheet)
WritableFont headfont = new WritableFont(WritableFont.createFont("宋体"), 11, WritableFont.BOLD); // 字体样式
WritableCellFormat headwcf = new WritableCellFormat(headfont);
headwcf.setBackground(Colour.GRAY_25); // 灰色颜色
// 设置列宽度setColumnView(列号,宽度)
for (int i = 0; i < titles.size(); i++) {
if(i==0) {
Sheet.setColumnView(i, 17);
}else {
Sheet.setColumnView(i, 8);
}
}
headwcf.setAlignment(Alignment.CENTRE); // 设置文字居中对齐方式;
headwcf.setVerticalAlignment(VerticalAlignment.CENTRE); // 设置垂直居中;
// Label(列号,行号, 内容)
ArrayList arrayList = new ArrayList<>();
for (int i = 0; i < titles.size(); i++) {
Label lable = new Label(i, 0, (String) titles.get(i), headwcf);
arrayList.add(lable);
}
//lable加入sheet
for (int i = 0; i < arrayList.size(); i++) {
Label lable = (Label) arrayList.get(i);
Sheet.addCell(lable);
}
writeBook.write();
writeBook.close();
}
return filepath;
}
}