Automated Validation with JDBC

import java.sql.*;

public class DatabaseHelper {
    private static final String URL = "jdbc:mysql://localhost:3306/banking_app";
    private static final String USER = "test_user";
    private static final String PASSWORD = "test_password";

    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, USER, PASSWORD);
    }

    // Run a query and return the result count
    public static int getRowCount(String query) throws SQLException {
        try (Connection conn = getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(query)) {
            rs.next();
            return rs.getInt(1);
        }
    }

    // Run a query and return a single string value
    public static String getSingleValue(String query, String column)
            throws SQLException {
        try (Connection conn = getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(query)) {
            if (rs.next()) {
                return rs.getString(column);
            }
            return null;
        }
    }
}

import org.testng.Assert;
import org.testng.annotations.Test;
import java.sql.*;

public class DataValidationTest {

    @Test
    public void testNoNullEmails() throws SQLException {
        String query = "SELECT COUNT(*) FROM users WHERE email IS NULL";
        int nullCount = DatabaseHelper.getRowCount(query);
        Assert.assertEquals(nullCount, 0,
            "Found " + nullCount + " users with NULL email!");
    }

    @Test
    public void testNoDuplicateEmails() throws SQLException {
        String query = "SELECT COUNT(*) FROM ("
            + "SELECT email FROM users GROUP BY email "
            + "HAVING COUNT(*) > 1) AS dupes";
        int dupeCount = DatabaseHelper.getRowCount(query);
        Assert.assertEquals(dupeCount, 0,
            "Found " + dupeCount + " duplicate emails!");
    }

    @Test
    public void testNoOrphanOrders() throws SQLException {
        String query = "SELECT COUNT(*) FROM orders o "
            + "LEFT JOIN users u ON o.user_id = u.user_id "
            + "WHERE u.user_id IS NULL";
        int orphanCount = DatabaseHelper.getRowCount(query);
        Assert.assertEquals(orphanCount, 0,
            "Found " + orphanCount + " orphan orders!");
    }

    @Test
    public void testOrderTotalsMatch() throws SQLException {
        String query = "SELECT COUNT(*) FROM ("
            + "SELECT o.order_id FROM orders o "
            + "JOIN order_items oi ON o.order_id = oi.order_id "
            + "GROUP BY o.order_id, o.total_amount "
            + "HAVING o.total_amount != SUM(oi.quantity * oi.unit_price)"
            + ") AS mismatches";
        int mismatchCount = DatabaseHelper.getRowCount(query);
        Assert.assertEquals(mismatchCount, 0,
            "Found " + mismatchCount + " orders with mismatched totals!");
    }

    @Test
    public void testBalanceConservation() throws SQLException {
        // Total money in the system should be constant
        String query = "SELECT SUM(balance) FROM accounts";
        try (Connection conn = DatabaseHelper.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(query)) {
            rs.next();
            double totalBalance = rs.getDouble(1);
            // This value should match your expected system total
            Assert.assertTrue(totalBalance > 0,
                "Total system balance is zero or negative!");
        }
    }
}

import java.sql.*;
import org.testng.Assert;
import org.testng.annotations.Test;

public class ParameterizedValidation {

    @Test
    public void verifyUserAfterRegistration() throws SQLException {
        String email = "neha.gupta@email.com";

        String query = "SELECT full_name, phone, status, created_at "
            + "FROM users WHERE email = ?";

        try (Connection conn = DatabaseHelper.getConnection();
             PreparedStatement stmt = conn.prepareStatement(query)) {

            stmt.setString(1, email);
            ResultSet rs = stmt.executeQuery();

            Assert.assertTrue(rs.next(), "User not found in database!");
            Assert.assertEquals(rs.getString("full_name"), "Neha Gupta");
            Assert.assertEquals(rs.getString("status"), "active");
            Assert.assertNotNull(rs.getTimestamp("created_at"),
                "created_at should not be null!");

            rs.close();
        }
    }

    @Test
    public void verifyTransferBetweenAccounts() throws SQLException {
        String sender = "ACC001";
        String receiver = "ACC002";
        double transferAmount = 5000.00;

        // Get balances BEFORE transfer
        double senderBefore = getBalance(sender);
        double receiverBefore = getBalance(receiver);

        // ... (perform transfer via UI or API) ...

        // Get balances AFTER transfer
        double senderAfter = getBalance(sender);
        double receiverAfter = getBalance(receiver);

        Assert.assertEquals(senderAfter, senderBefore - transferAmount,
            0.01, "Sender balance mismatch!");
        Assert.assertEquals(receiverAfter, receiverBefore + transferAmount,
            0.01, "Receiver balance mismatch!");

        // Conservation check
        Assert.assertEquals(
            senderAfter + receiverAfter,
            senderBefore + receiverBefore,
            0.01, "Money was created or destroyed!");
    }

    private double getBalance(String accountId) throws SQLException {
        String query = "SELECT balance FROM accounts WHERE account_id = ?";
        try (Connection conn = DatabaseHelper.getConnection();
             PreparedStatement stmt = conn.prepareStatement(query)) {
            stmt.setString(1, accountId);
            ResultSet rs = stmt.executeQuery();
            rs.next();
            return rs.getDouble("balance");
        }
    }
}