1. 封装链接数据库的类：
import java.sql.ResultSet;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class DBhelp {
public static final String url = "jdbc:mysql://***:3306/databasename";
public static final String user = "user";
public static final String password = "password";
public java.sql.Connection con = null;
public java.sql.PreparedStatement ps = null;
ResultSet rs = null;
userdata userdata= new userdata();
public DBhelp(String sql) {
try {
con = DriverManager.getConnection(url, user, password);//获取连接
ps = con.prepareStatement(sql);//准备执行语句
rs = ps.executeQuery();
} catch (Exception e) {
e.printStackTrace();
}
}
public void close() {
try {
this.con.close();
this.ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
2. 封装登录的用户类：
public class userdata {
public String username;
public String password;
public String errNote;
public void setusername(String username){
this.username=username;
}
public String getusername(){
return username;
}
public void setpassword(String password){
this.password=password;
}
public String getpassword(){
return password;
}
public void seterrNote(String errNote){
this.errNote=errNote;
}
public String geterrNote(){
return errNote;}
}
3. testng读取表数据测试登录：
import org.testng.annotations.Test;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import org.openqa.selenium.By;
import org.openqa.selenium.WebElement;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;
import java.util.List;
import org.testng.annotations.BeforeSuite;
import org.testng.annotations.BeforeTest;
import org.testng.annotations.DataProvider;
public class LoginJDBC {
public String sql = "select USER_NM,PASSWORD,NOTE from data_user";
static ResultSet ret = null;
userdata user=new userdata();
Login wm=new Login();
public String baseUrl = "url";
public String LinkTest;
public String LinkTestPassWord;
@Test(dataProvider = "jdbc_data")
public void DBlogin(userdata user) throws Exception {
wm.Loginindex(baseUrl, user.username,user.password);
WebElement tips=wm.driver.findElement(By.xpath("//*[@id='J_Mod_Login']/div[2]/div[1]/form/div[1]/div"));
if(tips.getText().matches(user.errNote)){
System.out.println(user.errNote+"：pass");
Thread.sleep(3000);
}
else{
System.out.println(user.errNote+"：error");
Thread.sleep(2000);
}
}
@DataProvider
public Iterator<Object[]> jdbc_data() {
List<userdata> userlist = new ArrayList<userdata>();
List<Object[]> userToBe = new ArrayList<Object[]>();
DBhelp db1 = new DBhelp(sql);//创建DBHelpe对象
//读取表数据，封装为步骤2定义的user对象数组
try {
ret = db1.ps.executeQuery();//执行语句，得到结果集
while (ret.next()) {
user.setusername(ret.getString("USER_NM"));
user.setpassword(ret.getString("PASSWORD"));
user.seterrNote(ret.getString("NOTE"));
userlist.add(user);
}//显示数据
ret.close();
db1.close();//关闭连接
//转为Object元素
for (userdata userData:userlist)
{
userToBe.add(new Object[] { userData } );
}
} catch (SQLException e) {
e.printStackTrace();
}
return userToBe.iterator();
}
}