In our last Selenium tutorial, we learned to troubleshoot some recurrent problems in Selenium scripts. We discussed some advanced concepts wherein we would deal with mouse and keyboard events, and accessing multiple links by implementing lists.
Moving ahead with our advanced topics in the Selenium training series, we will introduce you to the concept of Database testing using Selenium WebDriver.
We would discuss the basic processes like database connection, executing queries, fetching data disconnecting database instances, etc. We would also discuss various practical implications where we need database testing with automation testing to test the complete end-to-end scenarios.
Table of Contents:
Database Testing Using Selenium WebDriver
Before moving ahead with the technical implications associated with Automated Database testing, let us discuss a few scenarios where we require performing Database testing along with Automation Testing.
Before moving forward, I want to clarify that Database testing is a specialized type of testing. Selenium WebDriver is a tool designed for simulating and automating user interactions with the Application UI.
In essence, we’re not exactly conducting Database Testing, but testing our application and Database together to detect defects early. Absolutely all the web applications need a backend to store the Data. Databases like MySQL, Oracle, and SQL Server are reasonably popular these days.
Application of Database Testing and Automation Testing
Consider the following scenarios:
#1) At times, we need to ensure that the data entered from the UI consistently reflects in the database. Thus, we retrieve the information from the Database and verify the retrieved information against the information supplied from the UI.
For example, registration forms, user data, user profiles, updates, and deletes of user data. Thus, the test scenario to automate can be “To verify that the user’s information is successfully saved into the database as soon as the user registers in the application”.
#2) Another use case of performing database testing with Selenium WebDriver may arise when the user is directed to load the test data or expected data from the Database.
Thus, in such a case, the user would connect with the Database using a third-party API, execute queries to retrieve data from the dataset, and then assert the data fetched from the Database with the actual data that is populated on the Application UI.
#3) Another use case is to perform associative Database Testing. Assume that we performed an operation on the application’s UI, and we want to test the reflection in the Database. It may be a case that the impacted data resides in various tables of the database due to the association. Therefore, it is always advisable to test data reflection in all the impacted areas.
Selenium simulates the user interactions with the application under test. It can simulate keyboard events, mouse actions, etc. But if the user desires to automate anything outside the vicinity of browser-web application interactions, then selenium can’t be of much help. Thus, we require other tools or capabilities to perform end–to–end testing.
Thus, in all the above scenarios, we may be required to perform Database Testing along with UI Automation. We may check business logic by manipulating the data and verifying its reflection. We may also check the technical aspects of the Database itself, like soft delete, field validation, etc.
Let us now move ahead with the actual implementation. Before developing Selenium WebDriver scripts to extract data from the data source, let us create test data in the database. For this tutorial, we will use MySQL as a database.
Creation of Test Data in the Database
If you haven’t downloaded the database yet, download it using the link. The user is expected to follow some basic steps to download and install the database.
=>> Read this tutorial to download and install MySQL Database.
Once the database is installed, the user can open the MySQL Command Line Prompt, which will look like this screenshot. The application might ask the user to enter the password. The default password is “root”.
Note: Users can also find GUI-based clients over the internet to connect with the database. To name a few, the user can download and install the Query Browser or Work Bench.
Creation of a New Database
The next step is to create the test database with a few tables and records stored in those tables to connect with the database and execute queries.
Step #1: Type “show databases” to see all the already available databases
show databases;
Step #2: Type “create database user;” to create a database named “user”.
create database user;
Note, you’ll be able to see the database name in the list once the user is created.
Step #3: Type “use user;” to select the newly created database. Also, type “show tables;” to view all the tables available in the user database.
use user;
show tables;
Take note that the Empty set is shown in the result of the “show tables;” query, as there were no tables available within the user database.
Let us now take a few tables and add records to them.
Step #4: Type 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)
);
The next step is to add some data records to the “userinfo” table.
Step #5: Type the following command to insert data into the table a table for all four fields 4 fields/columns (userId, userName, userAge, userAddress).
insert into userinfo (userID, userName, userAge, userAddress) values ('1', 'shruti', '25', 'Noida');
To view the added data, type the following command:
select * from userinfo;
Similarly, you can add more data to your table and can create other tables as well. Now, that we have created our database. We can move ahead and understand the implementation of automated queries to fetch the records from the database.
As we also iterated earlier, Selenium WebDriver is a tool for UI Automation. Thus, Selenium WebDriver alone is ineligible to perform database testing, but this can be done using Java Database Connectivity API (JDBC).
The API lets the user connect and interact with the data source and fetches the data with the help of automated queries. To exploit the JDBC API, it is required to have a Java Virtual Machine (JVM) running on the system.
JDBC Workflow
We would keep our focus aligned with the following processes:
- Creating a connection with the database.
- Executing queries and update statements to extract/fetch data (CRUD Operations).
- Using and manipulating the data extracted from the Database as the result set. (Result set is a collection of data organized in rows and columns).
- Disconnecting the database connection.
As said earlier, to test the database automatically from our Selenium WebDriver test scripts, we would connect with the Database via JDBC connectivity within our test scripts. Post to the connection, we can trigger as many CRUD (Create, Read, Update, and Delete) operations on the Database.
In this tutorial, we will discuss “Read operation and its variants” and their implementation in the Selenium WebDriver script. But before that, let us check the test scenario manually using the “MySQL command line”.
Scenario:
#1) Open the Database server and connect to the “user” database.
#2) List down all the records from the “userinfo” table.
Syntax: select * from userinfo;
#3) Close the Database connection.
Notice that the read query will list down all the user data present in the userinfo table. The table consists of the following columns.
- userId
- username
- userAge
- userAddress
The result also show that there is only a single data set present within the table.
Now, let us execute the same scenario using the Java Class.
To access the Database, the user is leveraged to choose amongst the diverse connector options available to connect with the Database. Most of the database connectors are freely distributed as “jar” files. As we are using MySQL as a data source, therefore we are required to download the jar file specific to MySQL.
The jar file can be downloaded from: here or here.
Step #1: The first and foremost step is to configure the project’s build path and add “mysql-connector-java-3.1.13-bin.jar” file as an external library.
Step #2: Create a java class named “DatabaseTesingDemo”.
Step #3: Copy and paste the below code into the class created in the above step.
Code Sample:
import org.junit.After;
import org.junit.Before;
import org.junit.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:3306/user";
// Constant for Database Username
public static String DB_USER = "root";
// Constant for Database Password
public static String DB_PASSWORD = "root";
@Before
public void setUp() throws Exception {
try{
// Make the database connection
String dbClass = "com.mysql.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 userinfo";
// 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("\t" + res.getString(2));
System.out.print("\t" + res.getString(3));
System.out.println("\t" + res.getString(4));
}
}
catch(Exception e)
{
e.printStackTrace();
}
}
@After
public void tearDown() throws Exception {
// Close DB connection
if (con != null) {
con.close();
}
}
}
The output of the above code is:
1 shruti 25 Noida
2 shrivastava 55 Mumbai
Read Statement Variants
Where clause with single condition
String query = "select * from userinfo where userId='" + 1 + "'";
ResultSet res = stmt.executeQuery(query);
Output:
1 shruti 25 Noida
Where clause with multiple conditions
String Address ="Mumbai";
String query = "select * from userinfo where userId='" + 2 + "' and userAddress='"+Address+"'";
ResultSet res = stmt.executeQuery(query);
Output:
2 shrivastava 55 Mumbai
Display userId
String query = "select userId from userinfo";
ResultSet res = stmt.executeQuery(query);
Output:
1
2
Display userId with where clause
String Address ="Noida";
String query = "select userId,userName from userinfo where userAddress='"+Address+"'";
ResultSet res = stmt.executeQuery(query);
Output:
2
shrivastava
Thus, in the same way, the user can execute various queries on the database.
With this, let us shed some light on result accessibility methods.
Result Accessibility Methods:
| Method name | Description |
|---|---|
| String getString() | Method is used to fetch the string type data from the result set |
| int getInt() | Method is used to fetch the integer type data from the result set |
| boolean getBoolean() | Method is used to fetch the boolean value from the result set |
| float getFloat() | Method is used to fetch the float type data from the result set |
| long getLong() | Method is used to fetch the long type data from the result set |
| short getShort() | Method is used to fetch the short type data from the result set |
| double getDouble() | Method is used to fetch the double type data from the result set |
| Date getDate() | Method is used to fetch the Date type object from the result set |
Result Navigation Methods:
| Method name | Description |
|---|---|
| boolean next() | Method is used to move to the next record in the result set |
| boolean previous() | Method is used to move to the previous record in the result set |
| boolean first() | Method is used to move to the first record in the result set |
| boolean last() | Method is used to move to the last record in the result set |
| boolean absolute(int rowNumber) | Method is used to move to the specific record in the result set |
Conclusion
Through this tutorial, we tried to make you acquainted with the concept of Automated Database Testing. We emphasized the technical implications and needs of Database Testing.
As our entire series was focused on Selenium, the reader may get misled and can create an impression that this tutorial would teach to perform Database testing using Selenium, but like I mentioned several times earlier, anything that lies outside the periphery of UI testing, cannot be handled by Selenium.
Therefore, we introduce Java Database Connectivity (JDBC) API to perform Database Testing by embedding the code within the Selenium WebDriver scripts.
JDBC makes it possible for the java class to connect to the Database, retrieve data from the database, or for a matter of fact, perform any of the CRUD operations, manipulate the resultant data, and close the connection.
Thus, the tutorial constitutes the basic sample implementation of the above-mentioned process.
Next Tutorial #29: We will move ahead with advanced Selenium topics. In the next tutorial, we will cover the Selenium GRID – which is used when you have to perform multi-browser testing and you have numerous test cases.
without main method how it is working and print the output ?DatabaseTesingDemo class
Nice! Too useful for professional testers!
Thank you!
This is NOT Selenium Webdriver.
you have used Junit to execute the database script using JDBC.
There is no single command of Selenium i can see in it.
please change the Title of your post.
Best Regards,
Ranga
Could someone answer like, what are the .jar files needed to write this code in Eclipse?
Thanks… but “Selenium webdriver” is not used above, how to exactly use webdriver for database testing?
thankq,,
really its very useful for me
Yes ….I like your postings on software testing help
Its very useful for learning new testers and experience peoples also .
1.create table in db and then
2.Download the MySQL JDBC connector jar file and add it to the build path of project
3.then connect to the database with “jdbc:mysql://ipaddress:portnumber/db_name”
Example:
package sqlConnector;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.Duration;
import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.chrome.ChromeDriver;
public class DBtesting {
public static void main(String[] args) throws ClassNotFoundException, SQLException, InterruptedException {
// Connection URL Syntax: “jdbc:mysql://ipaddress:portnumber/db_name”
String url = “jdbc:mysql://localhost:3306/otp”;
String dbUsername = “****”;
String dbPassword = “*********”;
String query = “select * from otp_login”;
WebDriver d = new ChromeDriver();
d.get(“https://www.amazon.com/”);
d.manage().window().maximize();
d.manage().timeouts().implicitlyWait(Duration.ofSeconds(10));
d.findElement(By.xpath(“//a[@id=’nav-link-accountList’]”)).click();
// Load mysql jdbc driver
Class.forName(“com.mysql.cj.jdbc.Driver”);
// cerate connection to DB
Connection con = DriverManager.getConnection(url, dbUsername, dbPassword);
// Create statement object
Statement smt = con.createStatement();
// Execute statement and store into result set
ResultSet rs = smt.executeQuery(query);
while (rs.next()) {
String OTP = rs.getString(1);
System.out.println(OTP);
d.findElement(By.cssSelector(“input[id=’ap_email’]”)).sendKeys(OTP);
d.findElement(By.cssSelector(“input[id=’continue’]”)).click();
Thread.sleep(2000);
}
con.close();
d.quit();
}
}
Good article… Excellent Coverage
Excellent Article.Can we Expect full fledged DB Testing Tutorials ….
Very helpful Info….
Thanks
Kindly show , how to automate DB queries using Selenium API , not only JUnit.
Thanks!
I gave an interview few days back….the interviewer
asked me ON clicking a submit button assume that 2000 records are inserted in a table….How will you test that all 2000 records are inserted in the table and the data in all the rows and columns are correct ? Please reply
I am working on Oracle DB and testing require sequence of validation in 2 different DB. Data validation include -population of data with specific status, validation on count of lines getting generated in next table, matching value/amount based on some per-defined formulas. Same steps are repeated for different flavors of action. could you please suggest if Selenuim can be used in this use case?if yes, what is the procedure to make a connection with Oracle (Toad) and executing scripts.
@Suresh
Thank you for the kind words..
Whether String getString() this method is used to get only the varchar type data from sql or it will get all type of data in the string format?
It this compatible with PostgreSQL.?
hi in the article you mentioned “To verify that the user’s information is successfully saved into the database as soon as the user registers in the application”. but the example doesn
Thanks for another good article
Thanks, it really very usefull
I got result in first attempt
hi
Hello Sir,
Can you please let me know how to avoid the fetching of data again and again from DB and validate the API response with database.
Suppose I am testing a REST API, is there any means, I can validate the API response with the DATA Base without establishing multiple connection object everytime ?
Dear Admin,
Nice topic. Very useful. But am getting error like “com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table ‘ DB name . Table Name’ doesn’t exist.
Kindly assit me. ASAP
Dear Sathish ,
Did u solve ur issue?
I have the same issue. When I run using git user name I have the following error But if I run with my userid ,password i do not have an issue.
com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name ‘gim_proc.dbo.events.
Kindly assit me. ASAP
Reply
Hi Shruti,
Thanxs for your info,
But i need more help, just extracting data from db is not enuf..
We have to compare this data with some otehr data as well.
like if i have to check the admin name for a webpage
if the out put from db is “abc”;
how can i comapre this value with teh value stored in soem txt file.
Hi dipti/apple,
its very simple to compare values…Just extract the value from the database by firing query using “PREPARED STATEMENT” and store in any variable and compare ith with expected result in by assertequals..
ta!
Excellent work Shruti!!! Really appreciate your efforts for putting the article here.. DB in Selenium..
@Ameet Somayaji : I think you can export data to excel from database and then once you insert data .Execute query using select command in webdriver and then compare those values with excel sheet value where we extracted data.
Error above. The results returned from the “Display userId with where clause” example should be the first user in the table, but the returned example is the second.
how to do this if database can only access through vpn ?
Very nice article.. thanks for sharing the Connectivity code.
very useful.
Any one send the code for “Reading data from excel and inserts into database using selenium WebDriver “
Excellent Article. My question is what if I want to extract that data for example username and password, and then use that data to enter it in a login page. How would I do that?
I learnt lots
Like many comments above said, this article has NOTHING to do with Selenium. Please elaborate more to include its application in Selenium testing.
Thanks for sharing the code. good article.
hi
in the article, you mentioned “To verify that the user’s information is successfully saved into the database as soon as the user registers in the application”.
but the exam you use doesn`t show this piece, so if i have a test senario, i manipulate the screen to input the the userid username, userage, useraddress then click submit button, how do i verify this set of information has already been inserted into the database, would you please give a example against this test scenario, thank you
Thanks Shruti for sharing this article. code is working for me…..:)
Hey Guys,
Webdriver do not have capability to read database. In the sequence of webdriver script execution, we can insert the above code the verify the database since you will get the input from webdriver sequence.
Hope this helps!
Jey
hello,
im trying to get otp from dtabase and put into password field of one of our application but no table to do that please guide me regarding that.
code:
package JDBCnew;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.chrome.ChromeDriver;
public class Canarydemo
{
public static void main(String[] args) throws ClassNotFoundException, SQLException, InterruptedException
{
System.setProperty(“webdriver.chrome.driver”, “C:\\Users\\admin\\Documents\\selenium setup\\chromedriver\\chromedriver.exe”);
WebDriver d= new ChromeDriver();
d.manage().window().maximize();
d.get(“https://app.canarytext.com/login”);
Thread.sleep(2000);
d.findElement(By.xpath(“/html/body/div/app-root/ion-app/ion-router-outlet/app-login/div/div/div/form/ion-input/input”)).sendKeys(“7722033278”);
d.findElement(By.xpath(“/html/body/div/app-root/ion-app/ion-router-outlet/app-login/div/div/div/form/ion-button”)).click();
Thread.sleep(20000);
Class.forName(“com.mysql.jdbc.Driver”);
System.out.println(“Driver loaded”);
Connection con = DriverManager.getConnection(“jdbc:mysql://52.165.37.155:3306/reviews?useSSL=false”,”canarysql@admin”,”Uhg@8d64!”);
System.out.println(“Connected to MySQL DB”);
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(“select * from users WHERE username = 7722033278;”);
while (rs.next())
{
String OTP = rs.getString(4);
System.out.println(” “+OTP);
d.findElement(By.xpath(“/html/body/div/app-root/ion-app/ion-router-outlet/app-login/div/div/div/form/ion-input[2]/input”)).sendKeys(“+OTP”);
}
// closing DB Connection
con.close();
d.findElement(By.xpath(“/html/body/div/app-root/ion-app/ion-router-outlet/app-login/div/div/div/form/ion-button”)).click();
System.out.println(“login successfully”);
}
}
Hi priya,
Try send the OTP instead of .sendKeys(“+OTP”), since its in double quotes it takes as a string.
how to connect if permission is granted from VDI machine
Good job, Appreciate
This is not Selenium Webdriver, sorry guys.
I try to run the code, but it failed. please help!