# How to Implement Database Testing with DatabaseHelpers in automationframeworkselenium

> Implement database testing in automationframeworkselenium using DatabaseHelpers. Connect to MySQL, run SQL queries, and validate data in TestNG tests for robust automation.

- Repository: [Anh Tester/automationframeworkselenium](https://github.com/anhtester/automationframeworkselenium)
- Tags: how-to-guide
- Published: 2026-02-24

---

**Use the `DatabaseHelpers.getMySQLConnection()` static method to establish a JDBC connection, execute SQL queries through standard Java SQL statements, and validate data directly in your TestNG test methods.**

The **automationframeworkselenium** repository by anhtester provides a lightweight, helper-based approach to database testing that integrates cleanly with existing Selenium TestNG suites. By leveraging the `DatabaseHelpers` utility class, testers can connect to MySQL databases without managing complex connection pools or driver configuration manually. This guide walks through the exact implementation patterns found in the source code, from Maven dependencies to resource cleanup.

## Understanding the DatabaseHelpers Architecture

The framework follows a *helper-only* design pattern that isolates database connectivity logic from test business logic. This architecture keeps test classes focused on assertions while the helper handles JDBC boilerplate.

### Core Connection Method

In [`src/main/java/com/anhtester/helpers/DatabaseHelpers.java`](https://github.com/anhtester/automationframeworkselenium/blob/main/src/main/java/com/anhtester/helpers/DatabaseHelpers.java), the `getMySQLConnection()` static method constructs a JDBC URL using the pattern `jdbc:mysql://<host>:3306/<db>` and returns a standard `java.sql.Connection` object. The method signature accepts four String parameters: host, database name, username, and password.

Because the method is static, you can invoke it directly without instantiating the helper class, making it ideal for use within `@Test` annotated methods.

### MySQL Driver Dependencies

The project’s [`pom.xml`](https://github.com/anhtester/automationframeworkselenium/blob/main/pom.xml) already includes the necessary MySQL drivers on the classpath. The dependencies `mysql-connector-j` and `mysql-connector-java` ensure the JDBC driver is available during test execution without additional setup.

```xml
<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>${mysql-connector-j.version}</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>${mysql-connector-java.version}</version>
</dependency>

```

## Implementing Database Testing with DatabaseHelpers

To implement database testing with DatabaseHelpers, create a connection, execute your query, iterate through the ResultSet, and perform assertions or logging. The commented example in [`src/test/java/com/anhtester/projects/crm/testcases/TestSimpleCode.java`](https://github.com/anhtester/automationframeworkselenium/blob/main/src/test/java/com/anhtester/projects/crm/testcases/TestSimpleCode.java) (lines 206-235) demonstrates this exact pattern.

```java
import com.anhtester.helpers.DatabaseHelpers;
import com.anhtester.keywords.WebUI;
import org.testng.annotations.Test;
import java.sql.*;

public class DatabaseValidationTest {

    @Test
    public void verifyCompanyData() throws SQLException {
        // 1. Establish connection using DatabaseHelpers
        Connection conn = DatabaseHelpers.getMySQLConnection(
                "sql6.freesqldatabase.com",
                "sql6464696",
                "sql6464696",
                "LIAGIkgd44");

        // 2. Create statement object
        Statement stmt = conn.createStatement();

        // 3. Execute SQL query
        String sql = "SELECT * FROM `company`";
        ResultSet rs = stmt.executeQuery(sql);

        // 4. Process results
        while (rs.next()) {
            int id = rs.getInt("ID");
            String companyId = rs.getString("COMPANY_ID");
            String name = rs.getString("COMPANY_NAME");
            String city = rs.getString("COMPANY_CITY");

            WebUI.logConsole("--------------------");
            WebUI.logConsole("ID: " + id);
            WebUI.logConsole("COMPANY_ID: " + companyId);
            WebUI.logConsole("NAME: " + name);
            WebUI.logConsole("CITY: " + city);
        }

        // 5. Close resources to prevent memory leaks
        rs.close();
        stmt.close();
        conn.close();
    }
}

```

## Externalizing Database Credentials

Hard-coding credentials in test methods creates security risks and maintenance overhead. The framework supports externalization through `PropertiesHelpers`, allowing you to store connection details in property files.

Create a properties file at `src/test/resources/config/db.properties`:

```properties
db.host=sql6.freesqldatabase.com
db.name=sql6464696
db.user=sql6464696
db.password=LIAGIkgd44

```

Then load these values before establishing the connection:

```java
import com.anhtester.utils.PropertiesHelpers;

// Load all properties files
PropertiesHelpers.loadAllFiles();

// Retrieve values
String host = PropertiesHelpers.getValue("db.host");
String db = PropertiesHelpers.getValue("db.name");
String user = PropertiesHelpers.getValue("db.user");
String pass = PropertiesHelpers.getValue("db.password");

// Use with DatabaseHelpers
Connection conn = DatabaseHelpers.getMySQLConnection(host, db, user, pass);

```

## Best Practices for JDBC Resource Management

When implementing database testing with DatabaseHelpers, proper resource management prevents connection leaks that can exhaust database connection pools. Always close `ResultSet`, `Statement`, and `Connection` objects in a finally block or use try-with-resources.

**Try-with-resources approach** (Java 7+):

```java
@Test
public void verifyDataWithAutoClose() throws SQLException {
    try (Connection conn = DatabaseHelpers.getMySQLConnection(host, db, user, pass);
         Statement stmt = conn.createStatement();
         ResultSet rs = stmt.executeQuery("SELECT * FROM company")) {
        
        while (rs.next()) {
            // Assertions here
        }
    } // Auto-closes all resources
}

```

**Manual cleanup approach** (as shown in TestSimpleCode.java):
Explicitly call `rs.close()`, `stmt.close()`, and `conn.close()` after processing, preferably in a `finally` block to ensure execution even if assertions fail.

## Summary

- **DatabaseHelpers** in [`src/main/java/com/anhtester/helpers/DatabaseHelpers.java`](https://github.com/anhtester/automationframeworkselenium/blob/main/src/main/java/com/anhtester/helpers/DatabaseHelpers.java) provides the static `getMySQLConnection()` method for establishing MySQL JDBC connections on port 3306.
- The required MySQL drivers are already declared in [`pom.xml`](https://github.com/anhtester/automationframeworkselenium/blob/main/pom.xml) as `mysql-connector-j` and `mysql-connector-java`.
- Implement test methods that obtain a Connection, create a Statement, execute queries, and iterate ResultSet objects to validate database state.
- Use `PropertiesHelpers` to externalize credentials from test code for improved security.
- Always close JDBC resources (ResultSet, Statement, Connection) to prevent memory leaks and connection pool exhaustion.

## Frequently Asked Questions

### What dependencies are required to use DatabaseHelpers?

The [`pom.xml`](https://github.com/anhtester/automationframeworkselenium/blob/main/pom.xml) in automationframeworkselenium already includes `mysql-connector-j` and `mysql-connector-java`. These dependencies provide the MySQL JDBC driver required by `DatabaseHelpers.getMySQLConnection()` to establish database connections. No additional Maven configuration is needed.

### How do I handle SQL exceptions when using DatabaseHelpers?

The `getMySQLConnection()` method throws `SQLException`, which you can either propagate to TestNG (causing test failure) or catch explicitly. For test validation, letting TestNG handle the exception is usually preferred, as it marks the test as failed with the full stack trace. For cleanup operations, use try-catch-finally blocks to ensure resources close properly.

### Can I use DatabaseHelpers with databases other than MySQL?

Currently, `DatabaseHelpers` is specifically designed for MySQL connections using the `jdbc:mysql://` URL pattern and port 3306. To connect to PostgreSQL, SQL Server, or Oracle databases, you would need to modify the helper class to accept different JDBC URL formats or create additional helper methods for those specific drivers.

### Where should I close the database connections in my tests?

Close connections, statements, and result sets in the cleanup phase of your test method, ideally in a `finally` block or using Java's try-with-resources syntax. In the automationframeworkselenium examples, resources are closed manually after the `while(rs.next())` loop completes. Failing to close connections can lead to "too many connections" errors during large test suite executions.