Database Testing Using Selenium WebDriver – MSSQL Server

Database Testing Using Selenium WebDriver

In this post, let’s see how to do Database Testing using Selenium WebDriver. As we all know Selenium WebDriver is a tool to automate User Interface. We could only interact with Browser using Selenium WebDriver.

Sometimes, we may face a situation to get the data from the Database or to modify (update/delete) the data from the Database.  If we plan to automate anything outside the vicinity of a browser, then we need to use other tools to achieve our task. To achieve the Database connection and work on it, we need to use JDBC API Driver.

Must Read: What is Gecko Driver and how to use it in Selenium WebDriver. 

The Java Database Connectivity (JDBC) API provides universal data access from the Java programming language. Using the JDBC API, you can access virtually any data source, from relational databases to spreadsheets and flat files. It lets the user connect and interact with the Database and fetch the data based on the queries we use in the automation script. JDBC is a SQL level API that allows us to execute SQL statements. It creates a connectivity between Java Programming Language and the database.

Using JDBC Driver we could do the following

1. Establish a Database connection
2. Send SQL Queries to the Database
3. Process the results

Loading the required JDBC (Java DataBase Connectivity) Driver class:

Class.forName("net.sourceforge.jtds.jdbc.Driver");

Note: The parameter varies between Data bases such as MySQL, DB2 etc.,

Data base Testing with MySQL

Data base Testing with DB2

Establishing a connection to the DataBase (MS SQL Server):

Connection con = DriverManager.getConnection("DataBaseURL", "userName", "password");

Connection URL Syntax (MS SQL Server):

jdbc:sqlserver://ipAddress:portNumber/dbName

Executing SQL Queries:

Statement sqlStatement = con.createStatement();
String sqlQuery = "SELECT * FROM table_name WHERE condition";
ResultSet resSet = sqlStatement.executeQuery(sqlQuery);

Results from the executed query are stored in the ResultSet Object.

Fetching data from result set:

while (resSet.next()) {
    System.out.println(resSet.getString(required_column_name));
}

Disconnecting the Database connection:

con.close();

List of Methods to process the results:

Note: Assuming that you have already Installed Database.

Steps to follow – Database Testing using Selenium WebDriver:

1. Click here to download the jar files.
2. Add the downloaded jar file to your project

Right click on your Java File – Click on Build Path – Configure build path – Select the libraries – Click on add external JARs – Select MSSQL Server JDBC jar – Click on open to add it to the project

3. Copy the below mentioned script and modify as per your requirement and execute it

Note: You could find the detailed explanation of the script in the code snippet below

Script to get the data from the Database – Database Testing:

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

public class DBTesting {
     public static void selectQuery() throws SQLException, ClassNotFoundException {	
	String dbURL = "jdbc:sqlserver://ipAddress:portNumber/dbName";
	String username = myUserName;
        String password = myPassword;
        //Load MS SQL JDBC Driver
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        //Creating connection to the database
        Connection con = DriverManager.getConnection(dbURL,username,password);
        //Creating statement object
    	Statement st = con.createStatement();
    	String selectquery = "SELECT * FROM <tablename> WHERE <condition>";
        //Executing the SQL Query and store the results in ResultSet
    	ResultSet rs = st.executeQuery(selectquery);
    	//While loop to iterate through all data and print results
    	while (rs.next()) {
    		System.out.println(rs.getString("transaction_datetime"));
    	}
        //Closing DB Connection
    	con.close();
	}
}

Script to update the data in the Database – Database Testing:

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

public class DBTesting {
     public static void updateQuery() throws ClassNotFoundException, SQLException{		
	String dbURL = "jdbc:sqlserver://ipAddress:portNumber/dbName";
	String username = myUserName;
        String password = myPassword;
        String updatequery = "UPDATE <table_name> SET <column_name> = <some_value>";
        
	Class.forName("net.sourceforge.jtds.jdbc.Driver");
   	Connection con = DriverManager.getConnection(dbURL,username,password);
    	Statement stmt = con.createStatement();
    	stmt.executeUpdate(updatequery);  
    	con.close();
     }
}

Script to delete the data in the Database – Database Testing:

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

public class DBTesting {
     public static void deleteQuery() throws SQLException, ClassNotFoundException {	
	String dbURL = "jdbc:sqlserver://ipAddress:portNumber/dbName";
	String username = myUserName;
        String password = myPassword;
        String query = "DELETE FROM <table_name>";		
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
   	Connection con = DriverManager.getConnection(dbURL,username,password);
    	Statement stmt = con.createStatement();			
    	stmt.executeUpdate(query);
    	//Closing DB Connection
    	con.close();
     }
}

I would like to conclude this post “MS SQL Database testing using Selenium WebDriver” here. If you find anything difficult, please comment below. Also, I have hand-picked few posts which will help you to learn more interview related stuff:

• Selenium WebDriver Tutorial
• Selenium Interview Questions
• Explain Test Automation Framework
• Test Automation Framework Interview Questions
• TestNG Interview Questions
• SQL Interview Questions
• Manual Testing Interview Questions
• Agile Interview Questions
• Why You Choose Software Testing As A Career
• General Interview Questions

If you have any more question, feel free to ask via comments. If you find this post useful, do share it with your friends on Social Networking.