How to Implement Database Testing with DatabaseHelpers in automationframeworkselenium

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, 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 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.

<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 (lines 206-235) demonstrates this exact pattern.

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:

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

Then load these values before establishing the connection:

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+):

@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 provides the static getMySQLConnection() method for establishing MySQL JDBC connections on port 3306.
• The required MySQL drivers are already declared in 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 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.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}