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.

Advertisement

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.

JDBC Step Real-Life Example
Connect to the database Enter the bank
Execute a query Submit a request form
Retrieve results Receive your account information
Close connection Leave the 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);
}

That example works, but it isn't how you should write JDBC today. If the query throws, close() is never called; catching Exception and printing it hides failures from your test; the password is hard-coded; and Statement with concatenated values is open to SQL injection. The version below fixes all four.

Modern JDBC: PreparedStatement and try-with-resources

We ran this program with an in-memory SQLite database (so it needs nothing installed). For MySQL, only the URL and driver change.

import java.sql.*;

public class JdbcDemo {
    public static void main(String[] args) throws SQLException {
        String url = "jdbc:sqlite::memory:";              // MySQL: "jdbc:mysql://localhost:3306/shop"
        try (Connection con = DriverManager.getConnection(url)) {
            try (Statement st = con.createStatement()) {
                st.executeUpdate("CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT, status TEXT)");
                st.executeUpdate("INSERT INTO users VALUES (1,'asha@x.com','ACTIVE'), (2,'ravi@x.com','LOCKED')");
            }

            // 1. Verify a record the way a test would
            String sql = "SELECT id, status FROM users WHERE email = ?";
            try (PreparedStatement ps = con.prepareStatement(sql)) {
                ps.setString(1, "ravi@x.com");
                try (ResultSet rs = ps.executeQuery()) {
                    if (rs.next()) System.out.println("Found id=" + rs.getInt("id") + " status=" + rs.getString("status"));
                    else System.out.println("No row");
                }
            }

            // 2. Why not concatenate strings? A malicious "email" turns into SQL.
            String input = "nobody@x.com' OR '1'='1";
            try (Statement st = con.createStatement();
                 ResultSet rs = st.executeQuery("SELECT COUNT(*) FROM users WHERE email = '" + input + "'")) {
                rs.next();
                System.out.println("Concatenated query matched " + rs.getInt(1) + " rows");
            }
            try (PreparedStatement ps = con.prepareStatement("SELECT COUNT(*) FROM users WHERE email = ?")) {
                ps.setString(1, input);
                try (ResultSet rs = ps.executeQuery()) {
                    rs.next();
                    System.out.println("PreparedStatement matched " + rs.getInt(1) + " rows");
                }
            }

            // 3. executeUpdate returns the number of affected rows
            try (PreparedStatement ps = con.prepareStatement("UPDATE users SET status = ? WHERE status = ?")) {
                ps.setString(1, "ACTIVE");
                ps.setString(2, "LOCKED");
                System.out.println("Rows updated: " + ps.executeUpdate());
            }
        }
    }
}

Output:

Found id=2 status=LOCKED
Concatenated query matched 2 rows
PreparedStatement matched 0 rows
Rows updated: 1
  • try-with-resources closes the ResultSet, statement and connection automatically, in the right order, even when something fails.
  • SQL injection, demonstrated: the concatenated query turned the input into … OR '1'='1' and matched every user; the PreparedStatement treated the same text as a plain value and matched none. Always bind values with ? placeholders.
  • Read columns by name (rs.getString("status")) rather than position, so tests don't break when the query's columns are reordered.
  • Use executeUpdate()'s return value — the number of affected rows is itself something to assert.
  • Credentials come from configuration or environment variables, never from code.

Step through the connection, statement and result-set objects line by line in the Java Code Visualizer. The SQL side: SQL for Testers.

JDBC in Automation Testing

JDBC is widely used in Selenium and API 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.

A reusable helper for tests

public static String fetchOrderStatus(Connection con, long orderId) throws SQLException {
    try (PreparedStatement ps = con.prepareStatement("SELECT status FROM orders WHERE order_id = ?")) {
        ps.setLong(1, orderId);
        try (ResultSet rs = ps.executeQuery()) {
            return rs.next() ? rs.getString("status") : null;       // null = no such order
        }
    }
}

// In a test, after placing an order through the UI or API:
Assert.assertEquals(fetchOrderStatus(con, orderId), "CONFIRMED");

Keep database access in a small utility class, open one connection per test class (or use a connection pool), and use a dedicated read-only test account where possible.

From Real Projects

Much of my testing on Apkope and Canolog was about data: prospect and customer data captured from many channels on Apkope, and records flowing between sales, inventory, finance and service on Canolog. Checking what the application shows is only half the job — the data behind it has to be right too, which is where SQL helps testers most. Always close connections and use prepared statements — test code deserves the same care as application code.

📖 Related tutorials

📚 Official documentation: MySQL Reference Manual · dev.java: Learn Java (Oracle)

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.