Database Testing using Selenium

Testing your web applications using Browser is limited in Selenium Webdriver. For Database Verification, you need to use JDBC (Java Database Connectivity) in Selenium Webdriver.

JDBC is a Java Level API that manages joining to a database and allows us to execute SQL statements. It provides a connection between the Java Programming language and a wide range of databases. The JDBC API will provide the below following interfaces and classes.

• Driver Manager
• Driver
• Connection
• Statement
• ResultSet
• SQLException

You need to observe the following 3 steps while your Database testing using Selenium.

1: Making a connection to the Database

The following is the syntax to make a connection to the database

DriverManager.getConnection(URL, “userid”, “password”)

Here,

• Userid is the username which is configured in the database
• The Password of the configured user
• URL is in the format jdbc:<dbtype>://ipaddress:portnumber/db_name”
• <dbtype>- The driver to the database which you are trying to connect. To connect to the SQL database this value will be “SQL”

To connect for the database with name “employee” in MYSQL the URL will be

bejdbc:mysql://localhost:3036/employee

And to create a connection the code looks like

Using the code you need to load the JDBC Driver

2: Sending Queries to the Database

You need to execute the queries once the connection is made. We use the Statement Object to send queries.

To execute the SQL queries we use the executeQuery method once the statement object is created

3: Processing the results

Results got from the executed query are stored in the ResultSet object.

Java provides advanced methods to process the results. Below are the few methods that are listed.

Database Testing Example Using Selenium WebDriver and JDBC

Step 1: Install MYSQL Server and MYSQL Workbench from below links

MYSQL Server: https://dev.mysql.com/downloads/mysql/

MYSQL Workbench: https://dev.mysql.com/downloads/workbench/

JDBC Connection, Eclipse Setup, and Selenium WebDriver:

To create a JDBC connection, we need a driver class. The SQL connector driver class can be downloaded from the link

https://dev.mysql.com/downloads/connector/j/

Once you download the zip file ‘mysql-connector-java-5.1.49.zip’, unzip the file to see the jar inside which is the actual driver file.

Follow below steps to setup eclipse for JDBC Connection:

Step 1: Right-click on the selenium project and navigate as ‘Build Path’ > ‘Configure Build Path’

Step 2: Click on the ‘Libraries’ tab and click on the ‘Add External JARs…’ button and select the path ‘mysql-connector-java-5.1.39.jar’ file as shown below and click on the Open button.

Step 3: Click on the Apply and Close button to finish the build path set up as shown below.

Step 4: Execute the test script as shown below once the driver class is set up.

i): Create a Package (seleniumpackage)

ii): Create a Class (SeleniumJDBCConnection)

package seleniumpackage;

import java.sql.Connection;
import java.sql.Statement;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.firefox.FirefoxDriver;
import java.sql.ResultSet;
import java.sql.DriverManager;
import java.sql.SQLException;

public class SeleniumJDBCConnection {
public static final String QUERY = "select * from TESTING;";
public static void main(String[] args) throws ClassNotFoundException, SQLException {
String baseWebUrl = "";
String expectedWebsiteTitle = "";
Class.forName("com.mysql.jdbc.Driver");//Load MySQL JDBC driver
//Create Connection to DB
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/webtesting", "MySQLDatabase","softwaretestingclass");
Statement stmt = con.createStatement(); //Create Statement Object.
ResultSet rs= stmt.executeQuery(QUERY); // Execute the SQL Query. Store results in ResultSet.
while (rs.next()){ // While Loop iterates through all data present in a table.
baseWebUrl = rs.getString(1);
expectedWebsiteTitle = rs.getString(2);
}
con.close();
System.out.println("baseWebUrl: "+baseWebUrl);
System.out.println("expectedWebsiteTitle: "+expectedWebsiteTitle);
System.setProperty("webdriver.gecko.driver","F:\\drivers\\geckodriver.exe");  
WebDriver driver = new FirefoxDriver();

String actualWebsiteTitle = "";
driver.get(baseWebUrl);
actualWebsiteTitle = driver.getTitle();
if (actualWebsiteTitle.contentEquals(expectedWebsiteTitle)){
System.out.println("Test has Passed!");
} else {
System.out.println("Test has Failed!");
}
driver.close();
}
}

Make a Database connection

After loading the JDBC driver and with the help of ConnectionManager we are making the MySQL DB connection. This class requires a URL, username, and password as shown in the above program.

Execute SQL queries in Database via JDBC connection

We have created a SQL query to select a record (here URL and title) from the database testing. We are getting this SQL in the pre-compiled state with the help of the Statement interface and ‘createStatement’ method of Connection interface.

Process the return result set from the database

Here we execute the pre-compiled SQL with the help of ‘executeQuery’ method which returns a result set that contains URL and title.

WebDriver Web Testing

By using the URL fetched from the database to load the website using Firefox WebDriver. The driver is fetching the title of the page that URL loads. This title is compared with the title retrieved from the database.

Output

Since the title retrieved from the database matches with the title fetched from the website test script will print the test has passed.