How to do Database testing using Selenium Webdriver Framework Example

In previous tutorial, we have discussed about database testing and JDBC Java API which is primarily used to connect and interact with Database. Check out the simple example of Database testing with selenium.

In this tutorial, we will discuss how to perform database testing in different environments and pass the parameters to different methods in a simple way.

To do this, first we need to create a class to establish Database connection and take care of executing the database query in environment which we specify in Test. When ever we are expecting a list of values, we should make sure to save the ResultSet in a List. And when we are sure that ResultSet returns only a single value, we should save that in String.

In the below example we have given both the methods 'executeSQLQuery_List(String testEnv, String sqlQuery)' which returns list of values based on the query and 'executeSQLQuery(String testEnv, String sqlQuery)' returns only a string value.

Now let us first create a class and call it as 'DataBaseConnector.class'.
 

/**
 * Created by VISISHTA on 12/19/2015.
 */
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;


public class DataBaseConnector {

    private static String dbusername;
    private static String dbpassword;

    //Should be defined as jdbc:mysql://host:port/database name
    private static String databaseURLQA= "jdbc:mysql://qahost:22020/easy";
    private static String databaseURLSTAGE= "jdbc:mysql://stagehost:2020/easyDB";
    private static String databaseURLPRODUCTION= "jdbc:mysql://prodhost:2020/easyDB";


    public static String executeSQLQuery(String testEnv, String sqlQuery) {
        String connectionUrl="";
        Connection connection;
        String resultValue = "";
        ResultSet rs;

        //To connect with QA Database
        if(testEnv.equalsIgnoreCase("QA")){
            connectionUrl = databaseURLQA;
            dbusername = "root";
            dbpassword = "root";
        }
        //To connect with Stage Database
        else if(testEnv.equalsIgnoreCase("STAGE")) {
            connectionUrl = databaseURLSTAGE;
            dbusername = "root";
            dbpassword = "stagepassword";
        }

        //To connect with Production Database
        else if(testEnv.equalsIgnoreCase("PRODUCTION")) {
            connectionUrl = databaseURLPRODUCTION;
            dbusername = "root";
            dbpassword = "prodpassword";
        }
        try {
            Class.forName("com.mysql.jdbc.Driver");
        }catch(ClassNotFoundException e) {
            e.printStackTrace();
        }

        try {
            connection = DriverManager.getConnection(connectionUrl,dbusername,dbpassword);
            if(connection!=null) {
                System.out.println("Connected to the database...");
            }else {
                System.out.println("Database connection failed to "+testEnv+" Environment");
            }
            Statement stmt = connection.createStatement();
            rs=stmt.executeQuery(sqlQuery);

            try {
                while(rs.next()){
                    resultValue = rs.getString(1).toString();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            catch (NullPointerException err) {
                System.out.println("No Records obtained for this specific query");
                err.printStackTrace();
            }
            connection.close();

        }catch(SQLException sqlEx) {
            System.out.println( "SQL Exception:" +sqlEx.getStackTrace());
        }
        return resultValue;
    }


    public static ArrayList<String> executeSQLQuery_List(String testEnv, String sqlQuery) {
        String connectionUrl="";
        Connection connection;
        ArrayList<String> resultValue = new ArrayList<String>();
        ResultSet resultSet;

        //To connect with QA Database
        if(testEnv.equalsIgnoreCase("QA")){
            connectionUrl = databaseURLQA;
            dbusername = "root";
            dbpassword = "root";
        }

        //To connect with Stage Database
        else if(testEnv.equalsIgnoreCase("STAGE")) {
            connectionUrl = databaseURLSTAGE;
            dbusername = "root";
            dbpassword = "stagepassword";
        }

        //To connect with Production Database
        else if(testEnv.equalsIgnoreCase("PRODUCTION")) {
            connectionUrl = databaseURLPRODUCTION;
            dbusername = "root";
            dbpassword = "prodpassword";
        }

        try {
            Class.forName("com.mysql.jdbc.Driver");
        }catch(ClassNotFoundException e) {
            e.printStackTrace();
        }

        try {
            connection = DriverManager.getConnection(connectionUrl,dbusername,dbpassword);
            if(connection!=null) {
                System.out.println("Connected to the database");
            }else {
                System.out.println("Failed to connect to "+testEnv+" database");
            }
            Statement statement = connection.createStatement();
            resultSet=statement.executeQuery(sqlQuery);

            try {
                while(resultSet.next()){
                    int columnCount = resultSet.getMetaData().getColumnCount();
                    StringBuilder stringBuilder = new StringBuilder();
                    for(int iCounter=1;iCounter<=columnCount; iCounter++){
                        stringBuilder.append(resultSet.getString(iCounter).trim()+" ");
                    }
                    String reqValue = stringBuilder.substring(0, stringBuilder.length()-1);
                    resultValue.add(reqValue);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            catch (NullPointerException ex) {
                System.out.println("No Records found for this specific query" +ex.getStackTrace());
            }
            finally {
                if (connection != null) {
                    try {
                        connection.close();
                    } catch (SQLException ex) {
                        System.out.println( "SQL Exception:" +ex.getStackTrace());
                    }
                }
            }

        }catch(SQLException sqlEx) {
            System.out.println( "SQL Exception:" +sqlEx.getStackTrace());
        }
        return resultValue;
    }
}

In the above example, we have hard coded values of database URL and credentials, But you can store them in a properties file and get these values. You can check here for more details on 'Read data from Properties file using Java Selenium'

Below is the database which is used for this example :
database testing with selenium

Now we will create 'SeleniumDataBaseTestingExample.class' which will have two tests to make use of both the methods defined in 'DataBaseConnector.class'
/**
 * Created by VISISHTA on 12/17/2015.
 */

import org.testng.Assert;
import org.testng.annotations.Test;

import java.util.ArrayList;
import java.util.List;

public class SeleniumDataBaseTestingExample {

    //Test to verify Employee ID '1' has employee name 'Jack'
    @Test(priority = 1)
    public void testVerifySpecificRecord() {
        String sqlQuery = "select EmpName from employee WHERE EmpId="1"";
        String expectedEmpName = "Jack";
        //Getting employee name by Id
        String actualEmpNameById = DataBaseConnector.executeSQLQuery("QA", sqlQuery);
        System.out.println("Employee name retrieved from database :" + actualEmpNameById);
        Assert.assertEquals(expectedEmpName, actualEmpNameById);
    }

    //Test to verify Employee table has a record with employee name 'Jack'
    @Test(priority = 2)
    public void tesVerifyListOfRecords() {
        boolean flag = false;
        List<String> listOfDBValues = new ArrayList<String>();
        String expEmployeeName = "Jack";
        String sqlQuery = "select EmpName from employee";
        //Getting list of employee names from employee table
        listOfDBValues = DataBaseConnector.executeSQLQuery_List("QA", sqlQuery);
        for (String strName : listOfDBValues) {
            if (strName.equalsIgnoreCase(expEmployeeName)) {
                flag = true;
                break;
            }
        }
        Assert.assertTrue(flag, "Retrieved values are not matching with Expected values");
    }

}

In the above example, 'testVerifySpecificRecord' test is calling 'executeSQLQuery' method and expecting a String to validate. And second test 'tesVerifyListOfRecords' is expecting list of employee names and then compare with the expected employee name in the list.

Hope this article helps you. Please let me know your feedback and issues if you face.

Thank You.

Selenium Tutorials: 

Comments

Thanks!

Sir i want to know that Can i check that data is entering into database bt i don't have database access and also it wont pop up message after entering data into field.
So is there any way to get know??

Very well explained.

Keep up the good work.

Above DB sample was very clear and detail.
Would love to see similar example consuming SOAP and/or REST calls.
This would be really a great help and sharing.
Thanking in advance!
Best Wishes!

Please share SOAP /REST example by using db testing

Hi
Thanks For This Tutorial
I have a problem i use java -jar selenium-server-standalone-3.9.1.jar and install here some program and print result on java server i want access this result on my wamp server page how to i do it help me .
Regarding
Pankaj kumar

Very well explained. Thank you very much!

Add new comment

CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.