Selenium Database Testing (Using WebDriver and JDBC API)

In the last Selenium tutorial, we delved into how to address recurrent problems in Selenium scripts. We touched on advanced topics such as keyboard and mouse events, as well as accessing multiple links using lists.

Proceeding with our advanced Selenium course series, we are now going to delve into the subject of Database testing utilizing Selenium WebDriver.

We’ll go over the key procedures, including connecting to the database, executing queries, accessing data, and disconnecting from the database. We will also explore the practical situations where Database testing is necessary for comprehensive testing.

Prior to delving into the technicalities of Automated Database testing, let’s consider a few scenarios where Database testing is mandatory with Automation Testing. Nevertheless, it’s crucial to realize that Database testing is a unique kind of testing, whereas Selenium WebDriver is a tool used to automate user interactions with the Application UI.

Technically speaking, we are not conducting Database Testing directly. We are testing the application in conjunction with the Database to ensure that modifications are reflected at both ends, thereby facilitating early defect detection.

Virtually all web applications necessitate a backend for data storage. MySQL, Oracle, and SQL Server are the databases frequently used.

Now, let’s examine some scenarios that emphasize the need for Database testing in conjunction with Automation Testing.

Examine the following situations

#1) At times, we need to confirm that data input from the UI is consistently reflected in the database. In such cases, we fetch data from the database and compare it against the data input from the UI. For instance, registration forms, user data, user profiles, and user data updates or deletions. A potential test scenario could be “Confirm that the user’s information is successfully stored into the database as soon as the user registers in the application.”

#2) An additional use case for conducting database testing with Selenium WebDriver is when the user needs to load test data or expected data from the Database. In such instances, the user connects to the Database utilizing a third-party API, executes queries to fetch data, and then compares the retrieved data with the data displayed on the Application UI.

#3) Another use case involves performing associative Database Testing. Suppose we carry out an operation on the application’s UI and want to test its reflection in the Database. It could be that the affected data resides in various tables of the database due to association. Therefore, it’s prudent to test data reflection in all impacted areas.

Selenium replicates user interactions with the application under test, including keyboard events and mouse actions. However, if the user needs to automate anything beyond browser-based web application interactions, Selenium alone may not suffice. Additional tools or abilities are needed for comprehensive testing.

Therefore, in all the above scenarios, it becomes necessary to conduct Database Testing in tandem with UI Automation. We need to verify business logics by manipulating data and confirming its reflection. We also need to validate technical elements of the Database itself, such as soft delete and field validation.

Moving on, let’s proceed with the actual implementation. Prior to creating Selenium WebDriver scripts to extract data from the database, we need to create test data in the database. For this lesson, we will employ MySQL as the database.

Generating test data in the Database

If you have not yet downloaded the database, you can do so by following this link. Follow the basic steps to download and install the database.

=>> Refer to this guide to download and install the MySQL Database.

Once the database is successfully installed, initiate the MySQL Command Line Prompt. You may be required to enter the password; the default password is “root”.

Note: You can also locate GUI-based clients online to connect with the database, such as Query Browser or Work Bench.

Establishing a New Database

The subsequent step is to create a test database with tables and records to establish a connection with the database and execute queries.

Step 1) Input the command “show databases” to view all the existing databases.

show databases;

Step 2) Enter the command “create database user;” to create a database named “user”.

create database user;

Ensure that the newly established “user” database appears in the list of databases.

Step 3) Type the commands “use user;” to select the newly created database, and “show tables;” to view all the tables in the “user” database.

use user;
show tables;

Note that the “show tables;” query displays an empty set since there are no tables in the “user” database yet.

Now, let’s create some tables and insert records.

Step 4) Input the following command to create a table with 4 fields/columns (userId, userName, userAge, userAddress).

create table userinfo
(
userId int,
userName varchar(255),
userAge int,
userAddress varchar(255)
);

Next, it’s time to add some data records to the “userinfo” table.

Step 5) Type the following command to insert data into the table:

insert into userinfo (userID, userName, userAge, userAddress) values (‘1’, ‘shruti’, ’25’, ‘Noida’);

To view the added data, enter the following command:

select * from userinfo;

Likewise, you can add more data to the table and generate additional tables.

Now that we’ve established our database, let’s proceed to learning how to perform automated queries to retrieve records from the database.

As mentioned earlier, Selenium WebDriver alone is incapable of performing Database testing. We need to use the Java Database Connectivity API (JDBC) to establish a connection and interface with the data source and retrieve data using automated queries. To utilize the JDBC API, Java Virtual Machine (JVM) must be operational on the system.

JDBC Workflow

We will concentrate on the following processes:

1. Establishing a connection with the database
2. Performing queries and updating statements to retrieve data (CRUD Operations)
3. Utilizing and manipulating the fetched data as a result set (a collection of data organized in rows and columns)
4. Disconnecting from the database

To automatically test the database with our Selenium WebDriver test scripts, we will employ JDBC connectivity within our test scripts to connect to the database. After the connection is established, we can execute various CRUD operations on the database.

In this tutorial, we’ll focus on the “Read operation and its variants” and their implementation in Selenium WebDriver scripts. However, before that, let’s manually test the scenario using the “MySQL command line”.

Scenario:

<

ol>

Open the Database server and connect to the “user” database.

Show all the records from the “userinfo” table.