How to fetch Key and value from My SQL…

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.

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:

package com.test.traveltest;

import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.chrome.ChromeDriver;
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://127.0.0.1:3306/hrmdb";
//Database Username
public static String DB_USER = "root";
// Database Password
public static String DB_PASSWORD = "rahul";

public static WebDriver driver;
public static String uname;
public static String uval;
public static String pwd;
public static String pval;
public static String loginbtn;

@BeforeTest
public void setUp() throws Exception {
	
	System.setProperty("webdriver.chrome.driver",System.getProperty("user.dir")+"\\src\\main\\java\\drivers\\chromedriver1.exe");
  	driver = new ChromeDriver();
	driver.manage().window().maximize();
	driver.get("https://opensource-demo.orangehrmlive.com/web/index.php/auth/login");
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 (priority = 0)
public void testuname() {
try{
String query = "select * from login where id = '" +1+"'";
// 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();
uname = res.getString(2);
uval = res.getString(3);
}
}
catch(Exception e)
{
e.printStackTrace();
}

driver.findElement(By.name(uname)).sendKeys(uval);

}

@Test (priority = 1)
public void testpwd() {
try{
String query = "select * from login where id = '" +2+"'";
// 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();
pwd = res.getString(2);
pval = res.getString(3);
}
}
catch(Exception e)
{
e.printStackTrace();
}

driver.findElement(By.name(pwd)).sendKeys(pval);

}

@Test (priority = 2)
public void testlogin() {
try{
String query = "select * from login where id = '" +3+"'";
// 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();
loginbtn = res.getString(2);

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

driver.findElement(By.xpath(loginbtn)).click();

}

@AfterTest
public void tearDown() throws Exception {
// Close DB connection
if (con != null) {
con.close();
}
}
}