JDBC Architecture

TL;DR

• JDBC (Java Database Connectivity) is a standard Java API used to connect Java applications with relational databases.
• Its architecture is based on two layers: the JDBC API (used by developers) and the JDBC Driver API (implemented by database vendors).
• It follows the Bridge Design Pattern, decoupling the Java program from the specific database implementation.

Concept

If you want to connect a Java application to MySQL, PostgreSQL, and Oracle, it would be a nightmare if you had to write completely different Java code for each one.

JDBC solves this using a two-tier architecture:

1. The JDBC API Layer: This is provided by the JDK (java.sql package). It consists entirely of abstract Interfaces (like Connection, Statement, ResultSet). You, the developer, write your code against these generic interfaces.
2. The Driver API Layer: Database vendors (Oracle, MySQL, etc.) write concrete classes that implement those JDBC interfaces. They package these classes into a .jar file (the JDBC Driver).

When your application asks the DriverManager for a connection, it looks for the appropriate Driver and returns a concrete instance of that vendor’s connection object, but typed as the generic java.sql.Connection interface.

Examples

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

public class JdbcDemo {
    public static void main(String[] args) {
        // The URL tells the DriverManager WHICH vendor driver to use (mysql)
        String url = "jdbc:mysql://localhost:3306/mydb";
        String user = "root";
        String password = "password";

        // 1. Connection (JDBC API interface, implemented by MySQL driver)
        try (Connection conn = DriverManager.getConnection(url, user, password);
             // 2. Statement
             Statement stmt = conn.createStatement();
             // 3. ResultSet
             ResultSet rs = stmt.executeQuery("SELECT name FROM users")) {
            
            while (rs.next()) {
                System.out.println(rs.getString("name"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Interview Questions

Q: What are the core components of the JDBC API?
A: 1. DriverManager: Manages a list of database drivers and establishes connections.
2. Connection: Represents the physical session with the database.
3. Statement / PreparedStatement: Used to execute SQL queries.
4. ResultSet: Holds data retrieved from a database after executing a SELECT query.

Q: What design pattern does JDBC heavily rely on?
A: The Bridge Pattern. The core idea of the Bridge pattern is to decouple an abstraction (the JDBC API in java.sql) from its implementation (the vendor drivers like mysql-connector.jar) so that the two can vary independently. This allows you to completely swap out your underlying database from MySQL to PostgreSQL without changing a single line of your core Java data-access logic (assuming standard SQL).