Writing Step Definitions to Query the Database

Prerequisites

1. Set up your db.properties file in the src/main/resources with the parameters for accessing the database. It's a simple text file containing the following information.

databaseName=name of the database
user=your username
password=your password

Creating Java Classes

Query Class

1. Create a Java class for storing queries as strings if your package doesn't have one already. To maintain naming conventions, give it a name in the form {PackageName}Queries.java. This example will use ExampleQueries.java.

2. Put the sql query in a public static string variable.

public static String QUERY_STRING = "This is your sql query without parameters. " + 
                                    "You can concatenate the string to break it up just like a normal string;";

OPTIONAL: Handling Parameters

3. Indicate the variables in the query by adding question marks to where the parameters should be passed.

public static String QUERY_STRING = "This is your sql query without parameters. " + 
                                    "You can concatenate the string to break it up just like a normal string. " +
                                    "Pass a variable like this, field = ?;";

Execute Query Class

1. Create a Java class for executing the query. To maintain naming conventions, give it a name in the form {QueryName}Query.java. The class needs three things to function a Connection global variable, a constructor, and an overridden execute method that contains the PreparedStatement and returns a ResultSet. You can copy and paste the following code after the package name to handle all three of these requirements.

import database.QueryOperation;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class QueryStringQuery implements QueryOperation {
    // global variables
    Connection connection;

    // constructor
    public QueryStringQuery(Connection connection) {
        this.connection = connection;
    }

    // overridden execute method
    @Override
    public ResultSet execute() {
        try {
            PreparedStatement statement = this.connection.prepareStatement(ExampleQueries.QUERY_STRING);
            return statement.executeQuery();
        } catch (SQLException e) {
            System.out.println("Error executing statement");
        }
        return null;
    }
}

OPTIONAL: Handling Parameters

2. Add the parameters to the global variables as strings.

String parameterValue;

3. Add the parameters to the constructor.

public QueryStringQuery(Connection connection, String parameterValue) {
    this.connection = connection;
    this.parameterValue = parameterValue;
}

4. Add the parameters to the prepared statement. This can be done using the setString method on the prepared statement variable. It takes two inputs, an integer representing which variable in the query you're replacing (useful if there are multiple parameters) and the parameter value as a string.

statement.setString(1, parameterValue);

The full class should look like this

import database.QueryOperation;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class QueryStringQuery implements QueryOperation {
    // global variables
    Connection connection;
    String parameterName;

    // constructor
    public QueryStringQuery(Connection connection, String parameterValue) {
        this.connection = connection;
        this.parameterValue = parameterValue;
    }

    // overridden execute method
    @Override
    public ResultSet execute() {
        try {
            PreparedStatement statement = this.connection.prepareStatement(ExampleQueries.QUERY_STRING);
            statement.setString(1, this.parameterValue);
            return statement.executeQuery();
        } catch (SQLException e) {
            System.out.println("Error executing statement");
        }
        return null;
    }
}

Building the Step Definition

1. Write the step definition in the gherkin and create the Java function in the appropriate class file.

@And("{actor} runs the query without parameters")
Public void runsTheQueryWithoutParameters(Actor actor) {}

2. Create a DBHelper object

DBHelper dbHelper = new DBHelper();

3. Add the execute query command method and store it in a ResultSet variable

ResultSet resultSet = dbHelper.executeQueryCommand(new QueryStringQuery(dbHelper.getConnection()));

4. Paste the following code after getting the ResultSet to check if the data was retrieved

actor.wasAbleTo(Ensure.that(resultSet.next()).isTrue().orElseThrow(new DatabaseConnectionException("No records returned from the database")));

5. Use the ResultSet method getString to save the data required for the test case in global string variables. Pass the name of the column as a parameter to the method.

variable = resultSet.getString("ColumnName");

6. OPTIONAL: log the data to the console using this line

logger.info("Value: " + variable);

Be sure to use this line at the start of the class to create the logger global variable

private static final Logger logger = (Logger) LoggerFactory.getLogger(ExampleStepDefinitions.class);

7. Close the db connection at the end of the method

dbHelper.closeConnection();

8. Your step definition without parameters should look like this

@And("{actor} runs the query without parameters")
public void runsTheQueryWithoutParameters(Actor actor) throws DatabaseConnectionException, SQLException {
    DBHelper dbHelper = new DBHelper();
    ResultSet resultSet = dbHelper.executeQueryCommand(new QueryStringQuery(dbHelper.getConnection()));
    actor.wasAbleTo(Ensure.that(resultSet.next()).isTrue().orElseThrow(new DatabaseConnectionException("No records returned from the database")));
    variable = resultSet.getString("ColumnName");
    logger.info("Value: " + variable);
    dbHelper.closeConnection();
}

OPTIONAL: Handling Parameters

9. Add whatever parameters you need to the cucumber tag and the function signature.

@And("{actor} runs the query with parameter {word}")
Public void runsTheQueryWithoutParameters(Actor actor, String parameterValue) {}

10. Add the parameters to the Query class builder in the execute query command method

ResultSet resultSet = dbHelper.executeQueryCommand(new QueryStringQuery(dbHelper.getConnection(), parameterValue));

11. Your step definition with parameters should look like this

@And("{actor} runs the query with parameter {word}")
Public void runsTheQueryWithoutParameters(Actor actor, String parameterValue) throws DatabaseConnectionException, SQLException {
    DBHelper dbHelper = new DBHelper();
    ResultSet resultSet = dbHelper.executeQueryCommand(new QueryStringQuery(dbHelper.getConnection(), parameterValue));
    actor.wasAbleTo(Ensure.that(resultSet.next()).isTrue().orElseThrow(new DatabaseConnectionException("No records returned from the database")));
    variable = resultSet.getString("ColumnName");
    logger.info("Value: " + variable);
    dbHelper.closeConnection();
}