How to validate API Response from Database in Rest Assured

 

To validate an API response against data in a MySQL database using Rest Assured, follow these steps:

Step-by-Step Guide

Step 1: Setup Project with Required Dependencies

<dependencies>
    <!-- Rest Assured for API Testing -->
    <dependency>
        <groupId>io.rest-assured</groupId>
        <artifactId>rest-assured</artifactId>
        <version>5.3.0</version>
        <scope>test</scope>
    </dependency>

    <!-- MySQL Connector for DB Connection -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.33</version>
    </dependency>

    <!-- JSON handling -->
    <dependency>
        <groupId>org.json</groupId>
        <artifactId>json</artifactId>
        <version>20230227</version>
    </dependency>

    <!-- TestNG (optional for test framework) -->
    <dependency>
        <groupId>org.testng</groupId>
        <artifactId>testng</artifactId>
        <version>7.4.0</version>
        <scope>test</scope>
    </dependency>
</dependencies>

Step 2: MySQL Database Setup

Suppose you have a table users with structure:

CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(255),
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    avatar VARCHAR(255)
);

Insert some matching test data from https://reqres.in/api/users?page=2







Step 3: Java Code




import io.restassured.RestAssured;
import io.restassured.response.Response;

import java.sql.*;
import java.util.List;
import java.util.Map;

import static io.restassured.RestAssured.given;

public class APIDatabaseValidation {

    // JDBC details
    static final String DB_URL = "jdbc:mysql://localhost:3306/testdb";
    static final String USER = "root";
    static final String PASS = "password";

    public static void main(String[] args) throws Exception {
        // Step 1: Get API response
        RestAssured.baseURI = "https://reqres.in";
        Response response = given()
                .when()
                .get("/api/users?page=2")
                .then()
                .statusCode(200)
                .extract().response();

        // Step 2: Extract JSON data from API
        List<Map<String, Object>> apiUsers = response.jsonPath().getList("data");

        // Step 3: Connect to MySQL and validate
        Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
        Statement stmt = conn.createStatement();

        for (Map<String, Object> apiUser : apiUsers) {
            int id = (Integer) apiUser.get("id");
            String email = (String) apiUser.get("email");
            String firstName = (String) apiUser.get("first_name");
            String lastName = (String) apiUser.get("last_name");
            String avatar = (String) apiUser.get("avatar");

            // Query DB for this user
            String query = "SELECT * FROM users WHERE id = " + id;
            ResultSet rs = stmt.executeQuery(query);

            if (rs.next()) {
                assert email.equals(rs.getString("email")) : "Email mismatch for ID " + id;
                assert firstName.equals(rs.getString("first_name")) : "First name mismatch for ID " + id;
                assert lastName.equals(rs.getString("last_name")) : "Last name mismatch for ID " + id;
                assert avatar.equals(rs.getString("avatar")) : "Avatar mismatch for ID " + id;
                System.out.println("User ID " + id + " matched successfully.");
            } else {
                System.err.println("User ID " + id + " not found in database.");
            }
            rs.close();
        }

        // Close DB connection
        stmt.close();
        conn.close();
    }
}