In the previous tutorials we have set up Bromine to run with Selenium Java test cases, so now it’s time to create a util library that will hold the classes used in multiple projects.

For now we have created a new project in eclipse for each test case, and talked about the placement of the libraries needed by Bromine and Selenium. Let me refresh your memory. Bromine requires each test case to be a separate JAR, so for each of them we created one project in eclipse and used the ant build script to create these jars.
In order to run the selenium script we need to add the dependency libraries to the classpath. There are two ways to do this. First is to include all the libraries in the newly created jar, but this will result in bigger file size, and we will have the same libraries on multiple places if we have more than one test case. This violates the DRY (don’t repeat yourself) principal, so we used the second option, which is to have the dependency libraries placed in a known location on the disc, and just to include their path in the classpath.

If we have a part of code that we want to use on multiple places, it is a good idea to place it in a separate class and just call a method instead of copying the same code over and over again. If this code is used in multiple test cases, then we can put it in a library and just include it to the classpath, the same way we did the other dependencies. Let me show you how this is done.

The test cases that we are using need to get some values from the Oracle database, and to check if those values are present on the web page we are testing. This means that we will need a jdbc driver to connect to the Oracle database, a connection pool that we can use to checkout a connection to the database, and a util class that holds the code for the select, update and delete statements. We will make this code as generic as possible, so we can put it in a library and just use it as a dependency in the other test cases.

Let’s create a new project in Eclipse by choosing New Java Project, and add a name. We will call it “ALAS_db”.

Now create a new package “com.alasdoo.db” and create a new “ConnectionPool” class in it.

Paste the following code to the newly created class. The code won’t be discussed this time, because it falls outside of the scope of this tutorial. I’ll just say that this code lets us create a pool of connections to the database and lets us use a connection from the pool when we need it.

package com.alasdoo.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.PropertyResourceBundle;
import java.util.ResourceBundle;

public class ConnectionPool {

  /** Returns a singleton instance of the pool. */
  public static ConnectionPool getInstance() {
    return instance;
  }

  /** Takes one connection from the pool. */
  public synchronized Connection checkOut() throws SQLException {
    Connection conn = null;
    if (freeConnections.size() > 0) {
      conn = (Connection)freeConnections.get(0);
      freeConnections.remove(0);
      usedConnections.add(conn);
    } else {
      if (connectCount < maxConnections) {
        conn = DriverManager.getConnection(
          jdbcURL, username, password);
        usedConnections.add(conn);
        connectCount++;
      } else {
        try {
          wait();
          conn = freeConnections.get(0);
          freeConnections.remove(0);
          usedConnections.add(conn);
        } catch (InterruptedException ex) {
          ex.printStackTrace();
        }
      }
    }
    return conn;
  }

  /** Returns the connection to the pool. */
  public synchronized void checkIn(Connection aConn) {
    if (aConn ==  null)
      return;
    if (usedConnections.remove(aConn)) {
      freeConnections.add(aConn);
      while (freeConnections.size() > maxIdleConnections) {
        int lastOne = freeConnections.size() - 1;
        Connection conn = freeConnections.get(lastOne);
        try { conn.close(); } catch (SQLException ex) { }
        freeConnections.remove(lastOne);
      }
      notify();
    }
  }

  /** Create a pool. */
  protected ConnectionPool(String driver, String jdbcURL, String username,
      String password, int preconnectCount, int maxIdleConnections,
      int maxConnections) throws ClassNotFoundException, SQLException {

    freeConnections = new ArrayList<Connection>();
    usedConnections = new ArrayList<Connection>();
    this.jdbcURL = jdbcURL;
    this.username = username;
    this.password = password;
    this.maxIdleConnections = maxIdleConnections;
    this.maxConnections = maxConnections;

    Class.forName(driver);
    for (int i = 0; i < preconnectCount; i++) {
      Connection conn = DriverManager.getConnection(
        jdbcURL, username, password);
      conn.setAutoCommit(false);
      freeConnections.add(conn);
    }
    connectCount = preconnectCount;
  }

  private static ConnectionPool instance;
  private String jdbcURL;
  private String username;
  private String password;
  private int connectCount;
  private int maxIdleConnections;
  private int maxConnections;
  private List<Connection> usedConnections;
  private List<Connection> freeConnections;

  static {
    ResourceBundle bundle =
      PropertyResourceBundle.getBundle(
          "com.alasdoo.db.DBConnection");
    String driver = bundle.getString("driver");
    String jdbcURL = bundle.getString("url");
    String username = bundle.getString("username");
    String password = bundle.getString("password");
    int preconnectCount = 0;
    int maxIdleConnections = 10;
    int maxConnections = 10;
    try {
      preconnectCount = Integer.parseInt(
        bundle.getString("preconnectCount"));
      maxIdleConnections = Integer.parseInt(
        bundle.getString("maxIdleConnections"));
      maxConnections = Integer.parseInt(
        bundle.getString("maxConnections"));
    } catch (Exception ex) {
      ex.printStackTrace();
    }
    try {
      instance = new ConnectionPool(driver,
        jdbcURL, username, password,
        preconnectCount, maxIdleConnections,
        maxConnections);
    } catch (Exception ex) {
      ex.printStackTrace();
    }
  }
}

As you can see from the code, we have used a Property Resource Bundle to store database connection configuration, so we need to create this properties file (DBConnection.properties).

And the content of it:

driver=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@localhost:dev
username=dev
password=dev
preconnectCount=3
maxIdleConnections=5
maxConnections=10

At this point we have the code to connect to the database, so now we need the util class that will perform our SQL queries. Create a new class called DBUtil.

This class will hold the code that will enable us to execute SQL select, update and delete queries, and to get their return values. Again, the code itself won’t be discussed in details.

package com.alasdoo.db;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class DBUtil {

/**
* Finds all the values for the given query
*
* @param query - The SQL query that will be used to get data from the DB
* @return Returns a list of strings that represent the data in the DB
*/
public static List<String> getAllValues(String query) {
try {

     Connection conn = ConnectionPool.getInstance().checkOut();

     Statement stmt = conn.createStatement();
     ResultSet rset = stmt.executeQuery(query);
     List<String> res = new ArrayList<String>();
     while (rset.next()) {
       res.add(rset.getString(1));
     }
     rset.close();
     stmt.close();

     ConnectionPool.getInstance().checkIn(conn);

     return res;

} catch (Exception ex) {
ex.printStackTrace();
}
return null;
}
/**
* Get a single value from the DB
* @param query - The SQL query that will be used to get data from the DB
* @return Returns the first hit from the DB
*/
public static String getFistValue(String query) {
return getAllValues(query).get(0);
}
/**
* Updates the DB
* @param query - The SQL query that will update the DB
* @return true if update performed successfully
*/
public static boolean update(String query) {
   boolean success = false;
   Connection conn = null;
   try {
     conn = ConnectionPool.getInstance().checkOut();
     Statement stmt = conn.createStatement();
     int rowsAffected = stmt.executeUpdate(query);
     success = rowsAffected > 0;
     stmt.close();
     conn.commit();
   } catch (Exception ex) {
     ex.printStackTrace();
   } finally {
     ConnectionPool.getInstance().checkIn(conn);
   }
   return success;
 }

 /**
  * Deletes from DB
  * @param query - The SQL query that will delete from DB
  * @return true if update performed successfully
  */
 public static boolean delete(String query) {
   return update(query);
 }
}

Finally we are done with coding, so we can pack this project in a JAR file, so we will use Eclips’ Export function. Select JAR file (not Runnable JAR file), and just follow the wizard.

Now we have a functional library that we can include to the test cases. Before we start modifying our test cases, we need to put the newly created JAR file to the Bromine project’s lib folder. By copying the dependency files to this location, we are able to use relative paths in our build script.
If you are using XAMPP to run Bromine, then the location is:

XAMPP/xamppfiles/htdocs/app/webroot/testscripts/ALASdoo test/jar

If you are using any other database other then MySQL, don’t forget to copy the jdbc driver to this same location.

Once we are done with the file copying, we can move on to modifying the test scripts. We will start by adding changes to the build script. The only change needed here is the classpath:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<project default="create_run_jar" name="Create Runnable Jar for Project Eversave_mySaves with libraries in sub-folder">
    <!--ANT 1.7 is required                                        -->
    <target name="create_run_jar">
        <jar destfile="../build_jars/MySaves.jar">
            <manifest>
                <attribute name="Main-Class" value="MySavesTest"/>
                <attribute name="Class-Path" value=". lib/ALAS_db.jar lib/ojdbc14.jar lib/BRUnit.jar lib/commons-lang-2.4.jar lib/mysql-connector-java-5.1.7-bin.jar lib/selenium-java-client-driver.jar"/>
            </manifest>
            <fileset dir="./"/>
        </jar>
    </target>
</project>

This takes care of the dependency linking. You’ll also need to add the ALAS_db.jar to the buildpath in Eclipse. This will not have any effect on the final JAR, so if you are not using Eclipse, just ignore this.

To query the database, all we need to do is to call the appropriate method from our DBUtil class. For the SELECT statement:

List<String> text = DBUtil.getAllValues("select username from members where firstname='joe'");

Or if you would like to get only the first returned value:

String text = DBUtil.getFistValue("SELECT username FROM members WHERE FirstName='Joe'");

UPDATE statement:

boolean updated = DBUtil.update("UPDATE Persons SET Address='Nissestien 67' WHERE LastName='Tjessem' AND FirstName='Jakob'");

DELETE statement:

boolean deleted = DBUtil.delete("DELETE FROM Persons WHERE LastName='Tjessem' AND FirstName='Jakob'");

After all the changes are done to the test case’s code, just run the ant build script and upload the new JAR file to Bromine.