Database and Web Communication
Open in the Padhaiko app3.1 Java database connectivity (JDBC)
JDBC (Java Database Connectivity) is Java's standard API (java.sql and javax.sql) for connecting to relational databases (PostgreSQL, MySQL, Oracle, SQLite), executing SQL statements, and processing result sets in a vendor-independent way.
3.1.1 JDBC architecture and drivers
JDBC follows a driver-manager abstraction that separates standard Java application code from vendor-specific database wire protocols:
+-----------------------+
| Java Application | (calls Connection, PreparedStatement, ResultSet)
+-----------------------+
|
v
+-----------------------+
| JDBC API (java.sql) |
+-----------------------+
|
v
+-----------------------+
| DriverManager | (selects matching driver from classpath by JDBC URL)
+-----------------------+
|
v
+-----------------------+
| Vendor JDBC Driver | (e.g., PostgreSQL / MySQL Type 4 Driver)
+-----------------------+
|
v
+-----------------------+
| Relational Database |
+-----------------------+- Two-Tier Architecture: The Java application communicates directly with the database server through the JDBC driver (
Client App -> JDBC Driver -> DB Server). Common in desktop tools and direct backend services. - Three-Tier Architecture: The client sends requests (such as HTTP/REST calls) to a middle-tier application server (e.g., Spring Boot), which uses JDBC (and connection pools) to query the database (
Client -> App Server -> JDBC -> DB Server).
The Four Types of JDBC Drivers
| Driver Type | Name | Mechanism | Advantages & Status |
|---|---|---|---|
| Type 1 | JDBC-ODBC Bridge | Translates JDBC calls into ODBC calls, then native DB calls | Slow, platform-dependent; removed in Java 8 |
| Type 2 | Native-API Driver | Converts JDBC calls to vendor's native C/C++ client library | Faster than Type 1, but requires native binaries on every host |
| Type 3 | Network Protocol Driver | Sends JDBC calls over network to a middleware server that translates to DB protocol | Flexible for multi-DB routing; requires maintaining middleware |
| Type 4 | Pure-Java (Thin) Driver | Speaks the database's TCP wire protocol directly in 100% Java | Industry standard—platform-independent, zero native libs, fastest |
3.1.2 Connection, statement, and result set management
A typical JDBC workflow has five sequential steps, all wrapped in try-with-resources so database connections and cursors never leak:
- Obtain a
Connection:- Using
DriverManager.getConnection("jdbc:postgresql://localhost:5432/college", "appuser", "secret"). - In production servers, obtain pooled connections from a
javax.sql.DataSource(such as HikariCP) instead of opening a fresh TCP socket per query.
- Using
- Create a Statement Object:
Statement: static SQL with no parameters (avoid for user input).PreparedStatement: precompiled parameterized SQL (?placeholders) — standard choice.CallableStatement: invokes database stored procedures.
- Execute SQL:
executeQuery()forSELECTstatements — returns aResultSet.executeUpdate()forINSERT,UPDATE,DELETE, or DDL — returns anintrow count affected.execute()for arbitrary SQL when the return kind is unknown at compile time.
- Process the
ResultSet: AResultSetmaintains a cursor initially positioned before the first row. Callwhile (rs.next())to advance row by row and extract typed columns (rs.getInt("id"),rs.getString("name"),rs.getDouble("gpa")). - Manage Transactions and Close Resources: By default,
conn.getAutoCommit()istrue(every SQL statement commits immediately). For multi-step atomic operations (such as bank transfers), disable auto-commit (conn.setAutoCommit(false)), execute all updates, callconn.commit()on success, orconn.rollback()insidecatchon failure.
import java.sql.*;
public class StudentDao {
private static final String URL = "jdbc:postgresql://localhost:5432/ioe";
public static void printTopStudents(double minGpa) throws SQLException {
String sql = "SELECT roll_no, full_name, gpa FROM students WHERE gpa >= ? ORDER BY gpa DESC";
try (
Connection conn = DriverManager.getConnection(URL, "user", "pass");
PreparedStatement ps = conn.prepareStatement(sql)
) {
ps.setDouble(1, minGpa);
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
System.out.printf("%d | %s | %.2f%n",
rs.getInt("roll_no"),
rs.getString("full_name"),
rs.getDouble("gpa"));
}
}
}
}
}3.1.3 Prepared statements for secure and efficient queries
Why PreparedStatement Beats Plain Statement
- Prevents SQL Injection: Concatenating untrusted user input into a
Statementstring lets attackers alter query structure. For example,"SELECT * FROM users WHERE username = '" + userInput + "'"with input' OR '1'='1turns the condition into always true, exposing all rows.PreparedStatementsends the SQL template and bound parameter values separately to the driver/database, so bound values are strictly treated as literal data, never executable SQL syntax. - Precompilation & Query Plan Caching: The SQL skeleton (
SELECT ... WHERE id = ?) is parsed, validated, and compiled once; repeated executions with different parameter values reuse the cached execution plan, lowering CPU overhead. - Batch Execution: Multiple inserts/updates can be queued via
ps.addBatch()and transmitted in bulk withps.executeBatch(), drastically reducing network round-trips.
// Safe parameterized batch insert
String insertSql = "INSERT INTO attendance(student_id, present) VALUES (?, ?)";
try (PreparedStatement ps = conn.prepareStatement(insertSql)) {
conn.setAutoCommit(false);
for (int id : studentIds) {
ps.setInt(1, id);
ps.setBoolean(2, true);
ps.addBatch();
}
int[] counts = ps.executeBatch();
conn.commit();
}3.2 Network programming and rest
Java supports both low-level transport sockets (java.net) and high-level HTTP/REST clients (java.net.http), forming the foundation for distributed systems and microservices.
3.2.1 Client-server model and basics of socket communication
In the client-server model, a server listens continuously on a known IP address and TCP/UDP port (0–65535; well-known ports 0–1023, e.g., HTTP 80, HTTPS 443), while clients actively initiate connections to that endpoint.
- TCP Sockets (Reliable, Connection-Oriented Byte Stream):
- Server side (
ServerSocket): Binds to a port (new ServerSocket(9000)), blocks onserver.accept()until a client connects, obtains aSocketrepresenting that specific client connection, and exchanges text/bytes throughsocket.getInputStream()andsocket.getOutputStream(). - Client side (
Socket): Connects to(host, port)vianew Socket("localhost", 9000)and reads/writes streams.
- Server side (
- UDP Sockets (Connectionless Datagrams): Uses
DatagramSocketandDatagramPacketfor low-latency, best-effort packet delivery (DNS, telemetry, streaming) without a persistent connection handshake.
import java.io.*;
import java.net.ServerSocket;
import java.net.Socket;
public class EchoServer {
public static void main(String[] args) throws IOException {
try (ServerSocket server = new ServerSocket(9090)) {
System.out.println("Listening on port 9090...");
while (true) {
Socket client = server.accept(); // blocks until client connects
new Thread(() -> handleClient(client)).start();
}
}
}
private static void handleClient(Socket client) {
try (
client;
BufferedReader in = new BufferedReader(new InputStreamReader(client.getInputStream()));
PrintWriter out = new PrintWriter(client.getOutputStream(), true)
) {
String msg;
while ((msg = in.readLine()) != null) {
out.println("ECHO: " + msg);
}
} catch (IOException e) {
System.err.println("Client error: " + e.getMessage());
}
}
}3.2.2 Consuming restful APIs using the HTTPClient from Java 11+
Before Java 11, developers relied on verbose HttpURLConnection (HTTP/1.1 only, synchronous). Standardized in Java 11 (java.net.http), the modern HttpClient supports HTTP/1.1 and HTTP/2, TLS, timeouts, and both synchronous (send) and non-blocking asynchronous (sendAsync returning CompletableFuture) calls.
The API revolves around three immutable/builder classes:
HttpClient: Configured once viaHttpClient.newBuilder().connectTimeout(Duration.ofSeconds(5)).build()and reused across requests (thread-safe).HttpRequest: Built with.uri(...),.timeout(...),.header(...), and HTTP method (.GET(),.POST(BodyPublishers.ofString(json)),.PUT(...),.DELETE()).HttpResponse<T>: Holds.statusCode(),.headers(), and.body()decoded by aBodyHandler(e.g.,HttpResponse.BodyHandlers.ofString()).
import java.net.URI;
import java.net.http.HttpClient;
import java.net.http.HttpRequest;
import java.net.http.HttpResponse;
import java.time.Duration;
public class RestConsumerDemo {
public static void main(String[] args) throws Exception {
HttpClient client = HttpClient.newBuilder()
.connectTimeout(Duration.ofSeconds(5))
.build();
// 1. Synchronous GET request
HttpRequest getReq = HttpRequest.newBuilder()
.uri(URI.create("https://api.example.edu/courses/ENCT327"))
.header("Accept", "application/json")
.GET()
.build();
HttpResponse<String> getRes = client.send(getReq, HttpResponse.BodyHandlers.ofString());
System.out.println("Status: " + getRes.statusCode());
System.out.println("Body: " + getRes.body());
// 2. Asynchronous POST JSON request
String payload = "{\"roll\":101,\"name\":\"Sita\"}";
HttpRequest postReq = HttpRequest.newBuilder()
.uri(URI.create("https://api.example.edu/students"))
.header("Content-Type", "application/json")
.POST(HttpRequest.BodyPublishers.ofString(payload))
.build();
client.sendAsync(postReq, HttpResponse.BodyHandlers.ofString())
.thenApply(HttpResponse::statusCode)
.thenAccept(code -> System.out.println("Created status: " + code))
.join();
}
}3.2.3 Overview of microservices architecture
Microservices architecture structures an application as a collection of small, loosely coupled, independently deployable services organized around specific business capabilities (e.g., UserService, CourseService, PaymentService), rather than one single unified codebase (monolith).
| Aspect | Monolithic Architecture | Microservices Architecture |
|---|---|---|
| Deployment Unit | Single WAR/JAR artifact for the entire app | Each service built, deployed, and versioned independently |
| Data Storage | Single shared relational database | Database-per-service (loose data coupling) |
| Scaling | Must scale the entire application together | Scale only bottleneck services independently |
| Communication | Fast in-process method calls | Network calls (REST/HTTP, gRPC, message brokers) |
| Fault Isolation | A memory leak or crash can bring down all modules | Faults are isolated (using timeouts and circuit breakers) |
| Operational Complexity | Low initially; simple local debugging | Higher (requires service discovery, distributed tracing, API gateway) |
Core Microservice Building Blocks
- API Gateway: Single entry point for clients that handles routing, authentication/JWT verification, rate limiting, and request aggregation.
- Service Discovery: Registry (such as Eureka or Kubernetes DNS) enabling services to locate dynamic network addresses of peer instances.
- Resilience (Circuit Breaker & Retries): Prevents cascading failures when a downstream service slows down or crashes (e.g., Resilience4j).
- Centralized Configuration & Observability: Externalized configs, aggregated logs, metrics, and distributed trace IDs across service hops.
Worked Examples
3.1
A banking service transfers 1,500 units from Account 101 (initial balance 5,000) to Account 202 (initial balance 2,200) using JDBC transactions (setAutoCommit(false)). Two PreparedStatement updates run:
UPDATE accounts SET balance = balance - ? WHERE id = ? AND balance >= ?UPDATE accounts SET balance = balance + ? WHERE id = ?
Trace the executeUpdate() return counts, commit()/rollback() decisions, and final account balances for:
- (a) Transferring
1,500(sufficient funds), and - (b) Transferring
6,000(insufficient funds).
Write the transactional JDBC skeleton:
conn.setAutoCommit(false);
try (
PreparedStatement debit = conn.prepareStatement(
"UPDATE accounts SET balance = balance - ? WHERE id = ? AND balance >= ?");
PreparedStatement credit = conn.prepareStatement(
"UPDATE accounts SET balance = balance + ? WHERE id = ?")
) {
debit.setDouble(1, amount);
debit.setInt(2, fromId);
debit.setDouble(3, amount);
int debitedRows = debit.executeUpdate();
if (debitedRows != 1) {
throw new IllegalStateException("Insufficient balance or account missing");
}
credit.setDouble(1, amount);
credit.setInt(2, toId);
int creditedRows = credit.executeUpdate();
if (creditedRows != 1) {
throw new IllegalStateException("Target account missing");
}
conn.commit();
} catch (Exception ex) {
conn.rollback();
throw ex;
}Trace (a) for amount = 1500:
debit.executeUpdate(): Account101has5000 >= 1500, so1row updates (5000 - 1500 = 3500).debitedRows = 1.credit.executeUpdate(): Account202updates (2200 + 1500 = 3700).creditedRows = 1.conn.commit()persists both changes atomically. Total system balance stays3500 + 3700 = 7200(5000 + 2200).
Trace (b) for amount = 6000 (assuming initial balances 5000 and 2200):
debit.executeUpdate(): Conditionbalance >= 6000fails (5000 < 6000), sodebitedRows = 0.- The
if (debitedRows != 1)guard throwsIllegalStateException, jumping intocatchand invokingconn.rollback(). - Neither account is modified: balances remain
5000and2200.
For (a) amount = 1500, both executeUpdate() calls return 1, conn.commit() succeeds, and final balances are 3,500 (101) and 3,700 (202). For (b) amount = 6000, debit.executeUpdate() returns 0, triggering conn.rollback() so balances remain unchanged at 5,000 and 2,200.
3.2
A Java 11+ HttpClient must fetch 240 student records from a paginated REST API (GET /api/students?page=P&size=40), where each HTTP call takes 60 ms round-trip time.
- How many HTTP GET requests are required?
- Compare the total network wait time if requests are sent sequentially with
client.send(...)versus concurrently usingclient.sendAsync(...)withCompletableFuture.allOf(...)(assuming the server handles 6 concurrent requests without queuing delay).
Compute the number of pages needed: pages = ceil(240 / 40) = 6 HTTP GET requests (page = 0, 1, 2, 3, 4, 5).
Calculate sequential versus concurrent wait time:
- Sequential (
client.send): Each request blocks until its response arrives before the next starts, so total time =6 * 60 ms = 360 ms. - Concurrent (
client.sendAsync): All 6 requests are dispatched concurrently and awaited withCompletableFuture.allOf(...), taking roughly the latency of the slowest single round-trip:1 * 60 ms = 60 ms(a360 / 60 = 6xspeedup, saving300 ms).
6 HTTP GET requests are required. Sequential send takes 360 ms, whereas concurrent sendAsync completes in 60 ms (saving 300 ms).
3.3
How should a student answer the past exam question: "Explain how SQL injection happens in JDBC Statement and how PreparedStatement prevents it, with code"?
Show the Vulnerable Statement Pattern First: Write the concatenated SQL query "SELECT * FROM users WHERE email='" + email + "'" and plug in a malicious input (x' OR '1'='1), showing the resulting SQL string where '1'='1' alters the WHERE logic to return every user row.
Explain PreparedStatement Mechanics and Write the Fix: State that PreparedStatement uses ? placeholders (WHERE email = ?) so the SQL syntax tree is parsed/compiled before user data is bound via setString(1, email). Even if the input contains ' OR '1'='1, the database compares it strictly as a literal string value rather than SQL code.
A full-mark answer demonstrates a concrete string-concatenation SQL injection payload on Statement, contrasts it with parameterized PreparedStatement (? + setString), and explains that separating query structure from bound data prevents user input from altering SQL syntax.
Exercises
Practice Exercises — Database and Web Communication
Describe the four types of JDBC drivers and explain why Type 4 (Pure-Java Thin Driver) is the standard choice for modern Java applications.
Summary of the Four JDBC Driver Types:
- Type 1 (JDBC-ODBC Bridge): Translates JDBC calls to ODBC calls and then native DB calls (slow, OS-dependent; removed in Java 8).
- Type 2 (Native-API Driver): Uses vendor-specific native C/C++ libraries on the client machine.
- Type 3 (Network Protocol / Middleware Driver): Communicates with a middle-tier server that translates calls into database-specific protocols.
- Type 4 (Pure-Java / Thin Driver): Converts JDBC calls directly into the database engine's network protocol in 100% Java.
Why Type 4 is Preferred: It requires no native OS libraries or middleware servers, ships as a single cross-platform .jar dependency (Maven/Gradle), integrates seamlessly with connection pools, and delivers optimal performance directly over TCP sockets.
The four JDBC driver types are Type 1 (JDBC-ODBC bridge), Type 2 (Native-API), Type 3 (Network Protocol middleware), and Type 4 (Pure-Java Thin). Type 4 is standard because it is 100% platform-independent Java, requires no native client binaries or extra middleware, and communicates directly over the database's TCP protocol.
Write a complete JDBC method transferSalaryUpdate(Connection conn, int deptId, double bonus) that uses PreparedStatement, disables auto-commit, increases salaries by bonus for all employees in deptId, commits if at least one row is updated, and rolls back if zero rows match or an SQLException occurs.
Transactional PreparedStatement Method:
public static int transferSalaryUpdate(Connection conn, int deptId, double bonus) throws SQLException {
String sql = "UPDATE employees SET salary = salary + ? WHERE dept_id = ?";
conn.setAutoCommit(false);
try (PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setDouble(1, bonus);
ps.setInt(2, deptId);
int updated = ps.executeUpdate();
if (updated == 0) {
conn.rollback();
} else {
conn.commit();
}
return updated;
} catch (SQLException ex) {
conn.rollback();
throw ex;
} finally {
conn.setAutoCommit(true);
}
}Key Safeguards Explained: try-with-resources closes the PreparedStatement automatically, conn.setAutoCommit(false) groups the update into an explicit transaction, conn.rollback() undoes partial work on zero matches or errors, and finally restores default auto-commit state if the Connection returns to a pool.
Disable auto-commit (conn.setAutoCommit(false)), bind parameters with PreparedStatement, call executeUpdate(), call conn.commit() when updated > 0 or conn.rollback() on zero rows/exceptions, and restore autoCommit in finally.
Explain the TCP client-server socket communication lifecycle in Java using ServerSocket and Socket, and state why a real server spawns a thread (or uses a thread pool) for each accepted client.
Server and Client Lifecycle:
- Server Startup: Creates
ServerSocket(port)bound to a local port and callsaccept(), which blocks until a client connects. - Client Connection: Client creates
new Socket(serverHost, port), completing the TCP three-way handshake. - Stream Exchange: Both sides obtain
getInputStream()andgetOutputStream()(often wrapped inBufferedReaderandPrintWriter) to exchange messages. - Teardown: Closing the
Socketterminates the client session whileServerSocketcontinues listening.
Need for Per-Client Concurrency: If the server handles reading and writing on the same thread that calls accept(), a slow or long-lived client blocks all other incoming clients from connecting. Dispatching each Socket to a separate thread or thread pool allows multiple clients to be served concurrently.
A TCP server binds with ServerSocket(port) and blocks on accept() to obtain a Socket per client, while the client initiates via new Socket(host, port) and exchanges data over input/output streams. Handling each client on a worker thread/pool prevents one client from blocking subsequent accept() calls.
A client uses Java 11+ HttpClient to fetch 450 records from a REST API whose maximum page size is 50 records per request, where each HTTP request has a round-trip latency of 80 ms.
- How many requests are needed to retrieve all 450 records?
- Compute the total latency using sequential
client.send(...)calls versus concurrentclient.sendAsync(...)calls (assuming all page requests execute in parallel in 80 ms), and compute the time saved.
Calculate Request Count: totalRequests = 450 / 50 = 9 GET requests.
Compare Sequential vs. Concurrent Execution:
- Sequential (
send):9 * 80 ms = 720 ms. - Concurrent (
sendAsync): All 9 requests run concurrently and complete in80 ms. - Time Saved:
720 ms - 80 ms = 640 ms.
9 requests are required. Sequential send takes 720 ms, concurrent sendAsync takes 80 ms, and concurrency saves 640 ms.
Compare Monolithic and Microservices architectures across deployment, data management, scaling, and fault isolation, and explain the roles of an API Gateway and Circuit Breaker in microservices.
Monolith vs. Microservices:
- Deployment: Monoliths ship as one single unit; microservices deploy independently per service.
- Data: Monoliths share one schema; microservices follow database-per-service for loose coupling.
- Scaling: Monoliths scale the full app; microservices scale only high-load services.
- Fault Isolation: An unhandled crash in a monolith can halt the entire app; in microservices, failures are contained to the failing service.
API Gateway and Circuit Breaker:
- API Gateway: Acts as the unified entry point handling request routing, authentication, rate limiting, and TLS termination so clients do not call internal services directly.
- Circuit Breaker: Monitors downstream service calls; if failures exceed a threshold, it opens the circuit and returns fast fallbacks instead of letting threads pile up and cause cascading timeouts.
Monoliths bundle all modules and one shared database into a single deployable unit, whereas microservices split business capabilities into independently deployed and scaled services with private databases. An API Gateway centralizes routing and security at the edge, while a Circuit Breaker prevents cascading failures by failing fast when a downstream service becomes unhealthy.