3 Ways To Access Database from a Selenium Automation Framework

Accessing data from a Database within a Test Automation Framework will be mostly for validation of data already in the tables, where the actual inputs come from our Application Under Test(AUT). In Java Database Connectivity(JDBC) API the java.sql.ResultSet interface provides methods for retrieving the results of executed queries.
Usually in a Test Automation Framework like Selenium Framework we separate the helper/util class for Database connection from the calling test methods residing in another class. The problem with ResultSet here is, it cannot be passed between classes as it maintains a connection to a database, and closing the connection will erase the ResultSet. And best practices say that we should always close the connection and ResultSet after the results are retrieved.
Below are few examples using ResultSet and the CachedRowSet - the "disconnected" ResultSet. The CachedRowSet stores(caches) its data in memory so that it can operate on its own data without being connected to its data source.

1) Example using ResultSet: In the helper/util class here we return the ResultSet, but we cannot close the Connection.

................
 // JDBC driver name and database URL
 private final String JDBC_DRIVER = "oracle.jdbc.driver.OracleDriver";
 final String DB_URL = "jdbc:oracle:thin:@192.168.56.102:1521:ora10g";

 // Database credentials
 private final String USER = "oe";
 final String PASS = "oe";

 Connection conn = null;
 Statement stmt = null;

 public ResultSet getResult(String sql) {
  try {
   Class.forName(JDBC_DRIVER);
   conn = DriverManager.getConnection(DB_URL, USER, PASS);
   stmt = conn.createStatement();
   ResultSet rs = stmt.executeQuery(sql);
   // stmt.close(); //cant do this, if we want to return rs.
   // conn.close(); //ResultSet is closed with the statement or conn
   return rs;
  } catch (SQLException | ClassNotFoundException se) {
   se.printStackTrace();
   return null;
  }
 }
.................

Now from the calling test methods we can only close the ResultSet but not the Connection.

..............
sql = "SELECT EMPLOYEE_ID, FIRST_NAME, SALARY FROM Employees";
ResultSet rs = db.getResult(sql);
 try {
  while (rs.next()) {
  int id = rs.getInt("EMPLOYEE_ID");
  System.out.print("ID: " + id);
 }
//close only the resultset in finally, we cannot close connection from here
..............

2) Another Example with ResultSet: We can also implement only the Connection in the helper/util method, but in that case the closing of Connections, Resultset will have to be implemented by the person writing the test methods. But we want testers to only concentrate on writing tests, better :)

..........
 public Connection getDBConnection() { 
  try {
   Class.forName(JDBC_DRIVER);
   conn = DriverManager.getConnection(DB_URL, USER, PASS);
   return conn; // return connection only, caller has to close all db connections
  } catch (SQLException | ClassNotFoundException se) {
   se.printStackTrace();
   return null;
  }
 }
..........

And so from the test method:

......................
  sql = "SELECT EMPLOYEE_ID, LAST_NAME, JOB_ID FROM Employees";
  Connection conn = db.getDBConnection();
  Statement stmt = conn.createStatement();
  ResultSet rs = stmt.executeQuery(sql);
  try {
   while (rs.next()) {

    int id1 = rs.getInt("EMPLOYEE_ID");
    System.out.print("ID: " + id1);
   }
  }
 //Close Connection, ResultSet in finally block
......................

3) Example with CachedRowSet:
The best approach seems to be using the OracleCachedRowSet object which can operate in a disconnected environment. Populate a OracleCachedRowSet object with data from ResultSet and we can send it over the network, or to other classes  or serialize it.
Below is example of DB helper/util class returning OracleCachedRowSet and a test class using the CachedRowSet from the  DB util.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import oracle.jdbc.rowset.OracleCachedRowSet;
/**
 * Java Class: DButilCacheRowSet.java
 * Util class to fetch data from database and return the results to calling methods
 *  @author sarats
 *
 */
public class DButilCacheRowSet {
 private final String JDBC_DRIVER = "oracle.jdbc.driver.OracleDriver";
 private final String DB_URL = "jdbc:oracle:thin:@192.168.56.102:1521:ora10g";
 private final String USER = "oe";
 private final String PWD = "oe";
 private OracleCachedRowSet crs;

 private void executeQuery(String sql) throws ClassNotFoundException, SQLException {
  Class.forName(JDBC_DRIVER);
  Connection conn = DriverManager.getConnection(DB_URL, USER, PWD);
  Statement stmt = conn.createStatement();
  ResultSet rs = stmt.executeQuery(sql);
  crs = new OracleCachedRowSet();
  crs.populate(rs);
  rs.close();
  stmt.close();
  conn.close(); //close datasource connection
 }

 public OracleCachedRowSet getCachedResultSet(String sql) {
  try {
   executeQuery(sql);
   return crs;
  } catch (ClassNotFoundException | SQLException e) {
   e.printStackTrace();
   return null;
  }
 }
}

Now we test the helper method with the below class:

import java.sql.SQLException;
import oracle.jdbc.rowset.OracleCachedRowSet;
/**
 * Java class to test the CachedRowSet returned from DButilCacheRowSet.java 
 * @author sarats
 *
 */
public class TestDButilCRS {
 public static void main(String[] args) {
  OracleCachedRowSet crs;
  DButilCacheRowSet dbutil = new DButilCacheRowSet();
  String sql;
  sql = "SELECT EMPLOYEE_ID, FIRST_NAME, SALARY FROM Employees";
  crs = dbutil.getCachedResultSet(sql);
  try {
   while (crs.next()) {
    // Retrieving the results and displaying it
    int id = crs.getInt("EMPLOYEE_ID");
    String first = crs.getString("FIRST_NAME");
    String sal = crs.getString("SALARY");

    System.out.print("ID: " + id);
    System.out.print("; Firstname: " + first);
    System.out.println("; Salary: " + sal);
   }
  } catch (SQLException se) {
   se.printStackTrace();
  } finally {
   try {
    crs.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }
  }
 }
}

Finally there are ofcourse other ways of doing this like spring-jdbc template, ApacheDButils, or creating a container for holding the ResultSet. We leave that for some other day :)

References:
http://docs.oracle.com/javase/tutorial/jdbc/basics/cachedrowset.html
http://www.onjava.com/pub/a/onjava/2004/06/23/cachedrowset.html