@DataProvider being a MySQL DataBase- JAVA,Selenium,Real time examples

In TestNG we use the @DAtaProvider for passing the data values to the testcases. In majority of these cases the Data source used as an two dimensional Array,XLS or XLSX.
In this post we will look into the example where the Data source is MYSQL. For using the MYSQL to read the contents of the DB we have to use the following dependency in the maven pom.xml.
MYSQL Connector Dependency


<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.1.21</version>
</dependency>

<groupId>mysql</groupId>

<artifactId>mysql-connector-java</artifactId>

</dependency>

Preconditions:

Install MYSQL in the laptop. Refer to the

link

for more details on installation,configuration and examples on MYSQL.
Open Workbench and make sure the MYSQL server is started.

for more information on installation.

DataProvider Example using DataSource as MYSQL


package org.totalqa.util;

 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

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

import com.mysql.jdbc.ResultSetMetaData;
 
public class ConnectionManager
{
	private static String url = "jdbc:mysql://localhost:3306/testDB";    
	private static String driverName = "com.mysql.jdbc.Driver";   
	private static String username = "root";   
	private static String password = "root";
	private static Connection con;
	private String[][] inputArr;
	/*
	 * 	<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.1.21</version>
		</dependency>
	 */
 
	public String[][]  readDB( ) 
	{
		try {
			Class.forName(driverName);
			try {
				//Create a connection to DB by passing Url,Username,Password as parameters
				con = DriverManager.getConnection(url, username, password);
				Statement stmt=con.createStatement();
				
				//Executing the Queries
				//stmt.executeUpdate("INSERT INTO testDB.employee VALUES ('john',2000)");
				//stmt.executeUpdate("truncate table testDB.employee");
				ResultSet rs = stmt.executeQuery("SELECT * FROM testDB.employee");
				rs.last();
				int rows = rs.getRow();
				 
				ResultSetMetaData rsmd = (ResultSetMetaData) rs.getMetaData();
				int cols = rsmd.getColumnCount();
				System.out.println(rows +"--" + cols);
				inputArr= new String[rows][cols];
 
				int i =0;
				rs.beforeFirst();
				//Iterating the data in the Table
				while (rs.next())
				{
					for(int j=0;j<cols;j++)
					{
						inputArr[i][j]=rs.getString(j+1);
						System.out.print("values:: " + inputArr[i][j] +":::"+i +":::"+j); 
 
					}
					System.out.println();
					i++;
				}
			} catch (SQLException ex) {
				ex.printStackTrace();
				System.out.println("Failed to create the database connection."); 
			}
		} catch (ClassNotFoundException ex) {
			ex.printStackTrace();
			System.out.println("Driver not found."); 
		}
		return inputArr;
 
	}

	@DataProvider(name="DP")
	public String[][] feedDP()
	{
		String data[][]=readDB();
		for(int i=0;i<data.length;i++)
		{
			for(int j=0;j<data[i].length;j++)
			{
		 
				System.out.println("in for :: " + data[i][j]);
				 
			}
		}
		
		return data;
	}
	@Test(dataProvider="DP")
	public void login(String uname,String pword)
	{
		System.out.println("col1"+ uname);
		System.out.println("col2" + pword);
	}
	
  
}

100

101

102

103

package org.totalqa.util;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import org.testng.annotations.DataProvider;

import org.testng.annotations.Test;

import com.mysql.jdbc.ResultSetMetaData;

public class ConnectionManager

{

private static String url = "jdbc:mysql://localhost:3306/testDB";

private static String driverName = "com.mysql.jdbc.Driver";

private static String username = "root";

private static String password = "root";

private static Connection con;

private String[][] inputArr;

* <dependency>

<groupId>mysql</groupId>

<artifactId>mysql-connector-java</artifactId>

</dependency>

public String[][] readDB( )

{

try {

Class.forName(driverName);

try {

//Create a connection to DB by passing Url,Username,Password as parameters

con = DriverManager.getConnection(url, username, password);

Statement stmt=con.createStatement();

//Executing the Queries

//stmt.executeUpdate("INSERT INTO testDB.employee VALUES ('john',2000)");

//stmt.executeUpdate("truncate table testDB.employee");

ResultSet rs = stmt.executeQuery("SELECT * FROM testDB.employee");

rs.last();

int rows = rs.getRow();

ResultSetMetaData rsmd = (ResultSetMetaData) rs.getMetaData();

int cols = rsmd.getColumnCount();

System.out.println(rows +"--" + cols);

inputArr= new String[rows][cols];

int i =0;

rs.beforeFirst();

//Iterating the data in the Table

while (rs.next())

{

for(int j=0;j<cols;j++)

{

inputArr[i][j]=rs.getString(j+1);

System.out.print("values:: " + inputArr[i][j] +":::"+i +":::"+j);

}

System.out.println();

i++;

}

} catch (SQLException ex) {

ex.printStackTrace();

System.out.println("Failed to create the database connection.");

}

} catch (ClassNotFoundException ex) {

ex.printStackTrace();

System.out.println("Driver not found.");

}

return inputArr;

}

@DataProvider(name="DP")

public String[][] feedDP()

{

String data[][]=readDB();

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

{

for(int j=0;j<data[i].length;j++)

{

System.out.println("in for :: " + data[i][j]);

}

return data;

}

@Test(dataProvider="DP")

public void login(String uname,String pword)

{

System.out.println("col1"+ uname);

System.out.println("col2" + pword);

}

Refer to the pom.xml details as mentioned below:


<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>

	<groupId>org.iit.mmp</groupId>
	<artifactId>mmpmodule</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>jar</packaging>

	<name>mmpmodule</name>
	<url>http://maven.apache.org</url>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
	</properties>
	<dependencies>
		<dependency>
			<groupId>log4j</groupId>
			<artifactId>log4j</artifactId>
			<version>1.2.17</version>
		</dependency>
		<dependency>
			<groupId>org.seleniumhq.selenium</groupId>
			<artifactId>selenium-java</artifactId>
			<version>3.141.59</version>
		</dependency>

		<dependency>
			<groupId>net.sourceforge.jexcelapi</groupId>
			<artifactId>jxl</artifactId>
			<version>2.6.12</version>
		</dependency>
		<dependency>
			<groupId>org.testng</groupId>
			<artifactId>testng</artifactId>
			<version>6.8</version>
		</dependency>
		<dependency>
			<groupId>io.github.bonigarcia</groupId>
			<artifactId>webdrivermanager</artifactId>
			<version>3.3.0</version>
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.1.21</version>
		</dependency>
	</dependencies>
	<profiles>

		<profile>
			<id>QA</id>
			<build>
				<plugins>
					<plugin>
						<artifactId>maven-compiler-plugin</artifactId>
						<version>3.1</version>
						<configuration>
							<fork>true</fork>
							<executable>C:\Program Files\Java\jdk1.8.0_65\bin\javac.exe</executable>
						</configuration>
					</plugin>
					<plugin>
						<groupId>org.apache.maven.plugins</groupId>
						<artifactId>maven-surefire-plugin</artifactId>
						<version>3.0.0-M3</version>
						<configuration>
							<suiteXmlFiles>
								<suiteXmlFile>qa.xml</suiteXmlFile>
							</suiteXmlFiles>
						</configuration>
					</plugin>
				</plugins>
			</build>
		</profile>

		<profile>
			<id>DEV</id>
			<build>
				<plugins>
					<plugin>
						<artifactId>maven-compiler-plugin</artifactId>
						<version>3.1</version>
						<configuration>
							<fork>true</fork>
							<executable>C:\Program Files\Java\jdk1.8.0_65\bin\javac.exe</executable>
						</configuration>
					</plugin>
					<plugin>
						<groupId>org.apache.maven.plugins</groupId>
						<artifactId>maven-surefire-plugin</artifactId>
						<version>3.0.0-M3</version>
						<configuration>
							<suiteXmlFiles>
								<suiteXmlFile>dev.xml</suiteXmlFile>
							</suiteXmlFiles>
						</configuration>
					</plugin>
				</plugins>
			</build>

		</profile>
	</profiles>
</project>

100

101

102

103

104

105

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">

<artifactId>mmpmodule</artifactId>

<version>0.0.1-SNAPSHOT</version>

<name>mmpmodule</name>

<url>http://maven.apache.org</url>

<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>

</properties>

</dependency>

<groupId>org.seleniumhq.selenium</groupId>

<artifactId>selenium-java</artifactId>

</dependency>

<groupId>net.sourceforge.jexcelapi</groupId>

</dependency>

<groupId>org.testng</groupId>

<artifactId>testng</artifactId>

</dependency>

<groupId>io.github.bonigarcia</groupId>

<artifactId>webdrivermanager</artifactId>

</dependency>

<groupId>mysql</groupId>

<artifactId>mysql-connector-java</artifactId>

</dependency>

</dependencies>

<build>

<artifactId>maven-compiler-plugin</artifactId>

<executable>C:\Program Files\Java\jdk1.8.0_65\bin\javac.exe</executable>

</configuration>

</plugin>

<groupId>org.apache.maven.plugins</groupId>

<artifactId>maven-surefire-plugin</artifactId>

</suiteXmlFiles>

</configuration>

</plugin>

</plugins>

</build>

</profile>

<build>

<artifactId>maven-compiler-plugin</artifactId>

<executable>C:\Program Files\Java\jdk1.8.0_65\bin\javac.exe</executable>

</configuration>

</plugin>

<groupId>org.apache.maven.plugins</groupId>

<artifactId>maven-surefire-plugin</artifactId>

</suiteXmlFiles>

</configuration>

</plugin>

</plugins>

</build>

</profile>

</profiles>

</project>

OUTPUT

PASSED: login(“james”, “1000”)
PASSED: login(“john”, “2000”)

Total-QA

Automation Tools

@DataProvider being a MySQL DataBase- JAVA,Selenium,Real time examples

Preconditions:

DataProvider Example using DataSource as MYSQL

Refer to the pom.xml details as mentioned below:

OUTPUT

Leave a Reply Cancel reply