Full Stack • Java • System Design • Cloud • AI Engineering

JDBC Architecture and Components

Learn JDBC Architecture in depth with DriverManager, Connection, Statement, ResultSet, execution flow, real-world examples, diagrams, code examples, interview questions, and enterprise best practices.

What You Will Learn

  • What is JDBC?
  • Why JDBC was created
  • JDBC Architecture
  • JDBC Components
  • JDBC Internal Flow
  • JDBC Execution Lifecycle
  • JDBC CRUD Operations
  • Statement vs PreparedStatement
  • ResultSet Processing
  • Connection Pooling
  • Enterprise Architecture
  • Interview Questions

What is JDBC?

JDBC stands for:

Java Database Connectivity

JDBC is a Java API that allows Java applications to communicate with relational databases.

Examples:

MySQL

Oracle

PostgreSQL

SQL Server

DB2

Why JDBC?

Without JDBC:

Java Application

↓

Database Specific API

↓

Vendor Lock-In

Problem:

Oracle API != MySQL API

JDBC provides:

Common Standard API

for all databases.

JDBC Architecture

flowchart LR

A[Java Application]

B[JDBC API]

C[JDBC Driver]

D[Database]

A --> B
B --> C
C --> D

JDBC Components

1. Java Application

Business logic.

Example:

UserService
OrderService
PaymentService

2. JDBC API

Provided by Java.

Package:

java.sql.*

Important Interfaces:

Connection

Statement

PreparedStatement

CallableStatement

ResultSet

3. JDBC Driver

Vendor-specific implementation.

Examples:

MySQL Connector/J

Oracle JDBC Driver

PostgreSQL Driver

Driver converts:

JDBC Calls

↓

Database Commands

4. Database

Stores actual data.

Examples:

Oracle

MySQL

DB2

PostgreSQL

JDBC Internal Flow

flowchart LR

A[Load Driver]

B[Create Connection]

C[Create Statement]

D[Execute SQL]

E[Process Result]

F[Close Resources]

A --> B
B --> C
C --> D
D --> E
E --> F

JDBC Lifecycle

Every JDBC operation follows:

1. Load Driver

2. Get Connection

3. Create Statement

4. Execute Query

5. Process Result

6. Close Resources

Step 1: Load Driver

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

Purpose:

Register JDBC Driver

Step 2: Create Connection

Connection connection =
    DriverManager.getConnection(
        "jdbc:mysql://localhost:3306/demo",
        "root",
        "password"
    );

Output:

Database Connection Established

Step 3: Create Statement

Statement statement =
        connection.createStatement();

Purpose:

Execute SQL Statements

Step 4: Execute Query

ResultSet rs =
        statement.executeQuery(
            "SELECT * FROM USERS"
        );

Database executes:

SELECT * FROM USERS;

Step 5: Process ResultSet

while (rs.next()) {

    System.out.println(
        rs.getInt("id")
    );

    System.out.println(
        rs.getString("name")
    );
}

Output:

1 John

2 David

3 Mike

Step 6: Close Resources

rs.close();

statement.close();

connection.close();

Always close resources.

Complete JDBC Program

Database Table

CREATE TABLE USERS(

 ID INT,

 NAME VARCHAR(100)

);

Sample Data

INSERT INTO USERS VALUES(1,'John');

INSERT INTO USERS VALUES(2,'David');

Java Code

import java.sql.*;

public class JdbcExample {

    public static void main(String[] args)
            throws Exception {

        Connection connection =
                DriverManager.getConnection(
                        "jdbc:mysql://localhost:3306/demo",
                        "root",
                        "password"
                );

        Statement statement =
                connection.createStatement();

        ResultSet rs =
                statement.executeQuery(
                        "SELECT * FROM USERS"
                );

        while (rs.next()) {

            System.out.println(
                    rs.getInt("id")
            );

            System.out.println(
                    rs.getString("name")
            );
        }

        rs.close();
        statement.close();
        connection.close();
    }
}

Output

1
John

2
David

JDBC Objects Relationship

flowchart LR

A[Connection]

B[Statement]

C[ResultSet]

A --> B
B --> C

Statement Types

JDBC provides:

Statement

PreparedStatement

CallableStatement

Statement

Simple SQL execution.

Statement stmt =
    connection.createStatement();

Example:

stmt.executeQuery(
  "SELECT * FROM USERS"
);

Problem:

SQL Injection Risk

PreparedStatement

Most commonly used.

PreparedStatement ps =
 connection.prepareStatement(
   "SELECT * FROM USERS WHERE ID=?"
 );

Parameter binding:

ps.setInt(1,1);

Execute:

ResultSet rs =
        ps.executeQuery();

Benefits:

Performance

Security

Reusable Queries

Example

PreparedStatement ps =
connection.prepareStatement(
"SELECT * FROM USERS WHERE ID=?"
);

ps.setInt(1,1);

ResultSet rs =
ps.executeQuery();

Output

1 John

CallableStatement

Used for Stored Procedures.

CallableStatement cs =
connection.prepareCall(
"{call GET_USERS()}"
);

ResultSet Architecture

flowchart LR

A[SQL Query]

B[Database]

C[ResultSet]

D[Java Object]

A --> B
B --> C
C --> D

CRUD Operations

Insert

PreparedStatement ps =
connection.prepareStatement(
"INSERT INTO USERS VALUES (?,?)"
);

ps.setInt(1,1);

ps.setString(2,"John");

ps.executeUpdate();

Update

PreparedStatement ps =
connection.prepareStatement(
"UPDATE USERS SET NAME=? WHERE ID=?"
);

Delete

PreparedStatement ps =
connection.prepareStatement(
"DELETE FROM USERS WHERE ID=?"
);

Read

PreparedStatement ps =
connection.prepareStatement(
"SELECT * FROM USERS"
);

JDBC Execution Flow

flowchart LR

A[Application]

B[PreparedStatement]

C[JDBC Driver]

D[Database]

E[ResultSet]

A --> B
B --> C
C --> D
D --> E
E --> A

Problems with Raw JDBC

Large applications face:

Too Much Boilerplate Code

Connection Management

Resource Cleanup

Exception Handling

Transaction Management

Example:

Connection

Statement

ResultSet

finally block

Repeated everywhere.

Solution Evolution

JDBC

↓

Spring JDBC

↓

Hibernate

↓

Spring Data JPA

Connection Pooling

Creating connection is expensive.

Bad:

New Connection Per Request

Good:

Connection Pool

Connection Pool Architecture

flowchart LR

A[Application]

B[Connection Pool]

C[Database]

A --> B
B --> C

Popular Pools:

HikariCP

C3P0

DBCP

Enterprise JDBC Architecture

flowchart LR

A[REST API]

B[Service Layer]

C[DAO Layer]

D[Connection Pool]

E[Database]

A --> B
B --> C
C --> D
D --> E

Real Banking Example

Fund Transfer API:

Transfer Request

↓

Account DAO

↓

JDBC

↓

Oracle DB

Advantages

✅ Database Independent

✅ Standard API

✅ High Performance

✅ Full SQL Control

✅ Lightweight

Limitations

❌ Boilerplate Code

❌ Manual Mapping

❌ Manual Transactions

❌ Resource Management

❌ Complex Queries Harder

JDBC vs Hibernate

Feature JDBC Hibernate
SQL Writing Manual Automatic
Performance Faster Slight Overhead
Learning Curve Easy Medium
Boilerplate High Low
Mapping Manual Automatic

JDBC vs Spring JDBC

Feature JDBC Spring JDBC
Boilerplate High Low
Exception Handling Manual Automatic
Transactions Manual Spring Managed

Interview Questions

What is JDBC?

Java API for database communication.

Main JDBC Interfaces?

Connection

Statement

PreparedStatement

CallableStatement

ResultSet

Why PreparedStatement?

Prevents SQL Injection

Improves Performance

Difference Between executeQuery and executeUpdate?

executeQuery()

Returns:

ResultSet

Used for:

SELECT

executeUpdate()

Returns:

Rows Affected

Used for:

INSERT

UPDATE

DELETE

What is Connection Pooling?

Reusing database connections to improve performance.

Key Takeaways

  • JDBC is the foundation of all Java database frameworks.
  • Every ORM ultimately uses JDBC internally.
  • Core objects are Connection, Statement, PreparedStatement, and ResultSet.
  • PreparedStatement should be preferred over Statement.
  • Connection Pooling is mandatory in production systems.
  • Understanding JDBC Architecture helps in mastering Spring JDBC, Hibernate, and Spring Data JPA.