JDBC Interview Questions with Answers

Almost nobody writes raw JDBC any more. Spring Data or Hibernate sits on top, and a developer can ship for years without calling getConnection() directly. JDBC questions survive anyway, and for a good reason: when the ORM misbehaves, it misbehaves at the JDBC layer. A connection pool exhausted at 3am, a query that loads two million rows into heap, a transaction that silently committed halfway through are all JDBC facts wearing a JPA coat.

So the questions below are not an answer key. Each one names what the interviewer is checking, the answer that satisfies it, and the follow-up that separates recall from understanding.

Written against Java 21 and JDBC 4.3.

1. DriverManager, DataSource, and why the answer is neither

The question: how do you obtain a connection?

DriverManager.getConnection(url, user, password) is the textbook answer and the wrong one for any application written this decade. It opens a fresh TCP connection, performs the handshake and the authentication, and hands it over. On PostgreSQL that is somewhere between 20 and 50 milliseconds before a single row moves.

The answer the interviewer wants is a DataSource backed by a pool. getConnection() then borrows an already-open connection and close() returns it rather than closing anything. HikariCP is the default in Spring Boot and has been since 2.0.

The follow-up: what should the pool size be? The instinct is “as high as the database allows”. The reality is the opposite. A connection that is not executing a statement is holding a backend process hostage, and past a certain point more connections mean more context switching and less throughput. HikariCP’s own guidance starts from cores * 2 + effective_spindles and treats double-digit pools as large. A pool of 200 against a database with 8 cores is a queue with extra steps.

2. Statement versus PreparedStatement

The question: what is the difference?

The expected answer is SQL injection, and it is correct. A PreparedStatement sends the SQL and the parameters separately, so a parameter can never be parsed as syntax. String concatenation into a Statement is how injection happens.

The follow-up: name a second reason. Two are good answers. The first is the plan cache: the database parses and plans the statement once and reuses it for later executions with different parameters. The second is type fidelity, since setTimestamp and setBigDecimal hand over a typed value instead of a string the database has to coerce.

A third answer earns credit and is where candidates usually stop short: parameters cannot be used everywhere. Table names, column names and the direction in ORDER BY are syntax, not values. ORDER BY ? does not work. When those genuinely need to vary, the only safe route is a whitelist of permitted identifiers, checked in code before the SQL is assembled.

3. ResultSet, and the question that catches people out

The question: how do you read a large result set without exhausting heap?

setFetchSize(n) is the answer, and stated alone it is incomplete. The fetch size is a hint, and what a driver does with it varies enough that the hint is worthless without knowing the driver.

On PostgreSQL, setFetchSize is ignored unless autocommit is off. With autocommit on, the driver reads the entire result into memory before next() returns once. The fix is two lines and neither is obvious from the API:

connection.setAutoCommit(false);
try (PreparedStatement ps = connection.prepareStatement("SELECT * FROM events")) {
    ps.setFetchSize(1000);
    try (ResultSet rs = ps.executeQuery()) {
        while (rs.next()) {
            process(rs.getLong("id"));
        }
    }
}

MySQL is stranger still. Its Connector/J streams only when the fetch size is Integer.MIN_VALUE, and while streaming the connection cannot be used for anything else until the result set is drained or closed.

A candidate who says “set the fetch size, and check what your driver does with it” has answered better than one who quotes the method signature.

4. Transactions and isolation levels

The question: how do you run several statements in one transaction?

setAutoCommit(false), execute, then commit() or rollback(). The mechanical answer is easy. The interesting part is what the interviewer asks next.

The follow-up: which isolation level, and what does it cost? The four levels defined by the standard are READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ and SERIALIZABLE, and each one removes a class of anomaly at the price of contention. Two details separate an answer that has been read from one that has been used:

Defaults differ by vendor. PostgreSQL and Oracle default to READ_COMMITTED; MySQL with InnoDB defaults to REPEATABLE_READ. Code that behaves on one and not the other is usually meeting that difference rather than a bug.

The names are also not promises. PostgreSQL has no READ_UNCOMMITTED; requesting it silently gives READ_COMMITTED. Connection.setTransactionIsolation accepts the constant and the driver is free to give something stricter.

Savepoints are worth knowing but rarely worth using. setSavepoint() marks a point a partial rollback can return to, which is occasionally the right tool inside a long batch and more often a sign the unit of work is too big.

5. Batch updates, and the one that gets forgotten

The question: how do you insert ten thousand rows efficiently?

addBatch() and executeBatch(), in chunks rather than all at once, so the driver does not build one enormous packet. A chunk of 500 to 1000 is a normal starting point.

The follow-up: what does executeBatch() return, and what happens when one statement fails? It returns an int[] of update counts, one per statement, and the values matter. A driver that cannot determine a count returns SUCCESS_NO_INFO. When a statement fails, the driver may stop or may continue with the rest, and it throws BatchUpdateException, whose getUpdateCounts() is the only way to learn how far it got. Code that catches SQLException and logs it has just thrown away the information about which half of the batch is in the database.

There is a vendor trap here too. MySQL does not actually batch inserts on the wire unless rewriteBatchedStatements=true is on the connection URL. Without it, the code looks batched and the network does not.

6. Closing things, and the leak that survives try-with-resources

The question: how do you release JDBC resources?

Try-with-resources on Connection, Statement and ResultSet. Closing a Connection closes the statements it created, and closing a Statement closes its result sets, so the nesting is belt and braces rather than strictly required.

The follow-up: where do connection leaks come from, given try-with-resources? Two places, and both are common. The first is a connection obtained outside the try block, so an exception between acquiring it and entering the block skips the close entirely. The second is a connection stored in a field or a ThreadLocal and reused, which defeats the pool’s accounting: the pool believes the connection is in use forever, because from its point of view it is.

The symptom is the same in both cases and it is not an exception at the leak site. It is a SQLTransientConnectionException half an hour later in unrelated code, when the pool finally runs out. HikariCP’s leakDetectionThreshold exists exactly to move the error back to the guilty stack trace.

7. SQLException, and the reason vendor codes exist

The question: how do you handle a SQLException?

The weak answer is to log it. The strong answer starts with the fact that a SQLException is a chain: getNextException() walks it, and the useful detail is frequently on the second link rather than the first. A batch failure in particular buries the actual constraint violation there.

For deciding what to do, getSQLState() returns the five-character standard code and getErrorCode() returns the vendor’s own integer. The state is portable and coarse; the vendor code is precise and not portable. Catching the typed subclasses is usually better than either: SQLIntegrityConstraintViolationException for a duplicate key, SQLTransientConnectionException for something a retry might fix.

That distinction between transient and non-transient is the useful one. Retrying a constraint violation will fail identically every time, and retrying a deadlock will very often succeed.

How to answer

The pattern across all of these is the same. The mechanical answer is one sentence and it is expected, not impressive. What is being tested is whether the candidate has seen the thing fail.

Answer the question asked, then name the condition under which the answer is wrong. “Use setFetchSize, though on PostgreSQL it does nothing with autocommit on” tells an interviewer more about production experience than three paragraphs about the JDBC architecture. If the failure has never come up, say so rather than reciting; “I have not hit that, but I would look at the driver’s documentation for it first” is a better answer than a confident wrong one.

Frequently asked questions

Is JDBC still worth learning if the project uses JPA?

Yes, because the failures surface as JDBC facts. Pool exhaustion, fetch behaviour, isolation levels and batch semantics are all decided below the ORM, and the ORM’s own logs quote them.

What replaced Class.forName for driver loading?

Nothing needs to replace it. Since JDBC 4.0 drivers are discovered through the service loader, so a driver on the classpath registers itself. Class.forName in modern code is a leftover.

Does closing a Connection from a pool actually close it?

No. The pool hands out a proxy, and close() returns the underlying connection to the pool. That is why calling it is still mandatory: skipping it is exactly how the pool runs dry.

What is the difference between execute, executeQuery and executeUpdate?

executeQuery returns a ResultSet and suits SELECT; executeUpdate returns a row count and suits INSERT, UPDATE and DELETE; execute returns a boolean and exists for statements whose shape is not known ahead of time, such as a stored procedure that may or may not produce results.

Why does my PreparedStatement not use the plan cache?

Usually because a new one is prepared for every execution. The reuse that pays off is executing the same PreparedStatement object repeatedly with different parameters, or relying on the pool’s statement cache, not preparing and discarding.

Can PreparedStatement parameters be used for table names?

No. Parameters substitute values, and a table name is syntax. Validate against a whitelist of allowed identifiers instead.

What is a CallableStatement for?

Stored procedures. It adds registration of OUT parameters through registerOutParameter, which a PreparedStatement has no way to express.

Is a connection thread-safe?

Treat it as not thread-safe. The specification does not require concurrent use to work, drivers vary, and sharing one connection across threads reintroduces every problem the pool exists to solve.

When should batch size be tuned?

When the batch is large enough that one packet becomes a problem, which is usually in the low thousands. Start at 500 to 1000 and measure; the optimum depends on row width and network latency more than on row count.

Why did my transaction commit when I never called commit?

Autocommit was on, which is the default. Every statement was its own transaction. Turning it off is the first line of any multi statement unit of work.