When Selenium Meets JDBC

Sometime back I wrote an Excel reader/writer https://lifeisatest.medium.com/a-simple-excel-reader-writer-d74c2b4a1705
I have taken the same scenario here as well. I will have a table containing some websites, I want my Selenium code to connect to this database, read the links, visit the websites and append the rows with the title of the pages. Let’s roll!

Step 0: Install MySQL Workbench (duhh!!) https://lifeisatest.medium.com/setting-up-mysql-workbench-6bc7714c7c7d

Step 1: Fetching the good old Maven dependencies

<!-- Selenium Dependency -->
    <dependency>
        <groupId>org.seleniumhq.selenium</groupId>
        <artifactId>selenium-java</artifactId>
        <version>4.27.0</version>
    </dependency>
    <!-- MySQL Connector Dependency -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.26</version>
    </dependency>

Step 2: Now let’s get the code that will connect to this database using JDBC and create a table

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class CreateTable {
    public static void main(String[] args) {
        // Database connection details
        String jdbcUrl = "jdbc:mysql://localhost:3306/avidb1";
        String username = "root";
        String password = "mysqlpass";

        // SQL statement to create the table
        String createTableSQL = "CREATE TABLE websites ("
            + "id INT AUTO_INCREMENT PRIMARY KEY, "
            + "url VARCHAR(255) NOT NULL, "
            + "title VARCHAR(255)"
            + ")";

        try (Connection connection = DriverManager.getConnection(jdbcUrl, username, password);
             Statement statement = connection.createStatement()) {

            // Execute the SQL statement
            statement.execute(createTableSQL);
            System.out.println("Table 'websites' created successfully.");

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Running this code will create a table named ‘websites’. Verify it using:

Step 3: The table doesn’t have data yet so let’s write a Java class to populate data.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class InsertDummyData {
    public static void main(String[] args) {
        // Database connection details
        String jdbcUrl = "jdbc:mysql://localhost:3306/avidb1";
        String username = "root";
        String password = "mysqlpass";

        // SQL statement to insert dummy data
        String insertSQL = "INSERT INTO websites (url, title) VALUES (?, ?)";

        try (Connection connection = DriverManager.getConnection(jdbcUrl, username, password);
             PreparedStatement preparedStatement = connection.prepareStatement(insertSQL)) {

            // Insert first dummy record
            preparedStatement.setString(1, "https://example.com");
            preparedStatement.setString(2, "");
            preparedStatement.executeUpdate();

            // Insert second dummy record
            preparedStatement.setString(1, "https://test.com");
            preparedStatement.setString(2, "");
            preparedStatement.executeUpdate();

            // Insert third dummy record
            preparedStatement.setString(1, "https://sample.com");
            preparedStatement.setString(2, "");
            preparedStatement.executeUpdate();

            System.out.println("Dummy data inserted successfully.");

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Step 4: Now it’s time for the real fun- bringing Selenium into the play. We’ll write a code for Selenium to visit each of the above websites, fetch the title and update it in the table.

package com.avishek.dbproject1;

import org.openqa.selenium.WebDriver;
import org.openqa.selenium.chrome.ChromeDriver;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class UpdateTitles {
    public static void main(String[] args) {
     
        // Database connection details
        String jdbcUrl = "jdbc:mysql://localhost:3306/avidb1";
        String username = "root";
        String password = "mysqlpass";

        // SQL queries
        String selectQuery = "SELECT id, url FROM websites";
        String updateQuery = "UPDATE websites SET title = ? WHERE id = ?";

        // Initialize WebDriver
        WebDriver driver = new ChromeDriver();

        try (Connection connection = DriverManager.getConnection(jdbcUrl, username, password);
             PreparedStatement selectStmt = connection.prepareStatement(selectQuery);
             PreparedStatement updateStmt = connection.prepareStatement(updateQuery);
             ResultSet resultSet = selectStmt.executeQuery()) {

            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String url = resultSet.getString("url");

                // Visit the website
                driver.get(url);
                String title = driver.getTitle();

                // Update the title in the database
                updateStmt.setString(1, title);
                updateStmt.setInt(2, id);
                updateStmt.executeUpdate();

                System.out.println("Updated title for URL: " + url);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // Close the WebDriver
            driver.quit();
        }
    }
}

And….. we have the page titles updated in the table!

Step 5 (Optional): Do you want to get rid of the table?

package com.avishek.dbproject1;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class DropTable {
    public static void main(String[] args) {
        // Database connection details
        String jdbcUrl = "jdbc:mysql://localhost:3306/avidb1";
        String username = "root";
        String password = "mysqlpass";

        // SQL statement to drop the table
        String dropTableSQL = "DROP TABLE IF EXISTS websites";

        try (Connection connection = DriverManager.getConnection(jdbcUrl, username, password);
             Statement statement = connection.createStatement()) {

            // Execute the SQL statement
            statement.execute(dropTableSQL);
            System.out.println("Table 'websites' dropped successfully.");

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

I will make some further refactoring in the code like moving the constants and parameterizing table name and test data (the URLs).

Some further reading at: https://medium.com/@Bharat2044/what-is-jdbc-introduction-to-java-database-connectivity-649677818a8b