#TECH

Database Testing using Selenium

Database Testing using Selenium

As we know that Database is used in every application where user information needs to be stored. For example: an eCommerce website. All the products which are listed on the screen are stored at back-end with complete information such as product availability, payment details, etc. So, whenever any action is performed on user interface such as someone orders something on this e-Commerce site then the state of this product in database gets changed. It may happen that there was a single product which user has ordered and now it becomes out of stock. This information will get updated in database first and then it will be reflected on user interface.

Here, testers have to check the database after every action just to ensure that all the information gets saved in correct tables with correct information.

There are two ways to test a database:

  1. Manually
  2. Automation

In manual testing, tester has to look on each table manually and can report the issue. But, it becomes more complex when there are multiple actions on the screen and checking all the tables one by one is time-consuming. For better performance, we can test the database with the help of automation. It will reduce manual effort up to a great extent and can deliver quality work on time.

Here, I am using Selenium Web driver for automating database testing of the application. Selenium Web driver is an automation tool used for automating web and mobile applications. It provides in-built APIs which helps in writing user-friendly and easy to understand code. It interacts with the web elements and performs the expected function. Tester can use any programming language such as Java, C#,  Ruby, etc to write Selenium based test scripts which will be executed on different browsers.

Here, in my blog, I will be discussing  database testing using selenium webdriver. Selenium webdriver will simply interact with my browser and do the expected tasks which I have scripted in test script using java.

Let’s Discuss database testing in more details.

What is Database Testing?

Database Testing is the verification of retrieved values from the database by a web or desktop application. Data which is displayed on UI should match as per the records stored in the database.

Significance

The importance of database testing in software testing should not be ignored as it is the data which is visible to the user. As software industry grows day by day, applications get more complex. So, there is a need to test its various parameters to make it reliable and robust.

Example:
Let’s take a real world example of banking application where we can understand about the significance of database testing in more depth. Suppose, there is client A who deposits it’s amount in the bank and gets acknowledgement, which should have a transaction id and payment amount with its status.
But, due to some technical issue in the banking application, transaction id gets generated in combination of alphanumeric + special characters and gets saved into database only. Ideally, it should be a numeric value.

User raises this issue with the bank. The bank finds that database migration was done yesterday for the application and it might cause this problem in generating transaction id for the transactions. They forward this issue to technical team and after analyzing, root cause was:

On saving transaction information from application UI, a transaction id gets generated in the Database. But, due to migration of this database from older to newer version, now, transaction id gets saved after manipulation. Hence, this manipulated id starts displaying on acknowledgement screen and for verifying it, database testing is required so that tester can retrieve the correct data from the correct table.

In this way, database testing came into existence and become more important for all platforms.

Aspects to be tested:

  • Data Mapping

    Data Mapping refers to the mapping of data between GUI and its relational database. So, here question arises, how to test data mapping. Tester needs to verify that all the information which is entered into the application, should be saved in the correct DB tables as well as in correct format. For example: If user is adding current date in application, and in the DB table it should be saved in dd/mm/yyyy format then here verification check is that entered date should also gets saved in same DB format i.e. dd/mm/yyyy. If it is any other format which is different from db table definition then tester can log it as a bug.
    Also, if user is performing any CRUD (Create, Retrieve, Update and Delete) operation, then here also, data mapping should be verified. The same value which is displayed on UI should be saved in DB and vice-versa.

  • Ensure ACID properties

    ACID Properties refer to Atomicity, Consistency, Isolation, and Durability. For a DB transaction, these properties must always hold true

  • . For more details about ACID properties, please refer the Link.
High-Level diagram of database testing

High-Level diagram of database testing

 

Scenario under consideration:

I will sign in into the Gmail application using selenium script and after this, will verify this username entry into Database. So, this interaction of application with its database will be performed by JDBC driver and will process SQL queries written by me in a property file.

Step-wise operations

Following are the step by step guide to automate database testing using the selenium script written in Java.

Step 1:

Make a connection with DB:
Install MySql and MySql Workbench. Don’t forget to note down the following items:
Username
Password
Port Number

Now, connect to MySql server from MySql Workbench.

connect to MySql server from MySql Workbench

 

Step 2:

Click on “Connect to Database” and enter the following details:

Connect to Database

A name to your DB connection
Port Number
Username
Password

NOTE: These Port Number, Username and Password are same as which we noted down in Step 1.
Click on “Test Connection” just to check whether the DB connection is established or not with the entered credentials.

Step3:

After successful connection, now, you need to create a database which is having a table with some data. Here, I am taking an example of “emp” named database having table named as “employee”.

create a database

 

Step 4:

Now, insert some values in the “employee’ table as following:

insert some values

 

Step 5:

Now, its time to do some coding. For this, you will need Eclipse and create a Java Project under it.
Here, I have created “Database” named Java Project having ‘dbpackage” as package name with few classes.

Eclipse and create a Java Project

 

Step 6:

Download the MySQL JDBC connector and add this jar into your project. JDBC (Java Database Connectivity) connector helps java application to interact with the database.

Download the MySQL JDBC connector

 

Step 7:

Now, we will create a property file which keeps key/value pair. Purpose of using property file is to manage dynamic data easily. If any property changes, then you need to change in property file only, not in massive code. Here, I am keeping:

  • DBConnectionString (of underlying Database)
  • Url (of application under consideration),
  • DB username and password,
  • Sql query which will fetch the record from table where sign-in data is stored.
  • Specific table column name which we need to verify. (optional)

Create a property file and paste the following code into it.

DBConnectionString = jdbc:mysql://localhost:3306/emp?user=root&password=ankita@123&useUnicode=true&characterEncoding=UTF-8
url = https://mail.google.com
username = ankita.jangra@quovantis.com
password = Test@123
query = select Emp_Age,Emp_Name from employee where Emp_Name like 'ankita%' ;
dbColumn = Emp_Name

 

Step 8:

Now, create a class for reading data from property file and paste the code as following-

package dbpackage;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;

import org.openqa.selenium.WebDriver;
import org.openqa.selenium.lift.find.InputFinder;


public class ReadPropertyFile {
	
	static Properties prop = null;
	InputStream input = ReadPropertyFile.class.getClassLoader().getResourceAsStream("dbpackage/Properties");
	
		public ReadPropertyFile() throws IOException 
		{
			prop = new Properties();

              prop.load(input);
}
	
	public String getUsername()
	{
		return prop.getProperty("username");

	}
	public String getUrl()
	{
		return prop.getProperty("url");
	}
	
	public String getPassword()
	{
		return prop.getProperty("password");
	}
	
	public String getConnectionString()
	{
		return prop.getProperty("DBConnectionString");
	}
	public void getBrowserCmd()
	{
		prop.getProperty("browser");
	}
	public String getQuery()
	{
		return prop.getProperty("query");
	}
	public String[] getColumn()
	{
		
		 String[] dbColumns = prop.getProperty("dbColumn").split(",");
         for (String w : dbColumns) {
        	 prop.getProperty("dbColumn");
         }
		
	return dbColumns;
	}
}
	

 

Step 9:

Create a separate class for verifying DB table data. This class is responsible for fetching the matched data as per the passed SQL query and db column name.

package dbpackage;

import java.awt.List;
import java.sql.Connection;

import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;

import static org.junit.Assert.*;

import com.mysql.jdbc.ResultSetMetaData;

public class dbVerification {
	static Connection conn = null;
	ReadPropertyFile file = null;

	// Create Connection to DB
	// String connectionString =
	// "jdbc:mysql://localhost:3306/emp?user=root&password=ankita@123&useUnicode=true&characterEncoding=UTF-8";

	public ArrayList<String> db(String query, String[] dbColumn)
			throws Throwable {

		try {
			String value = null;
			file = new ReadPropertyFile();
			/*
			 * String dbUrl = "jdbc:mysql://192.168.1.100:3306/lotus_phase2";
			 * String username = "root"; String password = "root";
			 */
			conn = DriverManager.getConnection(file.getConnectionString());
			Class.forName("com.mysql.jdbc.Driver");

			// Create statement for database connection
			Statement stat = conn.createStatement();

			// Send query to db
			ResultSet rs = stat.executeQuery(query);
			/*
			 * java.sql.ResultSetMetaData metaData = rs.getMetaData(); int count
			 * = metaData.getColumnCount(); System.out.println(count);
			 */
			ArrayList<String> values = new ArrayList<>();
			while (rs.next()) {
				for (int i = 0; i < dbColumn.length; i++) {
					values.add(rs.getString(dbColumn[i]));
					System.out.println("Stored Username in DB:" +" " +values);
			
				}
				// value[] = rs.getString(dbColumn);

			}
			return values;

		} catch (Throwable e) {
			throw e;
		} finally {
			if (conn != null) {
				conn.close();
			}
		}
	}

}

 

Step 10:

Now, we need to write a selenium script which will help in automating Gmail sign in process. For this, create a separate class for all the methods used in the test script. The written script is-

package dbpackage;

import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.firefox.FirefoxDriver;

public class Methods {

	WebDriver driver = null;

	public String login() throws Exception

	{
		ReadPropertyFile file = new ReadPropertyFile();
		try {
			driver = new FirefoxDriver();
			driver.get(file.getUrl());
			driver.findElement(By.id("Email")).sendKeys(file.getUsername());
			driver.findElement(By.id("next")).click();
			Thread.sleep(1000);
			String errMsg1 = driver.findElement(By.id("errormsg_0_Email"))
					.getText();
			driver.findElement(By.id("Passwd")).sendKeys(file.getPassword());
			driver.findElement(By.id("signIn")).click();
			Thread.sleep(3000);
			String url = driver.getCurrentUrl();
			if (url.contains("https://mail.google.com/mail")) {
				System.out.println("Entered Username on UI: " + file.getUsername());
			} else {
				String errMsg2 = driver.findElement(By.className("error-msg"))
						.getText();
				System.out.println("Unable to Login with:" + file.getUsername()
						+ errMsg2);
				driver.close();
			}

			driver.close();

		} catch (Exception e) {
			System.out.println(e);
		}
		return file.getUsername();

	}
}

 

Step 11:

Here, we have a Main class which will call the above classes. First of all, property file is loaded and then login() method (written in selenium script) is called which performs the Gmail sign in action and then control is sent  to the dbVerification() class where cross checking of both (Username with which user Signs in and an entry in Database after Sign in) will be done.

package dbpackage;

import java.util.ArrayList;

import org.openqa.selenium.WebDriver;

public class login {

	WebDriver driver = null;
	static ReadPropertyFile file = null;

	public static void main(String[] args) throws Throwable {
		file = new ReadPropertyFile();
		Methods method = new Methods();
		String temp = method.login();

		dbVerification dbVerify = new dbVerification();
		ArrayList<String> dbValue = dbVerify.db(file.getQuery(),
				file.getColumn());
		/*System.out.println(dbValue.get(0));*/
		if ((temp.equals(dbValue.get(0)))) {
			System.out.println("UI and DB value matches");
		} 
		else {
			System.out.println("Record doesn't exists in Database");
		}

	}

}

 

Output

User login with ankita.jangra@quovantis.com into the Gmail account . After login this username entry gets inserted into under “emp” table name (as provided in SQL query). Here, dbVerification() class cross checked this username and found that this entry is available in “employee” table under passed column name as “Emp_Name”.

Output

As this record exists in Database, hence, this test results as “UI and DB value matches” which means that username with which user sign in into Gmail account is available in Database.

Conclusion

  • For testing database using Selenium, you will need to:
    1. Setup a connection to the Database
    2. Send Queries to the Database
    3. Process the results
  • The Syntax to connect to Database is

    DriverManager.getConnection(URL, “userid”, “password” )

  • For sending SQL queries, Statement Object is used as:

    Statement stmt = con.createStatement();

  • Store the query result in resultset as:
    ResultSet rs = stmt.executeQuery(select * from employee;);
  • Use verification method to verify DB and UI values.

Source Code for downloading:

Zip File

References:

  1. Link – http://www.guru99.com/database-testing-using-selenium-step-by-step-guide.html
  2. Youtube Video – https://www.youtube.com/watch?v=HDRoiK0BWYs