10.Core Java Concepts: JDBC

Core Java Concepts for Selenium Automation: JDBC (Java Database Connectivity)

JDBC (Java Database Connectivity) is the API provided by Java to connect with databases and perform operations like executing queries, retrieving data, and handling results.

1. Basics of JDBC

JDBC allows Java programs to connect to relational databases such as MySQL, Oracle, and SQL Server. It uses drivers to communicate with the database and supports executing SQL queries through Java code.

JDBC Core Components:

• DriverManager: Manages the database drivers and establishes a connection to the database.
• Connection: Represents a session with the database.
• Statement or PreparedStatement: Used to execute SQL queries.
• ResultSet: Holds the data returned from a query execution.

2. Connecting to a Database

To connect to a database, you'll need the following details:

• JDBC URL
• Database username
• Database password

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

public class JDBCExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/mydatabase";
        String username = "root";
        String password = "password";

        try {
            // Establish a connection
            Connection connection = DriverManager.getConnection(url, username, password);
            System.out.println("Connected to the database!");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

3. Executing SQL Queries

Once the connection is established, you can create a Statement or PreparedStatement to execute SQL queries.

Example: Executing a SQL Query

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

public class JDBCExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/mydatabase";
        String username = "root";
        String password = "password";

        try (Connection connection = DriverManager.getConnection(url, username, password)) {
            // Create a statement
            Statement statement = connection.createStatement();
            
            // Execute a SQL query
            String sql = "INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com')";
            int rowsAffected = statement.executeUpdate(sql);
            System.out.println("Rows affected: " + rowsAffected);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

4. ResultSet Handling

The ResultSet object is used to handle the results of a query, such as SELECT queries, by iterating over the data returned from the database.

Example: Retrieving Data Using ResultSet

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

public class JDBCExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/mydatabase";
        String username = "root";
        String password = "password";

        try (Connection connection = DriverManager.getConnection(url, username, password)) {
            // Create a statement
            Statement statement = connection.createStatement();
            
            // Execute a SELECT query
            String sql = "SELECT * FROM users";
            ResultSet resultSet = statement.executeQuery(sql);

            // Iterate through the ResultSet
            while (resultSet.next()) {
                System.out.println("ID: " + resultSet.getInt("id"));
                System.out.println("Name: " + resultSet.getString("name"));
                System.out.println("Email: " + resultSet.getString("email"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Conclusion

JDBC is essential for working with databases in Java. It is especially useful in Selenium automation projects for database testing or verifying data consistency with UI actions.