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.
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 (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.
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
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
This getConnection accepts three parameters of String Type:
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
// 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
SELECT * from testuser
We can execute our formed SQL query using
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 code
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/u316685130_db", "u316685130_karthiQ", "[email protected]");
} 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
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 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 :
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
1. Navigate to https://jdbc.postgresql.org/download.html
2. Download the Current jar from current version section.
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.
1. We have to load the PostgreSQL driver and register it using
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
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
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());
}
}
}