JDBC and Database Connectivity

🎯Learning Objectives

• JDBC is the Java API boundary for database access.
• You will connect a matched driver and configured data source to prepared statements, result sets, update counts and transaction decisions.
• The examples are backend Java fragments; uploading them to a static frontend does not execute SQL.

• The lab transfers fictional practice tokens between two local learners.
• It compares one transaction with statement-by-statement auto-commit and shows a failure after the debit.
• This synchronous JavaScript model runs no Java, database, driver or network connection.

🔌API, Driver and Database Have Different Jobs

• JDBC defines Java interfaces such as Connection, PreparedStatement and ResultSet.
• A compatible JDBC driver implements the database-specific communication.
• The database stores data and executes supported SQL; JDBC is not itself a database engine.

• A project needs the driver dependency, a reachable database and correct server configuration.
• A java.sql import does not install a driver or create a schema.
• A JDBC URL format and supported features depend on the chosen database and driver.

• Modern drivers can be discovered through service-provider loading when installed correctly.
• Older examples may explicitly load a driver class.
• Follow the actual driver’s setup rather than assuming one class name works for every product.

This lesson adds frontend teaching files only. No backend database or credential is created by the patch.

🧰DriverManager and DataSource

• DriverManager can open a connection using a suitable JDBC URL and connection properties.
• DataSource provides a connection-factory abstraction that application configuration can supply, including pool-backed implementations.
• They are different entry points into the same JDBC work.

• In a web application, a configured data source keeps connection setup separate from controller and repository logic.
• Store credentials in protected backend configuration, not browser JavaScript, a public ZIP, source comments or a JSP page.

• javax.sql.DataSource remains a Java platform API.
• Modern jakarta.servlet imports do not require mechanically renaming it to jakarta.sql.
• A data source abstraction does not guarantee that pooling has been configured.

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;

// dataSource is supplied by protected backend configuration.
try (Connection con = dataSource.getConnection()) {
    // Prepare and execute database work here.
}

• A pooled connection close normally releases the logical connection back to its pool.
• Check the pool’s contract for validation, reset and failure disposal; do not assume every DataSource has identical behavior.

📦Connection Ownership and Resource Lifetimes

• A Connection represents a database session.
• Statements and result sets are associated with it and have their own resource lifetimes.
• Close the resources your code owns instead of depending on garbage collection or a final servlet shutdown callback.

• Try-with-resources closes resources in reverse declaration order and records additional closing failures as suppressed exceptions.
• Nest a result set inside the statement and connection lifetime so the mapper cannot read rows from an already closed resource.

• Do not keep one mutable Connection as a shared servlet field for all callers.
• Request handling, transaction ownership and thread safety require deliberate boundaries.
• A repository receiving a caller-owned connection should not close it unexpectedly.

• The lab exposes mock opened/closed counts.
• These are teaching counters, not evidence of real connection-pool, driver or cleanup behavior.

🔑Prepared Statements Bind Values

• Create a prepared statement with a fixed SQL structure and bind values to question-mark placeholders.
• JDBC parameter positions start at one.
• Choose suitable setters for the database/application type and bind every required parameter.

• A bound value remains a value.
• It does not become a table name, column name, sort direction or arbitrary SQL fragment.
• If a query supports dynamic structure, choose that structure from an application-controlled allowlist rather than treating a placeholder as an identifier.

• Prepared statements address separation of values from SQL syntax.
• They do not replace input validation, authorization, transaction rules or safe output rendering.

String sql = "SELECT learner_id, tokens FROM practice_tokens "
           + "WHERE learner_id = ?";
try (java.sql.PreparedStatement ps = con.prepareStatement(sql)) {
    ps.setString(1, acceptedLearnerId);
    try (java.sql.ResultSet rs = ps.executeQuery()) {
        // Map accepted rows here.
    }
}

• The snippet assumes an open caller-owned con and a validated identifier.
• It is not a complete repository or database schema.
• The lab shows fixed SQL plus a separate binding panel; it never interpolates submitted text into executable SQL.

📤Choose the Execution Method

executeQuery is used when the statement produces a result set. executeUpdate returns an update count for relevant data-changing statements; an operation that changes no matching row can legitimately return zero. execute handles cases where the result kind needs to be inspected.

• Do not read executeUpdate’s return value as an automatically generated row ID.
• Generated keys use their own statement configuration and result retrieval, with driver/database support.
• Multiple results and batches have additional API contracts.

• For the token transfer, a conditional debit must affect exactly one row.
• A zero debit count means this operation was not accepted, such as insufficient tokens or a missing source; the credit must not run.

int changed = debit.executeUpdate();
if (changed != 1) {
    throw new java.sql.SQLException("Debit was not accepted");
}
// Only then attempt the paired credit.

• This application expects one affected row.
• Other operations need their own deliberate count contract rather than assuming every successful update returns one.

📋ResultSet Cursors and Row Mapping

• A ResultSet cursor begins before the first row.
• Call next to advance and test whether a row exists before reading columns.
• A forward-only result is not a collection that you can randomly index or repeatedly rewind.

• Read columns by an intentional label or index and map them into an application model.
• Validate the required shape, ranges and nullability.
• Do not pass a live result set to the JSP and let the view depend on a database cursor remaining open.

• Primitive getters need attention to SQL NULL: getInt can return zero for null and wasNull can distinguish it immediately after the read.
• A zero count and an absent database value may have different meanings.

try (java.sql.ResultSet rs = ps.executeQuery()) {
    while (rs.next()) {
        String id = rs.getString("learner_id");
        int tokens = rs.getInt("tokens");
        if (rs.wasNull()) {
            throw new java.sql.SQLException("Missing required tokens");
        }
        // Check id and tokens, then create an application value.
    }
}

• Use a nullable Java representation when null is allowed by the contract.
• For exact monetary decimal values, choose a suitable decimal database/Java type rather than assuming binary floating-point is exact.
• The lab uses bounded integer practice tokens, not money.

🧱Repository Boundaries and Schema Assumptions

• A repository or DAO contains SQL details and row mapping behind an application boundary.
• A service coordinates operations, and a controller validates request values and chooses the response.
• A JSP should render the accepted model rather than run SQL or own transaction cleanup.

• The transfer illustration assumes a practice_tokens table with a unique learner_id and non-negative integer tokens.
• Database constraints, authorization and transaction support must exist in the real implementation; the local model does not create those protections on a server.

• The toy fixture starts with A = 12 and B = 4.
• Learner identifiers are exactly A or B.
• Amount is a canonical integer string from 1 to 9, and source must differ from destination.
• These are teaching rules for a tiny model.

Illustrative SQL schema assumptions:
practice_tokens(learner_id PRIMARY KEY, tokens NOT NULL)
tokens must remain non-negative

Application operation:
validate → acquire owned connection → debit → credit
→ commit/rollback → close → choose response

• DDL syntax and constraint enforcement depend on the database.
• Level 26 develops database design and SQL further; this lesson focuses on the Java access boundary.

⚡Auto-Commit and Partial Operations

• A newly created JDBC connection defaults to auto-commit.
• In that mode, statement completion commits its transaction; completion rules depend on the statement/result type.
• Two successful updates are not automatically one atomic application operation.

• If a debit commits and the following credit fails, the earlier committed debit is not undone by rolling back a later transaction.
• The caller has a partial outcome and needs a deliberate recovery strategy; retrying blindly can make the situation worse.

• The lab’s auto-commit mode publishes the debit immediately.
• A simulated credit error or zero-row credit then returns 500 while keeping the partial debit visible.
• The earlier committed snapshot is not silently restored.

• The model simplifies two update statements and their outcomes.
• It does not represent all JDBC result-completion rules or distributed failures.

🔄One Transaction: Commit or Roll Back

• Disable auto-commit deliberately when one application operation needs multiple statements in a transaction.
• Execute both updates on the same owned connection and call commit only after both satisfy the application’s update-count contract.

• On a failure before commit, attempt rollback and propagate the failure.
• Rollback can itself fail.
• Preserve the original error and record the cleanup failure instead of pretending the operation is known to be undone.

• The lab’s manual transaction holds a separate working copy.
• A successful credit publishes both updates together; a known simulated credit failure discards the working copy and leaves the prior committed snapshot unchanged.

// Illustrative owned-connection transaction fragment.
// Inputs were validated; dataSource and statement helper are supplied.
try (java.sql.Connection con = dataSource.getConnection()) {
    con.setAutoCommit(false);
    try {
        // Execute debit and credit on THIS connection.
        // Require the expected affected-row count for each.
        performTransferStatements(con, fromId, toId, amount);
        con.commit();
    } catch (java.sql.SQLException | RuntimeException failure) {
        try {
            con.rollback();
        } catch (java.sql.SQLException rollbackFailure) {
            failure.addSuppressed(rollbackFailure);
        }
        throw failure;
    }
}

• The helper is defined by the surrounding repository; this is a fragment, not a complete compilable application.
• It owns the acquired connection and closes it after resolution.
• Pool state reset/disposal belongs to the configured connection contract.
• Do not re-enable auto-commit to hide an unresolved transaction: changing that mode can commit outstanding work.

🧮Conditional Updates and Count Checks

• The debit statement can require both a matching source identifier and enough tokens in its WHERE clause.
• That avoids a simplistic read-then-write check in application code, but real concurrency, isolation and database behavior still need testing.

• Bind the amount twice in the debit: once for subtraction and once for the minimum check.
• Then bind the credit amount and destination separately.
• Only an accepted debit allows the credit to proceed.

• A credit affecting zero rows is not a successful paired transfer in this contract.
• In a manual transaction, treat it as a failure and roll back the debit.
• In auto-commit, the already committed debit remains partial.

UPDATE practice_tokens
SET tokens = tokens - ?
WHERE learner_id = ? AND tokens >= ?

Debit bindings: 1 = amount, 2 = fromId, 3 = amount

UPDATE practice_tokens
SET tokens = tokens + ?
WHERE learner_id = ?

Credit bindings: 1 = amount, 2 = toId

• The local model checks these decisions synchronously against its own integers.
• It does not prove atomic SQL predicates, lock behavior or concurrent transfers in a real database.

🧵Isolation, Savepoints and Concurrent Work

• A transaction boundary and a transaction isolation level solve related but different problems.
• Isolation controls which effects of concurrent work can be observed.
• Supported levels and behavior vary with the driver/database; do not infer them from a synchronous browser demonstration.

• Some drivers/databases support savepoints for partial rollback within a transaction.
• A savepoint is not a separate permanent commit and is not a universal substitute for rolling back an operation whose invariants failed.

• Keep transactions short and avoid waiting for user interaction while holding database resources.
• Design a consistent update order and a deliberate retry policy for recognized transient conflicts.
• A retry must consider whether an earlier attempt actually committed.

The lab has no locks, threads, savepoints, deadlocks or retries. Those runtime behaviors remain separate integration checks.

⚠️Errors and Uncertain Outcomes

• SQLException provides database failure information, including SQLState, a vendor code and chained exceptions.
• Backend diagnostics can retain those details; public responses should avoid leaking credentials, SQL internals or production stack traces.

• Distinguish validation rejection, unavailable connection, unacceptable update count and execution failure.
• A stable error response should not claim success merely because the first statement ran.
• In this lab an insufficient debit returns 409, an unavailable fixture 503 and a failed credit 500.

• A connection loss or timeout around commit can leave the client unsure whether the database committed.
• Do not claim rollback always proves nothing happened.
• A real system needs a recovery/idempotency strategy appropriate to its operation.

• The lab deliberately models only known outcomes before commit.
• Its failure fixture always knows the credit did not apply.
• It does not simulate commit ambiguity or rollback failure.

📦Pools, Configuration and Deployment

• Configure a driver and data source on the backend, with appropriate credentials, network access, limits and connection validation.
• A pool can reuse physical connections, but exhausted pools, stale sessions and leaked resources still need monitoring.

• Borrow a connection for its intended operation and resolve the transaction before returning it.
• The pool must have a deliberate reset/disposal strategy so a later borrower does not inherit unexpected transaction state.

• Do not copy a local database URL or password into the public frontend to make browser JDBC work.
• Browser code calls an authorized backend API; that backend owns JDBC work and decides what accepted model to expose.

This course patch does not modify the Render tracers or add a JDBC backend. Its resource counters are local model data only.

🧪Test Logic and Real JDBC Separately

• Test input normalization, update-count decisions, transaction outcome and resource ownership with controlled fixtures.
• Then test the actual Java component with the chosen database/driver, schema constraints, transaction support, isolation and error handling.

• For paired transfers, test successful commit, insufficient source, zero-row credit and an execution failure after the debit.
• Check the persisted database state after each case rather than trusting a success message or log line alone.

• A JavaScript token model can verify its own snapshots, invariants and reset behavior.
• It cannot compile Java, validate a driver, open JDBC connections or demonstrate real rollback, concurrency and pool cleanup.

• Use the visualizer for an accepted manual transaction, then change the lab’s mode and credit outcome to predict the difference.
• Level 26 continues with Databases and SQL for Web Applications.

🧪Premium Interactive — Commit, Rollback and Partial Transfer

• Transfer fictional integer practice tokens between A and B.
• Initially A has 12 and B has 4.
• This is a synchronous JavaScript model, not Java, JDBC, SQL execution, a database or a financial account.
• Results persist only in this tab’s in-memory fixture until Reset.

• POST /transfer accepts exactly from, to and amount as scalar strings.
• IDs must be A/B and distinct; amount must be exactly one canonical digit 1–9.
• Rejection occurs before mock connection acquisition.
• The fixed SQL and bindings are displayed separately.

• Manual transaction: stage debit and credit, then publish both together.
• A known credit error or zero-row credit rolls back the staged debit.
• Auto-commit: publish each statement; a failed credit leaves the earlier debit committed.
• Insufficient source returns 409 and no credit runs.
• The unavailable fixture returns 503 before opening resources.

• Opened/closed resource counts belong to the current demonstration run, not a real driver or pool.
• The working snapshot shows attempted changes, while committed state shows what remains afterward.
• No concurrency, rollback failure or uncertain commit is simulated.

No accepted bindings yet.

No operation yet.

No transfer attempted.

No resources opened.

No response yet.

🛠️Debugging — Check Counts, State and Ownership

• If the debit succeeds but tokens disappear on a credit failure, check whether auto-commit published it independently.
• If a missing destination still reports success, inspect the credit update count.
• If no source was affected, stop before executing the paired credit.

• If database connections run out, inspect borrowed connections, unclosed statements/results, long transactions and pool settings.
• A browser teaching counter cannot diagnose a real leak.

• If a timeout occurs around commit, report the uncertainty and use a deliberate verification/recovery policy.
• Do not infer that the database rolled back simply because the client received an exception.

💬Interview Questions — Flip to Explain

❓MCQ Practice — Explain JDBC Decisions

💻Extra Practice — Predict Persisted State

📌Quick Revision

📖Glossary — Flip to Learn

🏆Final Challenge — Specify an Owned JDBC Operation

• Design a backend operation that validates a source, destination and bounded integer amount, acquires an owned connection and executes two prepared updates.
• Require the expected affected-row count for each before committing.
• Keep identifiers and values separate from SQL structure.

• Define the transaction boundary, rollback/error propagation and resource ownership.
• Preserve the original failure if rollback or close also fails.
• Explain why auto-commit cannot make two independent updates one atomic operation.

• Test actual persisted state for success, zero debit, zero credit and a known execution failure.
• Specify separate handling for uncertain commit outcomes and real concurrency.
• A JSP renders accepted results; it does not own SQL or credentials.

• Success criterion: trace the bindings, row counts, committed state and cleanup for every outcome.
• Next: Level 26 — Databases and SQL for Web Applications.