上一篇我们介绍用如何用yaml结合testng做数据驱动，就又想来个数据库的参数化
备注：@DataProvider的返回值类型只能是Object[][]与Iterator<Object>[]
思路：
解析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<Map<String,String>> getDataList(String jdbcUrl,String sql) {
List<Map<String,String>> paramList = new ArrayList<Map<String,String>>();
Map<String,String> 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<String,String> map = new LinkedHashMap<String,String>();
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<Map<String,String>> 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
import org.testng.annotations.Test; java.util.Map; public class TestDbData extends DbDataHeleper { @Test(dataProvider = "dbDataMethod") 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 rongrong 123456 3 xiaoqiang 123654 4 gates 112121 1 gates 112121 2 =============================================== Default Suite Total tests run: 4,Failures: 0,Skips: 0 =============================================== Picked up JAVA_TOOL_OPTIONS: -Dfile.encoding=UTF-8 Process finished with exit code 0
