MySQL Database testing using selenium webdriver

In this post, we will discuss Database testing using selenium webdriver using the connector. You can take data using excel or CSV file as well but some company still use the database to take data from the different database.

As we already know, Selenium does not support Database Testing but partially we can do using JDBC and ODBC. We will connect Java program with database and will fetch data tables and will verify the data based on our requirement.

I have also uploaded video on YouTube for SQL Database Connection

[フレーム]

You will get this question in interview as well in a different way like Database testing using selenium webdriver

[画像:Database testing using selenium webdriver]

Database testing using selenium webdriver

Part 1- Connect with MS Access Database

Part 2- Connect with Oracle Database

Database testing using selenium webdriver using MS Access Database

These are the simple steps which we will follow in our program

Step 1- First Load the driver

Step 2-Then Create a connection

Step 3- Then Create a statement

Step 4- Then Execute your SQL query

Step 5- And Store the data in Result set

Step 6- Finally verify whether data (table) is updated or not

Let’s implement the same using Java and DB as MS Access

Precondition- Set the DSN (Data Source Name)

Step to setup DSN-

1- Open Control Panel > Then go to Administrative tool > Then Click on ODBC

2- Go to User DSN tab and Click Add button

3- Now Select the respective database which you want to connect in this case I have selected MS Access

4- Specify the name of DSN and Click on Save button

Complete Database testing using selenium webdriver

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.testng.annotations.Test;
public class TestDatabase {
 
 
 
 @Test
 public void TestVerifyDB(){
 
 try {
            
    // This will load the driver
   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
   System.out.println("Driver Loaded");
  
  
  // Specify the path of the database
   String dblocation= "C:\\Users\\Desktop\\DB\\FacebookDB1.accdb";
       
  // This will connect with Database , getConnection taking three argument
  // first argument Test_Oracle is the dsn which you can change as per your system,
    Connection con=DriverManager.getConnection("jdbc:odbc:AscentAccess;DBQ="+dblocation);      
    System.out.println("Connection created");
 
  // This will create statement 
    Statement smt=con.createStatement();
     
    System.out.println("Statement created");
  // Now execute the query, here facebook is the table which I have created in DB 
  
    ResultSet rs= smt.executeQuery("Select * from Facebook");
 
    System.out.println("Query Executed");
 
 // Iterate the resultset now
    while(rs.next()){
  
    String uname=  rs.getString("username");
    String pass=    rs.getString("password");
    String email=   rs.getString("email");
 
    System.out.println("Uname is "+uname+" password is "+pass+" email id is "+email);
  
 }
}
    catch (Exception e) {
     System.out.println(e.getMessage());
 }
  
 
 }
}

Oracle Database connection using JDBC.

Let see how to connect with Database using ODBC Driver

These are the simple steps to follow

Step 1- Load the driver

Step 2-Create a connection

Step 3- Create a statement

Step 4- Execute your query

Step 5- Store the data in Result set

Step 6- Verify whether data (table) is updated or not

Let’s implement the same using Java and DB as Oracle

Precondition- Set the DSN (Data Source Name)

Step to setup DSN-

1- Control Panel > Administrative Tool > Click on ODBC

2- Go to User DSN and Click on Add button

3- Select the respective database which you want to connect in this case I selected Oracle 11G

4- Give the name to DSN and Save

Database testing in Selenium using Oracle

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.testng.annotations.Test;
public class TestDatabase {
 
 
 
 @Test
 public void TestVerifyDB(){
 
 try {
             
             // This will load the driver
  Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
  System.out.println("Driver Loaded");
  
  
   
  // This will connect with Database , getConnection taking three argument
  // first argument Test_Oracle is the dsn which you can change as per your system,
  // second argument is username and third argument is password
  Connection con=DriverManager.getConnection("jdbc:odbc:Test_Oracle",        "system","selenium");
  System.out.println("Connection created");
  
 // This will create statement 
     Statement smt=con.createStatement();
     
     System.out.println("Statement created");
 // Now execute the query, here facebook is the table which I have created in DB 
   
 ResultSet rs= smt.executeQuery("Select * from Facebook");
 
 System.out.println("Query Executed");
 
 // Iterate the resultset now
 while(rs.next()){
  
 String uname=  rs.getString("username");
 String pass=  rs.getString("password");
 String email=  rs.getString("email");
 
 System.out.println("Uname is "+uname+" password is "+pass+" email id is "+email);
  
 }
}
 catch (Exception e) {
  System.out.println(e.getMessage());
 }
  
 
 }
}