JDBC practice questions

Worked examples for JDBC

1. Question 1

   `setAutoCommit` is never called anywhere in this application. ```java static void transfer(String url) throws SQLException { try (Connection conn = DriverManager.getConnection(url); Statement st = conn.createStatement()) { st.executeUpdate("INSERT INTO ledger VALUES (1, -50)"); st.executeUpdate("INSERT INTO ledger VALUES (2, 50)"); conn.commit(); } } ``` Both INSERT statements succeed. According to the `java.sql.Connection` contract, what happens?

   1. A. The commit() call throws SQLException, and because the transaction never completed, both inserts are rolled back when the connection closes.

      Wrong: this spots the exception but reasons that a failed transaction must roll back. There was no open transaction to roll back - both rows were already committed, one per statement, in auto-commit mode.

   2. B. Each insert was already committed as it completed, because new connections start in auto-commit mode; the commit() call then throws SQLException, because commit() may not be called on a connection in auto-commit mode.Correct answer

      Correct: new connections start in auto-commit mode, so each insert commits as it completes, and Connection.commit() is specified to throw SQLException when called on a connection in auto-commit mode.

   3. C. Each insert was already committed as it completed, and the commit() call is simply a harmless no-op.

      Wrong: this gets the auto-commit default right but assumes a redundant commit() is tolerated. Unlike setAutoCommit, whose Javadoc blesses the redundant case, commit() is specified to throw in auto-commit mode.

   4. D. Neither insert is durable until commit() runs, which then makes both permanent as a single atomic transaction.

      Wrong: this is the classic misconception that auto-commit defaults to false and JDBC gives a free transaction. It is exactly backwards - each insert is already durable on completion, and there is no atomicity here.

   Explanation

   The method never calls `setAutoCommit(false)`, and the `Connection` Javadoc is unambiguous about the starting state: "By default, new connections are in auto-commit mode." In that mode each statement is its own transaction — the Javadoc says the commit "occurs when the statement completes", and for DML "the statement is complete as soon as it has finished executing". So the first INSERT is permanent before the second one even begins. There is no atomicity here at all: this code cannot transfer money safely, which is the real bug the question is pointing at. Then `conn.commit()` runs, and it does not quietly do nothing. `Connection.commit()` documents "This method should be used only when auto-commit mode has been disabled", and lists among its throws: SQLException if "this Connection object is in auto-commit mode". So the call is an error, and the method exits by throwing. Crucially, that thrown exception cannot undo anything — the two rows were committed on completion, and no amount of failure afterwards takes them back. Why the others are wrong: `Neither insert is durable until commit() runs...` is the single most common JDBC misconception: that auto-commit defaults to false and JDBC gives you a transaction for free. It is exactly backwards. If this were true the method would be correct, which is presumably why it was written this way. `Each insert was already committed as it completed, and the commit() call is simply...` gets the auto-commit default right but then assumes a redundant `commit()` is tolerated. Compare `setAutoCommit`, where the Javadoc explicitly blesses the redundant case ("If setAutoCommit is called and the auto-commit mode is not changed, the call is a no-op") — `commit()` gets no such licence; it is specified to throw. `The commit() call throws SQLException, and because the transaction never completed...` spots the exception but then reasons that a failed transaction must roll back. There was no open transaction to roll back — both rows were already committed, one per statement. One caveat worth knowing, because it bites in real code: `commit()` throwing here is what the *contract* requires, and drivers vary in how strictly they enforce it. PostgreSQL and MySQL throw as specified; H2 accepts the redundant call and returns normally. The exam tests the contract, and so should you -- code that relies on a lenient driver breaks the day it moves. Exam tip: a fresh `Connection` is in auto-commit mode, so `commit()` and `rollback()` are BOTH errors until you call `setAutoCommit(false)` first. Watch for the reverse trap too: switching auto-commit off mid-flight is not free — `setAutoCommit` commits any transaction currently in progress.

2. Question 2

   After executing a query, what does a freshly returned ResultSet's cursor position require before reading the first row?

   1. A. You must call beforeFirst() to initialize the cursor

      beforeFirst() repositions a scrollable result set back to the start; it is unnecessary on a fresh cursor and throws SQLException on the default forward-only type.

   2. B. You must call first() because next() is not supported on forward-only result sets

      next() is precisely the method forward-only result sets do support; first() is the one that needs scrollability.

   3. C. You must call next() once; the cursor starts before the first rowCorrect answer

      A freshly returned ResultSet's cursor sits before the first row; the first next() call advances onto row one and returns true (or false for an empty result), so every read loop starts with next().

   4. D. The cursor is already on the first row; call getXxx directly

      Calling getXxx before any next() throws SQLException because no row is current yet.

   Explanation

   A newly returned ResultSet positions its cursor before the first row rather than on it, so a read must first advance the cursor. The canonical pattern is a while loop driven by next(), where each call moves forward one row and reports whether a row is now current. Attempting to read column values before advancing throws SQLException because no row is yet current.

3. Question 3

   Which two statements about the shape of the `java.sql` API in Java 17 are correct? (Choose two.)

   1. A. Statement.close() declares no checked exception, so it can be called from any method without handling SQLException.

      Wrong: this is a tempting belief because close() is usually the quiet method, but Statement.close() is declared void close() throws SQLException, so calling it outside try-with-resources still needs catch-or-declare.

   2. B. PreparedStatement extends Statement, so a PreparedStatement may be passed to a method whose parameter is declared as Statement.Correct answer

      Correct: PreparedStatement is declared as a plain subinterface of Statement, so a PreparedStatement reference is assignable to a Statement variable or parameter without a cast.

   3. C. SQLException extends java.lang.Exception, so a method that calls DriverManager.getConnection must either catch it or declare throws SQLException.Correct answer

      Correct: SQLException's superclass chain never passes through RuntimeException, so it is checked, and a method calling DriverManager.getConnection must catch it or declare throws SQLException.

   4. D. DriverManager.getConnection returns null when no registered driver accepts the URL, so its result must be null-checked before use.

      Wrong: this imports a null-returning failure convention JDBC does not use. When no registered driver accepts the URL, DriverManager throws SQLException, so a null check on getConnection is dead code.

   Explanation

   Both correct statements fall straight out of the declarations. `SQLException extends java.lang.Exception, so a method that calls...` — `SQLException`'s superclass chain is SQLException to Exception to Throwable to Object. It never passes through `RuntimeException`, so it is a checked exception, and essentially every method in `java.sql` declares it. A call to `DriverManager.getConnection(url)` in a method that neither catches nor declares SQLException does not compile. `PreparedStatement extends Statement, so a PreparedStatement may be passed...` — `PreparedStatement` is declared `public interface PreparedStatement extends Statement`. It is a plain subinterface, so a `PreparedStatement` reference is assignable to a `Statement` variable or parameter without a cast. (This is also the root of a classic bug: because it inherits `executeQuery(String)`, that call compiles on a PreparedStatement even though the spec forbids it at run time.) Why the others are wrong: `Statement.close() declares no checked exception, so it can be called...` — a very tempting belief, because in most APIs `close()` is the safe, quiet method. Here it is not: the declaration is `void close() throws SQLException`. Calling it outside a try-with-resources, without catching or declaring SQLException, is a compile error like any other. (Try-with-resources handles this for you, which is why the belief survives so long.) `DriverManager.getConnection returns null when no registered driver...` — imports the null-returning failure convention from other APIs. JDBC does not use it. When no driver accepts the URL, `DriverManager` throws SQLException; `getConnection` has no path that hands you a null Connection, so a null check on it is dead code that hides nothing. Exam tip: two structural facts carry a surprising number of JDBC questions — everything in `java.sql` throws the CHECKED `SQLException` (including `close()`), and `Connection`, `Statement` and `ResultSet` all extend `AutoCloseable`, which is what makes try-with-resources both legal and the idiom. The reverse trap: JDBC signals failure by throwing, never by returning null.

4. Question 4

   How are parameters bound in a PreparedStatement, and what index do they start at?

   1. A. By string name only, never by index

      Core JDBC has no named-parameter binding for PreparedStatement; binding is by index. Named parameters exist only for CallableStatement's stored-procedure parameters.

   2. B. By concatenating values into the SQL before prepare

      Concatenating values into the SQL text before prepare() throws away every injection and type-safety benefit; it is the anti-pattern the class exists to prevent.

   3. C. With setXxx(index, value); indexes are 0-based

      The binding call setXxx(index, value) is right, but index 0 is invalid and produces an SQLException at bind time — JDBC indexes are 1-based, not 0-based.

   4. D. With setXxx(index, value); JDBC parameter indexes are 1-basedCorrect answer

      PreparedStatement placeholders are bound positionally with setXxx(index, value), and the first ? is index 1 — all JDBC indexes, parameters and result-set columns alike, are 1-based.

   Explanation

   PreparedStatement placeholders are filled positionally through setXxx(index, value) calls, and JDBC counts positions starting at 1, not 0 — the same 1-based convention that applies to result-set columns. Passing index 0 is the classic off-by-one trap and raises SQLException at bind time. Every placeholder must be bound before execution or the driver throws.

5. Question 5

   A reporting tool runs two SQL strings through the same plain `Statement`: ```java try (Statement st = conn.createStatement()) { int a = st.executeUpdate("CREATE TABLE audit (id INT, note VARCHAR(50))"); boolean b = st.execute("SELECT id, note FROM audit"); System.out.println(a + " " + b); } ``` The table is created successfully and the SELECT runs without error. Which statement correctly describes the two values printed?

   1. A. executeUpdate returns the number of rows in the newly created table, and execute returns false because a SELECT modifies no rows.

      Gets both halves backwards; a new table has no rows to count, and execute's boolean means "first result is a ResultSet", not "did it modify data", so it is true for the SELECT.

   2. B. executeUpdate returns 0, because a DDL statement returns no row count; execute returns true, because the first result is a ResultSet.Correct answer

      executeUpdate returns 0 for DDL like CREATE TABLE (which returns no row count), and execute returns true because a SELECT's first result is a ResultSet, giving 0 true.

   3. C. executeUpdate throws SQLException because CREATE TABLE is not a DML statement; only execute may be used to run DDL.

      Assumes the method name forbids DDL; executeUpdate's Javadoc explicitly lists DDL as a supported case returning 0, so CREATE TABLE does not throw.

   4. D. executeUpdate returns -1 to signal that no update count is available, and execute returns true.

      Borrows the -1 sentinel from getUpdateCount(); executeUpdate never returns -1, its floor is 0.

   Explanation

   Two separate contracts are in play, and the exam loves to blur them. `executeUpdate` is declared `int executeUpdate(String) throws SQLException`, and its Javadoc pins the return value down to exactly two cases: "either (1) the row count for SQL Data Manipulation Language (DML) statements or (2) 0 for SQL statements that return nothing, such as a DDL statement." `CREATE TABLE` is DDL, so it returns nothing, so the count is 0. `executeUpdate` is emphatically not DML-only — running DDL through it is the normal, documented way to do it. `execute` is declared `boolean execute(String) throws SQLException`, and its boolean does not mean "did it succeed" or "did it change data". Its Javadoc says it is "true if the first result is a ResultSet object". A SELECT produces a ResultSet as its first result, so it returns true. That is why `execute` is the general-purpose method: you call it when you do not know in advance what came back, then branch to `getResultSet()` or `getUpdateCount()`. So the printed line is `0 true`. Why the others are wrong: `executeUpdate returns the number of rows in the newly...` gets both halves backwards. A brand-new table has no rows to count, and it reads `execute`'s boolean as "did this statement modify data" — a natural but wrong reading that inverts the answer for every SELECT. `executeUpdate returns -1 to signal that no update...` borrows the sentinel from the wrong method. -1 is what `getUpdateCount()` returns when the current result is not an update count (or there are no more results). `executeUpdate` itself never returns -1; its floor is 0. `executeUpdate throws SQLException because CREATE TABLE...` encodes the belief that the method name is a hard constraint on the SQL category. It is not — the Javadoc explicitly lists DDL as a supported case that yields 0. Exam tip: memorise the three return types as a set, because questions cycle through them — `executeQuery` returns a ResultSet (never null), `executeUpdate` returns an int (row count, or 0 for anything that returns nothing), `execute` returns a boolean (true iff the first result is a ResultSet). The reverse trap is the -1: that value belongs to `getUpdateCount()`, not to `executeUpdate`.

6. Question 6

   What is the effect of calling Connection.setAutoCommit(false)?

   1. A. Statements are grouped into a transaction that you must commit() or rollback() explicitlyCorrect answer

      Disabling auto-commit starts manual transaction mode — subsequent statements accumulate in one transaction that becomes permanent only on commit() and can be discarded with rollback().

   2. B. All subsequent statements are silently discarded

      Statements still execute normally; only the commit boundary changes.

   3. C. Every statement is immediately committed

      Immediate commit of every statement describes auto-commit being ON — the default that is being switched off here.

   4. D. The connection becomes read-only

      Read-only is a separate hint set via setReadOnly(true); transaction demarcation does not restrict writes.

   Explanation

   Turning off auto-commit switches the connection into manual transaction mode, where statements no longer commit individually but instead accumulate into a single transaction. That work becomes permanent only when commit() is called and can be undone with rollback(). Switching auto-commit back on mid-transaction commits the pending work immediately, and closing a connection with an uncommitted transaction is implementation-defined.

7. Question 7

   Which Statement method should you call to run a SELECT and obtain a ResultSet?

   1. A. execute(sql) always returns a ResultSet

      execute(sql) returns a boolean — true means the first result is a ResultSet, which you then fetch via getResultSet() — so it does not return the ResultSet itself, and 'always returns a ResultSet' is doubly wrong.

   2. B. executeUpdate(sql)

      executeUpdate is for INSERT/UPDATE/DELETE and DDL, returning an int row count; running a SELECT through it throws SQLException.

   3. C. executeQuery(sql)Correct answer

      executeQuery(sql) is defined for statements that produce a single ResultSet — a SELECT — and returns it directly, never null.

   4. D. getResultSet(sql)

      getResultSet() takes no SQL string; it retrieves the current result after execute().

   Explanation

   The three Statement execution methods are distinguished by return type: the query method returns a ResultSet for a SELECT, the update method returns an int row count for DML and DDL, and the general-purpose method returns a boolean signalling whether the first result is a ResultSet. A SELECT needs the method that hands back a ResultSet directly and never null. Feeding the wrong kind of SQL to these methods fails at run time with SQLException, never at compile time.

8. Question 8

   A developer writes this lookup against a live database: ```java static void lookup(Connection conn) throws SQLException { String sql = "SELECT name FROM person WHERE id = ?"; try (PreparedStatement ps = conn.prepareStatement(sql)) { ps.setInt(1, 7); try (ResultSet rs = ps.executeQuery(sql)) { while (rs.next()) { System.out.println(rs.getString(1)); } } } } ``` Note the argument passed to `executeQuery`. What is the result of compiling and running this method?

   1. A. It compiles, but throws SQLException at run time: executeQuery(String) is inherited from Statement and must not be called on a PreparedStatement.Correct answer

      PreparedStatement inherits executeQuery(String) from Statement so ps.executeQuery(sql) compiles, but the contract forbids that inherited method on a PreparedStatement, so the driver throws SQLException at run time.

   2. B. It compiles and runs. The SQL string passed to executeQuery replaces the prepared SQL, and the value bound by setInt is applied to it.

      Assumes re-supplying the identical SQL is harmless; the call is forbidden on a PreparedStatement regardless of the string's contents, so it does not run.

   3. C. It does not compile: PreparedStatement.executeQuery() takes no arguments, so the String argument matches no method.

      Assumes the no-arg executeQuery() hides the inherited String overload; a subinterface's method merely overloads alongside the inherited one, so executeQuery(String) is still callable and compiles.

   4. D. It compiles and runs, but the bound parameter is ignored and the ? is sent to the database as a literal character.

      Imagines a silent-wrong-answer mode sending '?' as a literal; the spec requires a thrown SQLException, not a silent degradation.

   Explanation

   This is a trap built out of the type hierarchy. `PreparedStatement` is declared as `public interface java.sql.PreparedStatement extends java.sql.Statement`. It *adds* the no-argument `executeQuery()`, but it cannot take away what it inherits — so `executeQuery(String)` is still a perfectly visible member of the `PreparedStatement` interface, and `ps.executeQuery(sql)` is a legal overload resolution. The compiler is happy. Nothing here is a compile error. The prohibition lives in the contract, not the type system. The Javadoc on `Statement.executeQuery(String)` carries an explicit note — "This method cannot be called on a PreparedStatement or CallableStatement" — and lists among its throws clause: SQLException if "the method is called on a PreparedStatement or CallableStatement". So the driver is required to reject the call at run time with a SQLException. The failure is a run-time one, which is precisely what makes this dangerous: it survives compilation and review, and blows up in production. Why the others are wrong: `It does not compile: PreparedStatement.executeQuery() takes no...` assumes the no-arg method somehow hides or replaces the inherited String overload. Inheritance does not work that way — a subinterface that declares `executeQuery()` merely overloads alongside `executeQuery(String)`. Both are callable, and the compiler picks the String one here. `It compiles and runs. The SQL string passed to executeQuery replaces...` assumes that re-supplying the identical SQL is harmless because the text matches. The driver never gets that far: the call is forbidden on a PreparedStatement regardless of what the string contains, even if it is character-for-character the prepared SQL. `It compiles and runs, but the bound parameter is ignored...` imagines a silent-wrong-answer failure mode, sending `?` through as data. The spec does not permit a silent degradation; it requires a thrown SQLException. Exam tip: on a `PreparedStatement`, always call the no-argument execution methods — `executeQuery()`, `executeUpdate()`, `execute()`. The SQL was already handed over at `prepareStatement(sql)`. The reverse trap is the mirror image, and it *is* a compile error: a plain `Statement` has no no-arg `executeQuery()`, so `st.executeQuery()` fails to compile.