Know How to Perform Database Testing Using Selenium – A Step By Step Guide

As the world is evolving towards big data, database plays a major role in handling the records and maintaining the sequence of it. To ensure that there are no defects while processing the data, Database Testing is essential. In Automation Testing, Selenium is one such tool that helps in providing functionalities to test the database. Get further details with the Selenium Online Training.

Java Database Connectivity

JDBC is one of the standard Java API for database-independent connectivity between the Java programming language and a wide range of databases. This application program interface (API), lets you encode the access request statements, in a Structured Query Language (SQL). They are then passed to the program that manages the database. It mainly involves opening a connection, creating a SQL Database, executing SQL queries and then arriving at the output.

We can use JDBC API to access tabular data stored in any relational database. With the help of this JDBC API, we can save, update, delete and fetch data from the databases. It is similar to the Open Database Connectivity (ODBC) provided by Microsoft.

Common JDBC Components

The JDBC API provides the following interfaces and classes −

• DriverManager: It is used to manage a list of database drivers. This driver recognizes a certain subprotocol under JDBC in order to establish a database Connection.
• Driver: It is an interface that handles the communications with the database server.
• Connection: It is an interface that consists of all the methods required to connect to a database. The connection object represents communication context wherein the entire communication with the database is through connection object only.

Now let’s move on to the next topic and look at the steps required to create a JDBC Application.

Steps to create a JDBC Application

In order to create a JDBC Application, we need to follow a few steps. Let’s see what are they.

1. Import the packages: First, you need to include the packages that contain the JDBC classes mainly needed for database programming. 
2. Register the JDBC driver: Here you have to initialize a driver so that you can open a communication channel with the database. You can register to the database with the help of below command like:
   Class.forName(“com.mysql.jdbc.Driver”); // class.forName load the Driver class
3. Open a connection: After the driver registration, you can use the getConnection() method to create a Connection object, which represents a physical connection with the database.
4. Execute a query: Here you need to use an object of type ‘Statement’ for building and submitting a SQL statement to the database.
5. Extract data from the result set: To retrieve the data from the result set, you need to use the appropriate getXXX() method.
6. Clean up the environment: Here you need to explicitly close all the database resources that rely on JVM garbage collection.

If you wish to know how to create a JDBC application and execute queries, you can check out this article on Advanced Java Tutorial. Now let’s see how to perform database testing using Selenium. Before I start off, first, let’s understand what is Selenium WebDriver.

What is Selenium WebDriver?

Selenium is one of the open source portable framework used to automate testing of web applications. It is flexible when it comes to testing functional and regression test cases. Selenium test scripts can be written in different programming languages like Java, Python, C# and many more. All these selenium test scripts can run across various browsers like Chrome, Safari, Firefox, Opera and also provides support across various platforms like Windows, Mac OS, Linux, Solaris. Selenium also helps in creating robust, browser-based regression automation suites and perform tests.

I hope you understood the fundamentals of Selenium. Now let’s move further and understand how to perform database testing using Selenium.

Database Testing using Selenium

In general, Selenium does not support Database Testing, still, it can be partially done using JDBC and ODBC. In this article, I am basically connecting the Java program with a database to fetch the data and verify it using TestNG.

Let’s see a step by step procedure of performing database testing using Selenium.

Step by Step Procedure of Database Testing

Step 1: You need to create a database. If you wish to learn how to execute MySQL commands, then you can check out this article on MySQL Tutorial.

Step 2: Once you finish creating tables and inserting values, then you can establish a connection to the database.

Step 3: After establishing the connection, you can execute the queries and process the records that are present in your database. You can refer to Advanced Java Tutorial article in order to understand how to execute the queries and process the result-set.

Now, the interesting thing is that I will integrate TestNG with JDBC to perform Database Testing. Let’s see how to do that with the help of the below program.

package co.edureka.pages;
import org.testng.annotations.AfterTest;
import org.testng.annotations.BeforeTest;
import org.testng.annotations.Test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class DatabaseTesingDemo {
// Connection object
static Connection con = null;
// Statement object
private static Statement stmt;
// Constant for Database URL
public static String DB_URL = "jdbc:mysql://localhost/emp";
// Constant for Database Username
public static String DB_USER = "your_user";
// Constant for Database Password
public static String DB_PASSWORD = "your_password";

@BeforeTest
public void setUp() throws Exception {
try{
// Make the database connection
String dbClass = "com.mysql.cj.jdbc.Driver";
Class.forName(dbClass).newInstance();
// Get connection to DB
Connection con = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
// Statement object to send the SQL statement to the Database
stmt = con.createStatement();
}
catch (Exception e)
{
e.printStackTrace();
}
}

@Test
public void test() {
try{
String query = "select * from employees";
// Get the contents of userinfo table from DB
ResultSet res = stmt.executeQuery(query);
// Print the result untill all the records are printed
// res.next() returns true if there is any next record else returns false
while (res.next())
{
System.out.print(res.getString(1));
System.out.print("	" + res.getString(2));
System.out.print("	" + res.getString(3));
System.out.println("	" + res.getString(4));
}
}
catch(Exception e)
{
e.printStackTrace();
}
}

@AfterTest
public void tearDown() throws Exception {
// Close DB connection
if (con != null) {
con.close();
}
}
}

In the above code, I have specified database URL, Database username and password to access the database.

Next, I have used Before Test annotation to perform the actions that should happen before executing the test cases. In the above example, I am establishing a connection to the database by registering the MySQL driver. This is because I am using MySQL Database. After that, I am creating a statement object.

Once the database connection is complete, the next step is to execute the queries and process the results. So all the procedures of executing the queries and printing the results and processing the records is a part of the test. So it will be followed by Test annotation of TestNG.

After performing the test, the last step is to close the database connection. That’s why it’s followed by AfterTest annotation. This is how you need to split the tasks accordingly. When you execute the above code as TestNG test, it will print all the details present in the database and execute the test cases.

Your output should look as shown below:

[RemoteTestNG] detected TestNG version 6.14.2
100 18 Zara Ali
101 25 Mahnaz Fatma
102 30 Zaid Khan
103 28 Sumit Mittal
PASSED: test

===============================================
Default test
Tests run: 1, Failures: 0, Skips: 0
===============================================

===============================================
Default suite
Total tests run: 1, Failures: 0, Skips: 0
===============================================