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.

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

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

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 the ‘createStatement’ method of the 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.

Conclusion

In conclusion, database testing using Selenium, enhanced with JDBC, enables testers to validate data integrity directly alongside UI testing. This combined approach ensures the front-end interactions reflect accurately in the backend, making it crucial for applications that rely on precise data consistency.

Enrolling in a Selenium automation testing course can help you master these skills, equipping you with the knowledge to perform comprehensive database and UI testing, essential for delivering high-quality, reliable applications.

Call to Action

Ready to enhance your testing skills with database integration in Selenium? Join H2K Infosys Selenium automation testing course and master the essential techniques for combining UI and database testing using JDBC. Gain hands-on experience with industry experts and take your testing expertise to the next level. Enroll today to ensure your applications meet the highest standards of quality and data integrity!

Join H2K Infosys today and gain practical experience with expert-led guidance and real-world projects. Whether new to Selenium or looking to expand your testing abilities, our course provides everything you need to excel in database testing, from foundational knowledge to advanced techniques. Don’t miss out on the opportunity to elevate your testing career enroll now!