What is database

Data base is nothing but the stack of details, placed in organized manner. It is like School Attendance Book, we can get the studen and details about the student like that database stores different data with different datatypes.

We can filter thse data using a Language called SQL, SQL (Structured Query language) helps retrieve, insert,update, and delete the data from the database. We can store large data in database and retrievs in the required data in seconds.

Why do we need database automation ?

  • Test Data - We can store the data in database instead of excel or properties, JSON/xml files, retrieval woud be faster incase of database if we are trying to get large set of data.
  • Front and Back end connectivity - Sometimes we have to verify the details we submitted / deleted / updated in front end is reaching the back end for future usage purpose. There are scenarios where details may fail during the parsing, in such cases details will not reach database
  • Faster Testing - If we are testing simple database tasks manually it may take lot of time and effort, so to avoid that we can go for automation testing of database

Database testing in selenium webdriver

We cannot perform database testing using selenium webdriver but we can use language ( java / python ...) APIs to automate the database in selenium.

Selenium does not provide any utility to do database testing, You will have to utilize feature available in programming language.

For example, if you are using Java, you need to add appropriate .jar file & write own method to read/write/manipuated data in database table.

JDBC with selenium Webdriver

JDBC (Java Database Connectivity) is an java api (like Actions, Collections ...), basically JDBC api is used to connect the databases with application using Java language.

JDBC connects almost all the databases present in current market like MYSQL, Oracle RDBMS, PostgreSQL, SQLite and few more databases.

JDBC supports basic SQL functionality, It provides database management access by allowing user to embed SQL inside Java code.

Integrate JDBC with selenium Webdriver for MySQL database

1. Navigate to https://dev.mysql.com/downloads/connector/j/
2. Download the Platform Independent Zip File under Generally Available (GA) releases for windows
jdbc-jar-selenium-webdriver 3. Now it navigates to download page, click on the 'No Thanks, Just download' link
4. Extract the downloaded file.
5. Now Right Click on The Project > Properties > Java Build Path >Add External Jar
6. Add the mysql-connector-java jar from the extracted folder. and click OK


Steps involved in database Connection

  1. Import JDBC packages.
  2. Load and register the JDBC driver.
  3. Open a connection to the database.
  4. Create a statement object to perform a query.
  5. Execute the statement object and return a query resultset.
  6. Process the resultset.
  7. Close the resultset and statement objects.
  8. Close the connection

This tutorial focuses on how to connect to the local system database using JDBC and manipulate the values from the database.

1. Import JDBC packages :
For Using any extenal class we have to import the class from the other packages, sometimes same classes could be present under different packages.

To use JDBC we have to import the below packages.


						import java.sql.Connection;
						import java.sql.DriverManager;
						import java.sql.ResultSet;
						import java.sql.Statement;
						


2. Load and register the JDBC driver :
We have to load the class to register our JDBC with database using Class.forName("com.mysql.jdbc.Driver");, this line of code tells the db that we are going use JDBC driver.

Internally this Driver class will register the driver by using static method called registerDriver().


						Class.forName("com.mysql.jdbc.Driver");
						


3. Open a connection to the database :
To connect with database we should call getConnection() static method present in DriverManager Class.

This getConnection accepts three parameters of String Type:

  • url : "jdbc:mysql://host:port/dbName"
  • username : user name for the provided database
  • password : password for the given database.

getConnection method also returns the database object, we have to store this return value for future data operations in database.


						Connection conn = DriverManager.getConnection("jdbc:mysql://host:port/dbName","username","password"); 
						

If your database is present in local machine then you can provide you localhost details and the port number.

Below query connect to testuser database which is present in localhost (local system) using 3306 port number and username :rootUser, password :rootPassword.


						DriverManager.getConnection("jdbc:mysql://localhost:3306/testuser","rootUser","rootPassword"); 
						


4. Create a statement object to perform a query :
We have to create Statemenet object for executing our SQL queries on database, We can create the object for Statement by calling the createStatement() method from the connection object (we created in step 3).


						// Object of Statement. It is used to create a Statement to execute the query
						Statement stmt = conn.createStatement();
						


5. Execute the statement object and return a query resultset :
Now we have to form our SQL queries to execute them on the database, Queries like Select, Create, Insert, Update, Delete.

testuser table looks like below sample-table-database-testing-selenium-webdriver


						SELECT * from testuser
						

We can execute our formed SQL query using executeQuery method present in stamenet object, Results from the executed query are stored in the Result Set Object.

The Resultset maintains a cursor that points to the current row in the result set.


						//Object of ResultSet => 'It maintains a cursor that points to the current row in the result set'
						ResultSet resultSet = stmt.executeQuery("SELECT * from testuser");
						


6. Process the resultset :
Once we receive out result set we can manipulate the data, now below code will displays the all the recods present in the testuser table from the database.
Note : Index for table columns starts from 1 not from 0


						while (resultSet .next()) {
							System.out.println(resultSet .getString(1) + " | " + resultSet .getString(2) +" | "+ resultSet .getString(3));
						}
						

output of above codedatabase-sample-output-selenium-webdriver


7. Close the resultset and statement objects :
After execution we must close the connection to the resutset and statment irrespective whether the execution passes or fails


						
							resultSet.close();
					
							stmt.close();
							
						


8. Close the connection :
We also should colse the connection to the database, we can keep it open when we are executing something after the first operation


						
							conn.close();
						
						


Complete program for reading database


						import java.sql.Connection;
						import java.sql.DriverManager;
						import java.sql.ResultSet;
						import java.sql.SQLException;
						import java.sql.Statement;
						import org.testng.annotations.BeforeMethod;
						import org.testng.annotations.Test;

						public class DBTest {
							
							public static Connection conn;
							public static Statement stmt;
							public static ResultSet resultSet;
							
							@BeforeMethod
							public void setupDatabaseConnection()
							{
								try {
									Class.forName("com.mysql.jdbc.Driver");
									// Object of Connection from the Database
									conn = DriverManager.getConnection("jdbc:mysql://sql132.main-hosting.eu/u200137511_db", "u200137511_karthiQ", "xxxx@123");
												
								} catch (ClassNotFoundException e) {
									System.out.println("Exception occured while loading the driver of JDBC");
									e.printStackTrace();
								} catch (SQLException e) {
									// TODO Auto-generated catch block
									e.printStackTrace();
								}	
							}
							
							@Test
							public static void dbTest() throws SQLException{
								try{
									
									// Object of Statement. It is used to create a Statement to execute the query
									stmt = conn.createStatement();
									
									//Object of ResultSet => 'It maintains a cursor that points to the current row in the result set'
									resultSet = stmt.executeQuery("SELECT * from testuser");
									while (resultSet .next()) {
										System.out.println(resultSet .getString(1) + " | " + resultSet .getString(2) +" | "+ resultSet .getString(3));
									}
								}catch(Exception e){
									System.out.println("Exeception occured in db testing");
									e.printStackTrace();
								}finally {
									// close the connection if not already closed
									if (resultSet != null) {
										resultSet.close();
									}
									if (stmt != null) {
										stmt.close();
									}
									if (conn != null) {
										conn.close();
									}
								}
							}
						}

						

Tip for DB Programs : Never close the DB connection in try or catch block, always try to close connections in finally block.

Scenario 1 : If you write code to close the db connection in try block there is a chance that when an exception arises before reaching the connection closing line the control of the program goes to catch block without reaching connection closing line in try block, which means your connection are not closed

Scenario 2 : If you try to close the connection in catch block then there is a chance that when no exception occurs in try block, the code in the catch block never gets executed, so the connection to db will be alive even after our operations.

Normally we can write the above program without using TestNG as well. We have used testNG for showcasing the automation tester that how database connections should look like inside the selenium webdriver testcases.

Remember that we donot write the database connections like this in selenium frameworks, we will be creating re-usable methods for reading / writing the database values

Hidden Locators in Selenium Webdriver

Connect to Remote database JDBC in Selenium Webdriver

In above tutorial we have seen how to connect to database on the local machine with JDBC in selenium, in this tutorial we are gonna connect to the remote machine database using JDBC along with selenium webdriver

All the steps remains same except what url we use, we have to give the exact ip address of the server or the exact domain name of the server to connect to the remote server

URL may or may not have the port number in it, before accessing the remote server make sure you have the right access.

Sometimes you also need to add ip address of your system to the remote server providers ( my hosting and remote server is present with hostinger).


						// Object of Connection from the Database
						String URL = "jdbc:mysql://xxxx.main-hosting.eu/chercher_db";
						conn = DriverManager.getConnection(URL, "chercherUser", "chercherTech");
						

PostgreSQL with Selenium Webdriver

PostgreSQL is another databse management system like MYSQL, it is been present in IT market since 1995, PostgreSQL is an open source developed by curious volunteers around the globe.

PostgreSQL is platform independent, PostgreSQL is also a fine choice, and it has some SQL language features that MySQL doesn't, like common table expressions and user-defined types and multi-language stored procedures.

Here are few things which makes people to prefer PostgreSQL over MYSQL, and MySQL doesnot support below features :

  • Check constraints
  • Rich data types (arrays, maps, json)
  • Rich Geo-spatial Support (postgis)
  • Rich Full text Support
  • Nonblocking index creation
  • Partial Indexes
  • Common Table Expressions
  • Analytics Functions

Please don't get confused with Postgre and PostgreSQL, In mid 1980 team have named it as Postgre but during 1995 they have changed the name to PostgreSQL

PostGreSQL Integration : Top of this page we have discussed how to add jar file for the JDBC driver, for running PostgreSQL we need JDBC and PostGreSQL connector jars.

1. Navigate to https://jdbc.postgresql.org/download.html
2. Download the Current jar from current version section.
postgresql-connector-jar-selenium-webdriver

3. (On eclipse) Project > Right Click > Properties > Java Build Path > Add External Jars.
4. Select the PostgreSQL jar from your local machine and clock OK button. Ta Da, we are done with integration.

Connecting to PostGreSQL database with selenium :
Warning :
I am giving my actual username and password in this tutorial, please donot mis-use it, may be I am a stupid guy to trust a stranger but my intention is everyone should be able to practice this tutorial with real examples. So please donot spam or delete any tables, db, username, password will be sent based on the request (see step 2)

Connecting to PostgreSQL in JDBC is similar to the connection we made with MySQL, Please follow below steps to connect to PostgreSQL.

1. We have to load the PostgreSQL driver and register it using Class.forname method.

Note : JDBC 4 version onwards no need to register the driver, JDBC automatically loads and registers it.


					Class.forName("org.postgresql.Driver");
					


2. Create connection to the PostgreSQL database using getConnection method present in DriverManager class.
This emthod accepts Three parameter namely url, name , password.

Url : Address and name of the database (Address is nothing but the ip address with/without port).
username : User name for the db.
password : Passowrd for the above user name.

Use below form to get my db access but please do remember With Great Power Comes Great Responsibility


Get the db details to your e-mail to have actual database access


					String url = "jdbc:postgresql://.com:5432/cherchertech";
					String username = "cherchertech";
					String password = "tIqw79pN16MymoAm";
					Connection db = DriverManager.getConnection(url, username, password);
					


3. Create Statement and executeQuery :
For executing SQL command we have to create the object for Statement class and call the executeQuery method from it.

We will receive execution result as ResultSet, we can process the ResultSet by creating object to ResultSet class.


					Connection db = DriverManager.getConnection(url, username, password);
					// create object for the Statement class
					Statement st = db.createStatement();
					// execute the quesry on database
					ResultSet rs = st.executeQuery("SELECT * FROM table_n");
					


4. Close the connections :
After performing the operatioon in the DB we have to close the connections of ResultSet, Statement, ConnectionManager.


					// close the result set
					rs.close();
					// close the Statemenet
					st.close();
					// close the connection
					db.close();
					

Please replace the url, username, password values using the details you got from the email. Complete program to manipulate PostgreSQL is present below.


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class PostGres {
	public static void main(String[] args) {
        try {
            Class.forName("org.postgresql.Driver");
        }
        catch (java.lang.ClassNotFoundException e) {
            System.out.println(e.getMessage());
        }
        // replace below details
        String url = "jdbc:postgresql://<replace>.com:5432/<replace>";
        String username = "<replace>";
        String password = "<replace>";

        try {
            Connection db = DriverManager.getConnection(url, username, password);
            // create object for the Statement class
            Statement st = db.createStatement();
            // execute the quesry on database
            ResultSet rs = st.executeQuery("SELECT * FROM testuser");
            System.out.println("Data retrieved from the PostgreSQL database ");
            while (rs.next()) {
                System.out.println(rs.getString(1) + " | "+rs.getString(2) +" | "+ rs.getString(3));
            }
            rs.close();
            // close the result set
            st.close();
            //close the database connection
            db.close();
            }
        catch (java.sql.SQLException e) {
            System.out.println(e.getMessage());
        }
    }
}
Output of the PostgreSQL program: postgresql-database-sample-output-selenium-webdriver

About Author

Myself KarthiQ, I am the author of this blog, I know ways to write a good article but some how I donot have the skills to make it to reach people, would you like help me to reach more people By sharing this Article in the social media.

Share this Article Facebook
Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions
  • somasekhar
    Hi...
    KarthikQ
    I Hope You Are Doing Well.....
    Really I Am Telling This Blog It is Very Nice, Its Really HelpFull for me.....
    
    
    Thanks And Regards
    M Somasekhar
    Reply
    • admin[Karthiq]
      We are glad Somasekar,
      We would be grateful if you can tell friend bout us. :)
      Reply
  • Recent Addition

    new tutorial Registrations for Selenium Online Training is Over.

    Below are the training details:
    Meeting link : https://zoom.us/j/737840591
    Starting Time : 9:00PM 18th DEC 2018
     
    Join My Facebook Group
    Join Group