Home Guide Database testing using Selenium

Database testing using Selenium

By Neha Vaidya, Community Contributor -

A database is a software subsystem that offers an efficient way to store data and request said data via a structured query language known as SQL. Databases store critical business information, thus functioning as the backbone of an entire application.

Naturally, database testing is essential to ensure the system is working correctly. As automation testing with Selenium is most frequently used by QAs, this article will detail how to perform database testing using Selenium.

This article will cover:

  1. Java database connectivity
  2. Selenium database connection
  3. Database testing using Selenium
  4. Database testing using Browserstack Automate

Note: Before proceeding, note that Selenium can perform only UI validations. Database validations can be performed using language APIs such as JDBC in Java.

1. Java Database Connectivity

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

Steps to create a JDBC Application

To create a JDBC application, follow the steps below:

  1. Import the packages: Include the packages that contain the JDBC classes required for database programming.
  2. Register the JDBC driver: Initialize a driver to open a communication channel with the database. Register to the database with the command:
    Class.forName(“com.mysql.jdbc.Driver”); // class.forName load the Driver class
  3. Open a connection: After the driver registration, use the getConnection() method to create a Connection object, representing a physical connection with the database.
  4. Execute a query: Use an object of type ‘Statement’ to build and submit a SQL statement to the database.
  5. Extract data from the result set: Use the appropriate getXXX() method for this purpose.
  6. Clean up the environment: Explicitly close all database resources that rely on JVM garbage collection.

2. Selenium Database Connection

Selenium is one of the prominent automation testing tools. As mentioned before, Selenium performs only UI validations. Thus, this article will depict the use of a JDBC connection as Selenium doesn’t support database testing directly, but it can be done with connectors like JDBC and ODBC. In this article, JDBC is used to connect to a database, and test cases are verified using TestNG framework.


Read More: Selenium with Java: Getting Started to Run Automated Tests


3. Database testing using Selenium

Step 1: Create a database in command prompt and insert the tables into it.
Step 2: Establish a connection to the database using JDBC.
Step 3: Execute the MySQL queries and process records present in the database.
Step 4: Integrate TestNG with JDBC to perform Database Testing.

Have a look at the script below:

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 SeleniumDatabaseTesting {
// Connection object
static Connection con = null;
// Statement object
private static Statement stmt;
// Constant for Database URL
public static String DB_URL = "jdbc:mysql://localhost/Testdata";
//Database Username
public static String DB_USER = "your_user";
// Database Password
public static String DB_PASSWORD = "your_password";

@BeforeTest
public void setUp() throws Exception {
try{
// 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 testingdata";
// 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();
}
}
}

Run Selenium Test on Real Devices for Free

In this example, the program has specified the database URL, database username, and password to establish a connection to the database. Once the database connection is complete, execute the queries, and process the results. On executing the above program using TestNG, the output appears as below:

[RemoteTestNG] detected TestNG version 7.2.0
Browserstack
Selenium
Automation testing
Cross-Browser testing
PASSED: test
===============================================
Default test
Tests run: 1, Failures: 0, Skips: 0
===============================================
===============================================
Default suite
Total tests run: 1, Failures: 0, Skips: 0
===============================================

Note: Since Selenium cannot be used to test databases directly, database features can be validated with TestNG test cases.

Database testing using Browserstack Automate

Now, let’s explore how to perform database testing with the help of the Browserstack Automate platform.

The code pattern remains almost the same, except that a couple of desired capabilities and the hub URL needs to be added as shown below:

package testngtest;
import org.openqa.selenium.By;
import org.openqa.selenium.Platform;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.remote.DesiredCapabilities;
import org.openqa.selenium.remote.RemoteWebDriver;
import java.net.URL;
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 SeleniumDatabaseTesting {
public static final String USERNAME = "nehapramodvaidya";
public static final String AUTOMATE_KEY = Your_key";
public static final String URL = "https://" + USERNAME + ":" + AUTOMATE_KEY + "@hub-cloud.browserstack.com/wd/hub";
public static void main(String[] args) throws Exception {
DesiredCapabilities caps = new DesiredCapabilities();

caps.setCapability("os", "Windows");
caps.setCapability("os_version", "10");
caps.setCapability("browser", "Chrome");
caps.setCapability("browser_version", "80");

caps.setCapability("name", "nehapramodvaidya's First Test");}
// Connection object
static Connection con = null;
// Statement object
private static Statement stmt;
// Constant for Database URL
public static String DB_URL = "jdbc:mysql://localhost/onlinebanking";
//Database Username
public static String DB_USER = "Your_Database_Username";
// Database Password
public static String DB_PASSWORD = "Your_Database_Password";

@BeforeTest
public void setUp() throws Exception {
try{
// 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 user";
// 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();
}
}
}

Execute the code above, and validate the directly on the Automate dashboard. As in all cases, database testing must be conducted on a real device cloud. BrowserStack’s cloud Selenium grid provides 2000+ real browsers and devices for automated testing, including the ability to run parallel testing in real user conditions to speed up results.

Try Selenium Testing for Free

BrowserStack Logo Run Selenium Tests on 2000+ Browsers & Devices Get Started Free