数据驱动测试的核心是：测试数据与测试脚本分离，实现测试脚本参数化，提高测试脚本的可重用性。在自动化功能测试中如果灵活使用数据源与测试脚本，便能轻松创建与运行成百上千个测试用例。自动化测试框架必须要有与电子表格、文本文件、数据库集成的能力。
TestNG
TestNG是受JUnit和NUnit测试框架的启发而设计的，但引其中入一些新的功能，使它更强大和更容易使用。(JUnit和NUnit我没用过，不做评价)
Apache POI
Apache POI项目的使命是开发和维护各种基于Office Open XML 标准(OOXML)和微软文档格式的Java api，使用Apache POI，可以方便读写微软EXCEL、word、ppt等文档。
怎么做：
第一步：工具准备
第二步：创建一个登录接口测试用例
第三步：用Excel创建测试数据
第四步：使用Apache POI打开与读取Excel数据
第五步：创建TestNg测试用例并使用Data Provider从Excel读取数据
第六步：根据测试用例名称运行测试
第一步：工具准备
1.OS：win10 家庭中文版
2.JDK:jdk-8u66-windows-x64.exe
3.Eclipse:eclipse-inst-win64.exe
4.TestNG插件
5.HttpClient:httpcomponents-client-4.5.2-bin.zip
6.Apache POI:poi-bin-3.14.zip
第二步：创建一个查询接口测试用例
1.创建一个TestNG类：DataProviderTest
2.引用注释Dataprovider,这个方法会返回对象数组
3.准备两条数据，一条url，一条POST的json参数
4.在@Test下写一个查询测试用例
| package testData; import java.io.IOException; import org.apache.http.HttpEntity; import org.apache.http.client.ClientProtocolException; import org.apache.http.client.methods.CloseableHttpResponse; import org.apache.http.client.methods.HttpGet; import org.apache.http.client.methods.HttpPost; import org.apache.http.entity.StringEntity; import org.apache.http.impl.client.CloseableHttpClient; import org.apache.http.impl.client.HttpClients; import org.apache.http.util.EntityUtils; import org.json.JSONException; import org.json.JSONObject; import org.testng.annotations.DataProvider; import org.testng.annotations.Test; public class DataProviderTest { @DataProvider(name="Authentication") public static Object[][] credentials(){ String p1="http://192.168.1.55:8182/ebe/api/query_server/v1/query"; String pd="{'sqlText':'select * from ts','pageSize':'100','pageNum':'1'}"; return new Object[][] {{p1,pd}}; } @Test(dataProvider="Authentication") public void QuickStart(String p1,String pd) throws Exception { CloseableHttpClient httpclient = HttpClients.createDefault(); try { HttpPost httpPost = new HttpPost(p1); JSONObject jsonParam=new JSONObject(pd); StringEntity entity = new StringEntity(jsonParam.toString(),"utf-8"); entity.setContentEncoding("UTF-8"); entity.setContentType("application/json"); httpPost.setEntity(entity); CloseableHttpResponse response2 = httpclient.execute(httpPost); try { System.out.println(response2.getStatusLine()); HttpEntity entity2 = response2.getEntity(); System.out.println("Response content: " + EntityUtils.toString(entity2)); // do something useful with the response body // and ensure it is fully consumed EntityUtils.consume(entity2); } finally { response2.close(); } } finally { httpclient.close(); } } } |
1.在该项目下新建一个testdata.xlxs文件，并写入内容
测试数据.png第四步：使用Apache POI打开与读取Excel数据
1.我们需要从Excel中读取测试数据，所以这里就是用Apache POI对Excel进行操作。
| package testData; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class ExcelUtils { private static XSSFSheet ExcelWSheet; private static XSSFWorkbook ExcelWBook; private static XSSFCell Cell; private static XSSFRow Row; public static Object[][] getTableArray(String FilePath, String SheetName) throws Exception { String[][] tabArray = null; try { FileInputStream ExcelFile = new FileInputStream(FilePath); // Access the required test data sheet ExcelWBook = new XSSFWorkbook(ExcelFile); ExcelWSheet = ExcelWBook.getSheet(SheetName); int startRow = 1; int startCol = 1; int ci,cj; int totalRows = ExcelWSheet.getLastRowNum(); System.out.println(totalRows); // you can write a function as well to get Column count int totalCols = 2; System.out.println(totalCols); tabArray=new String[totalRows][totalCols]; ci=0; for (int i=startRow;i<=totalRows;i++, ci++) { cj=0; for (int j=startCol;j<=totalCols;j++, cj++){ tabArray[ci][cj]=getCellData(i,j); System.out.println(tabArray[ci][cj]); } } } catch (FileNotFoundException e){ System.out.println("Could not read the Excel sheet"); e.printStackTrace(); } catch (IOException e){ System.out.println("Could not read the Excel sheet"); e.printStackTrace(); } return(tabArray); } public static String getCellData(int RowNum, int ColNum) throws Exception { try{ Cell = ExcelWSheet.getRow(RowNum).getCell(ColNum); int dataType = Cell.getCellType(); if (dataType == 3) { return ""; } else{ String CellData = Cell.getStringCellValue(); return CellData; } } catch (Exception e){ System.out.println(e.getMessage()); throw (e); } } } |
1.创建TestNG类：DataProviderWithExcel,从excel中导入数据，并执行query操作
| package testData; import org.apache.http.HttpEntity; import org.apache.http.client.methods.CloseableHttpResponse; import org.apache.http.client.methods.HttpPost; import org.apache.http.entity.StringEntity; import org.apache.http.impl.client.CloseableHttpClient; import org.apache.http.impl.client.HttpClients; import org.apache.http.util.EntityUtils; import org.json.JSONObject; import org.testng.annotations.DataProvider; import org.testng.annotations.Test; public class DataProviderWithExcel { @DataProvider public Object[][] Authentication() throws Exception{ Object[][] testObjArray=ExcelUtils.getTableArray("F://Users//Tangxi//workspace//apiTest//src//testData//testData.xlsx", "Sheet1"); return (testObjArray); } @Test(dataProvider="Authentication") public void QuickStart(String p1,String pd) throws Exception { CloseableHttpClient httpclient = HttpClients.createDefault(); try { HttpPost httpPost = new HttpPost(p1); JSONObject jsonParam=new JSONObject(pd); StringEntity entity = new StringEntity(jsonParam.toString(),"utf-8"); entity.setContentEncoding("UTF-8"); entity.setContentType("application/json"); httpPost.setEntity(entity); CloseableHttpResponse response2 = httpclient.execute(httpPost); try { System.out.println(response2.getStatusLine()); HttpEntity entity2 = response2.getEntity(); System.out.println("Response content: " + EntityUtils.toString(entity2)); // do something useful with the response body // and ensure it is fully consumed EntityUtils.consume(entity2); } finally { response2.close(); } } finally { httpclient.close(); } } } |
| package testData; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class ExcelUtils2 { private static XSSFSheet ExcelWSheet; private static XSSFWorkbook ExcelWBook; private static XSSFCell Cell; private static XSSFRow Row; //This method is to set the File path and to open the Excel file, Pass Excel Path and Sheetname as Arguments to this method public static void setExcelFile(String Path,String SheetName) throws Exception { try { // Open the Excel file FileInputStream ExcelFile = new FileInputStream(Path); // Access the required test data sheet ExcelWBook = new XSSFWorkbook(ExcelFile); ExcelWSheet = ExcelWBook.getSheet(SheetName); } catch (Exception e){ throw (e); } } public static Object[][] getTableArray(String FilePath, String SheetName, int iTestCaseRow) throws Exception { String[][] tabArray = null; try{ FileInputStream ExcelFile = new FileInputStream(FilePath); // Access the required test data sheet ExcelWBook = new XSSFWorkbook(ExcelFile); ExcelWSheet = ExcelWBook.getSheet(SheetName); int startCol = 1; int ci=0,cj=0; int totalRows = 1; int totalCols = 2; tabArray=new String[totalRows][totalCols]; for (int j=startCol;j<=totalCols;j++, cj++) { tabArray[ci][cj]=getCellData(iTestCaseRow,j); System.out.println(tabArray[ci][cj]); } } catch (FileNotFoundException e) { System.out.println("Could not read the Excel sheet"); e.printStackTrace(); } catch (IOException e) { System.out.println("Could not read the Excel sheet"); e.printStackTrace(); } return(tabArray); } //This method is to read the test data from the Excel cell, in this we are passing parameters as Row num and Col num public static String getCellData(int RowNum, int ColNum) throws Exception{ try{ Cell = ExcelWSheet.getRow(RowNum).getCell(ColNum); String CellData = Cell.getStringCellValue(); return CellData; }catch (Exception e){ return""; } } public static String getTestCaseName(String sTestCase)throws Exception{ String value = sTestCase; try{ int posi = value.indexOf("@"); value = value.substring(0, posi); posi = value.lastIndexOf("."); value = value.substring(posi + 1); System.out.println(value); return value; }catch (Exception e){ throw (e); } } public static int getRowContains(String sTestCaseName, int colNum) throws Exception{ int i; try { int rowCount = ExcelUtils2.getRowUsed(); for ( i=0 ; i |
1.获取测试用例名称
2.根据测试用例名称获取对应的行号
3.从对应的行中获取测试数据
| package testData; import java.util.concurrent.TimeUnit; import org.testng.annotations.Test; import org.testng.annotations.BeforeMethod; import org.apache.http.HttpEntity; import org.apache.http.client.methods.CloseableHttpResponse; import org.apache.http.client.methods.HttpPost; import org.apache.http.entity.StringEntity; import org.apache.http.impl.client.CloseableHttpClient; import org.apache.http.impl.client.HttpClients; import org.apache.http.util.EntityUtils; import org.json.JSONObject; import org.testng.annotations.AfterMethod; import org.testng.annotations.DataProvider; import testData.ExcelUtils2; public class DataProviderWithExcel_002 { private String sTestCaseName; private int iTestCaseRow; @Test(dataProvider = "Authentication") public void QuickStart(String p1,String pd) throws Exception { CloseableHttpClient httpclient = HttpClients.createDefault(); try { HttpPost httpPost = new HttpPost(p1); JSONObject jsonParam=new JSONObject(pd); StringEntity entity = new StringEntity(jsonParam.toString(),"utf-8"); entity.setContentEncoding("UTF-8"); entity.setContentType("application/json"); httpPost.setEntity(entity); CloseableHttpResponse response2 = httpclient.execute(httpPost); try { System.out.println(response2.getStatusLine()); HttpEntity entity2 = response2.getEntity(); System.out.println("Response content: " + EntityUtils.toString(entity2)); // do something useful with the response body // and ensure it is fully consumed EntityUtils.consume(entity2); } finally { response2.close(); } } finally { httpclient.close(); } } @DataProvider public Object[][] Authentication() throws Exception{ // Setting up the Test Data Excel file ExcelUtils2.setExcelFile("F://Users//Tangxi//workspace//apiTest//src//testData//testData.xlsx","Sheet1"); sTestCaseName = this.toString(); // From above method we get long test case name including package and class name etc. // The below method will refine your test case name, exactly the name use have used sTestCaseName = ExcelUtils2.getTestCaseName(this.toString()); System.out.println(sTestCaseName); // Fetching the Test Case row number from the Test Data Sheet // Getting the Test Case name to get the TestCase row from the Test Data Excel sheet iTestCaseRow = ExcelUtils2.getRowContains(sTestCaseName,0); System.out.println(iTestCaseRow); Object[][] testObjArray = ExcelUtils2.getTableArray("F://Users//Tangxi//workspace//apiTest//src//testData//testData.xlsx","Sheet1",iTestCaseRow); return (testObjArray); } } |
