JDBC and Database Connectivity in Java: Steps & Examples

What Is JDBC?

JDBC (Java Database Connectivity) is a Java API (Application Programming Interface) that enables Java applications to interact with relational databases.

It acts as a bridge between a Java program and a database, allowing applications to:

• Connect to a database.
• Execute SQL queries.
• Retrieve query results.
• Insert, update, and delete records.
• Perform other database operations.

In simple terms, JDBC allows Java applications to communicate with databases such as MySQL, Oracle, SQL Server, and PostgreSQL.

What Is a JDBC Driver?

A JDBC Driver is a software component that enables communication between a Java application and a database.

It translates Java JDBC calls into database-specific commands that the database can understand.

Without a JDBC driver, a Java application cannot communicate with a database.

Common JDBC Drivers

• MySQL JDBC Driver
• Oracle JDBC Driver
• PostgreSQL JDBC Driver
• Microsoft SQL Server JDBC Driver

Steps Involved in JDBC Database Connectivity

To connect a Java application to a database using JDBC, you generally follow these steps.

1. Load the JDBC Driver

Load the appropriate JDBC driver.

Class.forName("com.mysql.cj.jdbc.Driver");

Explanation

This tells Java which database driver to use (for example, MySQL, Oracle, or PostgreSQL).

Note: Since JDBC 4.0, this step is optional because drivers are automatically loaded from the classpath.

2. Create a Connection

Establish a connection with the database.

Connection con = DriverManager.getConnection(
    "jdbc:mysql://localhost:3306/sonoo",
    "root",
    "root"
);

Explanation

The getConnection() method requires:

• Database URL
• Username
• Password

Once connected, the Java application can communicate with the database.

3. Create a Statement

Create a Statement or PreparedStatement object.

Statement stmt = con.createStatement();

Explanation

The Statement object is used to execute SQL commands such as:

• SELECT
• INSERT
• UPDATE
• DELETE

4. Execute the Query

Execute the SQL statement.

For a SELECT query:

ResultSet rs = stmt.executeQuery("SELECT * FROM emp");

For INSERT, UPDATE, or DELETE:

int rows = stmt.executeUpdate(sql);

5. Process the ResultSet

Retrieve the data returned by the query.

while (rs.next()) {
    System.out.println(
        rs.getInt(1) + " " +
        rs.getString(2) + " " +
        rs.getString(3)
    );
}

Explanation

The ResultSet object stores the rows returned by a SELECT query.

The next() method moves to the next row until no more records exist.

6. Close Database Resources

Always close JDBC resources after use.

rs.close();
stmt.close();
con.close();

Explanation

Closing resources:

• Frees memory.
• Prevents resource leaks.
• Improves application performance.

Always close resources in the following order:

1. ResultSet
2. Statement
3. Connection

JDBC Connectivity Flow

The standard JDBC workflow is:

Load Driver
      ↓
Create Connection
      ↓
Create Statement
      ↓
Execute Query
      ↓
Process ResultSet
      ↓
Close Connection

Real-Life Analogy

Think of visiting a bank.

Complete JDBC Example

try {

    Class.forName("com.mysql.cj.jdbc.Driver");

    Connection con = DriverManager.getConnection(
        "jdbc:mysql://localhost:3306/sonoo",
        "root",
        "root"
    );

    Statement stmt = con.createStatement();

    ResultSet rs = stmt.executeQuery("SELECT * FROM emp");

    while (rs.next()) {
        System.out.println(
            rs.getInt(1) + " " +
            rs.getString(2) + " " +
            rs.getString(3)
        );
    }

    rs.close();
    stmt.close();
    con.close();

} catch (Exception e) {
    System.out.println(e);
}

JDBC in Automation Testing

JDBC is widely used in Selenium automation frameworks for backend database validation.

Common use cases include:

• Verifying data stored in the database.
• Comparing UI values with backend values.
• Fetching dynamic test data.
• Validating transactions.
• Verifying reports and calculations.

Using JDBC ensures that both the UI and the database remain consistent.

Real-Time Testing Example (STAR Method)

Situation

During a testing cycle, we needed to verify that product prices displayed on the application matched the prices stored in the database.

Task

Retrieve product prices directly from the database and compare them with the UI.

Action

I:

• Loaded the JDBC driver.
• Created the database connection.
• Executed a SELECT query.
• Retrieved the product prices.
• Compared the database values with the UI values.

Result

We detected pricing mismatches before production deployment, preventing incorrect prices from being shown to customers.

FAQs

1. What Is JDBC?

JDBC (Java Database Connectivity) is a Java API that allows Java applications to communicate with relational databases by executing SQL statements, retrieving results, and performing database operations.

2. What Is a JDBC Driver?

A JDBC Driver is a software component that acts as a bridge between a Java application and a database. It converts Java JDBC calls into database-specific commands.

3. What Are the Steps in JDBC Database Connectivity?

The typical JDBC workflow is:

1. Load or register the JDBC driver.
2. Create a database connection.
3. Create a Statement or PreparedStatement.
4. Execute the SQL query.
5. Process the ResultSet.
6. Close the database resources.

4. Is Loading the Driver with Class.forName() Still Required?

No.

Since JDBC 4.0, loading the driver using Class.forName() is optional because JDBC drivers are automatically loaded from the application's classpath.

5. What Is the Difference Between executeQuery() and executeUpdate()?

executeQuery()

• Executes SELECT statements.
• Returns a ResultSet.

executeUpdate()

• Executes INSERT, UPDATE, and DELETE statements.
• Returns the number of rows affected.

6. How Is JDBC Used in Automation Testing?

JDBC is commonly used in automation testing to:

• Validate backend database records.
• Compare UI data with database values.
• Fetch dynamic test data.
• Verify transactions and reports.
• Ensure UI and database consistency during end-to-end testing.