Oracle DB Testing with Selenium

1. For Database Verification in To use Selenium Webdriver, we need to use the JDBC ("Java Database Connectivity"). Its API provides following classes and interfaces:

• Driver Manager
• Connection
• Statement
• ResultSet
• SQLException

2. In order to test our Database using Selenium, we need to perform the following steps:

a. Make a connection to the Database. Syntax is:
Connection DB_Con= DriverManager.getConnection(URL, "USERID", "PASSWORD" )

And load JDBC Driver class using the syntax:

Class.forName("oracle.jdbc.driver.OracleDriver");

b. Execute Queries to the Database

Statement stmt = DB_Con.createStatement();

stmt.executeQuery(select *  from employee;);

ResultSet rs= stmt.executeQuery(query);

c. Process the result set based on your need:

while (rs.next()){

String FisrtName= rs.getString(1);         
String LastName= rs.getString(2);
System.out.println("First Name is: " +FisrtName);
System.out.println("LastName is: " +LastName);
}

d. close DB connection

DB_Con.close();

Note* We should download an Oracle Jar from Oracle website like for Oracle11 (Classes12.jar) we can get it from http://www.oracle.com/technetwork/apps-tech/jdbc-10201-088211.html  and add this JAR in our project Build Path.


Sample code:

package com.qa.Util;

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

public class ConnectDB {

public static ResultSet getData(String query, String environment) {
String JDBC_DRIVER = null;
String DB_URL = null;
String PASS = null;
String USER = null;

if (environment.equalsIgnoreCase("QA")) {
JDBC_DRIVER = "oracle.jdbc.driver.OracleDriver";
DB_URL = "jdbc:oracle:thin:@dbtest.site.com:1001:<TEST>";
PASS = "testpwd";
USER = testuser";

}

Connection conn = null;
Statement stmt = null;
ResultSet rs = null;

try {

Class.forName(JDBC_DRIVER);

conn = DriverManager.getConnection(DB_URL, USER, PASS);

stmt = conn.createStatement();

rs = stmt.executeQuery(query);

} catch (Exception e) {
e.printStackTrace();
}
return rs;
}
}

package com.qa.Util;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.Collection;

import java.util.List;

import java.util.TreeSet;

public class FetchDBData {

String querytoFetch = "select rcv_ser_no from testDB.activation where BOX_NUMBER like 'DEF%' and (to_char(contract_eff_dt) >= to_date('01-MAR-18') and to_char(contract_eff_dt) <= to_date('01-MAY-18')) and rownum=1";

public static String FREE_BOX;

public static String GetTSN() throws SQLException {

ResultSet rs = ConnectDB.getData(querytoFetch, "QA");

while (rs.next()) {

FREE_BOX= rs.getString("box_no");

}

return FREE_BOX;

}

}