How to use TestNG BeforeSuite and AfterSuite Hooks for connecting and closing ms sql database in Selenium with Cucumber

In this post, we will see, how to create a database connection only once and how to close the connection once using TestNG BeforeSuite and AfterSuite annotations. 

@BeforeSuite

• The method that is annotated with BeforeSuite will run after all scenarios or tests. 
• That is BeforeSuite will be executed only once before executing all scenarios or tests.

@AfterSuite

• The method that is annotated with AfterSuite will run after all scenarios or tests. 
• That is AfterSuite will be executed only once after executing all scenarios or tests. 

Example: 

Connect to MS-SQL server in Selenium with Cucumber and TestNG project only once to perform database operations. 

DatabaseHooks.java

@BeforeHook

Create the connection before executing all the cucumber scenarios. 
In the code below, ff the connection is null then establish the connection and if 
the connection is not null then print a message saying that the connection is already created. 
@AfterHook
Close the connection after executing all the cucumber scenarios. 

package com.sadakar.cucumber.common;

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

import org.testng.annotations.AfterSuite;
import org.testng.annotations.BeforeSuite;

public class DatabaseHooks {

	// Connection object
	private static Connection dbConn = null;
	// Statement object
	@SuppressWarnings("unused")
	private static Statement stmt;
	// Constant for Database URL
	public static String DB_URL = "jdbc:sqlserver://14.456.9.20:1433;user=TestUser;password=TestPassWord;database=TestDB";

	@BeforeSuite //TestNG annotation
	public static Connection getDBConnection() {

		try {
			
			if (dbConn == null) {
				Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
				dbConn = DriverManager.getConnection(DB_URL);
				
				System.out.println("Connected to Database - BeforeSuite TestNG");
			}
			else {
				System.out.println("Already Connected to Database - BeforeSuite TestNG");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} 
		return dbConn;
	}
	
	
	@AfterSuite //TestNG
	public static void closeConnection() {
		if (dbConn != null)
			try {
				dbConn.close();
				System.out.println("Database connection closed - AfterSuite TestNG");
			} catch (SQLException e) {
				e.printStackTrace();
			}
	}
}

CreateUser.java

Create a Connection Object in the CreateUser class. 
Create Statement(s) and execute and store the result in res1 variable of ResultSet
Connection for the database is created once in the above Hook and we are using it in 
CreateUser class and similarly, we can make use of the same connection in other classes. 

package com.sadakar.cucumber.stepdefinitions;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.Duration;
import java.util.List;
import org.openqa.selenium.By;
import org.testng.Assert;
import com.sadakar.cucumber.common.DatabaseHooks;
import com.sadakar.resource.utilities.Log;
import com.sadakar.webui.common.BasePage;
import io.cucumber.java.en.Then;

public class CreateUser extends BasePage {

Connection con = DatabaseHooks.getDBConnection();

@Then("Fills the user on the User form")
	public void fillTheUserFrom(io.cucumber.datatable.DataTable dataTableAddUser)
			throws InterruptedException, SQLException {

		if (dataTableAddUser == null)
			throw new NullPointerException("cells can not be null");
		List<List<String>> cells = dataTableAddUser.cells();

		Statement sta = con.createStatement();

		String AutomationUser1 = "select * from Users where username='AutomationUser1'";

		ResultSet res1 = sta.executeQuery(AutomationUser1);

		if (res1.next()) {

			System.out.println("ResultSet is not empty, 1 row returned");
			System.out.println("AutomationUser1 test user already exists  so deleting the existing and creating it again");
			
			String deleteUser = "delete from Users where UserName='AutomationUser1'";
			String deleteUserEmail = "delete from UserAdditionalInfo where Email='AutomationUser1@email.com'";

			sta.executeUpdate(deleteUser);
			sta.executeUpdate(deleteUserEmail);

			driver.findElement(By.xpath("//input[@id='UserName']")).clear();
			driver.findElement(By.xpath("//input[@id='UserName']")).sendKeys(cells.get(1).get(1));
			driver.findElement(By.xpath("//input[@id='UserEmail']")).sendKeys(cells.get(2).get(1));

		} else if (!res1.next()) {
			System.out.println("ResultSet is empty, no rows returned");
			System.out.println("Test user does not exist  so creating test user : AutomationUser1");

			driver.findElement(By.xpath("//input[@id='User']")).clear();
			driver.findElement(By.xpath("//input[@id='User']")).sendKeys(cells.get(1).get(1));
			driver.findElement(By.xpath("//input[@id='UserEmail']")).sendKeys(cells.get(2).get(1));
		} else {
			Assert.assertTrue(false);
		}

	}
}