Advanced Java | Unit 1: JDBC Study Notes

Advanced Java
Unit No. 1 – JDBC Study Notes

Java Database Connectivity – Complete Question & Answer Guide

Q.1) What is JDBC? Explain the JDBC Architecture in Detail.

1.1 What is JDBC?

Definition
JDBC (Java Database Connectivity) is a standard Java API (Application Programming
Interface)
that enables Java programs to interact with relational databases.
It is part of the Java Standard Edition platform, from Oracle Corporation.

JDBC provides a uniform interface through which Java applications can:

• Connect to a database (any relational DB via a vendor-specific driver)
• Send SQL queries (SELECT, INSERT, UPDATE, DELETE)
• Retrieve and process results returned from the database
• Manage transactions

JDBC is defined in the packages:

• [Link] – core JDBC API (Connection, Statement, ResultSet, etc.)
• [Link] – extended/server-side API (DataSource, RowSet, etc.)

1.2 JDBC Architecture

JDBC follows a two-tier or three-tier architecture. The overall design separates the application logic
from the database-specific code through a driver layer.

Page 1 | Advanced Java – JDBC Unit 1

Advanced Java | Unit 1: JDBC Study Notes

Layer-by-Layer Architecture
┌───────────────────────────────────────────────────────────────┐
│ Java Application (User Program) │
│ (Uses JDBC API – [Link].*, [Link].*) │
└───────────────────────┬───────────────────────────────────────┘
│ calls
▼
┌───────────────────────────────────────────────────────────────┐
│ JDBC Driver Manager │
│ ([Link] – manages multiple drivers) │
└──────────┬───────────────────────────┬────────────────────────┘
│ loads │ loads
▼ ▼
┌──────────────────────┐ ┌────────────────────────────────────┐
│ JDBC Driver A │ │ JDBC Driver B │
│ (e.g. MySQL Driver) │ │ (e.g. Oracle Driver) │
└──────────┬───────────┘ └────────────────┬───────────────────┘
│ │
▼ ▼
┌──────────────────────┐ ┌────────────────────────────────────┐
│ MySQL Database │ │ Oracle Database │
└──────────────────────┘ └────────────────────────────────────┘

1.3 Key Components of JDBC Architecture

Component Responsibility
DriverManager Manages a list of database drivers. Matches requests from the Java
application with the proper driver using subprotocol.
Driver The interface that handles communications with the database. The
vendor provides the driver implementation.
Connection Represents a session/connection with a specific database. SQL
statements are executed and results returned within the context of a
connection.
Statement Used to submit SQL statements to the database. Types: Statement,
PreparedStatement, CallableStatement.
ResultSet Holds the data retrieved by a SELECT query. Provides methods to
iterate over rows and read column values.
SQLException Provides information on database access errors or other errors.

1.4 Two-Tier vs Three-Tier Architecture

TWO-TIER MODEL THREE-TIER MODEL
───────────── ────────────────
Java App (Client) Java App (Client)
│ │
│ JDBC (direct) │ HTTP / RMI
▼ ▼
Database Server Middle Tier (App Server / Business Logic)
│
│ JDBC

Page 2 | Advanced Java – JDBC Unit 1

 Advanced Java | Unit 1: JDBC Study Notes

▼
Database Server

In Two-Tier: the Java application directly talks to the database using JDBC. Simple but tightly
coupled.
In Three-Tier: a middle tier (e.g., a Java EE application server) intermediates between client and
database, providing scalability, security, and business logic.

Q.2) Explain the 4 JDBC Drivers with Block Diagram.

JDBC defines four types of drivers. Each type differs in how it communicates between the Java
application and the underlying database.

Type 1 – JDBC-ODBC Bridge Driver

Java App → JDBC API → JDBC-ODBC Bridge → ODBC Driver → Database

• Translates JDBC calls into ODBC (Open Database Connectivity) calls.

• Relies on ODBC drivers installed on the client machine.
• Platform-dependent (ODBC is primarily a Windows technology).
• Slow due to multiple translation layers.
• Deprecated in Java 8 and removed in Java 11.
• Example: [Link]

Type 2 – Native-API Driver (Partly Java Driver)

Java App → JDBC API → Native-API Driver (Java + Native code) → DB Client
Library → Database

• Converts JDBC calls into database-vendor-specific native API calls.

• Uses native code (C/C++) of the database client libraries.
• Faster than Type 1, but still platform-dependent (requires native libs on client).
• Example: Oracle OCI driver, Sybase driver.

Type 3 – Network Protocol Driver (Middleware Driver)

Java App → JDBC API → Type 3 Driver (pure Java) → Middleware Server → DB
Driver → Database

Page 3 | Advanced Java – JDBC Unit 1

Advanced Java | Unit 1: JDBC Study Notes

• Uses a pure Java client driver that communicates with a middleware server via a database-
independent protocol.
• The middleware server then translates the calls into database-specific calls.
• Platform-independent (pure Java), flexible, supports multiple databases.
• Additional middleware server adds complexity and single point of failure.
• Example: IDS Server, WebLogic RMI driver.

Type 4 – Thin Driver / Native Protocol Driver (Pure Java Driver)

Java App → JDBC API → Type 4 Driver (Pure Java) → Database Server
(directly)

• Converts JDBC calls directly into the database-specific network protocol.

• Pure Java – no native code or middleware required.
• Fastest and most portable; most widely used today.
• Driver is DB-specific: one driver per database vendor.
• Examples: MySQL Connector/J, PostgreSQL JDBC Driver, Oracle Thin JDBC Driver.

Summary Comparison
Driver Type Key Characteristics
Type 1 JDBC-ODBC Bridge. Uses ODBC. Deprecated. Platform-dependent.
Slowest.
Type 2 Native-API. Java + native code. Needs DB client lib on machine.
Faster.
Type 3 Network Protocol / Middleware. Pure Java + middleware server.
Flexible.
Type 4 Thin / Native Protocol. Pure Java. No middleware. Fastest. Most
common.

Q.3) Explain the JDBC API in Detail.

Overview
The JDBC API is a collection of classes and interfaces found in the [Link] and [Link]
packages.
It defines a standard way for Java programs to interact with databases.

Page 4 | Advanced Java – JDBC Unit 1

Advanced Java | Unit 1: JDBC Study Notes

3.1 Core Interfaces and Classes

a) [Link]
• A class (not an interface) that manages the list of registered JDBC drivers.
• Key method: getConnection(url, user, password) – establishes a connection.
• Calls each driver's acceptsURL() to find the right driver for the given URL.

b) [Link]
• Represents an active connection/session to a specific database.
• Obtained from [Link]() or a DataSource.
• Key methods: createStatement(), prepareStatement(sql), commit(), rollback(), close(),
setAutoCommit().

c) [Link]
• Used to execute a simple SQL query with no parameters.
• executeQuery(sql) – returns ResultSet (for SELECT).
• executeUpdate(sql) – returns int (rows affected, for INSERT/UPDATE/DELETE).
• execute(sql) – returns boolean (for any SQL).

d) [Link] (extends Statement)

• Pre-compiled SQL statement with placeholders (?).
• More efficient for repeated execution; prevents SQL injection.
• Example: PreparedStatement ps = [Link]("SELECT * FROM emp WHERE
id=?");
• [Link](1, 101); then [Link]();

e) [Link] (extends PreparedStatement)

• Used to execute stored procedures in the database.
• Example: CallableStatement cs = [Link]("{call myProc(?,?)}");

f) [Link]
• Represents the result of a SELECT query, as a table of data.
• Cursor starts before the first row; use next() to advance.
• Key methods: next(), getString(col), getInt(col), getDouble(col), close().
• Types: TYPE_FORWARD_ONLY (default), TYPE_SCROLL_INSENSITIVE,
TYPE_SCROLL_SENSITIVE.

Page 5 | Advanced Java – JDBC Unit 1

Advanced Java | Unit 1: JDBC Study Notes

g) [Link]
• Provides information about the columns of a ResultSet.
• Key methods: getColumnCount(), getColumnName(i), getColumnType(i).

h) [Link]
• Provides metadata about the entire database (tables, stored procedures, SQL capabilities).
• Obtained via: [Link]().

i) [Link]
• Thrown when a database access error occurs.
• Provides: getMessage(), getSQLState(), getErrorCode().

3.2 [Link] Package (Extended API)

Interface / Class Purpose
DataSource Alternative to DriverManager; used in enterprise/server environments.
Supports connection pooling.
RowSet A JavaBeans-based interface for ResultSet; supports disconnected
and connected modes.
ConnectionPooling [Link] – manages a pool of physical
connections.
PooledConnection Represents a pooled physical connection.

3.3 Steps to Use JDBC API

1. Load/Register the Driver: [Link]("[Link]");
2. Establish Connection: Connection con = [Link](url, user, pass);
3. Create Statement: Statement stmt = [Link]();
4. Execute Query: ResultSet rs = [Link]("SELECT * FROM students");
5. Process Results: while([Link]()) { [Link]([Link]("name")); }
6. Close Resources: [Link](); [Link](); [Link]();

Q.4) What is URL? Give Proper Example.

Page 6 | Advanced Java – JDBC Unit 1

Advanced Java | Unit 1: JDBC Study Notes

4.1 What is a JDBC URL?

Definition
A JDBC URL (Uniform Resource Locator) is a string that uniquely identifies
a database so that a JDBC driver can connect to it.
It tells the DriverManager which driver to use and where the database is located.

4.2 General Syntax of JDBC URL

jdbc:<subprotocol>:<subname>

Where:
jdbc – Mandatory protocol prefix for all JDBC URLs
subprotocol – Identifies the database driver/type (e.g., mysql, oracle,
postgresql)
subname – Driver-specific information: host, port, database name,
parameters

4.3 Examples of JDBC URLs

Database JDBC URL Example
MySQL jdbc:mysql://localhost:3306/studentdb host=localhost, port=3306,
database=studentdb
Oracle jdbc:oracle:thin:@localhost:1521:orcl thin = driver type,
@host:port:SID
PostgreSQL jdbc:postgresql://localhost:5432/mydb host=localhost, port=5432,
db=mydb
MS SQL Server jdbc:sqlserver://localhost:1433;databaseName=testdb uses
semicolon-separated parameters
H2 (In-Memory) jdbc:h2:mem:testdb in-memory database for testing
SQLite jdbc:sqlite:C:/data/[Link] file path to the SQLite database file

4.4 Complete Code Example (MySQL)

import [Link].*;

public class JdbcExample {

public static void main(String[] args) throws Exception {
// JDBC URL
String url = "jdbc:mysql://localhost:3306/studentdb";
String user = "root";
String pass = "admin123";

// Step 1: Load driver (optional from JDBC 4.0+)

Page 7 | Advanced Java – JDBC Unit 1

 Advanced Java | Unit 1: JDBC Study Notes

[Link]("[Link]");

// Step 2: Get connection using the URL

Connection con = [Link](url, user, pass);

// Step 3: Execute query

Statement st = [Link]();
ResultSet rs = [Link]("SELECT * FROM students");

while ([Link]()) {
[Link]([Link](1) + " " + [Link](2));
}
[Link]();
}
}

The URL jdbc:mysql://localhost:3306/studentdb instructs the DriverManager to use the MySQL JDBC
driver, connect to localhost on port 3306, and select the studentdb database.

Q.5) Discuss the Client-Side and Server-Side Communication.

Context
In a networked Java application, client-side code runs on the user's machine
while server-side code runs on a remote server.
Communication between them is fundamental to web and enterprise applications.

5.1 Client-Side Communication

Client-side communication refers to all activities and code that execute on the client machine (e.g.,
the user's browser or Java desktop application).

Key Characteristics:
• Initiates requests to the server (e.g., HTTP GET/POST, socket connections).
• Receives and processes server responses.
• In Java: uses [Link], [Link], HttpURLConnection, or higher-level frameworks.
• Responsible for rendering output or presenting data to the user.
• Must handle network delays and errors gracefully.

Client-Side Technologies in Java:

Technology Description

Page 8 | Advanced Java – JDBC Unit 1

 Advanced Java | Unit 1: JDBC Study Notes

[Link] Low-level TCP/IP socket for raw client-server communication.

[Link] Represents a URL; can open streams to read web resources.
HttpURLConnection Supports HTTP-specific features (GET, POST, headers, cookies).
RMI (Remote Method Inv.) Allows calling methods on objects residing in another JVM.
Web Services (JAX-WS) SOAP-based web service client invocation.
REST Client (JAX-RS) RESTful HTTP-based client communication using JSON/XML.

Client-Server Diagram:
┌──────────────────────────────────┐ ┌──────────────────────────────────┐
│ CLIENT (Java App) │ │ SERVER (Java EE)
│
│ │ │
│
│ User Input / UI │ │ Servlet / JSP / REST API
│
│ │ │ │ │
│
│ HTTP Request / Socket ─────────┼─────────┼──► │
│
│ ▲ │ │ ▼
│
│ │ │ │ Business Logic Layer
│
│ HTTP Response / Data ◄────────┼─────────┼── │
│
│ │ │ │ ▼
│
│ Display / Process │ │ JDBC ──► Database
│
└──────────────────────────────────┘ └──────────────────────────────────┘
Internet / LAN / Intranet

5.2 Server-Side Communication

Server-side communication refers to the logic and processes executing on the server that handle
client requests, process data, and send back responses.

Key Characteristics:
• Listens for incoming client requests (via ServerSocket, or application server container).
• Processes requests: business logic, authentication, data validation.
• Communicates with backend systems: databases (via JDBC), other services.
• Sends back structured responses: HTML, JSON, XML, binary data.
• Manages sessions, transactions, and state.

Page 9 | Advanced Java – JDBC Unit 1

 Advanced Java | Unit 1: JDBC Study Notes

Server-Side Technologies in Java:

Technology Description
[Link] Listens for incoming TCP/IP connections from clients.
Servlet API Java EE standard for handling HTTP requests (HttpServlet, doGet,
doPost).
JSP (JavaServer Pages) Server-side templating to dynamically generate HTML.
EJB (Enterprise Beans) Managed components for business logic, transactions, security.
JAX-RS (REST) Annotations-based RESTful web services on the server (@GET,
@POST).
JDBC Server-side database access – queries, inserts, transactions.

5.3 Request-Response Cycle (HTTP)

Step 1: Client creates an HTTP Request
GET /students HTTP/1.1
Host: [Link]

Step 2: Request travels over the network to the server

Step 3: Server receives the request

→ Servlet/REST controller handles it
→ Executes JDBC query to fetch data from DB
→ Formats data as JSON / HTML

Step 4: Server sends HTTP Response

HTTP/1.1 200 OK
Content-Type: application/json
[{"id":1, "name":"Alice"}, {"id":2, "name":"Bob"}]

Step 5: Client receives and processes the response

→ Parses JSON / renders HTML → Displays to user

5.4 Socket-Based Communication (Low Level)

SERVER SIDE CLIENT SIDE
─────────── ───────────
ServerSocket ss = new ServerSocket(8080);
Socket s = [Link](); // wait Socket s = new Socket("host", 8080);

InputStream in = [Link](); OutputStream out = [Link]();

OutputStream out = [Link](); InputStream in = [Link]();

// Read client data from 'in' // Send data via 'out'

// Send response via 'out' // Read response from 'in'

In socket-based communication: the server creates a ServerSocket on a specific port and calls
accept() to block until a client connects. The client creates a Socket with the server's host and port.
Both sides then use InputStream and OutputStream for bidirectional data exchange.

Page 10 | Advanced Java – JDBC Unit 1

Advanced Java | Unit 1: JDBC Study Notes

5.5 Key Differences: Client-Side vs Server-Side

Aspect Client-Side / Server-Side
Location Executes on the user's machine. Executes on the remote server.
Initiates Client initiates the request. Server listens and responds.
Responsibilities UI, user input, display, initiating calls. Business logic, DB access,
security, responses.
Technologies Socket, URL, HttpURLConnection, RMI. ServerSocket, Servlet, JSP,
EJB, JDBC.
State May be stateless (HTTP) or stateful (sockets). Manages sessions,
transactions, pools.
Security Should not expose sensitive logic. Authentication, authorization
handled here.

— End of Unit 1 Study Notes —

Page 11 | Advanced Java – JDBC Unit 1