Over a million developers have joined DZone.

FitNesse to Perform UAT: Part II

DZone's Guide to

FitNesse to Perform UAT: Part II

SQL testing is an integral part of application acceptance testing. How does it factor into the use of FitNesse and UAT?

· Agile Zone
Free Resource

See how three solutions work together to help your teams have the tools they need to deliver quality software quickly. Brought to you in partnership with CA Technologies

In Part I, we have seen the basics of FitNesse. In this part, let's explore SQL testing, which is important in application acceptance testing. 

Testing SQL queries is pretty easy using Fitnesse. Let's see how to use it.

Testing SQL Queries

The table structure looks like this:

create table employee(
  emp_id        int NOT NULL,
  emp_name       VARCHAR2(100 BYTE) NOT NULL,
  address        VARCHAR2(100 BYTE) NOT NULL,
  grade          VARCHAR2(100 BYTE) NOT NULL,
  dept      VARCHAR2(20 BYTE) NOT NULL

The data in the table for the employee looks like this:

EMP_ID      EMP_NAME          ADDRESS           GRADE       DEPT
101         arun              Gorakhpur         106         IT
102         varun             Lucknow           104         HR
103         tarun             Mumbai            105         IT

Now, let's add one more test page named "TestPage2" under "ChildTestSuite" so you can see the page below after addition of Test page:

Image title

Now, edit the page and add the test data as below:

contents -R2 -g -p -f -h
!4 !style_red[Sql-Query-Test]
-!| Query:exper.test.fitnesse.DataSqlValidationTest| select * from employee|
| 101| arun| Gorakhpur | 106| IT |
| 102| vrun| delhi| 104| HR|

Now save it. You will see the page below:

Image title

We need the below fixture (Java code) to test this.


package exper.test.fitnesse;

import java.util.List;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;

public class DataSqlValidationTest {
  private String sql;

  public DataSqlValidationTest(String sql){
    this.sql = sql;

  public List<List<List<String>>> query(){

    List<List<List<String>>> list_2 = new ArrayList<List<List<String>>>();
    try {
      ResultSet rs = SqlUtil.executeQuery(this.sql);
      ResultSetMetaData rsMetaData = rs.getMetaData();
      int colCount = rsMetaData.getColumnCount();

      List<List<String>> list_1 = null;

        list_1 = new ArrayList<List<String>>(2000);
        for(int index = 1; index <= colCount; index ++){
          String res = SqlUtil.getStringFromSqlResultset(rsMetaData.getColumnTypeName(index), 
                                                         rsMetaData.getColumnName(index), rs);
          list_1.add(ListUtility.list(rsMetaData.getColumnName(index), res));
    } catch (SQLException e) {
    return list_2;

Below are the supporting classes for the above fixture.


package exper.test.fitnesse;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class SqlUtil {
  private static String VARCHAR = "VARCHAR";
  private static String VARCHAR2 = "VARCHAR2";
  private static String CHAR = "CHAR";
  private static String LONGVARCHAR= "LONGVARCHAR";
  private static String NUMERIC=  "NUMERIC";
  private static String SMALLINT= "SMALLINT";
  private static String INTEGER= "INTEGER";
  private static String NUMBER = "NUMBER";

  private static Connection conn = null;

  public synchronized static Connection getConnection() {                

    if(null == conn){
      try {
        DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
        conn = DriverManager.getConnection("jdbc:oracle:thin:@hostName:Port:DB", "userName", "pwd");          
      } catch (SQLException e) {
        System.out.println("SQL execption occurs ------"+ e.getCause());
    return conn;

  public static ResultSet executeQuery(String sqlStatement) {

    Connection connnection = getConnection();
    try {
      PreparedStatement preparedStatement = connnection.prepareStatement(sqlStatement);
      ResultSet rs = preparedStatement.executeQuery();
      return rs;
    } catch (SQLException e) {
      return null;

  public static String getStringFromSqlResultset(String colType, String colName, ResultSet rs) throws SQLException{

    String value = null;

    if(CHAR.equals(colType) || VARCHAR.equals(colType) || VARCHAR2.equals(colType)
       || LONGVARCHAR.equals(colType)){
      value = rs.getString(colName);
    else if(NUMBER.equals(colType) || INTEGER.equals(colType)){
      value = String.valueOf(rs.getInt(colName));
    else if(NUMERIC.equals(colType)){
      value = String.valueOf(rs.getBigDecimal(colName));
    else if(SMALLINT.equals(colType)){
      value = String.valueOf(rs.getShort(colName));
    return value;


package exper.test.fitnesse;

import java.util.ArrayList;
import java.util.List;

public class ListUtility {

  public static <T> List<T> list(T... objects) {
    List<T> list = new ArrayList<T>();   
    for (T object : objects) {     
    return list; 

  public static List<String> list(String... strings) {
    List<String> list = new ArrayList<String>(); 
    for (String string : strings) {   
    return list;

We added "oracle-" in the path, as we have added experiment.jar.

Now, run the test using the Test button and you will see the below page:

Image title

The output is self-explanatory. Green indicates that the test passed and red indicates that test failed. The last row is showing that the query result has a third row that is not present on the Wiki page, so it's been marked as "surplus."

Discover how TDM Is Essential To Achieving Quality At Speed For Agile, DevOps, And Continuous Delivery. Brought to you in partnership with CA Technologies

fitnesse ,user acceptance testing ,agile ,sql

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}