Java JDBC API

JDBQC (Java Database Connectivity) is a standard Java API for connecting to relational databases, executing SQL, and processing results.

It provides a vendor-neutral way to access databases from Java applications.

Most core interfaces are in java.sql (and additional features in javax.sql).

A JDBC driver is a vendor-provided implementation that allows Java applications to communicate with a specific database.

• Driver
• Connection
• Statement
• PreparedStatement
• CallableStatement
• ResultSet
• DatabaseMetaData
• ResultSetMetaData

A connection string that tells the driver how to connect, e.g. jdbc:postgresql://localhost:5432/appdb.

A class that manages JDBC drivers and creates connections via getConnection().

By calling DriverManager.getConnection(url, user, password) (or via a DataSource).

Usually not for modern drivers (auto-loading works), but it may be needed in legacy setups.

Used to execute static SQL without parameters.

A precompiled SQL statement with placeholders (= ? =) for parameters.

Better performance for repeated execution and safer against SQL injection.

Used to call stored procedures/functions in the database.

A cursor-like object representing query results row by row.

Use a loop with while (rs.next()) { ... }.

Executes SELECT SQL and returns a ResultSet.

Executes INSERT/UPDATE/DELETE (and DDL in many drivers) and returns affected row count.

Runs any SQL and returns true if first result is a ResultSet, otherwise false.

A security vulnerability caused by unsafe string concatenation in SQL.

Using PreparedStatement parameters instead of concatenating user input.

The ? symbol in prepared SQL representing a parameter value.

Use preparedStatement.setInt(index, value).

Use preparedStatement.setString(index, value).

They are 1-based.

Use rs.getString("column_name") or rs.getString(columnIndex).

Use rs.getInt(...): Check rs.wasNull() if null handling matters.

To determine whether the last read column value was SQL NULL.

A Java construct that automatically closes resources implementing AutoCloseable.

It prevents connection/statement/result set leaks.

Resources close in reverse order of declaration.

Yes, typically associated statements are closed as well.

Yes, its current ResultSet is usually closed.

The standard JDBC exception for database access errors.

• getMessage()
• getSQLState()
• getErrorCode()
• getNextException()

A standardized 5-character error code category for SQL errors.

SQLState is standardized; vendor code is database-specific.

Create prepared SQL, bind parameters, executeUpdate, optionally fetch generated keys.

Create statement with RETURN_GENERATED_KEYS and call getGeneratedKeys().

Yes, e.g. CREATE/ALTER/DROP via executeUpdate() or execute().

When true, each SQL statement is committed automatically after execution.

Usually yes, for most JDBC drivers.

Call connection.setAutoCommit(false).

Call connection.commit().

Call connection.rollback().

On exceptions or business validation failures during transactional work.

All operations in a transaction succeed together or fail together.

• READ_UNCOMMITTED
• READ_COMMITTED
• REPEATABLE_READ
• SERIALIZABLE

Use connection.setTransactionIsolation(Connection.TRANSACTION_...).

A factory for connections, often supporting pooling and external configuration.

Better integration, pooling support, and cleaner configuration.

Reusing existing DB connections to avoid expensive connection creation.

You can exhaust DB/server resources and degrade performance.

Generally no; treat JDBC objects as not thread-safe unless documented otherwise.

Information about DB/database objects via DatabaseMetaData and ResultSetMetaData.

Inspecting returned columns dynamically (name, type, count, etc.).

Use setNull(index, sqlType) when SQL type must be explicit.

Primitive getters return defaults (e.g., 0 for int), requiring wasNull() checks.

Grouping multiple similar statements to reduce network round trips.

Call addBatch() repeatedly, then executeBatch().

An int[] of update counts for each batched command.

Bulk inserts/updates/deletes for better throughput.

A hint to driver/database for how many rows to fetch per trip.

Use statement.setFetchSize(n).

No, it is a hint and driver-dependent.

Limits number of rows a ResultSet can contain from a statement.

Use statement.setQueryTimeout(seconds).

Driver attempts to cancel statement and throws SQLException.

Calling statement.cancel() to attempt interruption of running SQL.

A ResultSet that allows cursor movement beyond forward-only.

• TYPE_FORWARDONLY
• TYPE_{SCROLLINSENSITIVE}
• TYPE_{SCROLLSENSITIVE}

ResultSet allowing direct row updates with concurrency mode CONCUR_UPDATABLE.

Less common; many teams prefer explicit SQL updates for clarity.

Defines whether cursors remain open after commit.

• HOLD_{CURSORSOVERCOMMIT}
• CLOSE_{CURSORSATCOMMIT}

A marker inside a transaction allowing partial rollback.

Use Savepoint sp = connection.setSavepoint().

Use connection.rollback(sp).

To recover from part of a complex transaction without rolling back everything.

Discovering tables, schemas, supported features, driver/db versions.

Metadata about prepared statement parameters (driver support varies).

Query DatabaseMetaData feature methods, e.g., batch support.

Update using a version column in WHERE clause; fail if row changed.

Lock rows explicitly (e.g., SELECT … FOR UPDATE) during transaction.

They hold locks/resources longer and increase contention.

Stream rows, set fetch size appropriately, avoid loading all in memory.

Prefer java.time types with JDBC 4.2 APIs where supported.

Often ps.setObject(index, localDate) with JDBC 4.2+ drivers.

Often rs.getObject("col", LocalDate.class).

Large Objects: BLOB (binary), CLOB (character).

Use setBinaryStream() or setBlob() depending on driver/use case.

Use getBinaryStream() or getBytes() for small payloads.

High memory usage; stream instead.

Non-fatal DB warning retrievable from Connection/Statement/ResultSet.

Call getWarnings() and optionally clearWarnings().

JDBC processing of SQL escape syntax before sending to DB.

Example: { call my_proc(?) }.

Register with registerOutParameter() before execution, then read values.

Depends on proc result; use execute() when output shape may vary.

Handling multiple results via getMoreResults() and update counts.

More readable and resilient to SELECT column order changes.

Potentially slightly faster and useful in dynamic column loops.

Repeated per-row queries causing many DB round trips and poor performance.

Use joins, IN queries, bulk prefetch patterns.

Integrity constraint violations (e.g., unique/foreign key).

Use SQLException subclasses and SQLState/vendor code mapping.

Useful for debugging, but avoid leaking secrets/PII.

No, always redact.

Two+ transactions block each other waiting on resources.

Rollback and retry transaction with backoff when appropriate.

Repeated same request leads to same final state.

Prevents duplicate side effects after transient failures.

Retry only retryable errors and idempotent operations.

Hint to DB/driver that transaction is read-only.

connection.setReadOnly(true) before executing statements.

Sometimes; depends on DB optimization and routing architecture.

Setting current schema/catalog for object name resolution.

connection.setSchema(...) and connection.setCatalog(...) (support varies).

Indicates requested JDBC feature isn’t supported by driver/DB.

To isolate persistence concerns and simplify service-layer handling.

Data Access Object encapsulates SQL/JDBC operations behind clear interfaces.

Too small causes waiting; too large increases DB contention and context switching.

Active, idle, pending threads, acquisition time, timeout count, connection lifetime.

To recycle stale connections before network/db kills them.

Checking connection health (test query or driver/isValid call) before use.

Each statement commits separately, increasing transaction overhead.

Prepared statement cached/managed on DB server side (driver/db dependent).

Reusing prepared statements to reduce parse/plan overhead.

Driver-level, pool-level, or database-level plan cache.

Memory pressure and degraded performance.

Rows appearing/disappearing between repeated range queries in same transaction.

SERIALIZABLE (and some DB-specific mechanisms under repeatable read variants).

Highest isolation often reduces concurrency and throughput.

How transactional boundaries interact across nested service calls.

Not directly; frameworks add higher-level transaction management semantics.

A distributed transaction spanning multiple resources using two-phase commit.

Operational overhead, locking duration, and failure handling complexity.

Use eventual consistency patterns (outbox, sagas, idempotent consumers).

Write domain change + outbound event record in same local DB transaction.

Single transaction containing business updates and outbox insert.

One transaction overwrites another’s change without noticing.

Optimistic locking (version column) or proper locking/isolation.

Improves maintainability, review quality, and performance reasoning.

Keyset uses last seen key for fast stable paging; offset can be slow on large pages.

Through SQL patterns (LIMIT/OFFSET or keyset predicates), not special API magic.

Controlling read/processing rate to avoid memory/resource exhaustion.

Forward-only ResultSet, tuned fetch size, incremental serialization.

rs.setFetchDirection(...); often advisory and may be ignored.

Mismatched JVM/DB/session timezone when mapping timestamps.

Store in UTC, convert at boundaries, use timezone-aware types when needed.

Can bypass indexes and degrade query performance.

Incorrect binding types may force casts and reduce plan efficiency.

Unnecessary data transfer, brittle mappings, harder index-only scans.

Reads go to replicas, writes to primary; app/router decides destination.

Replication lag can cause stale reads after write.

Route critical follow-up reads to primary or use session stickiness/windowing.

Missing close in error paths, holding connections across slow operations.

Pool leak detection thresholds, thread dumps, acquisition timeout metrics.

Increases transaction duration and lock holding time.

Too many clients retry simultaneously, amplifying outage load.

Exponential backoff + jitter + retry budgets/circuit breakers.

Backward-compatible, phased rollout, dual-read/write if needed, feature flags.

Application and schema versions must be compatible during cutover.

Use integration tests with real DB (or testcontainers), not only mocks.

SQL dialect/behavior differs from production DB.

Query latency, error rates by SQLState, pool metrics, slow query logging.

Add trace/span IDs around DAO calls and include DB timing tags.

Log statement template, timing, SQLState/vendor code; redact sensitive values.

Timeouts, retries (carefully), idempotency, bulkheads, circuit breakers, metrics.

When productivity/maintainability needs exceed benefits of direct control.

It builds strong fundamentals for performance tuning and debugging across all Java data stacks.