testng连接MySQL_java结合testng，利用mysql数据库做数据源的数据驱动实例

上一篇我们介绍用如何用yaml结合testng做数据驱动，就又想来个数据库的参数化

备注：@DataProvider的返回值类型只能是Object[][]与Iterator[]

思路：

解析sql查询返回结果，然后把解析出来的list转换成Object[][]类型的数据，且结合在@DataProvider中。

然后把解析出来的list转换成Object[][]类型的数据，且结合在@DataProvider中。

DbDataHeleper.java

import org.testng.annotations.DataProvider;

import java.sql.*;

import java.util.*;

/**

* 数据库操作工具

*

* @author longrong.lang

*/

public class DbDataHeleper {

static Connection conn = null;

public static String driverClassName = "com.mysql.jdbc.Driver";

public static String url = "jdbc:mysql://127.0.0.1:3306/demo";

public static String username = "root";

public static String password = "root";

/**

* 执行sql

*

* @param jdbcUrl 数据库配置连接

* @param sql sql语句

* @return

*/

public static List> getDataList(String jdbcUrl, String sql) {

List> paramList = new ArrayList>();

Map param = new HashMap<>();

Statement stmt = null;

try {

// 注册 JDBC 驱动

Class.forName(driverClassName);

// 打开链接

conn = DriverManager.getConnection(jdbcUrl, username, password);

// 执行查询

stmt = conn.createStatement();

ResultSet rs = null;

rs = stmt.executeQuery(sql);

String columns[] = {"username", "passWord", "remark"};

// 展开结果集数据库

while (rs.next()) {

Map map = new LinkedHashMap();

for (int i = 0; i < columns.length; i++) {

String cellData = rs.getString(columns[i]);

map.put(columns[i], cellData);

}

paramList.add(map);

}

// 完成后关闭

rs.close();

stmt.close();

conn.close();

} catch (SQLException se) {

// 处理 JDBC 错误

System.out.println("处理 JDBC 错误!");

} catch (Exception e) {

// 处理 Class.forName 错误

System.out.println("处理 Class.forName 错误");

} finally {

// 关闭资源

try {

if (stmt != null) stmt.close();

if (conn != null) conn.close();

} catch (SQLException se) {

se.printStackTrace();

}

}

return paramList;

}

@DataProvider

public Object[][] dbDataMethod() {

String sql = "SELECT * FROM `account`;";

List> result = getDataList(url, sql);

Object[][] files = new Object[result.size()][];

for (int i = 0; i < result.size(); i++) {

files[i] = new Object[]{result.get(i)};

}

return files;

}

}

再通过测试文件来测试一下：

TestDbData.java

importorg.testng.annotations.Test;importjava.util.Map;public class TestDbData extendsDbDataHeleper {

@Test(dataProvider= "dbDataMethod")public void testmethod1(Map, ?>param) {

System.out.println(param.get("username") + "\t" + param.get("passWord") + "\t" + param.get("remark"));

}

}

运行效果：

[TestNG] Running:

C:\Users\Administrator\.IntelliJIdea2018.2\system\temp-testng-customsuite.xml

rongrong123456 3xiaoqiang123654 4gates112121 1gates112121 2

===============================================Default Suite

Total tests run:4, Failures: 0, Skips: 0

===============================================Picked up JAVA_TOOL_OPTIONS:-Dfile.encoding=UTF-8Process finished with exit code0