Database Testing using Selenium and TestNG

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. Database testing is a vital part of software testing that involves verifying the accuracy and completeness of data stored in a database.

Automated database testing can cover a wide range of tests, such as data integrity, performance, security, and integration. It can also detect defects or issues in the database, such as data corruption, missing data, incorrect data, and duplicate data.

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

1. Java Database Connectivity

JDBC is the standard Java API required for database-independent connectivity between the Java programming language and many 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.
6. Clean up the environment: 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 a JDBC connection as Selenium doesn’t support database testing directly, but it can be done with connectors like JDBC and ODBC. This article uses JDBC to connect to a database, and test cases are verified using TestNG framework.

3. Database testing using Selenium

Step 1: Create a database in command prompt and insert the tables.
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

In this example, the program has specified the database URL, 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
===============================================

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 it 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 3000+ 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

Frequently Asked Questions

1. Can we automate database testing?

Yes, automating database testing can improve software quality and help achieve faster delivery times while reducing costs. Executing repetitive test cases quickly and accurately can save time and effort. However, the tests can be manual, automated, or both based on your project needs.

2. Which tools are preferred for database testing?

Selenium WebDriver, TestNG, JUnit, and Robot Framework have been preferred for automating database testing. These tools and frameworks allow you to write and execute test scripts automatically, reducing the chances of human errors and increasing the testing speed.

3. Which types of tests do automated database testing cover?

Automated database testing can cover a wide range of tests, such as data integrity tests, performance tests, security tests, and integration tests.