A DBConnection class to connect to any database

DBConnection

package org.powertester.yourprojectname.database;

import com.typesafe.config.Config;
import lombok.extern.slf4j.Slf4j;
import org.powertester.yourprojectname.config.TestEnvironment;

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

/**
 * For this to work:
 * 1) You would need the maven dependency of the database type driver you need to connect (MySQL, PostGre, MongoDb).
 * 2) Configuration to be stored/fetched from a config system (I used typesafe here but you can use a config system of your choice).
 * 3) A logger (I used Slf4j), but you can use whatever you like. 
 * Another generic rule to follow: The class calling the connection must close the connection.
 * (object -> dbConnection.getConnection() will give connection to close).
 */
@Slf4j
public class DBConnection {
    private Connection connection;

    private static final Config config = TestEnvironment.getConfig();
    private static final String DB_CONNECTION_STRING = config.getString("DB_CONNECTION_STRING");
    private static final String DB_USER = config.getString("DB_USER");
    private static final String DB_PASSWORD = config.getString("DB_PASSWORD");

    public DBConnection(String database) {
        setConnection(database);
    }

    private void setConnection(String database) {
        try {
            connection = DriverManager.getConnection(DB_CONNECTION_STRING + database, DB_USER, DB_PASSWORD);
            if (connection != null) {
                log.debug("DBConnection Successful!");
            }
        } catch (SQLException e) {
            throw new IllegalStateException("DBConnection failed!", e);
        }
    }

    public Connection getConnection(){
        return connection;
    }
    
    public ResultSet getResultSet(String sql) {
        try {
            return connection.prepareStatement(sql).executeQuery();
        } catch (SQLException e) {
            throw new IllegalStateException("executeQuery failed!", e);
        }
    }

    public void setResultSet(String sql) {
        try {
            connection.prepareStatement(sql).executeUpdate();
        } catch (SQLException e) {
            throw new IllegalStateException("executeUpdate failed!", e);
        }
    }
}

Below are a few example usages.
Example 1: You are having a result to work with : Select query.

public class AssertRegistration {
    public static void customersTableContain(Response response, RegistrationBody registrationBody) {
        String registrationId = ResponseUtils.getId(response);
        if (isNotEmpty(registrationId)) {
            DBConnection dbConnection = new DBConnection("customers-database-name");
            try {
                String sqlSelect = String.format("SELECT * FROM customers-table where id='%s';", registrationId);
                ResultSet resultSet = dbConnection.getResultSet(sqlSelect);
                if (resultSet.next()) {
                    do {
                        assertAll("Name Details"
                                , () -> assertEquals(registrationBody.getFirstName(), resultSet.getString("first_name"), "first_name:")
                                , () -> assertEquals(registrationBody.getLastName(), resultSet.getString("last_name"), "last_name:")
                                , () -> assertEquals(registrationBody.getEmail().toLowerCase(), resultSet.getString("email"), "email")
                                , () -> assertEquals(registrationBody.getPhoneNumber(), resultSet.getString("phone"), "phone")
                        );
                    } while (resultSet.next());
                } else {
                    fail("Expected to find some records here. But found no records to assert! \n");
                }
            } catch (SQLException e) {
                throw new IllegalStateException("SQL Exception occurred!", e);
            } finally {
                DbUtils.closeQuietly(dbConnection.getConnection());
            }
        }else{
            fail("Registration id is empty!");
        }
    }
}

Example 02: You are executing a query (Insert, Update or Delete) and have no results to work with.

package org.powertester.tables.asserttables;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.dbutils.DbUtils;
import org.powertester.database.DBConnection;

import java.sql.ResultSet;
import java.sql.SQLException;

import static org.junit.jupiter.api.Assertions.assertTrue;
import static org.junit.jupiter.api.Assertions.fail;

@Slf4j
public class SelectedTable {
    public static void assertThatThereAreRecordsInResultSet(String database, String sql) {
        DBConnection dbConnection = new DBConnection(database);
        try {
            ResultSet resultSet = dbConnection.getResultSet(sql);
            if (resultSet.next()) {
                assertTrue(true);
            } else {
                fail("Expected to find some records here. But found no records to assert! \n");
            }
        } catch (SQLException e) {
            throw new IllegalStateException("SQL Exception occurred!", e);
        } finally {
            DbUtils.closeQuietly(dbConnection.getConnection());
        }
    }

    public static void assertThatThereAreNoRecordsInResultSet(String database, String sql) {
        DBConnection dbConnection = new DBConnection(database);
        try {
            ResultSet resultSet = dbConnection.getResultSet(sql);
            if (resultSet.next()) {
                fail("Not expecting any records in this case. Found one or more! \n");
            } else {
                assertTrue(true);
            }
        } catch (SQLException e) {
            throw new IllegalStateException("SQL Exception occurred!", e);
        } finally {
            DbUtils.closeQuietly(dbConnection.getConnection());
        }
    }

    public static void deleteRecord(String databaseName, String sqlSelect, String sqlDelete){
        DBConnection dbConnection = new DBConnection(databaseName);
        try {
            // Assert that there is a record to delete
            SelectedTable.assertThatThereAreRecordsInResultSet(databaseName, sqlSelect);

            // Delete record.
            dbConnection.setResultSet(sqlDelete);

            // Assert that record was deleted.
            SelectedTable.assertThatThereAreNoRecordsInResultSet(databaseName, sqlSelect);
        } finally {
            DbUtils.closeQuietly(dbConnection.getConnection());
        }
    }

    public static String getFieldValueFromTable(String database, String sql, String fieldName) {
        DBConnection dbConnection = new DBConnection(database);
        try {
            ResultSet resultSet = dbConnection.getResultSet(sql);
            // Check if there are any records to fetch, if not, fail (Since we expect to find records here.)
            String fieldValue = "";
            if (resultSet.next()) {
                log.info("Getting field value from Table");
                int i = 0;
                do {
                    log.debug("record: {}", i);
                    fieldValue = resultSet.getString(fieldName);
                    i++;
                } while (resultSet.next());
            } else {
                fail("Expected to find some records here. But found no records to assert! \n");
            }
            return fieldValue;
        } catch (SQLException e) {
            throw new IllegalStateException("SQL Exception occurred!", e);
        } finally {
            DbUtils.closeQuietly(dbConnection.getConnection());
        }
    }
}