How do you use SQL in test automation for database validation?

Test Scenario:
1. User submits an order form (UI action)
2. Assert success message appears (UI assertion) ← verifies response
3. Assert order exists in DB with correct data (DB assertion) ← verifies persistence

// src/main/java/utils/DatabaseUtil.java
public class DatabaseUtil {
    private static Connection connection;

    public static void init() throws SQLException {
        String url  = System.getProperty("db.url",  "jdbc:mysql://localhost:3306/testdb");
        String user = System.getProperty("db.user", "qa_user");
        String pass = System.getProperty("db.pass", "qa_pass");
        connection = DriverManager.getConnection(url, user, pass);
    }

    public static ResultSet executeQuery(String sql) throws SQLException {
        return connection.createStatement().executeQuery(sql);
    }

    public static int executeUpdate(String sql) throws SQLException {
        return connection.createStatement().executeUpdate(sql);
    }

    public static String getSingleValue(String sql) throws SQLException {
        ResultSet rs = executeQuery(sql);
        if (rs.next()) return rs.getString(1);
        return null;
    }

    public static int getCount(String sql) throws SQLException {
        ResultSet rs = executeQuery(sql);
        if (rs.next()) return rs.getInt(1);
        return 0;
    }

    public static void close() throws SQLException {
        if (connection != null) connection.close();
    }
}

@Test
public void testOrderCreatedInDatabase() {
    // 1. Place order via UI
    OrderPage orderPage = new OrderPage(driver);
    String orderId = orderPage.placeOrder("Laptop", 1);

    // 2. Validate in database
    String sql = "SELECT status, total_amount FROM orders WHERE order_id = '" + orderId + "'";
    // ⚠️ Use PreparedStatement in real code to avoid SQL injection:
    // SELECT status, total_amount FROM orders WHERE order_id = ?

    String status = DatabaseUtil.getSingleValue(
        "SELECT status FROM orders WHERE order_id = '" + orderId + "'"
    );
    assertEquals("pending", status, "Order status should be 'pending' after creation");

    int count = DatabaseUtil.getCount(
        "SELECT COUNT(*) FROM orders WHERE order_id = '" + orderId + "'"
    );
    assertEquals(1, count, "Exactly one order should exist with this ID");
}

-- 1. Verify record was created
SELECT COUNT(*) FROM users WHERE email = 'newuser@test.com';
-- Expected: 1

-- 2. Verify field values after update
SELECT first_name, last_name, phone
FROM users
WHERE email = 'alice@test.com';
-- Assert: first_name='Alice', last_name='Smith', phone='9999999999'

-- 3. Verify record was deleted
SELECT COUNT(*) FROM products WHERE id = 42 AND deleted_at IS NULL;
-- Expected: 0 (soft-delete) or hard delete = no row at all

-- 4. Verify order total is calculated correctly
SELECT SUM(quantity * unit_price) AS calculated_total, total_amount
FROM order_items oi
JOIN orders o ON oi.order_id = o.id
WHERE o.order_id = 'ORD-001'
GROUP BY total_amount;
-- Assert: calculated_total = total_amount

-- 5. Verify referential integrity after insert
SELECT COUNT(*) FROM order_items oi
LEFT JOIN orders o ON oi.order_id = o.id
WHERE o.id IS NULL;
-- Expected: 0 (no orphan order items)

-- 6. Verify audit log entry created
SELECT action, performed_by, timestamp
FROM audit_logs
WHERE entity_type = 'user' AND entity_id = 101
ORDER BY timestamp DESC
LIMIT 1;

@BeforeMethod
public void setupTestData() throws SQLException {
    // Insert test data before test
    DatabaseUtil.executeUpdate(
        "INSERT INTO users (email, name, status) " +
        "VALUES ('test_auto@test.com', 'Test User', 'active')"
    );
}

@AfterMethod
public void cleanupTestData() throws SQLException {
    // Remove test data after test
    DatabaseUtil.executeUpdate(
        "DELETE FROM users WHERE email = 'test_auto@test.com'"
    );
}

@Test
public void testCreateUserApiAndVerifyInDB() {
    // 1. API call
    Response response = given()
        .contentType(ContentType.JSON)
        .body("{\"name\":\"Alice\",\"email\":\"alice@api.com\"}")
        .post("/api/users")
        .then()
        .statusCode(201)
        .extract().response();

    int userId = response.jsonPath().getInt("id");

    // 2. Verify in database
    String dbName = DatabaseUtil.getSingleValue(
        "SELECT name FROM users WHERE id = " + userId
    );
    assertEquals("Alice", dbName, "Name should match in DB");
}