PadhaikoAll subjects

Database and Web Communication

Java Programming (ENCT 327)

Open in the Padhaiko app

3.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:

JDBC Layered ArchitectureText
+-----------------------+
|   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  |
+-----------------------+

The Four Types of JDBC Drivers

Classification of JDBC Drivers (Types 1–4)
Driver TypeNameMechanismAdvantages & Status
Type 1JDBC-ODBC BridgeTranslates JDBC calls into ODBC calls, then native DB callsSlow, platform-dependent; removed in Java 8
Type 2Native-API DriverConverts JDBC calls to vendor's native C/C++ client libraryFaster than Type 1, but requires native binaries on every host
Type 3Network Protocol DriverSends JDBC calls over network to a middleware server that translates to DB protocolFlexible for multi-DB routing; requires maintaining middleware
Type 4Pure-Java (Thin) DriverSpeaks the database's TCP wire protocol directly in 100% JavaIndustry 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:

  1. 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.
  2. 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.
  3. Execute SQL:
    • executeQuery() for SELECT statements — returns a ResultSet.
    • executeUpdate() for INSERT, UPDATE, DELETE, or DDL — returns an int row count affected.
    • execute() for arbitrary SQL when the return kind is unknown at compile time.
  4. Process the ResultSet: A ResultSet maintains a cursor initially positioned before the first row. Call while (rs.next()) to advance row by row and extract typed columns (rs.getInt("id"), rs.getString("name"), rs.getDouble("gpa")).
  5. Manage Transactions and Close Resources: By default, conn.getAutoCommit() is true (every SQL statement commits immediately). For multi-step atomic operations (such as bank transfers), disable auto-commit (conn.setAutoCommit(false)), execute all updates, call conn.commit() on success, or conn.rollback() inside catch on failure.
Java
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

Definition

Why PreparedStatement Beats Plain Statement

  1. Prevents SQL Injection: Concatenating untrusted user input into a Statement string lets attackers alter query structure. For example, "SELECT * FROM users WHERE username = '" + userInput + "'" with input ' OR '1'='1 turns the condition into always true, exposing all rows. PreparedStatement sends 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.
  2. 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.
  3. Batch Execution: Multiple inserts/updates can be queued via ps.addBatch() and transmitted in bulk with ps.executeBatch(), drastically reducing network round-trips.
Java
// 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.

Java
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:

  1. HttpClient: Configured once via HttpClient.newBuilder().connectTimeout(Duration.ofSeconds(5)).build() and reused across requests (thread-safe).
  2. HttpRequest: Built with .uri(...), .timeout(...), .header(...), and HTTP method (.GET(), .POST(BodyPublishers.ofString(json)), .PUT(...), .DELETE()).
  3. HttpResponse<T>: Holds .statusCode(), .headers(), and .body() decoded by a BodyHandler (e.g., HttpResponse.BodyHandlers.ofString()).
Java
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).

AspectMonolithic ArchitectureMicroservices Architecture
Deployment UnitSingle WAR/JAR artifact for the entire appEach service built, deployed, and versioned independently
Data StorageSingle shared relational databaseDatabase-per-service (loose data coupling)
ScalingMust scale the entire application togetherScale only bottleneck services independently
CommunicationFast in-process method callsNetwork calls (REST/HTTP, gRPC, message brokers)
Fault IsolationA memory leak or crash can bring down all modulesFaults are isolated (using timeouts and circuit breakers)
Operational ComplexityLow initially; simple local debuggingHigher (requires service discovery, distributed tracing, API gateway)

Core Microservice Building Blocks

  1. API Gateway: Single entry point for clients that handles routing, authentication/JWT verification, rate limiting, and request aggregation.
  2. Service Discovery: Registry (such as Eureka or Kubernetes DNS) enabling services to locate dynamic network addresses of peer instances.
  3. Resilience (Circuit Breaker & Retries): Prevents cascading failures when a downstream service slows down or crashes (e.g., Resilience4j).
  4. Centralized Configuration & Observability: Externalized configs, aggregated logs, metrics, and distributed trace IDs across service hops.

Worked Examples

Example 3.1

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:

  1. UPDATE accounts SET balance = balance - ? WHERE id = ? AND balance >= ?
  2. UPDATE accounts SET balance = balance + ? WHERE id = ?

Trace the executeUpdate() return counts, commit()/rollback() decisions, and final account balances for:

Write the transactional JDBC skeleton:

Java
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(): Account 101 has 5000 >= 1500, so 1 row updates (5000 - 1500 = 3500). debitedRows = 1.
  • credit.executeUpdate(): Account 202 updates (2200 + 1500 = 3700). creditedRows = 1.
  • conn.commit() persists both changes atomically. Total system balance stays 3500 + 3700 = 7200 (5000 + 2200).

Trace (b) for amount = 6000 (assuming initial balances 5000 and 2200):

  • debit.executeUpdate(): Condition balance >= 6000 fails (5000 < 6000), so debitedRows = 0.
  • The if (debitedRows != 1) guard throws IllegalStateException, jumping into catch and invoking conn.rollback().
  • Neither account is modified: balances remain 5000 and 2200.
Answer:

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.

Example 3.2

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.

  1. How many HTTP GET requests are required?
  2. Compare the total network wait time if requests are sent sequentially with client.send(...) versus concurrently using client.sendAsync(...) with CompletableFuture.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 with CompletableFuture.allOf(...), taking roughly the latency of the slowest single round-trip: 1 * 60 ms = 60 ms (a 360 / 60 = 6x speedup, saving 300 ms).
Answer:

6 HTTP GET requests are required. Sequential send takes 360 ms, whereas concurrent sendAsync completes in 60 ms (saving 300 ms).

Example 3.3

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.

Answer:

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

Question 1

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:

  1. Type 1 (JDBC-ODBC Bridge): Translates JDBC calls to ODBC calls and then native DB calls (slow, OS-dependent; removed in Java 8).
  2. Type 2 (Native-API Driver): Uses vendor-specific native C/C++ libraries on the client machine.
  3. Type 3 (Network Protocol / Middleware Driver): Communicates with a middle-tier server that translates calls into database-specific protocols.
  4. 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.

Answer:

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.

Question 2

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:

Java
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.

Answer:

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.

Question 3

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:

  1. Server Startup: Creates ServerSocket(port) bound to a local port and calls accept(), which blocks until a client connects.
  2. Client Connection: Client creates new Socket(serverHost, port), completing the TCP three-way handshake.
  3. Stream Exchange: Both sides obtain getInputStream() and getOutputStream() (often wrapped in BufferedReader and PrintWriter) to exchange messages.
  4. Teardown: Closing the Socket terminates the client session while ServerSocket continues 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.

Answer:

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.

Question 4

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.

  1. How many requests are needed to retrieve all 450 records?
  2. Compute the total latency using sequential client.send(...) calls versus concurrent client.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 in 80 ms.
  • Time Saved: 720 ms - 80 ms = 640 ms.
Answer:

9 requests are required. Sequential send takes 720 ms, concurrent sendAsync takes 80 ms, and concurrency saves 640 ms.

Question 5

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.
Answer:

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.

← Java I/O and Data HandlingSpring Boot and Modern Web Development (part 1: 4.1–4.2) →