自动化框架创建

1.IDEA创建maven项目

1.file--->new--->project--->maven--->SDK(1.8)--->NEXT--->finish

2.配置maven,file--->setting---->maven

3.添加pom依赖

 <!-- testng-->
        <dependency>
            <groupId>org.testng</groupId>
            <artifactId>testng</artifactId>
            <version>6.8.8</version>
            <scope>test</scope>
        </dependency>
        <!-- log4j-->
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>
  <!-- 操作excel的依赖-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>
 <!-- 连接操作数据库-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>
        <!-- 解析初始化sql（json数组）-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.75</version>
        </dependency>

4.创建目录结构

需要将resource设置为root

5.配置文件

log4j.properties

### 根logger 设置###
log4j.rootLogger = INFO,console,file

### 输出信息到控制台 ###
log4j.appender.console = org.apache.log4j.ConsoleAppender
log4j.appender.console.Target = System.out
log4j.appender.console.layout = org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern = [%p] %d{HH:mm:ss} method: %l----%m%n

### 输出INFO级别以上的日志文件设置（因为根配置的是INFO） ###
log4j.appender.file = org.apache.log4j.DailyRollingFileAppender
log4j.appender.file.File = logs/testAuto.log
log4j.appender.file.Append = true
log4j.appender.file.layout = org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern = %d{HH:mm:ss} method: %l - [ %p ] ----%m%n

jdbc.properties

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://192.168.117.180:3309/gift?useUnicode=true&characterEncoding=utf-8&useSSL=true
jdbc.username=root
jdbc.password=test

在testngXML目录下创建testng.xml文件，指定测试类

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE suite SYSTEM "http://testng.org/testng-1.0.dtd">
<suite name="apiAutoTest" verbose="1">
    <parameter name="variableSheetName" value="variables"/>
    <test name="test" enabled="true">  <!--test必须有name属性-->
        <parameter name="excelPath" value="caseData/caseData.xlsx"/>
        <parameter name="dataSheetName" value="case"/>
        <classes>
            <class name="com.lizi.testcase.BaseCase"/>
        </classes>
    </test>
</suite>

testcase下创建BaseCase.java，测试方法是test

public class BaseCase {
    public static Logger logger = Logger.getLogger(BaseCase.class);

    @Test
    public void test(){
        System.out.println("hello");
        logger.info("test");
    }

}

caseData下caseData.xlsx文件

variables

Name	Value	Description
#{username}	lizi	用户名
#{password_correct}	123456	正确的密码
#{password_wrong}	1234567	错误的密码
#{productname}	thinkpad	商品名称

case

Describe	Url	RequestType	Headers	Cookies	Parameters	UploadFile	InitSql	GlobalVariables	AssertFields
注册	/demo/register	post	{"Content-Type":"application/json; charset=utf-8"}		{"name":"username"}		[{“sqlNo":"1","sql":"deleter from user where username = '#{username}',"}]		$.msg=注册成功

6.java类

1.实体类

public class CaseData {
    private String caseId;
    private String apiName;
    private String describe;
    private String url;
    private String requestType;
    private String headers;
    private String cookies;
    private String parameters;
    private String uploadFile;
    private String initSql;
    private String globalVariables;
    private String assertFields;

}



public class Variable {
    private String name;
    private String value;
    private String description;

}


public class Sql {

    private String sqlNo;
    private String sql;
}

2.工具类

ExcelUtil.java，读取excel文件中的数据

package com.lizi.utils;

import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.*;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.List;

/**
 * @ClassName: ExcelUtil
 * @Description:
 * @Author: lz.hu
 * @Date: 2024/3/4 16:42
 */
public class ExcelUtil {
    public static Logger logger = Logger.getLogger(ExcelUtil.class);

    // 解析指定excel表单的数据，封装到对象中【对象类型使用泛型】
    public static <T> List<T> loadExcel(String excelPath, String sheetName, Class<T> clazz){
        logger.info("===================开始读取sheet: " + sheetName);
        // 创建一个list
        List<T> list = new ArrayList<T>();
        InputStream in = null;
        // 创建WorkBook对象
        try {
            File file = new File(excelPath);
            in = new FileInputStream(file);
            // 获取workbook对象
            Workbook workbook = WorkbookFactory.create(in);
            // 获取sheet对象
            Sheet sheet = workbook.getSheet(sheetName);
            // 获取第一行，Row是行对象类型，通过行对象可以操作列
            Row firstRow = sheet.getRow(0);
            // 获取最后一列的列号
            int lastCellNum = firstRow.getLastCellNum();
            // System.out.println(lastCellNum);

            // 定义存放表头的数组
            String[] titles = new String[lastCellNum];
            // 将表头放入数组
            for (int i = 0; i < lastCellNum; i++) {
                // 通过行对象和列索引，获取单元格对象
                Cell cell = firstRow.getCell(i, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                // 设置列的类型为字符串
                // cell.setCellType(CellType.STRING);
                // 获取单元格的值
                String title = cell.getStringCellValue();
                // title = title.substring(0,title.indexOf(":"));
                // 值保存到数组
                titles[i] = title;
            }
            // 打印解析出来的标题
            // logger.info("解析出来的首行标题：" + Arrays.toString(titles));

            // 获取sheet最后一行的行号
            int lastRowNum = sheet.getLastRowNum();
            // System.out.println(lastRowNum);
            // 循环处理每一行数据，从2行开始是数据行
            for (int i = 1; i <= lastRowNum  ; i++) {
                // 每行数据一个对象
                T obj = clazz.newInstance();
                // 获取一行数据
                Row rowData = sheet.getRow(i);
                if (rowData==null || rowDataIsEmpty(rowData)){
                    continue;
                }
                // 获取此行的列数据，封装到caseObject对象中
                for (int j = 0; j < lastCellNum ; j++) {
                    Cell cell = rowData.getCell(j, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                    cell.setCellType(CellType.STRING);
                    String cellValue = cell.getStringCellValue();
                    // 打印获取到的值
                    // System.out.print("【"+ titles[j] + "="+ cellValue+"】");
                    // 获取要反射的方法名
                    String methodName = "set" + titles[j];
                    // 获取要反射的方法对象
                    Method method = clazz.getMethod(methodName, String.class);
                    // 反射调用
                    method.invoke(obj, cellValue);
                }
                // logger.info("封装的第【"+i+"】个对象（也就是第"+i+"行数据）：" + obj);
                list.add(obj);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (in!=null){
                try {
                    in.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        logger.info("===================读取sheet完成: " + sheetName);
        return list;
    }


    // 判断行的单元格数据是否都是空
    public static boolean rowDataIsEmpty(Row rowData) {
        int lastCellNum = rowData.getLastCellNum();
        for (int i = 0; i < lastCellNum; i++) {
            Cell cell = rowData.getCell(i, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
            cell.setCellType(CellType.STRING);
            String cellValue = cell.getStringCellValue();
            if (cellValue!=null && cellValue.trim().length()>0){
                return false;
            }
        }
        return true;
    }
}

VariableUtil

package com.lizi.utils;

import com.lizi.entity.Variable;
import org.apache.log4j.Logger;

import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * @ClassName: VariableUtil
 * @Description:
 * @Author: lz.hu
 * @Date: 2024/3/4 18:48
 */
public class VariableUtil {
    public static Logger logger = Logger.getLogger(VariableUtil.class);

    // 存放变量和值的map
    public static Map<String, String> variableMap = new HashMap<String, String>();

    // 从对象列表variableList中获取变量和值，放到map中
    public static void loadVariablesToMap(List<Variable> variableList) {
        for (Variable variable : variableList){
            String name = variable.getName();
            String value = variable.getValue();
            variableMap.put(name,value);
        }
    }

    // 替换变量
    public static String variableSubstitution(String parameters) {
        // 获取所有变量名
        Set<String> names = variableMap.keySet();
        for (String name : names) {
            if (parameters.contains(name)){
                parameters = parameters.replace(name,variableMap.get(name));
            }
        }
        return parameters;
    }
}

JDBCUtil.java

public class JDBCUtil {
    public static Logger logger = Logger.getLogger(JDBCUtil.class);

    public static Properties properties = new Properties();
    static {
        // 解析jdbc.properties获取数据库配置信息
        try {
            InputStream in = JDBCUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
            properties.load(in);
        } catch (IOException e) {
            logger.info("解析发生异常。");
            e.printStackTrace();
        }
    }

    public static void query(String sql){
        HashMap<String, Object> stringObjectHashMap = null;
        try {
            // 1、调用方法，获取连接
            Connection connection = getConnection();
            // 2、获取PreparedStatement
            Statement statement = connection.createStatement();
            // 3、调用查询方法，执行查询，返回结果集（ResultSet）
            ResultSet resultSet = statement.executeQuery(sql);
            // 获取查询相关的信息
            ResultSetMetaData metaData = resultSet.getMetaData();
            // 获取sql中有多少个查询字段
            int columnCount = metaData.getColumnCount();
            // System.out.println("查询字段数为：" + columnCount);

            stringObjectHashMap = new HashMap<String, Object>();
            // 从结果集获取查询数据：循环取出每个查询字段
            while (resultSet.next()){
                for (int i = 1; i <= columnCount; i++) {
                    String columnLabel = metaData.getColumnLabel(i);
                    String columnValue = resultSet.getObject(columnLabel).toString();
                    stringObjectHashMap.put(columnLabel, columnValue);
                }
            }
            logger.info("查询结果：" + stringObjectHashMap);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void nonQuery(String sql){
        try {
            // 1、调用方法，获取连接
            Connection connection = getConnection();
            // 2、获取statement
            Statement statement = connection.createStatement();
            // 3、调用查询方法，执行查询，返回结果集（ResultSet）
            int n = statement.executeUpdate(sql);
            if (n>0){
                logger.info("操作成功数据条数：【" + n + "】");
            } else {
                logger.info("操作成功数据条数：【0】");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // 获取数据库连接
    public static Connection getConnection() throws SQLException {
        String url = properties.getProperty("jdbc.url");
        String username = properties.getProperty("jdbc.username");
        String password = properties.getProperty("jdbc.password");
        Connection connection = DriverManager.getConnection(url, username, password);
        return connection;
    }
}