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