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;
}

Comments

Post a Comment

Popular posts from this blog

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} !

A brief introduction

And then I'll get into my first rant. I'm some guy who thinks about stuff. Stuff you probably don't care about but I'm going to tell you anyway! Now I don't think I'm going to cure cancer or create world peace but it'd be nice if I could make the world a little better. Stuff I'd like to talk about will focus on: Games I've always been a game player and am fascinated in how one designs a game. Yes it's mostly computer games these days and even then it's mostly consumed by World of Warcraft . Shut-up you in the back! We all have our vices and I'll avoid sniggering at your hentai collection if you leave my level 80 mage alone. Programming Only 2 years ago I was introduced to this weird concept of Test Driven Developmen t and the broader concept of Agile . Programming has been part of my life since I was eight. When I haven't been doing it professionally I've found myself doing it more in my free time. So it's nice that after 20+