Gyan Code

Data-Driven Testing in Selenium

Excel, CSV, JSON & Database integration

Introduction to Data-Driven Testing

Data-Driven Testing (DDT) separates test logic from test data, allowing the same test to run with multiple data sets. This approach improves test coverage, reduces code duplication, and makes tests easier to maintain.

Benefits of Data-Driven Testing

  • Test same functionality with multiple data sets
  • Separate test logic from test data
  • Easier to maintain and update test data
  • Non-technical users can update test data
  • Improved test coverage with minimal code
  • Reusable test scripts

Excel Data Provider

Apache POI library enables reading and writing Excel files in Java. It’s the most popular choice for data-driven testing.

Maven Dependencies

<!-- Apache POI for Excel -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>5.2.5</version>
</dependency>

<!-- For .xlsx files -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.2.5</version>
</dependency>

Excel Utility Class

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileInputStream;

public class ExcelUtils {
    private Workbook workbook;
    private Sheet sheet;
    
    public ExcelUtils(String filePath, String sheetName) {
        try {
            FileInputStream fis = new FileInputStream(filePath);
            workbook = new XSSFWorkbook(fis);
            sheet = workbook.getSheet(sheetName);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    public int getRowCount() {
        return sheet.getLastRowNum() + 1;
    }
    
    public int getColumnCount() {
        return sheet.getRow(0).getLastCellNum();
    }
    
    public String getCellData(int rowNum, int colNum) {
        Cell cell = sheet.getRow(rowNum).getCell(colNum);
        return getCellValue(cell);
    }
    
    private String getCellValue(Cell cell) {
        if (cell == null) return "";
        
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                return String.valueOf((int) cell.getNumericCellValue());
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            default:
                return "";
        }
    }
    
    public void closeWorkbook() {
        try {
            workbook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Excel DataProvider

import org.testng.annotations.DataProvider;

public class TestDataProvider {
    
    @DataProvider(name = "loginData")
    public Object[][] getLoginData() {
        String filePath = "./testdata/TestData.xlsx";
        String sheetName = "LoginData";
        
        ExcelUtils excel = new ExcelUtils(filePath, sheetName);
        
        int rows = excel.getRowCount() - 1; // Exclude header
        int cols = excel.getColumnCount();
        
        Object[][] data = new Object[rows][cols];
        
        for (int i = 0; i < rows; i++) {
            for (int j = 0; j < cols; j++) {
                data[i][j] = excel.getCellData(i + 1, j);
            }
        }
        
        excel.closeWorkbook();
        return data;
    }
}

Using Excel Data in Test

import org.testng.annotations.Test;

public class LoginTest {
    
    @Test(dataProvider = "loginData", dataProviderClass = TestDataProvider.class)
    public void testLogin(String username, String password, String expectedResult) {
        driver.get("https://example.com/login");
        
        driver.findElement(By.id("username")).sendKeys(username);
        driver.findElement(By.id("password")).sendKeys(password);
        driver.findElement(By.id("loginBtn")).click();
        
        if (expectedResult.equals("Success")) {
            Assert.assertTrue(driver.findElement(By.id("dashboard")).isDisplayed());
        } else {
            Assert.assertTrue(driver.findElement(By.id("error")).isDisplayed());
        }
    }
}

Write Data to Excel

import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileOutputStream;

public void writeTestResults(String filePath, int row, String result) {
    try {
        FileInputStream fis = new FileInputStream(filePath);
        Workbook workbook = new XSSFWorkbook(fis);
        Sheet sheet = workbook.getSheetAt(0);
        
        Row rowObj = sheet.getRow(row);
        Cell cell = rowObj.createCell(3); // Result column
        cell.setCellValue(result);
        
        fis.close();
        
        FileOutputStream fos = new FileOutputStream(filePath);
        workbook.write(fos);
        fos.close();
        workbook.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

CSV Data Provider

CSV files are simple, lightweight alternatives to Excel for storing test data.

Maven Dependencies

<!-- OpenCSV -->
<dependency>
    <groupId>com.opencsv</groupId>
    <artifactId>opencsv</artifactId>
    <version>5.9</version>
</dependency>

CSV Utility Class

import com.opencsv.CSVReader;
import java.io.FileReader;
import java.util.List;

public class CSVUtils {
    
    public static Object[][] readCSV(String filePath) {
        Object[][] data = null;
        
        try {
            CSVReader reader = new CSVReader(new FileReader(filePath));
            List<String[]> allRows = reader.readAll();
            reader.close();
            
            // Skip header row
            int rows = allRows.size() - 1;
            int cols = allRows.get(0).length;
            
            data = new Object[rows][cols];
            
            for (int i = 0; i < rows; i++) {
                data[i] = allRows.get(i + 1);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        
        return data;
    }
}

CSV DataProvider

@DataProvider(name = "csvData")
public Object[][] getCSVData() {
    String filePath = "./testdata/testdata.csv";
    return CSVUtils.readCSV(filePath);
}

@Test(dataProvider = "csvData")
public void testWithCSV(String username, String password, String expectedResult) {
    // Test implementation
}

Read CSV Without Library

import java.io.BufferedReader;
import java.io.FileReader;
import java.util.ArrayList;
import java.util.List;

public static Object[][] readCSVManually(String filePath) {
    List<String[]> data = new ArrayList<>();
    
    try (BufferedReader br = new BufferedReader(new FileReader(filePath))) {
        String line;
        boolean isHeader = true;
        
        while ((line = br.readLine()) != null) {
            if (isHeader) {
                isHeader = false;
                continue; // Skip header
            }
            String[] values = line.split(",");
            data.add(values);
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
    
    return data.toArray(new Object[0][]);
}

CSV vs Excel

  • CSV Pros: Simple, lightweight, version control friendly, faster to read
  • CSV Cons: No formatting, single sheet, limited data types
  • Excel Pros: Multiple sheets, formatting, formulas, data validation
  • Excel Cons: Larger files, requires external library

JSON Data Provider

JSON is ideal for complex, nested test data structures and API test data.

Maven Dependencies

<!-- Gson for JSON -->
<dependency>
    <groupId>com.google.code.gson</groupId>
    <artifactId>gson</artifactId>
    <version>2.10.1</version>
</dependency>

<!-- Or Jackson -->
<dependency>
    <groupId>com.fasterxml.jackson.core</groupId>
    <artifactId>jackson-databind</artifactId>
    <version>2.16.1</version>
</dependency>

Sample JSON File

{
  "testData": [
    {
      "username": "user1@test.com",
      "password": "Pass@123",
      "expectedResult": "Success"
    },
    {
      "username": "user2@test.com",
      "password": "Pass@456",
      "expectedResult": "Success"
    },
    {
      "username": "invalid@test.com",
      "password": "wrong",
      "expectedResult": "Failed"
    }
  ]
}

POJO Class

public class LoginData {
    private String username;
    private String password;
    private String expectedResult;
    
    // Getters and Setters
    public String getUsername() {
        return username;
    }
    
    public void setUsername(String username) {
        this.username = username;
    }
    
    public String getPassword() {
        return password;
    }
    
    public void setPassword(String password) {
        this.password = password;
    }
    
    public String getExpectedResult() {
        return expectedResult;
    }
    
    public void setExpectedResult(String expectedResult) {
        this.expectedResult = expectedResult;
    }
}

JSON Utility with Gson

import com.google.gson.Gson;
import com.google.gson.JsonObject;
import com.google.gson.JsonArray;
import java.io.FileReader;

public class JSONUtils {
    
    public static Object[][] readJSON(String filePath) {
        Object[][] data = null;
        
        try {
            Gson gson = new Gson();
            FileReader reader = new FileReader(filePath);
            
            JsonObject jsonObject = gson.fromJson(reader, JsonObject.class);
            JsonArray testData = jsonObject.getAsJsonArray("testData");
            
            data = new Object[testData.size()][1];
            
            for (int i = 0; i < testData.size(); i++) {
                LoginData loginData = gson.fromJson(testData.get(i), LoginData.class);
                data[i][0] = loginData;
            }
            
            reader.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        
        return data;
    }
}

JSON DataProvider

@DataProvider(name = "jsonData")
public Object[][] getJSONData() {
    String filePath = "./testdata/logindata.json";
    return JSONUtils.readJSON(filePath);
}

@Test(dataProvider = "jsonData")
public void testWithJSON(LoginData data) {
    driver.get("https://example.com/login");
    
    driver.findElement(By.id("username")).sendKeys(data.getUsername());
    driver.findElement(By.id("password")).sendKeys(data.getPassword());
    driver.findElement(By.id("loginBtn")).click();
    
    // Assertions based on expectedResult
}

Complex JSON Structure

{
  "users": [
    {
      "username": "admin",
      "password": "admin123",
      "profile": {
        "firstName": "John",
        "lastName": "Doe",
        "email": "john@test.com"
      },
      "permissions": ["read", "write", "delete"]
    }
  ]
}

Database Data Provider

For large datasets or dynamic test data, reading directly from a database provides flexibility and real-time data access.

Maven Dependencies

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

Database Utility Class

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

public class DatabaseUtils {
    private static Connection connection;

    private static final String URL = "jdbc:mysql://localhost:3306/testdb";
    private static final String USER = "root";
    private static final String PASSWORD = "password";

    public static Connection getConnection() {
        try {
            if (connection == null || connection.isClosed()) {
                connection = DriverManager.getConnection(URL, USER, PASSWORD);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    public static void closeConnection() {
        try {
            if (connection != null && !connection.isClosed()) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Database DataProvider

@DataProvider(name = "dbData")
public Object[][] getDBData() {
    List<Object[]> data = new ArrayList<>();

    try {
        Connection conn = DatabaseUtils.getConnection();
        String query = "SELECT username, password, expected_result FROM login_test_data";

        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(query);

        while (rs.next()) {
            String username = rs.getString("username");
            String password = rs.getString("password");
            String expectedResult = rs.getString("expected_result");

            data.add(new Object[]{username, password, expectedResult});
        }

        rs.close();
        stmt.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }

    return data.toArray(new Object[0][]);
}

@Test(dataProvider = "dbData")
public void testWithDatabase(String username, String password, String expected) {
    // Test implementation
}

Parameterized Query

public Object[][] getDataByEnvironment(String environment) {
    List<Object[]> data = new ArrayList<>();

    try {
        Connection conn = DatabaseUtils.getConnection();
        String query = "SELECT * FROM test_data WHERE environment = ?";

        PreparedStatement pstmt = conn.prepareStatement(query);
        pstmt.setString(1, environment);

        ResultSet rs = pstmt.executeQuery();

        while (rs.next()) {
            data.add(new Object[]{
                rs.getString("username"),
                rs.getString("password"),
                rs.getString("expected_result")
            });
        }

        rs.close();
        pstmt.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }

    return data.toArray(new Object[0][]);
}

Properties File Configuration

Properties files store configuration data and simple test parameters.

config.properties

# Application Configuration
base.url=https://example.com
browser=chrome
timeout=10

# Test Credentials
valid.username=testuser
valid.password=Test@123
invalid.username=wronguser
invalid.password=wrong

Properties Reader

import java.io.FileInputStream;
import java.util.Properties;

public class ConfigReader {
    private static Properties properties;

    static {
        try {
            FileInputStream fis = new FileInputStream("./config/config.properties");
            properties = new Properties();
            properties.load(fis);
            fis.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static String get(String key) {
        return properties.getProperty(key);
    }

    public static String getBaseUrl() {
        return get("base.url");
    }

    public static String getBrowser() {
        return get("browser");
    }

    public static int getTimeout() {
        return Integer.parseInt(get("timeout"));
    }
}

Using Properties in Tests

public class LoginTest extends BaseTest {

    @Test
    public void testValidLogin() {
        String username = ConfigReader.get("valid.username");
        String password = ConfigReader.get("valid.password");

        loginPage.login(username, password);
        Assert.assertTrue(homePage.isDisplayed());
    }

    @Test
    public void testInvalidLogin() {
        String username = ConfigReader.get("invalid.username");
        String password = ConfigReader.get("invalid.password");

        loginPage.login(username, password);
        Assert.assertTrue(loginPage.isErrorDisplayed());
    }
}

Environment-Specific Properties

// config-dev.properties
base.url=https://dev.example.com

// config-qa.properties
base.url=https://qa.example.com

// config-prod.properties
base.url=https://example.com

Environment-Based Config Reader

public class ConfigReader {
    private static Properties properties;

    static {
        String env = System.getProperty("env", "dev");
        String configFile = "./config/config-" + env + ".properties";

        try {
            FileInputStream fis = new FileInputStream(configFile);
            properties = new Properties();
            properties.load(fis);
            fis.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static String get(String key) {
        return properties.getProperty(key);
    }
}

// Run with: mvn test -Denv=qa

TestNG Parameters

TestNG allows passing parameters from testng.xml directly to test methods.

testng.xml with Parameters

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE suite SYSTEM "https://testng.org/testng-1.0.dtd">
<suite name="Data-Driven Suite">

    <!-- Suite-level parameters -->
    <parameter name="browser" value="chrome"/>
    <parameter name="baseUrl" value="https://example.com"/>

    <test name="Login Tests">
        <!-- Test-level parameters -->
        <parameter name="username" value="testuser"/>
        <parameter name="password" value="Test@123"/>

        <classes>
            <class name="tests.LoginTest"/>
        </classes>
    </test>

</suite>

Using Parameters in Test

import org.testng.annotations.Parameters;

public class LoginTest {

    @Parameters({"browser", "baseUrl"})
    @BeforeClass
    public void setup(String browser, String baseUrl) {
        if (browser.equals("chrome")) {
            driver = new ChromeDriver();
        } else if (browser.equals("firefox")) {
            driver = new FirefoxDriver();
        }
        driver.get(baseUrl);
    }

    @Parameters({"username", "password"})
    @Test
    public void testLogin(String username, String password) {
        driver.findElement(By.id("username")).sendKeys(username);
        driver.findElement(By.id("password")).sendKeys(password);
        driver.findElement(By.id("loginBtn")).click();
    }
}

Optional Parameters

import org.testng.annotations.Optional;

@Parameters({"browser"})
@BeforeClass
public void setup(@Optional("chrome") String browser) {
    // Uses "chrome" if parameter not provided in testng.xml
    if (browser.equals("chrome")) {
        driver = new ChromeDriver();
    } else if (browser.equals("firefox")) {
        driver = new FirefoxDriver();
    }
}

Cross-Browser Testing

<suite name="Cross Browser Suite" parallel="tests" thread-count="2">

    <test name="Chrome Test">
        <parameter name="browser" value="chrome"/>
        <classes>
            <class name="tests.LoginTest"/>
        </classes>
    </test>

    <test name="Firefox Test">
        <parameter name="browser" value="firefox"/>
        <classes>
            <class name="tests.LoginTest"/>
        </classes>
    </test>

    <test name="Edge Test">
        <parameter name="browser" value="edge"/>
        <classes>
            <class name="tests.LoginTest"/>
        </classes>
    </test>

</suite>

Best Practices

Recommended Project Structure

project/
├── src/test/
│   ├── java/
│   │   ├── tests/
│   │   │   └── LoginTest.java
│   │   ├── dataproviders/
│   │   │   ├── ExcelDataProvider.java
│   │   │   ├── CSVDataProvider.java
│   │   │   └── JSONDataProvider.java
│   │   └── utils/
│   │       ├── ExcelUtils.java
│   │       ├── CSVUtils.java
│   │       ├── JSONUtils.java
│   │       └── ConfigReader.java
│   └── resources/
│       ├── testdata/
│       │   ├── login_data.xlsx
│       │   ├── user_data.csv
│       │   └── api_data.json
│       └── config/
│           ├── config-dev.properties
│           ├── config-qa.properties
│           └── config-prod.properties

Choosing the Right Data Source

Data Source    | Best For                      | Pros                    | Cons
---------------|-------------------------------|-------------------------|------------------
Excel          | Business users, complex data  | Familiar, multi-sheet   | Library required
CSV            | Simple data, CI/CD            | Lightweight, VCS-friendly| No formatting
JSON           | API data, nested structures   | Flexible, readable      | Complex parsing
Database       | Dynamic data, large datasets  | Real-time, scalable     | Setup overhead
Properties     | Config, simple key-value      | Built-in Java support   | Limited structure
TestNG XML     | Static parameters             | No code change needed   | Not scalable

Key Takeaways

  • Choose data source based on data complexity and team skills
  • Excel works best when business users need to maintain test data
  • CSV is ideal for simple data and CI/CD pipelines
  • JSON excels at API testing and complex nested data
  • Database is best for large, dynamic, or shared datasets
  • Properties files work well for configuration and environment settings

Next Steps

Continue Your Learning Journey

  • Selenium Grid - Parallel execution across multiple browsers and machines
  • Extent Reports - Generate rich HTML reports with screenshots
  • CI/CD Integration - Automate test execution with Jenkins or GitHub Actions
  • BDD with Cucumber - Combine data-driven testing with Gherkin scenarios
  • API Testing - Data-driven REST API testing with RestAssured
  • Test Data Generation - Use Faker library for dynamic test data