Selenium – Access Database using JDBC

by seetaram on November 26, 2011

There might be some test scenarios in which the Application Under Test (AUT) will be sending SMS to a mobile.  Then, how do you validate whether the SMS sent or not? In some other test scenario such as “Forgot Password”, the password that is newly generated might have sent to user’s email address.

In both the cases, it is better to access database from the Selenium Test and get the data needed by executing a database query.

In this blog post, we will see how to use JDBC to access database considering the Selenium is using Java client driver.

JDBC – Java Database Connectivity

This Java API supports executing database queries from Java code. Some of the classes & methods that are used for basic query execution are:

  1. Class.forName()
  2. DriverManager.getConnection()
  3. conn.createStatement()
  4. smt.executeQuery()

First of all, we need to create a file DSN which will be having the information of the type of driver, username, password, server, etc.

Follow the below steps to create a file DSN (on Windows XP):

  1. Start –>Settings—>Control Panel—>Administrative tools
  2. Click Data Sources –>File DSN –> Add
  3. Select Microsoft ODBC for Oracle –> Give a name –> Provide credentials
  4. Now the File DSN is created (Data Source Name)
package com.practice.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;
/*
 * This class helps in retrieving the data from Oracle
 * Author - Seetaram Hegde
 */
public class Dataset {
	private String conURL;
	private String uname;
	private String pwd;
	private String dURL;
	private Connection con = null;
	
	public Dataset(String connectionURL, String driverURL, String username, String password){
		this.conURL=connectionURL;
		this.dURL=driverURL;
		this.uname=username;
		this.pwd=password;
	}
	
	public HashMap<Integer, String> ExecQuery(String Query,int colIndex) throws Exception{
		HashMap<Integer, String> columnVal=new HashMap<Integer, String>();
		int i=0;
	    Connection conn = getDatabaseConnection();
	    if(con!= null){
		    Statement smt = conn.createStatement();
		    ResultSet rslt =
		         smt.executeQuery(Query);
		    while (rslt.next()) {
		    	columnVal.put(i, rslt.getString(colIndex));
		    	i++;
		    }
		    smt.close();

	    }else{
		    System.out.println("Could not Get Connection");
	    }
		
		
		return columnVal;
	}
	
	private Connection getDatabaseConnection(){

		try {
			Class.forName(dURL);	

		} catch(java.lang.ClassNotFoundException e) {
			System.err.print("ClassNotFoundException: ");
			System.err.println(e.getMessage());
		}

		try {
		   this.con = DriverManager.getConnection(conURL, uname, pwd);
		} catch(SQLException ex) {
			System.err.println("SQLException: " + ex.getMessage());
		}

		return con;
	}
	
}

The above class can directly be used in retrieving the data from a database. Just for demonstration, we will use EMP table of the Oracle and try to fetch data from “Ename” column of the table.

Let us have a look at the below code which uses the above class to get the data from the Oracle database.

package com.practice.jdbc;

import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;

/*
 * This Java program utilizes the Dataset class to retrieve the data from EMP table
 * Author - Seetaram Hegde
 */

public class JDBCEx {

	public static void main(String[] args) {
		HashMap<Integer, String> colData = new HashMap<Integer, String>();
		Dataset DB = new Dataset("jdbc:odbc:OraDSN",
				     "sun.jdbc.odbc.JdbcOdbcDriver","scott","tiger");
		try {
			colData=DB.ExecQuery("select ename from emp", 1);
			Set set = colData.entrySet();
			Iterator i = set.iterator();
			
			while(i.hasNext()) {
				Map.Entry me = (Map.Entry)i.next();
				System.out.print(me.getKey() + ": ");
				System.out.println(me.getValue());
				}
				System.out.println(); 
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

}

Let us take a look at the above code snippets and find out what actually we have done in the code.

The dataset class uses Class.forName() to establish a connection to database and returns connection object. The connection object is used to create a statement using “Statement smt = conn.createStatement()”.  Once the statement is created, the executeQuery() method of statement is executed to get Resultset.  The Resultset gets the entire set of data in the table.

How to use this in a Selenium test?

1. Write a query to get the “Newly generated password” from the database table

2. Write a query to get the “SMS sent” from the database table.  

I hope the above explanation on how to use JDBC to access database from a Selenium test will be helpful.

{ 3 comments… read them below or add one }

Hina February 23, 2012 at 4:06 pm

Hello,

I had a quick question for you. I came across a test framework called Hermes . It seems easy to use. As we are evaluating using Selenium , I wanted to know whether is there any reason why this framework does not seem to be very popular?

Thanks!

Reply

umesh February 17, 2013 at 6:46 am

Hi Hena,

I am umesh. In my office I am asked to develop a framework and I downloaded Hermes.But I am unable to use it correctly.
Can you please guide me regarding this,

Thanks,
Umesh M.

Reply

Sanjay March 28, 2014 at 7:20 am

Thanks

Reply

Leave a Comment

Previous post:

Next post:

Do you want to get updates on the recent articles written? Please subscribe to RSS feed or Email