上一篇我们介绍用如何用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