Database Testing Using Selenium WebDriver and JDBC API

In our last Selenium blogs we learned below Some Selenium frameworks Concepts:

We discussed some Advanced concepts like how we deal with Data Manipulation in Excel sheet by Selenium WebDriver. So moving ahead with our advance topics of selenium blog series we will introduce you with concepts of Database Testing using Selenium WebDriver.

We would discuss the basic process like Database Connection, executing queries, fetching data and disconnecting database instances etc. As we know that all the web applications need a backend to store the Data. Databases like MySQL, oracle, and SQL server are popularly use in these days

Here I take a example of Bookingcommerce in which i explain how user can register in bookingcommerce.com , So Bookingcommerce is basically a Saas based online booking and Appointment scheduling software.

Consider a following scenarios

1) In any application we make sure that the data entered by code is consistently reflected in our databases. Thus we are able to retrieve the information by executing a sql query and verify the retrieved information against information we pass in our application by code. for examples, registration forms, user data, user profiles, and delete user data. Thus after Running our registered user Test code we need to make sure that whatever information we pass by Test code will get successfully saved into the Database as soon as user registered in our application.

2) Another use case of performing database testing with Selenium WebDriver by building mysql connection either with our localhost and either with another system IP.

Below Code with mention all scenarios for User Register in Bookingcommerce.com

package Bookingcommerce;

import java.awt.AWTException;
import java.awt.Robot;
import java.awt.event.KeyEvent;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.concurrent.TimeUnit;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.openqa.selenium.By;
import org.openqa.selenium.JavascriptExecutor;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.chrome.ChromeDriver;
import org.openqa.selenium.firefox.FirefoxDriver;
import org.openqa.selenium.support.ui.ExpectedConditions;
import org.openqa.selenium.support.ui.Select;
import org.openqa.selenium.support.ui.WebDriverWait;
import org.testng.annotations.BeforeSuite;
import org.testng.annotations.DataProvider;
import org.testng.annotations.Test;

public class Bookingcommerce_Register {
	Connection conn = null;
	String url2 = "jdbc:mysql://localhost/database Name";
	String databaseName = "database Name";
	String username = "****";
	String dbpassword = "****";
	public static WebDriver driver;
	
	String chrome_path;
	public static HSSFWorkbook workbook;
	public static HSSFSheet worksheet;
	public static DataFormatter formatter= new DataFormatter();
	public static String file_location = System.getProperty("user.dir")+"/booking-Register.xls";
	public static String SheetName= "sheet";
	public  String Res;
	Write obj1=new Write();
	public int DataSet=-1;


	@BeforeSuite
	public void OpenBrowser() throws InterruptedException

	{
		System.setProperty("webdriver.chrome.driver", "/home/users/shivani.kukreti/Downloads/chromedriver");
			 driver = new ChromeDriver();
			//WebDriverWait wait =  new WebDriverWait(driver, 90);
			driver.manage().window().maximize();
			driver.navigate().to("https://bookingcommerce.com/en/");
			Thread.sleep(4000);
	}

@Test 
(dataProvider="ReadVariant")

public void AddVariants(String Name, String Email, String CompanyName, String Companydomain, String password, String Confirmpassword, String RS) throws Exception
{
	DataSet++;
	System.out.println("NAme of Registered user are:" +Name);
	System.out.println("Email of Reigister user:" +Email);
	System.out.println("Register user company name:" +CompanyName);
	System.out.println("Registered user company domain:" +Companydomain);
	System.out.println("Registered user login password:" +password);
	System.out.println("Registeref user confirm login password:" +Confirmpassword);
        WebDriverWait wait =  new WebDriverWait(driver, 90);
	 
	//Enter Username
	WebElement element = wait.until(ExpectedConditions.visibilityOfElementLocated(By.cssSelector("a[href='https://bookingcommerce.com/en/member/register']")));
	element.click();
	//Enter Name
	WebElement element1 = wait.until(ExpectedConditions.visibilityOfElementLocated(By.cssSelector("form div:nth-child(1) input[ng-reflect-name='name']")));
	element1.sendKeys(Name);
	//Enter Email
	WebElement element2 = wait.until(ExpectedConditions.visibilityOfElementLocated(By.cssSelector("form div:nth-child(2) input[ng-reflect-name='email']")));
	element2.sendKeys(Email);
	//Enter Bookingcompany NAME
	WebElement element3 = wait.until(ExpectedConditions.visibilityOfElementLocated(By.cssSelector("form div:nth-child(3) input[ng-reflect-name='companyName']")));
	element3.sendKeys(CompanyName);
	//Enter Bookingcompany URL
	WebElement element4 = wait.until(ExpectedConditions.visibilityOfElementLocated(By.cssSelector("form div:nth-child(4) input[ng-reflect-name='companyDomain']")));
	element4.sendKeys(Companydomain);
	// Enter password
	
	WebElement element5 = wait.until(ExpectedConditions.visibilityOfElementLocated(By.cssSelector("form div:nth-child(5) div[ng-reflect-name='0'] div:nth-child(1) input[ng-reflect-name='first']")));
         element5.sendKeys(password);
	//Confirm password
	
	WebElement element6 = wait.until(ExpectedConditions.visibilityOfElementLocated(By.cssSelector("form div:nth-child(5) div[ng-reflect-name='0'] div:nth-child(2) input[ng-reflect-name='second']")));
	//executor.executeScript("arguments[0].click();", element6);
	
         element6.sendKeys(Confirmpassword);
	//click on save
	WebElement element8 = wait.until(ExpectedConditions.visibilityOfElementLocated(By.cssSelector("form div:nth-child(6) button[type='submit']")));
	element8.click();
	
	//DataBase Connection Start:
       try{
	
		 System.out.println(username +dbpassword);
		
    	 DriverManager.registerDriver(new com.mysql.jdbc.Driver ());
    	 DriverManager.getConnection("jdbc:mysql://localhost/Database Name","username","dbpassword");

    	 
    	 Class.forName("com.mysql.jdbc.Driver").newInstance();	 
    	
    	 conn = DriverManager.getConnection(url2, username, dbpassword);
    	  
 
          
    	 // String sqlQuerry = "Select * from company";
  
    	 String sqlQuerry = "DELETE FROM `company` WHERE `domain` ="+Companydomain;
    	
    		
    	
         Statement statement = conn.createStatement();
     	 
         statement.executeUpdate(sqlQuerry);
         
         

     	System.out.println("Successfully query get generated");
    	
    	Res="Pass";
    	obj1.WriteResult(Res, DataSet+1);
    
     }
    catch (Exception e){
    
    	System.out.println(e);
    } finally {
    	
    	if(conn!=null){
    		conn=null;
    	}
  	
  //  statement.executeUpdate(sqlQuerry);
     
 //call WriteResult function here
 Res="Pass";
 obj1.WriteResult(Res, DataSet+1);
 	

    }
	    
}	
	
@DataProvider
	public static Object[][] ReadVariant() throws IOException
	{
	FileInputStream fileInputStream= new FileInputStream("/home/users/shivani.kukreti/Documents/booking-Register.xls");
		workbook = new HSSFWorkbook (fileInputStream);
		worksheet=workbook.getSheet(SheetName);
   	    HSSFRow Row=worksheet.getRow(0);   	    
   	    
    	int RowNum = worksheet.getPhysicalNumberOfRows();
    	int ColNum= Row.getLastCellNum();
    	
    	Object Data[][]= new Object[RowNum-1][ColNum];
    	
    		for(int i=0; i<RowNum-1; i++)
    		{  
    			HSSFRow row= worksheet.getRow(i+1);
    			
    			for (int j=0; j<ColNum; j++)
    			{
    				if(row==null)
    					Data[i][j]= "";
    				else 
    				{
    					HSSFCell cell= row.getCell(j);
    					if(cell==null)
    						Data[i][j]= "";
    					else
    					{
    						String value=formatter.formatCellValue(cell);
    						Data[i][j]=value;
    					}
    				}
    			}
    		}

    	return Data;
    }
}

Below Code for Write Results i.e pass or fail after executing a whole programme.

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

package Bookingcommerce;

import java.awt.AWTException;

import java.awt.Robot;

import java.awt.event.KeyEvent;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.IOException;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.concurrent.TimeUnit;

import org.apache.poi.hssf.usermodel.HSSFCell;

import org.apache.poi.hssf.usermodel.HSSFRow;

import org.apache.poi.hssf.usermodel.HSSFSheet;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.ss.usermodel.DataFormatter;

import org.openqa.selenium.By;

import org.openqa.selenium.JavascriptExecutor;

import org.openqa.selenium.WebDriver;

import org.openqa.selenium.WebElement;

import org.openqa.selenium.chrome.ChromeDriver;

import org.openqa.selenium.firefox.FirefoxDriver;

import org.openqa.selenium.support.ui.ExpectedConditions;

import org.openqa.selenium.support.ui.Select;

import org.openqa.selenium.support.ui.WebDriverWait;

import org.testng.annotations.BeforeSuite;

import org.testng.annotations.DataProvider;

import org.testng.annotations.Test;

public class Bookingcommerce_Register {

Connection conn = null;

String url2 = "jdbc:mysql://localhost/database Name";

String databaseName = "database Name";

String username = "****";

String dbpassword = "****";

public static WebDriver driver;

String chrome_path;

public static HSSFWorkbook workbook;

public static HSSFSheet worksheet;

public static DataFormatter formatter= new DataFormatter();

public static String file_location = System.getProperty("user.dir")+"/booking-Register.xls";

public static String SheetName= "sheet";

public String Res;

Write obj1=new Write();

public int DataSet=-1;

@BeforeSuite

public void OpenBrowser() throws InterruptedException

{

System.setProperty("webdriver.chrome.driver", "/home/users/shivani.kukreti/Downloads/chromedriver");

driver = new ChromeDriver();

//WebDriverWait wait = new WebDriverWait(driver, 90);

driver.manage().window().maximize();

driver.navigate().to("https://bookingcommerce.com/en/");

Thread.sleep(4000);

}

@Test

(dataProvider="ReadVariant")

public void AddVariants(String Name, String Email, String CompanyName, String Companydomain, String password, String Confirmpassword, String RS) throws Exception

{

DataSet++;

System.out.println("NAme of Registered user are:" +Name);

System.out.println("Email of Reigister user:" +Email);

System.out.println("Register user company name:" +CompanyName);

System.out.println("Registered user company domain:" +Companydomain);

System.out.println("Registered user login password:" +password);

System.out.println("Registeref user confirm login password:" +Confirmpassword);

WebDriverWait wait = new WebDriverWait(driver, 90);

//Enter Username

WebElement element = wait.until(ExpectedConditions.visibilityOfElementLocated(By.cssSelector("a[href='https://bookingcommerce.com/en/member/register']")));

element.click();

//Enter Name

WebElement element1 = wait.until(ExpectedConditions.visibilityOfElementLocated(By.cssSelector("form div:nth-child(1) input[ng-reflect-name='name']")));

element1.sendKeys(Name);

//Enter Email

WebElement element2 = wait.until(ExpectedConditions.visibilityOfElementLocated(By.cssSelector("form div:nth-child(2) input[ng-reflect-name='email']")));

element2.sendKeys(Email);

//Enter Bookingcompany NAME

WebElement element3 = wait.until(ExpectedConditions.visibilityOfElementLocated(By.cssSelector("form div:nth-child(3) input[ng-reflect-name='companyName']")));

element3.sendKeys(CompanyName);

//Enter Bookingcompany URL

WebElement element4 = wait.until(ExpectedConditions.visibilityOfElementLocated(By.cssSelector("form div:nth-child(4) input[ng-reflect-name='companyDomain']")));

element4.sendKeys(Companydomain);

// Enter password

WebElement element5 = wait.until(ExpectedConditions.visibilityOfElementLocated(By.cssSelector("form div:nth-child(5) div[ng-reflect-name='0'] div:nth-child(1) input[ng-reflect-name='first']")));

element5.sendKeys(password);

//Confirm password

WebElement element6 = wait.until(ExpectedConditions.visibilityOfElementLocated(By.cssSelector("form div:nth-child(5) div[ng-reflect-name='0'] div:nth-child(2) input[ng-reflect-name='second']")));

//executor.executeScript("arguments[0].click();", element6);

element6.sendKeys(Confirmpassword);

//click on save

WebElement element8 = wait.until(ExpectedConditions.visibilityOfElementLocated(By.cssSelector("form div:nth-child(6) button[type='submit']")));

element8.click();

//DataBase Connection Start:

try{

System.out.println(username +dbpassword);

DriverManager.registerDriver(new com.mysql.jdbc.Driver ());

DriverManager.getConnection("jdbc:mysql://localhost/Database Name","username","dbpassword");

Class.forName("com.mysql.jdbc.Driver").newInstance();

conn = DriverManager.getConnection(url2, username, dbpassword);

// String sqlQuerry = "Select * from company";

String sqlQuerry = "DELETE FROM `company` WHERE `domain` ="+Companydomain;

Statement statement = conn.createStatement();

statement.executeUpdate(sqlQuerry);

System.out.println("Successfully query get generated");

Res="Pass";

obj1.WriteResult(Res, DataSet+1);

}

catch (Exception e){

System.out.println(e);

} finally {

if(conn!=null){

conn=null;

}

// statement.executeUpdate(sqlQuerry);

//call WriteResult function here

Res="Pass";

obj1.WriteResult(Res, DataSet+1);

}

@DataProvider

public static Object[][] ReadVariant() throws IOException

{

FileInputStream fileInputStream= new FileInputStream("/home/users/shivani.kukreti/Documents/booking-Register.xls");

workbook = new HSSFWorkbook (fileInputStream);

worksheet=workbook.getSheet(SheetName);

HSSFRow Row=worksheet.getRow(0);

int RowNum = worksheet.getPhysicalNumberOfRows();

int ColNum= Row.getLastCellNum();

Object Data[][]= new Object[RowNum-1][ColNum];

for(int i=0; i<RowNum-1; i++)

{

HSSFRow row= worksheet.getRow(i+1);

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

{

if(row==null)

Data[i][j]= "";

else

{

HSSFCell cell= row.getCell(j);

if(cell==null)

Data[i][j]= "";

else

{

String value=formatter.formatCellValue(cell);

Data[i][j]=value;

}

return Data;

}

Below Code for Write Results i.e pass or fail after executing a whole programme.

package Bookingcommerce;


import java.io.FileInputStream;
import java.io.FileOutputStream;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.testng.annotations.Test;

import Bookingcommerce.Bookingcommerce_Register;


public class Write 
{
	public static HSSFWorkbook workbook;
	public static HSSFSheet worksheet;
	public String ColName="Result";
	public int col_num;
@Test
public void WriteResult(String Ress, int DR) throws Exception
{
	FileInputStream file_input_stream= new FileInputStream("/home/users/shivani.kukreti/Documents/booking-Register.xls");
	workbook=new HSSFWorkbook(file_input_stream);
	worksheet=workbook.getSheet(Bookingcommerce.Bookingcommerce_Register.SheetName);
	HSSFRow Row=worksheet.getRow(0);
	int sheetIndex=workbook.getSheetIndex(Bookingcommerce.Bookingcommerce_Register.SheetName);
	DataFormatter formatter = new DataFormatter();
	if(sheetIndex==-1)
	{
		System.out.println("No such sheet in file exists");
	}
	else
	{
		col_num=-1;
			for(int i=0;i<Row.getLastCellNum();i++)
			{
				HSSFCell cols=Row.getCell(i);
				String colsval=formatter.formatCellValue(cols);
				if(colsval.trim().equalsIgnoreCase(ColName.trim()))
				{
					col_num=i;
					break;
				}
			}
			Row= worksheet.getRow(DR);
			try
				{
					HSSFCell cell=worksheet.getRow(DR).getCell(col_num);
					if(cell==null)
						cell=Row.createCell(col_num);
					cell.setCellValue(Ress);
				}
			catch (Exception e)
			{
			} 

	}
	FileOutputStream file_output_stream=new FileOutputStream("/home/users/shivani.kukreti/Documents/booking-Register.xls");
	workbook.write(file_output_stream);
	file_output_stream.close();
		if(col_num==-1)
			{
				System.out.println("Column you are searching for does not exist");
			}
			}
              }

package Bookingcommerce;

import java.io.FileInputStream;

import java.io.FileOutputStream;

import org.apache.poi.hssf.usermodel.HSSFCell;

import org.apache.poi.hssf.usermodel.HSSFRow;

import org.apache.poi.hssf.usermodel.HSSFSheet;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.ss.usermodel.DataFormatter;

import org.testng.annotations.Test;

import Bookingcommerce.Bookingcommerce_Register;

public class Write

{

public static HSSFWorkbook workbook;

public static HSSFSheet worksheet;

public String ColName="Result";

public int col_num;

@Test

public void WriteResult(String Ress, int DR) throws Exception

{

FileInputStream file_input_stream= new FileInputStream("/home/users/shivani.kukreti/Documents/booking-Register.xls");

workbook=new HSSFWorkbook(file_input_stream);

worksheet=workbook.getSheet(Bookingcommerce.Bookingcommerce_Register.SheetName);

HSSFRow Row=worksheet.getRow(0);

int sheetIndex=workbook.getSheetIndex(Bookingcommerce.Bookingcommerce_Register.SheetName);

DataFormatter formatter = new DataFormatter();

if(sheetIndex==-1)

{

System.out.println("No such sheet in file exists");

}

else

{

col_num=-1;

for(int i=0;i<Row.getLastCellNum();i++)

{

HSSFCell cols=Row.getCell(i);

String colsval=formatter.formatCellValue(cols);

if(colsval.trim().equalsIgnoreCase(ColName.trim()))

{

col_num=i;

break;

}

Row= worksheet.getRow(DR);

try

{

HSSFCell cell=worksheet.getRow(DR).getCell(col_num);

if(cell==null)

cell=Row.createCell(col_num);

cell.setCellValue(Ress);

}

catch (Exception e)

{

}

FileOutputStream file_output_stream=new FileOutputStream("/home/users/shivani.kukreti/Documents/booking-Register.xls");

workbook.write(file_output_stream);

file_output_stream.close();

if(col_num==-1)

{

System.out.println("Column you are searching for does not exist");

}

Below is my Excel sheet :

Database Testing Using Selenium WebDriver and JDBC API

Consider a following scenarios

Leave a Comment Cancel Reply

COOKIE USAGE POLICY