JDBC, Transactions, Connection Pooling and DAO Design

JDBC driver

A JDBC driver implements the standard database interfaces and translates JDBC calls and wire values for a particular database protocol.

Connection Statement and ResultSet

Connection represents a database session, Statement sends SQL and ResultSet provides cursor-based access to returned rows.

PreparedStatement

PreparedStatement sends SQL with typed parameter bindings, improving safety and often plan reuse while keeping data separate from SQL syntax.

parameterized SQL

SQL containing placeholders whose values are bound separately, preventing data from being interpreted as SQL syntax.

transaction isolation

Transaction isolation defines which effects of concurrent transactions may be observed and therefore which anomalies the database prevents.

commit rollback and savepoint

Commit makes a transaction durable, rollback undoes work and a savepoint marks a position for partial rollback.

batch operation

A group of similar database commands submitted together to reduce communication overhead while preserving per-command results.

connection pool

A connection pool maintains reusable physical database connections and lends bounded logical connections to callers.

DAO pattern

A Data Access Object places persistence operations behind a domain-oriented interface so callers do not depend directly on SQL or connection handling.

resource cleanup

Deterministic closing of files, streams, sockets and database objects, normally through try-with-resources.

JDBC architecture

JDBC consists mainly of interfaces in java.sql and javax.sql. A database vendor supplies a driver that implements those interfaces and translates calls into the database's protocol.

Application
    │ JDBC interfaces
DriverManager or DataSource
    │ vendor driver
Relational database

Core types include:

• Connection: a database session and transaction context;
• Statement: execution of SQL text without parameters;
• PreparedStatement: parameterized SQL operation;
• CallableStatement: stored procedure or function call;
• ResultSet: cursor over query output;
• SQLException: database-access failure with vendor and SQL-state details.

JDBC is synchronous. A call may block while waiting for a connection, network response, lock or database execution.

Auto-commit and transaction boundaries

Connections ordinarily start in auto-commit mode. Each completed SQL statement forms its own transaction. Disable auto-commit when several statements must succeed or fail together:

try (Connection connection = dataSource.getConnection()) {
    connection.setAutoCommit(false);
    try {
        debit(connection, fromId, amount);
        credit(connection, toId, amount);
        connection.commit();
    } catch (Exception ex) {
        connection.rollback();
        throw ex;
    }
}

Both operations must use the same Connection. Two separate auto-commit connections do not form one atomic transfer. The transaction boundary should match the business invariant and the service or use-case layer usually owns it when several DAO calls participate.

Commit makes successful work permanent. Rollback discards changes since transaction start or the chosen savepoint.

Opening connections

DriverManager can open a direct connection:

String url = "jdbc:postgresql://localhost:5432/college";
try (Connection connection =
         DriverManager.getConnection(url, user, password)) {
    // use connection
}

Enterprise applications normally obtain connections from a DataSource. It supports configuration outside business code and works naturally with pooling and managed environments.

Connection creation is expensive because it can involve authentication, network setup and server resources. Do not open a new physical connection for every small SQL statement when a pool is available. Do not store one Connection globally across concurrent requests; a transaction belongs to one controlled unit of work.

Savepoints

A savepoint marks a position inside a transaction:

Savepoint beforeOptionalWork = connection.setSavepoint();
try {
    insertOptionalDetails(connection);
} catch (SQLException ex) {
    connection.rollback(beforeOptionalWork);
}

Partial rollback is correct only when the remaining transaction still satisfies the business contract. It should not conceal a failure that makes the overall operation invalid. Database and driver support can vary and savepoints consume resources until released or the transaction ends.

Statement choices

Statement executes fixed SQL that contains no input values:

try (Statement statement = connection.createStatement();
     ResultSet rs = statement.executeQuery(
         "SELECT department_id, name FROM department")) {
    // traverse rows
}

PreparedStatement defines placeholders and binds values:

String sql = "SELECT id, name FROM student WHERE course_id = ?";
try (PreparedStatement ps = connection.prepareStatement(sql)) {
    ps.setInt(1, courseId);
    try (ResultSet rs = ps.executeQuery()) {
        // traverse rows
    }
}

CallableStatement invokes stored routines and can register output parameters. PreparedStatement should be the default for SQL containing application values because it separates data from SQL grammar and often supports efficient repeated execution.

Resource ownership

Connection, Statement and ResultSet implement AutoCloseable. Nest scopes in ownership order:

try (Connection connection = dataSource.getConnection();
     PreparedStatement ps = connection.prepareStatement(sql)) {

ps.setLong(1, id);
    try (ResultSet rs = ps.executeQuery()) {
        return rs.next()
                ? Optional.of(mapStudent(rs))
                : Optional.empty();
    }
}

Resources close in reverse order: result set, statement, then connection. Closing a statement normally closes its current result set and closing a connection closes associated handles, but explicit nested scopes document lifetime and handle exceptions predictably.

A DAO must not return a ResultSet whose connection is about to close. Map rows to domain or data-transfer objects inside the resource scope.

Robust rollback structure

Rollback can itself fail. Preserve the original failure and attach rollback failure rather than erasing the main cause:

SQLException primary = null;
try {
    performWork(connection);
    connection.commit();
} catch (SQLException ex) {
    primary = ex;
    try {
        connection.rollback();
    } catch (SQLException rollbackFailure) {
        ex.addSuppressed(rollbackFailure);
    }
    throw ex;
}```

With pooled connections, restore altered state if the pool does not guarantee reset. Auto-commit, isolation, read-only status, catalog, schema and session settings must not leak to the next borrower. A mature pool normally validates and resets connections, but application code must follow its contract.

Never catch an exception, roll back and then report success. Transaction failure belongs in the returned outcome or propagated exception.
## Batch operations

Batching can reduce client–server round trips:

```java
String sql = "INSERT INTO mark(student_id, score) VALUES (?, ?)";
try (PreparedStatement ps = connection.prepareStatement(sql)) {
    for (Mark mark : marks) {
        ps.setLong(1, mark.studentId());
        ps.setInt(2, mark.score());
        ps.addBatch();
    }
    int[] counts = ps.executeBatch();
}

Execute a batch within an appropriate transaction. The returned counts may contain update numbers or special constants such as SUCCESS_NO_INFO. A BatchUpdateException can expose partial counts, so decide whether to roll back all work or handle partial success according to the contract.

Very large batches consume memory and may exceed database limits. Process in measured chunks.

Performance and correctness practices

• Select required columns instead of SELECT *.
• Add indexes based on measured query plans and access patterns.
• Avoid N+1 query loops when one joined or batched query can express the work.
• Apply statement/query timeouts where supported.
• Keep transactions short, while still protecting the full invariant.
• Paginate large results and define a stable ordering.
• Stream large values carefully because their resources must remain open during consumption.
• Never share a ResultSet, Statement or ordinary Connection concurrently unless its contract explicitly permits it.

Optimization begins after correctness and measurement. A fast query that commits half a business operation is still wrong.

Connection pooling

A pool maintains physical connections and lends logical handles. Calling close() on a borrowed handle normally returns the physical connection to the pool; failing to close exhausts the pool.

Important pool settings include maximum size, acquisition timeout, idle timeout, maximum lifetime, connection validation and leak detection. A larger pool is not always faster: the database has finite worker, memory and lock capacity.

Size from observed workload, query latency, database capacity and service concurrency. Monitor wait time, active count, timeouts, transaction duration and slow queries.

DAO responsibilities

A Data Access Object hides persistence mechanics behind domain-oriented methods:

interface StudentDao {
    Optional<Student> findById(long id);
    List<Student> findByCourse(long courseId);
    Student save(Student student);
}

The implementation owns SQL, binding, row mapping and JDBC resource handling. The caller works with domain values rather than ResultSet or PreparedStatement.

Keep business rules and multi-repository transaction orchestration in a service layer. A DAO may accept a transaction-scoped Connection internally, but exposing raw handles through the public domain API couples layers and obscures ownership.

JDBC driver types

The traditional driver categories describe how Java reaches the database:

Type	Architecture
Type 1	JDBC translated through a bridge such as the historical JDBC–ODBC bridge
Type 2	Java calls a database-specific native client library
Type 3	Java sends requests to middleware that communicates with databases
Type 4	Pure Java driver speaks the database's wire protocol directly

Type 4 is the normal modern choice because deployment does not require a native bridge or intermediary solely for JDBC translation.

Current drivers usually register through Java's service-provider mechanism when the driver JAR is available. Explicit Class.forName was historically used to force driver loading and may still appear in legacy code.

Parameters and SQL injection

JDBC parameter indexes start at 1. Bind a value with a type-appropriate setter such as setInt, setString, setBigDecimal, setDate or setObject.

String sql = """
        UPDATE account
        SET balance = balance - ?
        WHERE id = ? AND balance >= ?
        """;
try (PreparedStatement ps = connection.prepareStatement(sql)) {
    ps.setBigDecimal(1, amount);
    ps.setLong(2, accountId);
    ps.setBigDecimal(3, amount);
    int changed = ps.executeUpdate();
}

Binding prevents a value from being interpreted as SQL syntax. It also handles quoting and representation correctly. A placeholder cannot ordinarily replace structural SQL such as a table name, column name, keyword or sort direction. When structure must be dynamic, select it from a strict allowlist and construct only that trusted fragment.

Validation remains necessary for business rules, but form validation is not a substitute for parameter binding.

Worked transaction trace

For a transfer of 500:

1. obtain one Connection and disable auto-commit;
2. debit the source with a condition ensuring sufficient funds;
3. require an update count of one;
4. credit the destination and require one updated row;
5. commit only after both checks succeed;
6. on any failure, roll back and propagate the failure;
7. close the Connection handle so the pool can reuse it.

If the debit uses one auto-commit connection and the credit uses another, a crash between them leaves a permanent partial transfer. The transaction boundary, not merely the use of JDBC, provides atomicity.

Generated keys

Request generated keys when inserting rows whose identifiers are created by the database:

try (PreparedStatement ps = connection.prepareStatement(
        "INSERT INTO course(name) VALUES (?)",
        Statement.RETURN_GENERATED_KEYS)) {
    ps.setString(1, name);
    ps.executeUpdate();
    try (ResultSet keys = ps.getGeneratedKeys()) {
        if (!keys.next()) {
            throw new SQLException("No generated key returned");
        }
        long id = keys.getLong(1);
    }
}

Support and exact behaviour vary by driver. Verify the affected row count and the presence of the expected key. Database-specific RETURNING clauses may offer another controlled approach.

Executing SQL

Choose execution methods from the expected result:

• executeQuery() runs a row-producing query and returns ResultSet;
• executeUpdate() runs INSERT, UPDATE, DELETE or suitable DDL and returns an update count;
• execute() handles SQL whose result form may vary and reports whether the first result is a ResultSet.

Always examine an update count when the business operation expects an exact number of rows. An account debit affecting zero rows may mean insufficient balance or a missing account; committing the rest of a transfer would be incorrect.

Database constraints remain the final defence for uniqueness, referential integrity and valid data. Application checks improve messages but may race with other transactions.

SQL NULL and Java values

SQL NULL means unknown or absent, not zero or an empty string. Reference getters commonly return null. Primitive getters return a primitive default, so call wasNull() immediately after the getter when the distinction matters:

int score = rs.getInt("score");
Integer nullableScore = rs.wasNull() ? null : score;

Modern JDBC supports typed getObject calls where the driver provides the mapping:

LocalDate joined = rs.getObject("joined_on", LocalDate.class);

Likewise, use setNull(index, sqlType) or a suitable typed setter for null input. Do not convert SQL NULL into misleading business defaults without a documented policy.

ACID properties

A transaction aims to provide:

• Atomicity: all operations commit or none do;
• Consistency: defined constraints and invariants hold at valid boundaries;
• Isolation: concurrent transactions interact according to an isolation policy;
• Durability: committed changes survive the promised failure model.

Consistency is shared by database constraints and correct application transactions. ACID does not mean every concurrent transaction behaves as if no other work exists; the chosen isolation level determines permitted observations.

ResultSet cursor model

A new ResultSet cursor begins before the first row. Call next(); it advances and returns true when a row is available:

try (ResultSet rs = ps.executeQuery()) {
    while (rs.next()) {
        long id = rs.getLong("id");
        String name = rs.getString("name");
        students.add(new Student(id, name));
    }
}

Column labels improve readability and tolerate SELECT-list reordering. Column indexes are one-based and may be useful in performance-sensitive generic mapping.

The default result set is commonly forward-only and read-only. Scrollability, concurrency and holdability can be requested, but driver and database support vary.

Isolation levels

JDBC exposes standard isolation constants:

Level	Dirty reads	Non-repeatable reads	Phantom reads
READ_UNCOMMITTED	May occur	May occur	May occur
READ_COMMITTED	Prevented	May occur	May occur
REPEATABLE_READ	Prevented	Prevented	Standard permits phantoms
SERIALIZABLE	Prevented	Prevented	Prevented in the standard model

A dirty read observes uncommitted data. A non-repeatable read sees a row change between reads. A phantom occurs when repeating a predicate query yields a changed set of rows.

Real database MVCC and locking implementations have vendor-specific details. Stronger isolation can reduce concurrency or cause retries. Select it from the invariant rather than always choosing the strongest level.

SQLException diagnosis

SQLException provides a message, SQLState, vendor error code, cause and potentially chained exceptions:

for (SQLException current = ex;
     current != null;
     current = current.getNextException()) {
    log(current.getSQLState(), current.getErrorCode());
}

Add safe operational context such as the operation name or record identifier, but do not log passwords, secret connection properties or sensitive bound values. Preserve the cause when translating to an application exception.

Transient failures such as deadlocks or connection interruptions may be retryable, but only with bounded policy and an idempotent operation. Blind retries can duplicate side effects.

Stored procedures

CallableStatement supports stored procedures and functions:

try (CallableStatement call =
         connection.prepareCall("{call calculate_grade(?, ?)}")) {
    call.setLong(1, studentId);
    call.registerOutParameter(2, Types.VARCHAR);
    call.execute();
    String grade = call.getString(2);
}

Input parameters are set, output parameters are registered and in/out parameters do both. Stored routines can centralize database-side logic but increase database coupling. Encapsulate vendor-specific calls behind repository or DAO interfaces.