Automate Your Test: 02/27/18

How to Do Database Connection in Selenium

Sometime in testing we need to validate data in database also or need to extract data from database. There are many type of database availabe now a days like MySQL, SQL, Oracle ect.

In this article we will see how to connect to database and execute query with selenium webdriver.

If we want execute query into database then we have to connect to that database first.

If you are using C# in Selenium then please go to below URL.
How to Do Database Connection in Selenium C#

How to connect to database?

Database would be local or on server also. So we need to first set class for database.

Syntax:

1
2
3

Class.forName("com.mysql.jdbc.Driver"); // For Local

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); // For on server

Now we have to connect database.

Syntax:

1	sqlConnection = DriverManager.getConnection("ConnetionString", "Username", "Password");

We are done with database connection. Now, we have to execute query on database.

Syntax:

1 2	Statement st = sqlConnection.createStatement(); ResultSet rs = st.executeQuery(vQuery);

You get result of query in object of Resultset. you can use resultset values as per your requirement.Once you are done with your stuff then you have to close database connection.

Syntax:

1	sqlConnection.close();

Please go to below blog for database operation in selenium wrapper automation.

Database Operation with Selenium Wrapper Automation

Database Operations with Selenium Wrapper Automation

When you need to connect with database and execute query then you have to do first connect with your database then execute query.

Suppose you have scenario in that you need to execute lots of query and get result and verfy the result. So, for that every time you need to open connection, execute query and close connection. It is not good approach for any framework.

I have created one class 'DatabaseOperations' for different operation on database. you can use this class into your automation framework and use all methods of it with object of this class.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import org.openqa.selenium.WebDriver;

public class DatabaseOperations 
{
 
 public Connection mConnectToServerDatabase(WebDriver driver, String vDatabaseName, String vUsername, String vPassword)
 {
  Connection sqlConnection = null;
  try
  {
   // Make database connection
   Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
  }
  catch (ClassNotFoundException E)
  {
   System.out.println("Error-SQL class not found.");
   E.printStackTrace();
  }
  
  try
  {
   // Get Database connection
   String vConnetionString = "jdbc:sqlserver://server ip\\QA:1444;databaseName";   
   sqlConnection = DriverManager.getConnection(vConnetionString, vUsername, vPassword);   
  }
  catch (Exception E)
  {
   System.out.println("Error in connection with database.");
   E.printStackTrace();
  }
  
  return sqlConnection;  
 }
 
 public Connection mConnectToLocalDatabase(WebDriver driver, String vDatabaseName, String vUsername, String vPassword)
 {
  Connection sqlConnection = null;
  try
  {
   // Make database connection
   Class.forName("com.mysql.jdbc.Driver");
  }
  catch (ClassNotFoundException E)
  {
   System.out.println("Error-SQL class not found.");
   E.printStackTrace();
  }
  
  try
  {
   // Get Database connection
   String vConnetionString = "jdbc:mysql://localhost:3306/user";   
   sqlConnection = DriverManager.getConnection(vConnetionString, vUsername, vPassword);   
  }
  catch (Exception E)
  {
   System.out.println("Error in connection with database.");
   E.printStackTrace();
  }
  
  return sqlConnection; 
 }
 
 public String[][] mExecuteQueryOnDatabase(WebDriver driver, Connection sqlConnection, String vQuery) throws SQLException
 {  
  int vRowCount, vColumnCount;  
 
  // Execute Query
  Statement st = sqlConnection.createStatement();
  ResultSet rs = st.executeQuery(vQuery);  
  ResultSetMetaData rsmd = rs.getMetaData();
     
  // Get row and column count
  vColumnCount = rsmd.getColumnCount();
  rs.last();
  vRowCount = rs.getRow();   
  rs.first();
        
  System.out.println("Number of Column in result : " + vColumnCount);
  System.out.println("Number of Row in result : " + vRowCount);      
  
  String[][] vResult = new String[vRowCount][vColumnCount];
  
  // Convert result set into String array
  int i=0;
  while(rs.next())
  {
   for(int j=0; j<vColumnCount; j++)
   {
    vResult[i][j] = rs.getString(j+1);
   }
   i++;
  }
   
  try
  {
   sqlConnection.close();
  }
  catch (SQLException E) 
  {   
   E.printStackTrace();
  }
  
  return vResult;
 }
 
 public String[][] mConnectDatabaseAndExecuteQuery(WebDriver driver, String vDatabaseType, String vDatabaseName, String vUsername, String vPassword, String vQuery) throws SQLException
 {
  Connection sqlConnection = null;
  
  if(vDatabaseType.equalsIgnoreCase("local"))
  {
   sqlConnection = mConnectToLocalDatabase(driver, vDatabaseName, vUsername, vPassword);
  }
  else if(vDatabaseType.equalsIgnoreCase("server"))
  {
   sqlConnection = mConnectToServerDatabase(driver, vDatabaseName, vUsername, vPassword);
  }
  
  String[][] vResult = mExecuteQueryOnDatabase(driver, sqlConnection, vQuery);
  
  return vResult;
 }
   
}

Please add comment if you have any question.

How to Handle Configuration File in Selenium

When we are working on automation framework then we have to follow such strategy so we can avoid hard code value as much as we can.

There are many parameters in every framework we can be changed frequently for running test case on different environemnt as well as with different configuration. So, we can not use hard code value for such parameters. We have to use configuration file for this scenario.

In that file, we can store all the parameters which are used in framework like browser name, browser type, application URL etc.

If you are using C# in selenium then go to below link for reading configuration file.
How to Read Configuration File in Selenium C#

We have to create one method to access value of those parameters from configuraiton file.

First of all we have to create one configuratioj file. Please follow below step.

Create Configs folder under src folder
Right click on Configs folder
Go to New > File
Give file name 'Configuration.properties'

Now add parameters which you are going to use in your framework.

Now, we have to create one method to access all these values of parameters. For that I have created one class 'ConfigFileReader'. I have created all methods for accessing values that class.

import java.io.BufferedReader;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.util.Properties;

public class ConfigFileReader 
{
 
 private Properties properties;
 private final String propertyFilePath= "Configs/Configuation.properties";
 
 
 public ConfigFileReader(){
  BufferedReader reader;
  try {
   reader = new BufferedReader(new FileReader(propertyFilePath));
   properties = new Properties();
   try {
    properties.load(reader);
    reader.close();
   } catch (IOException e) {
    e.printStackTrace();
   }
  } catch (FileNotFoundException e) {
   e.printStackTrace();
   throw new RuntimeException("Configuration.properties not found at " + propertyFilePath);
  }  
 }
 
 public String getChromeDriverPath()
 {
  String driverPath = properties.getProperty("chromeDriverPath");
  if(driverPath!= null) return driverPath;
  else throw new RuntimeException("driverPath not specified in the Configuration.properties file.");  
 }
 
 public String getBrowserName()
 {
  String browserName = properties.getProperty("browserName");
  if(browserName!= null) return browserName;
  else throw new RuntimeException("Browser name not specified in the Configuration.properties file.");  
 }
 
 public String getBrowserType()
 {
  String browserType = properties.getProperty("browserType");
  if(browserType!= null) return browserType;
  else throw new RuntimeException("Browser Type not specified in the Configuration.properties file.");  
 }
 
 public long getImplicitlyWait()
 {  
  String implicitlyWait = properties.getProperty("ImplicitlyWait");
  if(implicitlyWait != null) return Long.parseLong(implicitlyWait);
  else throw new RuntimeException("implicitlyWait not specified in the Configuration.properties file.");  
 }
 
 public String getApplicationUrl()
 {
  String url = properties.getProperty("appURL");
  if(url != null) return url;
  else throw new RuntimeException("APP URL not specified in the Configuration.properties file.");
 }
 
 public String getTestDataSheetPath()
 {
  String path = properties.getProperty("testDataSheetPath");
  if(path != null) return path;
  else throw new RuntimeException("Test data sheet not specified in the Configuration.properties file.");
 }

}

You can see I have created separate function for access value of particular parameter. So you have to create separate function for all parameters.

Please add comment if you have any question.

How to Compare Two Images in Selenium

Some time in testing we have to do image comparision as verification point. So, in this blog I expalin how to compare two images in selenium.

I have created one function for compare two images. you can use directly into your framework.

first of all you have to import below packages into your code.

import java.awt.image.BufferedImage;
import java.io.File;
import javax.imageio.ImageIO;

Now, you can use below function for comparison of two images.

public boolean mCompareImages(BufferedImage img1, BufferedImage img2)
 {
  boolean bCompareImage = true;
  try
  {
   if (img1.getWidth() == img2.getWidth() && img1.getHeight() == img2.getHeight())
   {
    for (int i = 0; i < img1.getWidth(); i++)
    {
     for (int j = 0; j < img1.getHeight(); j++)
     {
      if (img1.getRGB(i, j) != img2.getRGB(i, j))
      {
       bCompareImage = false;
      }
     }
    }
   }
   else
   {
    bCompareImage = false;
   }   
  }
  catch(Exception E)
  {
   bCompareImage = false;
  }
  
  return bCompareImage;
 }

Here, we are comparing tow images first in terms of heights and width. If Height and Width both are same then we are comparing RGB components of both images. If RGB components are same then images are same otherwise it is different.

Function is return Boolean value. If images are same then true and not same then false.

Please add comment if you have any question.

Automate Your Test

Tuesday 27 February 2018

How to Do Database Connection in Selenium

Database Operations with Selenium Wrapper Automation

How to Handle Configuration File in Selenium

How to Compare Two Images in Selenium

Popular