Database testing in selenium - DB validation

Database Testing In Selenium

So Database connectivity or Database testing is the most important and frequently used task that we all need to perform in selenium - Data base connectivity.

DB connection is always required to make automation script cover maximum of the task such as validation and verification of data entered or stored in DB.

In Java we can use JDBC driver which help to connect to other db and execute queries and fetch the required data. In Selenium we will take the same approach with JDBC.

Lets see how can we connect to DB in selenium with below code. Before starting if want to know more about selenium click here -> Learn Selenium

JDBC connection ->

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.IOException;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.Properties;

import org.apache.log4j.Logger;

public class DBr {

static Logger dbLogger= Logger.getLogger(DBHelper.class);

public static Connection getConnection() throws AccountException

{

Connection conn=null;

try {

FileInputStream fin=new FileInputStream("database.properties");

Properties prop= new Properties();

prop.load(fin);

dbLogger.info("Property file loaded..");

System.out.println("properties loaded..");

String driver= prop.getProperty("driver");

String url= prop.getProperty("url");

String user= prop.getProperty("username");

String passwd= prop.getProperty("password");

Class.forName(driver);

conn=DriverManager.getConnection(url,user,passwd);

dbLogger.info("database connected...");

return conn;

} catch (SQLException e) {

dbLogger.error(e.getMessage());

throw new AccountException(e.getMessage());

} catch (FileNotFoundException e) {

dbLogger.error(e.getMessage());

throw new AccountException(e.getMessage());

} catch (IOException e) {

dbLogger.error(e.getMessage());

throw new AccountException(e.getMessage());

} catch (ClassNotFoundException e) {

dbLogger.error(e.getMessage());

throw new AccountException(e.getMessage());

}

}

public long insertAccountHolderDetails(AccountHolder acc) 

throws AccountException{

try {

Connection conn= getConnection();

String sql= "insert into AccountHolder values(custId_seq.nextval,?,?,?,?)";

PreparedStatement ps= conn.prepareStatement(sql);

ps.setString(1,acc.getFirstName());

    ps.setString(2,acc.getLastName());

    ps.setLong(3,acc.getMobilenumber());

    ps.setString(4, acc.getAddress());

    int rec=ps.executeUpdate();

    if(rec==1){

    sql= "select custId_seq.currval from abc

    ps= conn.prepareStatement(sql);

    ResultSet rs= ps.executeQuery();

    if(rs.next())

    {

    long custId= rs.getLong(1);

    dbLogger.info("record inserted :" +custId);

    return custId;

    }

    }

    

} catch (SQLException e) {

dbLogger.error(e.getMessage());

throw new AccountException(e.getMessage());

}

return 0;

}

}

Data driven testing - Get value from excel in selenium

Data Driven Testing in Selenium

We require to perform Data driven testing many times while running selenium scripts.Below is the best method to fetch data from excel sheet and write in another excel sheet.

We can perform data driven using excel file with help of Apache POI library.

Java Code for Data driven Testing :

 

package com.seleniumblog.selenium.test;

import java.io.*;
import java.util.concurrent.TimeUnit;

import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.firefox.FirefoxDriver;

public class DataDriven{

 public static void main(String[] args) {

        WebDriver driver = new FirefoxDriver();

        driver.get("http://www.google.com");

        driver.manage().window().maximize(); 
      
        WebElement searchbox = driver.findElement(By.name("q"));

 try {
   
  FileInputStream file = new FileInputStream(new File("C:\\data.xlsx"));
  XSSFWorkbook workbook = new XSSFWorkbook(file);

  XSSFSheet sheet = workbook.getSheetAt(0);

for (int i=1; i <= sheet.getLastRowNum(); i++){

        String keyword = sheet.getRow(i).getCell(0).getStringCellValue();

        searchbox.sendKeys(keyword);

        searchbox.submit();      
 
        driver.manage().timeouts().implicitlyWait(1000, TimeUnit.MILLISECONDS);

}

  workbook.close();
  file.close();

 } catch (FileNotFoundException fe) {
  fe.printStackTrace();
 }
 }
}
 

So now lets see how the above code works:

Below code is the required packages for JAVA IO to make integration with excel file and Timeunit.

   
import java.io.*;
import java.util.concurrent.TimeUnit;

Below code is the required packages for Apache POI library.

   
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

Below code is the required packages for Selenium.

   
import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.firefox.FirefoxDriver;
Following code is to initialize the Firefox driver.

WebDriver driver = new FirefoxDriver();

Below code is to open the hello selenium blog in browser. 

driver.get("http://www.google.com");

You can also use the Below code is to open the hello selenium blog in browser.

driver.navigate().to("http://www.google.com");

Below code is to maximize the Firefox Driver instance.
   
driver.manage().window().maximize();

Below code is to store WebElement into a variable.
   
WebElement searchbox = driver.findElement(By.name("md"));

Below code is to locate the path of excel file.

FileInputStream file = new FileInputStream(new File("C:\\testdata.xlsx"));

Below code is to initialize the excel file as a workbook.
   
XSSFWorkbook workbook = new XSSFWorkbook(file);

Below code is to initialize the excel sheet of the workbook. Here 0 (zero) refers to the first sheet of the workbook.
   
XSSFSheet sheet = workbook.getSheetAt(0);

Below code is to run the loop till it found cell value of last row.
   
for (int i=1; i <= sheet.getLastRowNum(); i++)

Below code is to get the keyword value from the worksheet.

String keyword = sheet.getRow(i).getCell(0).getStringCellValue();

Below code is to type the keyword into search textbox.

input.sendKeys(keyword);

Below code is to press RETURN within textbox.
   
input.submit();

Below code is wait for 10 seconds.
   
driver.manage().timeouts().implicitlyWait(100, TimeUnit.MILLISECONDS);

You can also use the Below code is to close the excel file.
   
workbook.close();
file.close();

For more data driven testing , follow this web page.

 

 

Let's start with Selenium IDE

Hi,

Before moving forward in selenium webdriver lets first see how we can automate using a firefox addon Selenium IDE.

Selenium IDE is a Integrated development environment for selenium testing.It allow us to record,edit and debug a test.It is only available as a firefox extension.It includes entire selenium core.

Features of Selenium IDE

• Easy record and playback
• We can get IDs,Name,Xpath as needed very easily
• Save a test script in HTML, Ruby Script,Java and many other
• Debug and set Breakpoints

STEPS TO INSTALL SELENIUM IDE IN FIREFOX

• First open Firefox browser
• Google search for SELENIUM IDE and click on firefox website link
• Click on Add to firefox
• A pop up will be displayed click on install
• After installing click on restart now
• Then go to Tools and at last you will see SELENIUM IDE click on it
• Hurray!!You are done now you are ready to use SELENIUM IDE.

TOOLS>SELENIUM IDE

SELENIUM IDE CONSOLE