DBTestCompare is a Java-based database testing framework designed to validate SQL query results across heterogeneous database platforms. It reads test definitions from XML configuration files, executes SQL queries against configured datasources, and compares the results using one of several comparison strategies. The framework is built on TestNG and supports automated testing workflows through CI/CD pipeline integration.
Scope: This document provides an architectural overview of DBTestCompare, its core components, comparison strategies, and technology stack. For detailed configuration instructions, see Configuration. For CI/CD pipeline details, see CI/CD Integration. For hands-on examples, see Examples and Use Cases.
DBTestCompare solves the challenge of validating database query behavior across different database engines and environments. The framework is particularly useful for:
Key capabilities:
| Capability | Description |
|---|---|
| Platform Independence | Runs on Windows and Linux with Java 8+ runtime |
| Multi-Database Support | Supports all JDBC-compliant databases; tested with SQL Server, MySQL, PostgreSQL, Teradata, Snowflake |
| Multiple Comparison Modes | Five comparison strategies: MINUS, FETCH, FILE, KEY, NMB_OF_RESULTS |
| Data-Driven Testing | XML-based test definitions enable test creation without code compilation |
| Parallel Execution | Configurable thread pools for concurrent test execution |
| Connection Pooling | c3p0 connection pooling for efficient database resource management |
| CI/CD Integration | TestNG framework enables integration with all major CI/CD tools |
| TeamCity Support | Real-time test progress reporting via TeamCity service messages |
Sources: README.md1-60 pom.xml1-115
DBTestCompare follows a layered architecture with distinct separation between test orchestration, comparison logic, and database connectivity.
Sources: pom.xml37-43 README.md7-55
The framework's execution begins with the RunTests main class and flows through TestNG's factory pattern to create data-driven test instances.
| Component | Type | Purpose |
|---|---|---|
uk.co.objectivity.test.db.RunTests | Main Class | Entry point that initializes TestNG and starts test execution |
DBTestCompare | Test Class | TestNG test class with @Test annotated methods that execute comparisons |
TestDataProvider | Factory Class | Uses @Factory pattern to create test instances from XML definitions |
TestParams | Data Class | Wraps CmpSqlResultsConfig and CmpSqlResultsTest for each test instance |
CmpSqlResultsConfig | Config Model | JAXB-mapped XML configuration for datasources and connection pools |
CmpSqlResultsTest | Test Model | JAXB-mapped XML test definition containing SQL queries and comparison settings |
Execution flow:
Sources: pom.xml37 README.md21-34
DBTestCompare implements the Strategy pattern through the CompareMode enumeration, which determines which Comparator implementation to use for each test. Each strategy is optimized for different use cases.
| CompareMode | Comparator Class | Use Case | Performance |
|---|---|---|---|
MINUS | MinusComparator | Same-database comparison using SQL set operators | Fastest - database-native |
FETCH | FetchComparator | Cross-database row-by-row comparison | Moderate - incremental fetch |
FILE | FileComparator | Compare query results to CSV files | Moderate - file I/O dependent |
KEY | KeyComparator | Validate specific records by key lookup | Fast - targeted queries |
NMB_OF_RESULTS | Direct assertion | Validate row count only | Fastest - single COUNT query |
MinusComparator leverages database-native SQL set operators (MINUS for Oracle/Teradata, EXCEPT for SQL Server/PostgreSQL) to identify differences between two queries executed on the same database. This is the most efficient strategy for same-platform comparisons.
FetchComparator implements row-by-row comparison with support for floating-point tolerance (configurable delta parameter). It fetches results incrementally using configurable fetchSize and chunk parameters to avoid memory exhaustion on large result sets. This strategy enables cross-database comparisons.
FileComparator validates SQL query results against expected data stored in CSV files. It supports custom separators, configurable starting row, and can generate CSV files from query results for future validation.
KeyComparator performs targeted validation of specific records by constructing SQL queries with WHERE clauses based on key columns and expected values from CSV files.
Sources: README.md39-58
DBTestCompare is built on industry-standard Java frameworks and libraries optimized for database testing and CI/CD integration.
| Technology | Version | Purpose |
|---|---|---|
| Java | 8+ | Runtime platform (JRE or JDK) |
| TestNG | 7.5.1 | Test framework providing @Factory, @Test, and reporting capabilities |
| Maven | - | Build tool and dependency management |
| JAXB | 2.2.7 | XML binding for configuration and test definition parsing |
| c3p0 | 0.10.1 | JDBC connection pooling library (ComboPooledDataSource) |
| Log4j 2 | 2.25.0/2.25.3 | Logging framework with configurable verbosity |
| Apache POI | 4.1.1 | Excel file generation for test reports |
| Apache Commons Lang | 3.18.0 | Utility classes for string and object manipulation |
| SIROCCO | 1.0 | ASCII table formatting for console output |
| JDBC Drivers | Various | Database connectivity (SQL Server, MySQL, PostgreSQL, Teradata, Snowflake) |
Build output: The Maven Assembly plugin packages the application as DBTestCompare-1.0-SNAPSHOT-jar-with-dependencies.jar with the manifest configured to set uk.co.objectivity.test.db.RunTests as the main class and reference external JDBC drivers in the classpath.
Sources: pom.xml11-114 README.md96-103
DBTestCompare follows a data-driven testing model where test behavior is controlled entirely through XML configuration files rather than code changes.
The application can be executed in multiple ways depending on JDBC driver placement:
Using manifest classpath (JDBC drivers in jdbc_drivers/ directory):
Explicit classpath (Windows):
Explicit classpath (Linux):
| File Path | Purpose | Format |
|---|---|---|
cmpSqlResults-config.xml | Datasource definitions with JDBC URLs, credentials, and connection pool settings | XML (JAXB-mapped) |
test-definitions/**/*.xml | Individual test definitions specifying SQL queries and comparison modes | XML (JAXB-mapped) |
test-definitions/**/*.csv | Reference data for FILE and KEY comparison modes | CSV |
jdbc_drivers/*.jar | JDBC driver JARs for database connectivity | Binary JAR |
Runtime behavior can be customized using Java system properties:
| Parameter | Default | Description |
|---|---|---|
-DtestsDir=<path> | test-definitions | Directory containing XML test definitions |
-DteamcityLogsEnabled=true | false | Enable TeamCity service message output |
-DfilterInclude=<paths> | - | Comma-separated paths to include (e.g., a.b,g.z.f) |
-DfilterExclude=<paths> | - | Comma-separated paths to exclude (e.g., a.b.test) |
Example:
Sources: README.md17-94 pom.xml37-49
DBTestCompare uses c3p0 connection pooling to efficiently manage database connections across parallel test execution. Each datasource configured in cmpSqlResults-config.xml maintains its own connection pool.
Connection lifecycle: Connections are created on-demand up to the configured pool size and reused across test instances. All connections are closed only after the TestNG suite completes, optimizing execution performance for test suites with many test cases.
Sources: pom.xml86-90 README.md38-77
DBTestCompare generates multiple output formats for different consumption needs:
| Output Type | Location | Purpose |
|---|---|---|
| TestNG HTML Report | test-output/index.html | Standard TestNG test results with pass/fail status and stack traces |
| Console ASCII Tables | stdout | Real-time comparison results formatted as tables (TRACE log level) |
| TeamCity Messages | stdout | Service messages for CI integration when -DteamcityLogsEnabled=true |
| Excel Reports | Configurable path | Optional detailed comparison results in Excel format |
| CSV Files | Test definition directory | Query results can be saved to CSV when fileOutputOn=true |
The TestResults class captures comparison metadata including executed queries, row counts, column names, and differing rows. This data flows into all reporting channels.
Sources: README.md49-59 pom.xml102-107
Refresh this wiki
This wiki was recently refreshed. Please wait 5 days to refresh again.