Skip to main content

JDBC Fixture

I created this class so I can run basic database commands from within Fitnesse. Dropping SQL into a Fitnesse page not recommended but it can still be a useful tool for a few reasons:
  1. It is reusable so you can drop it into lots of tests without making new classes all the time
  2. It's a good bridging tool in trying to get developers using fitnesse who are not used to creating an abstraction layer for their tests
  3. It's handy to combine with RestFixture when you need to adjust or validate the data but cannot do it through a rest command
But again it's not recommended. Better to create a new fixture with a function like
public void createNewUser(String name, String password)


Here is what running the JDBCFixture looks like in the fitnesse
!path lib/*.jar

!|import                  |
|com.warmage.util.fixtures|

!|script|DBQueryFixture|mydatabase                                           |user      |pass         |
|check  |statement     |UPDATE users SET city='New York' WHERE name='Big Joe'|1                       |

!|script|DBQueryFixture|mydatabase                                           |user      |pass         |
|query  |SELECT * FROM users WHERE name='Big Joe'                                                     |
|show   |get row       |0                                                    |and column|name         |
|check  |get row       |0                                                    |and column|city|New York|
So it's pretty straight forward. If you're already familiar with script tables you'll know how you can drop switch between check and show for when you just want to see the result vs when you want to validate it. There are probably lots of statements you'll want to run without looking at the number returned (number of rows modified or -1 if there is an error). My fixuture is hard coded to use a local postgres database (and the postgres jdbc jar is in my lib folder). But it's easy enough to change that for your own version
package com.warmage.util.fixtures;

import java.util.ArrayList;
import java.util.List;
import java.sql.*;

import static util.ListUtility.list;

public class DBQueryFixture {

    // constants
    final private String baseUrl = "jdbc:postgresql://localhost:5432/";

    /**
     * Constructor for script table
     *
     * @param database - Database name
     * @param dbUser   - User to use
     * @param dbPassword - Password to use
     */
    public DBQueryFixture(String database, String dbUser, String dbPassword) {
        try {
            jdbcConnection = DriverManager.getConnection(baseUrl + database, dbUser, dbPassword);
        } catch (SQLException e) {
            System.out.println("Error connecting to the database");
        }
    }

    public int statement(String query) {
            try {
                Statement st = jdbcConnection.createStatement();
                return st.executeUpdate(query);
            }
            catch (SQLException e){
                System.out.println("Error executing statement");
            }
            return -1;
    }

    public void query(String query) {
        try {
            Statement st = jdbcConnection.createStatement(
                  ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            results = st.executeQuery(query);
        }
        catch (SQLException e){
            System.out.println("Error executing query");
        }
    }

    public String getCell(String column) {
        if (results != null) {
            try {
                return results.getString(column);
            }
            catch (SQLException e){
               System.out.println("Error fetching cell for column " + column);
            }
        }
        return "null";
    }

    public String getRowAndColumn(int row, String column) {
        if (results != null && row >= 0) {
            try {
                // Iterate to the right row
                results.beforeFirst();
                boolean resultsValid = true;
                while(resultsValid && 0 <= row) {
                    resultsValid = results.next();
                    row--;
                }

                if (resultsValid) {
                    return getCell(column);
                } else {
                    System.out.println("Error row count too big");
                }

            }
            catch (SQLException e){
                System.out.println("Error moving through rows");
            }
        }
        return "null";
    }

    private Connection jdbcConnection;
    private ResultSet results = null;
}

Popular posts from this blog

When Agile is not common sense

It's been about 8 years since I got my Agile training from James Grenning and started an Agile Pilot with my team. Right from the start I was super keen and got people practising TDD and Pair Programming even though I didn't really know if that effort would pay off. I just knew we had lots of problems with the old way so I wanted to embrace the new.

So I'd run my own little sessions with the broader team in Sydney sharing Agile concepts and ideas. Giving us a chance to discuss things and keep learning long after James had left. But often I would present new ideas and a few people in the group would respond "oh of course, that's just common sense". Initially I thought it was great as they were embracing these ideas as valuable even though it was not what we were doing before. But after a while I started to get frustrated because they we're not really listening. If some small part of a concept fit nicely with their "common sense" then they took th…

Setting up Fitnesse on Ubuntu in 7 steps

Some pretty basic steps but just to make sure it's here for everyone to see. Setting up fitnesse and running the jar is easy enough. Just go to http://fitnesse.org/ and get started and do it on your desktop just to see it in action. But for me that wasn't good enough I wanted it to run as service on ubuntu.

I stole a few tricks from how ubuntu runs jenkins and setup fitnesse a similar way.

1. Create a user and group for fitnesse (optional)
I didn't do this because I wanted tomcat, jenkins and fitnesse all running as the same user. Call it laziness to avoid any permissions classing but it doesn't change the process that you need to create or choose what user you're going to make it run as. Don't make it run as your user or root!

2. Download the jar file and place it in /usr/share/fitnesse
Make the folder too of course. It can belong to root as long as the fitnesse user has read access

3. Create the folder to run in at /var/lib/fitnesse
Fitnesse user needs write…

RestFixture

So most of the tests I'm writing now in Fitnesse are using RestFixture. Being able to do all this black box style testing has helped me get a lot of tests up and running without having to change the existing code base. Now I've taken a step future with my own little fork so I can use scenarios and build nice BDD style scripts. But first I want to give me own quick guide to using RestFixture

Step 1: Installing
You can dive straight in by grabbing the latest jar files for RestFixture here https://github.com/smartrics/RestFixture/downloads
If you know what you're doing can get the nodep version to work nicely along side other libraries you may be including in Fitnesse. But I grabbed the 'full' version and unzipped it into a RestFixture folder alongside my FitNesseRoot folder.
Step 2: Write your first test
I took advantage of the built in Fitnesse api as a basic test and wrote a page called RestFixture with the following contents
!define TEST_SYSTEM {slim} !path RestFix…