Database Testing Using Selenium
This article will present you with a complete idea about how to perform DataBase Testing using selenium webdriver
Basically Selenium WebDriver is a tool to automate UI applications .Some time we may have to validate data base in parallel with UI testing such as data extraction , update some data in data base etc.
We need to use JDBC API Driver as selenium require third party tool/library to automate such scenarios where data base testing is involved.
So JAVA Database Connectivity (JDBC) is a Java API which is used to connect and interact with Database.
JDBC API provides set of interfaces which are written and developed in Java programming language , although ODBC is written in C language and provided by Microsoft.
JDBC provides following interfaces and classes −
- DriverManager: It is used to manage a list of database drivers. This driver recognizes a certain subprotocol under JDBC in order to establish a database Connection.
- Driver: It is an interface that handles the communications with the database server.
- Connection: It is an interface that consists of all the methods required to connect to a database. The connection object represents communication context wherein the entire communication with the database is through connection object only.
How to Connect to database using JDBC?
Below are few steps required for connecting to database and doing some operation on it.
- Import the packages
- Load and Registering the Driver
- Establishing Connection.
- Creating Statement Object
- Execute the Statement
- Process the Result
- Closing the connection.
Import the packages
Include the packages that contain the JDBC classes required for database programming.
Load and Registering the Driver
For loading and registering the Driver we Load the Driver class using forName() method.
Class.forName("com.mysql.jdbc.Driver");Parameter inside this method can vary depending upon type of database.
Internally this Driver class will register the driver by using static method called registerDriver().
Establishing Connection.
For establishing connection with database we have to call static method getConnection(…) present in DriverManager Class.
This method contains three arguments of string type. i.e., url, username and password
DriverManager.getConnection("jdbc:mysql://localhost:3306/Employee","root","root"); The return type of this method is Connection Object so complete statement is
Connection con =DriverManager.getConnection("jdbc:mysql://localhost:3306/Employee","root","root"); Creating Statement Object
For creating statement object we need to call a method called createStatement()present in Connection Interface.
Statement st= con.createStatement();
Execute the Statement
For executing the query we have to call executeQuery(“****Query***”) method.Argument inside this method is SQL query and we save result in ResultSet.
Resultset rs= st.executeQuery("Select * from Student");Process the Result
Results from the executed query are stored in the ResultSet Object.
For example :
while (res.next())
{
System.out.print(res.getString(1));
System.out.print(" " + res.getString(2));
System.out.print(" " + res.getString(3));
System.out.println(" " + res.getString(4));
}
There are different methods present for process the result.
Closing the connection.
Once execution of all statements were completed we need to close all the connections by using method called close() present in Connection interface
con.close();
Below is the complete program for DB testing using selenium webdriver
package com.test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class ReadExcelFillo {
public static void dBTesting() throws SQLException, ClassNotFoundException {
String dbURL = "jdbc:mysql://ipAddress:portNumber/dbName";
String username = myUserName;
String password = myPassword;
//Load MySQL JDBC Driver
Class.forName("com.mysql.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();
}
}