学习使用TestNG数据驱动连接MySQL

一、MySQL数据库测试数据准备

-- 创建数据库
CREATE DATABASE test;

-- 使用该数据库
use test;

-- 创建data_provider表，供测试使用
CREATE TABLE data_provider (
    username VARCHAR (20),
    userpasswd VARCHAR (20),
    playgame VARCHAR (20)
) ENGINE = INNODB DEFAULT CHARSET = utf8;

-- 在表中插入数据
INSERT INTO data_provider(username,userpasswd,playgame) VALUES('张三','123456','王者荣耀');
INSERT INTO data_provider(username,userpasswd,playgame) VALUES('李四','654321','绝地战场');
INSERT INTO data_provider(username,userpasswd,playgame) VALUES('王五','987654','穿越火线');

二、MySQL数据库连接依赖准备
maven添加依赖jar包

<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.38</version>
</dependency>

三、练习代码如下：

package com.chenfeng.Demo;

import org.testng.annotations.DataProvider;
import org.testng.annotations.Test;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class jdbc {
    @DataProvider(name = "testData")
    public static Object[][] toBeDataProvider() throws SQLException, ClassNotFoundException {
        return getTestData("data_provider");
    }

    @Test(dataProvider = "testData")
    public void f(String s1, String s2, String s3) {
        System.out.println(s1 + ":" + s2 + ":" + s3);
    }

    public static Object[][] getTestData(String tableName) throws ClassNotFoundException, SQLException {
        //设置mysql驱动
        Class.forName("com.mysql.jdbc.Driver");
        //建立数据库的连接
        Connection conn = DriverManager.getConnection("jdbc:mysql://192.168.235.133:3306/test", "root", "123456");
        //判断数据连接是否成功
        if (!conn.isClosed()) {
            System.out.println(tableName + "连接成功");
        } else {
            System.out.println(tableName + "连接失败");
        }
        //创建Statement对象可以用对应的方法executeQuery(sql语句)获取测试数据
        Statement statement = conn.createStatement();
        //创建一个结果集存放数据库执行完sql的数据
        ResultSet resultSet = statement.executeQuery("select * from " + tableName);
        //声明存放泛型string数组的list对象
        List<Object[]> list = new ArrayList<Object[]>();
        int colNum = resultSet.getMetaData().getColumnCount();
        while (resultSet.next()) {
            String[] strings = new String[colNum];
            for (int i = 0; i < strings.length; i++) {
                strings[i] = resultSet.getString(i + 1);
            }
            list.add(strings);
        }
        resultSet.close();
        conn.close();

        //将list对象转换成二位数组
        Object[][] results = new Object[list.size()][];
        for (int i = 0; i < list.size(); i++) {
            results[i] = list.get(i);
        }
        return results;
    }
}

返回结果：

data_provider连接成功
张三:123456:王者荣耀
李四:654321:绝地战场
王五:987654:穿越火线