Database Testing Using Java Selenium and TestNG

Database testing is very important in software testing because every application has a database that stores data. In this article, we will learn about Database testing using Java, Selenium, and TestNG.

Learn more : Automation testing

What is Database Testing?

Database testing is a Type of Software Testing that checks the data integrity, consistency schema, tables, triggers, etc. It involves creating difficult queries to load and stress testing the database and reviewing its responsiveness.

For example, suppose there is data stored in a database and data is entered into an application through the graphical user interface; matching the entered data and stored data is an example of database testing.

Database Testing Using Java Selenium with TestNG

To start database testing, the initial step is to establish a connection with the database. Let's learn how to do that.

Steps to Create Java JDBC Connectivity:

If we want to perform Database testing, then the very first thing we have to do is to Connect to the database. Now let's understand how to Connect to the database. To connect to a database from a Java application, we use JDBC (Java Database Connectivity), a Java API for connecting and executing SQL queries on a database. These are some steps to connect with the database using JDBC.

Step 1: Load the JDBC Driver: Load the JDBC Driver: With the help of the Class.forName() method, we will load the JDBC Driver for our database. This is the very first step.

Class.forName("com.mysql.cj.jdbc.Driver");

Step 2: Establish a Connection: We will use DriverManager.getConnection(databaseURL, user, password) method to establish the connection to the database. Here URL, user, and password are the parameters that are being passed into the given method.

Stringdatabase URLL = "jdbc:mysql://localhost:3306/org"; //Database URL - Contains jdbc , localhost, port & org is name of database

String user = "root"; // username

String password = "root@123"; // password

connection = DriverManager.getConnection(databaseURL, user, password);

Step 3: Execute SQL Queries: Once the connection is set up, we can start running SQL commands. These commands can be things like fetching data (SELECT), adding new data (INSERT), updating existing data (UPDATE), or removing data (DELETE).

Step 4: Process the Results: If our SQL query gives back some data (like with a SELECT query), we can get that data from the result set and work with it however we want.

Step 5: Close the Connection: At last, we will close the database connection using the connection.close().

Now Let's Understand how to perform Database Testing Using Java Selenium and TestNG through an example.

Example of Database Testing Using Selenium TestNG

Step 1: Open the Eclipse IDE.

Step 2: Create a Java Project.

package com.geeksforgeeks.test;

import org.testng.annotations.AfterClass;
import org.testng.annotations.BeforeClass;
import org.testng.annotations.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DatabaseTesting {

    private Connection connection;
    private Statement statement;
    private ResultSet resultSet;

    @BeforeClass
    public void establishDatabaseConnection() throws ClassNotFoundException, SQLException  {
         // Database connection details
        String databaseURL = "jdbc:mysql://localhost:3306/org";
        String user = "root";
        String password = "root@123";
        
     // Load the MySQL JDBC driver and establish connection
         Class.forName("com.mysql.cj.jdbc.Driver");
         System.out.println("Connecting to Database");
         connection = DriverManager.getConnection(databaseURL, user, password);
         
      // Check if the connection is successful
         if (connection == null) {
                System.out.println("Database Connection Failed");
            }else {
                System.out.println("Database Connection Successful");
            }
        }
    

    @Test
    public void retrieveworkerFromDatabase() {
        try {
            
            // SQL query to retrieve worker from the database
            String query = "SELECT * FROM worker";
            statement = connection.createStatement();
            resultSet = statement.executeQuery(query);
            
         // Iterate through the result set and print worker details
            while (resultSet.next()) {
                int empId = resultSet.getInt("WORKER_ID");
                String empFirstName = resultSet.getString("FIRST_NAME");
                String empLastName = resultSet.getString("LAST_NAME");
                int salary = resultSet.getInt("SALARY");
                String department = resultSet.getString("DEPARTMENT");
                
             // Print worker details
                System.out.println("Employee ID: " + empId + ", First Name: " + empFirstName + ", Last Name: " 
+ empLastName + ", Salary: " + salary + ", Department: " + department);

            }
        } catch (Exception error) {
            error.printStackTrace();
        }
    }

    @AfterClass
    public void closeDatabaseConnection() {
        
        // Close the database connection
        if (connection != null) {
            try {
                System.out.println("Closing Database Connection...");
                connection.close();
            } catch (Exception error) {
                error.printStackTrace();
            }
        }
    }
}

Step 3: Run this TestNG test Class Right click on the Database Testing Class, move the cursor down to Run As, and then click on the 1 TestNG Suite.

Step 4: Add TestNG Dependency.

If you're using Maven for your project, open your pom.xml file and add the following TestNG dependency:

<dependency>
    <groupId>org.testng</groupId>
    <artifactId>testng</artifactId>
    <version>7.4.0</version>
    <scope>test</scope>
</dependency>

Note: If you're not using Maven, download the TestNG JAR manually from TestNG's official site and add it to your project libraries.

Step 5: Add MySQL JDBC Driver.

Download the MySQL JDBC Driver (MySQL Connector/J) from here.

Add it to your project:

• Right-click on your project > Build Path > Configure Build Path.
• Under the Libraries tab, click Add External JARs and select the downloaded JDBC driver JAR file.

Step 6: Verify Database Connection Details.

Check your database connection details to make sure they match your MySQL setup:

• Database URL: "jdbc:mysql://localhost:3306/org"
• Username: "root"
• Password: "root@123" Ensure that the org database exists in your MySQL instance, and update the credentials as per your setup.

Step 7: Create a Worker Table in MySQL.

• Open MySQL Workbench or another MySQL client.
• Run the following SQL query to create a worker table:

CREATE TABLE worker (
    WORKER_ID INT AUTO_INCREMENT PRIMARY KEY,
    FIRST_NAME VARCHAR(50),
    LAST_NAME VARCHAR(50),
    SALARY INT,
    DEPARTMENT VARCHAR(50)
);

• Insert test data into the worker table to use in your tests:

INSERT INTO worker (FIRST_NAME, LAST_NAME, SALARY, DEPARTMENT)
VALUES ('John', 'Doe', 50000, 'HR'),
       ('Jane', 'Smith', 60000, 'Finance');

*Change Values as per your requirements*

Step 8: Ensure TestNG Configuration.

• Right-click on your project > New > Other... > TestNG > TestNG XML File.
• Create a testng.xml file if it doesn't exist. Your testng.xml should look like this:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE suite SYSTEM "http://testng.org/testng-1.0.dtd">
<suite name="Database Testing Suite">
  <test name="Database Testing">
    <classes>
      <class name="com.geeksforgeeks.test.DatabaseTesting"/>
    </classes>
  </test>
</suite>

Step 9: Run the Test.

• Right-click on your DatabaseTesting class file.
• Select Run As > TestNG Test to run the test suite.
• If everything is configured correctly, the test will execute, and the output will show in the Console window.

Step 10: Check Test Results.

After the test runs, check the Console for results:

• If the database connection is successful, you'll see output similar to:

If there are any issues, error messages will help you troubleshoot (e.g., incorrect database credentials, missing JDBC driver).

Step 11: Debugging (If Needed).

If the test fails check these cases:

• Make sure the MySQL service is running.
• Verify that the MySQL JDBC driver is properly added to your project libraries.
• Double-check that your database URL, username, and password match the MySQL configuration.

Explanation

This Java class DatabaseTesting is a TestNG test class that shows how to connect to a MySQL database and retrieve data using JDBC. Let's explain this code.

• Imports: Import necessary classes from TestNG and JDBC packages.
• Class Declaration: The class DatabaseTesting is declared.
• Instance Variables: We declare three things as private instance variables: connection, statement, and resultSet. These are used for connecting to the database, running SQL queries, and handling the results of those queries.

1. @BeforeClass Method - establishDatabaseConnection():

This function is marked with @BeforeClass, meaning it runs before any test methods in the class. It sets up a database connection by loading the MySQL JDBC driver (Class.forName()) and gets a connection using DriverManager.getConnection(). It then checks if the connection is successful and prints relevant messages.

2. @Test Method - retrieveworkerFromDatabase():

This function is marked with @Test, showing it's a test method. It gets information from the worker table in the database by running a SQL query (SELECT * FROM worker). Then, it goes through each row of the result and prints details about each worker, like their employee ID, first name, last name, salary, and department.

3. @AfterClass Method - closeDatabaseConnection():

This function is marked with @AfterClass, meaning it runs after all the test methods in the class are done. It closes the connection to the database (connection.close()) to free up resources.

4. Exception Handling

Exceptions such as ClassNotFoundException, SQLException, and Exception are caught and their stack traces are printed for debugging purposes.

Learn more: TestNG Annotations

Conclusion

Database testing with Java, Selenium, and TestNG helps make sure that data moves correctly and is accessible in the new system. First, connect to the database using JDBC and run SQL queries to get or update data. With TestNG, automate the tests using @BeforeClass, @Test, and @AfterClass to set up, test, and clean up.

The tests check if the data is correct, consistent, and if the system works well. If there are issues, check the connection settings, JDBC driver, and login details. This method verify the smooth data migration and that the system works properly.

cyrust8t7y

Follow

Improve

Article Tags :

• Software Testing
• Automation Testing
• Selenium