Java 17 1Z0-829: Accessing Databases Using JDBC

Topic snapshot

Sample questions

These questions are original IT Mastery practice items aligned to this topic area. They are designed for self-assessment and are not official exam questions.

Question 1

Topic: Accessing Databases Using JDBC

Assume the imports shown are complete. At runtime, no registered JDBC driver accepts URLs beginning with jdbc:inventory:. What happens when this program is run?

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DbPing {
    public static void main(String[] args) throws SQLException {
        var url = "jdbc:inventory://db.example.com:5432/app";
        try (Connection con =
                DriverManager.getConnection(url, "app", "secret")) {
            System.out.print(con.isValid(2));
        }
    }
}

Options:

• A. It throws IllegalArgumentException because the URL includes a host.

• B. It compiles, then getConnection() throws SQLException.

• C. It does not compile because Class.forName() is missing.

• D. It prints true after creating a connection from the URL.

Best answer: B

Explanation: The code compiles because the JDBC API calls and imports are valid. At runtime, DriverManager.getConnection() must find a registered driver that accepts the connection URL. If none does, it throws a SQLException before the try block body runs.

DriverManager does not create a JDBC driver merely from the text of a URL. It asks registered drivers whether they accept the supplied JDBC URL, then uses the matching driver to attempt the connection. In this scenario, no registered driver accepts jdbc:inventory:..., so DriverManager.getConnection() fails with a SQLException, commonly reported as no suitable driver found. The call to con.isValid(2) is never reached.

Modern JDBC drivers are often auto-registered when present, so an explicit Class.forName() is not a compile-time requirement. The key issue is whether an appropriate driver is available and accepts the URL.

• URL creates driver fails because the URL selects among registered drivers; it does not instantiate an unavailable driver.
• Missing Class.forName() fails because the program can compile and modern JDBC drivers can auto-register when present.
• Host in URL fails because JDBC URLs commonly include host and port details; that is not an IllegalArgumentException rule.

Question 2

Topic: Accessing Databases Using JDBC

A method should insert an order row and an audit row as one transaction. The Connection is open and has auto-commit enabled when the method is called. During testing, audit.executeUpdate() throws a SQLException, but the order row remains in the database.

void save(Connection con) throws SQLException {
    try (var order = con.prepareStatement("insert into orders(id) values (?)");
         var audit = con.prepareStatement("insert into order_audit(order_id) values (?)")) {
        order.setInt(1, 101);
        order.executeUpdate();
        audit.setInt(1, 101);
        audit.executeUpdate();
        con.commit();
    } catch (SQLException e) {
        con.rollback();
        throw e;
    }
}

Which change best fixes the problem?

Options:

• A. Disable auto-commit only inside the catch block before rollback.

• B. Disable auto-commit before the first update, then commit or roll back.

• C. Remove con.commit() because try-with-resources commits automatically.

• D. Move con.commit() immediately after order.executeUpdate().

Best answer: B

Explanation: The transaction boundary must start before the first database change. With auto-commit enabled, each successful SQL statement is committed when it completes, so rollback() cannot undo the already committed order insert. Disable auto-commit before both updates, then commit only after both succeed.

JDBC Connection objects use auto-commit mode unless it is disabled. In auto-commit mode, each statement is its own transaction. Here, order.executeUpdate() completes successfully, so that insert is committed before audit.executeUpdate() fails. Calling rollback() after the failure cannot undo a statement that has already been committed. The fix is to call con.setAutoCommit(false) before executing the first update, then call commit() after both updates succeed and rollback() if either update fails.

The key transaction boundary is before the first update, not inside the error handler.

• Late auto-commit change fails because disabling auto-commit in the catch block cannot undo a statement already committed.
• Early commit fails because committing after the order insert makes the two updates separate transactions.
• Try-with-resources commit fails because closing statements does not commit a manual JDBC transaction.

Question 3

Topic: Accessing Databases Using JDBC

In JDBC, which java.sql.Connection rule correctly describes transaction control when an application needs several update statements to succeed or fail as one unit?

Options:

• A. Keep auto-commit enabled; call rollback() after a failed statement.

• B. Re-enable auto-commit to roll back pending changes.

• C. Call commit() while auto-commit is enabled after each update.

• D. Disable auto-commit; end the transaction with commit() or rollback().

Best answer: D

Explanation: JDBC connections start in auto-commit mode, where each completed statement is committed individually. To create a transaction boundary across multiple updates, disable auto-commit and explicitly call commit() or rollback().

The core JDBC transaction rule is controlled through Connection.setAutoCommit(false). In manual commit mode, SQL statements executed on that connection participate in the current transaction until the application calls commit() to make changes permanent or rollback() to discard them. After a commit or rollback, the next transactional statement begins a new transaction on that connection.

Calling commit() or rollback() while auto-commit is enabled is not the normal transaction-control mode and may throw SQLException. Also, changing auto-commit from false back to true commits the active transaction rather than rolling it back.

• Auto-commit rollback fails because completed statements are committed individually when auto-commit is enabled.
• Commit after each update fails because auto-commit mode already commits each completed statement.
• Re-enable to rollback fails because switching auto-commit back to true commits pending work if the mode changes.

Question 4

Topic: Accessing Databases Using JDBC

A DAO method compiles, but a test fails with SQLException: statement does not produce a ResultSet when the account exists.

int addBonus(Connection con, long id) throws SQLException {
    try (var ps = con.prepareStatement(
            "update accounts set balance = balance + 10 where id = ?")) {
        ps.setLong(1, id);
        try (var rs = ps.executeQuery()) {
            return rs.next() ? 1 : 0;
        }
    }
}

Which change is the best fix?

Options:

• A. Keep executeQuery() and call rs.next() earlier.

• B. Call execute() and then read getResultSet().

• C. Call executeUpdate() and return its update count.

• D. Disable auto-commit before calling executeQuery().

Best answer: C

Explanation: executeQuery() is for SQL that produces a ResultSet, such as a SELECT. This statement is an UPDATE, so the appropriate JDBC method is executeUpdate(), which returns the number of affected rows.

JDBC chooses execution methods based on the kind of result expected. executeQuery() expects a single ResultSet; using it for an UPDATE causes a SQLException because no result set is produced. For INSERT, UPDATE, DELETE, and many DDL statements, use executeUpdate(), which returns an int update count. The general execute() method is useful when the SQL might produce different result types, but for a known update statement, executeUpdate() is the clearest fix.

The method can simply return ps.executeUpdate() after setting the parameter.

• General execute trap fails because execute() would not make an update produce a ResultSet; the update count would need getUpdateCount().
• Cursor-position trap fails because the exception occurs before a ResultSet exists.
• Transaction trap fails because auto-commit changes commit behavior, not whether executeQuery() can run an UPDATE.

Question 5

Topic: Accessing Databases Using JDBC

A method executes a JDBC query with no bind parameters and creates a Connection, a PreparedStatement, and a ResultSet. The method signature already declares throws SQLException. The team requires each of those three JDBC objects to be explicitly managed by try-with-resources so that the main SQLException is propagated and close failures can be inspected as suppressed exceptions. Which implementation choice correctly applies this rule?

Options:

• A. Declare only Connection in the try-with-resources header.

• B. Declare Connection, then PreparedStatement, then ResultSet in the try-with-resources header.

• C. Catch SQLException, close the objects in the catch block, and return a default value.

• D. Create Connection before the try block and manage only PreparedStatement and ResultSet.

Best answer: B

Explanation: The correct choice explicitly registers all three JDBC objects as try-with-resources resources. Since the method declares throws SQLException, it can let the primary exception propagate while cleanup exceptions are recorded as suppressed exceptions.

Try-with-resources works with objects that implement AutoCloseable, including Connection, Statement/PreparedStatement, and ResultSet. Resources in the header are closed automatically in reverse order, so declaring them as Connection, then PreparedStatement, then ResultSet causes the ResultSet to close first, followed by the statement and connection. If the query body throws a SQLException and a close() also throws, Java propagates the primary exception and attaches the cleanup failure as a suppressed exception. A catch block is not required when the method declares throws SQLException.

The key takeaway is to register every resource that must be explicitly managed, in dependency-creation order.

• Only connection fails because it does not explicitly manage the PreparedStatement and ResultSet as required.
• Connection before try can leak the connection if later resource creation fails before cleanup is registered.
• Catch and return fails because it swallows the SQLException and prevents the caller from inspecting the real failure.

Question 6

Topic: Accessing Databases Using JDBC

A developer is troubleshooting this JDBC update. The done column is SQL BOOLEAN, and id is SQL INTEGER; the team wants type-compatible bindings and no SQL concatenation. It throws java.sql.SQLException: Parameter index out of range (0 < 1) before executing the update.

String sql = "UPDATE task SET done = ? WHERE id = ?";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
    ps.setString(0, "true");
    ps.setInt(1, taskId);
    ps.executeUpdate();
}

Which change is the best fix?

Options:

• A. Change the SQL placeholders to ?0 and ?1.

• B. Use ps.setBoolean(0, true); and keep ps.setInt(1, taskId);

• C. Use ps.setBoolean(1, true); and ps.setInt(2, taskId);

• D. Use ps.setString(1, "true"); and ps.setInt(2, taskId);

Best answer: C

Explanation: PreparedStatement parameter indexes start at 1, not 0. The first ? is for done, so it should be bound at index 1 with a boolean-compatible setter, and the second ? should be bound at index 2 with setInt.

JDBC binds PreparedStatement parameters by the position of each ? placeholder, starting with index 1. In this SQL statement, done = ? is the first placeholder and id = ? is the second. Because done is a SQL BOOLEAN, setBoolean(1, true) is the type-compatible binding. Because id is a SQL INTEGER, setInt(2, taskId) binds the second placeholder correctly.

The exception occurs before execution because index 0 is invalid for JDBC parameter binding. Changing the placeholder text or relying on string conversion does not address the one-based indexing rule and the requested type-compatible binding.

• Zero-based indexes fails because JDBC parameter positions start at 1, not 0.
• Text boolean binding fixes the index but relies on conversion instead of binding a boolean value.
• Numbered placeholders fails because standard JDBC PreparedStatement placeholders are plain ?, not ?0 or ?1.

Question 7

Topic: Accessing Databases Using JDBC

The imports shown are complete. Assume the DataSource works, the SQL is valid, executeQuery() creates a ResultSet with at least one row, and all close() calls succeed. What happens when printFirst() is called?

import java.sql.*;
import javax.sql.DataSource;

class Report {
  private final DataSource ds;
  Report(DataSource ds) { this.ds = ds; }

  ResultSet findNames() throws SQLException {
    try (Connection con = ds.getConnection();
         PreparedStatement ps = con.prepareStatement("select name from users")) {
      return ps.executeQuery();
    }
  }

  void printFirst() throws SQLException {
    ResultSet rs = findNames();
    System.out.print(rs.next());
  }
}

Options:

• A. true is printed.

• B. The code does not compile.

• C. false is printed.

• D. A SQLException is thrown at rs.next().

Best answer: D

Explanation: The code compiles, but printFirst() throws a SQLException before printing anything. In JDBC, closing a Statement or PreparedStatement closes its current ResultSet, and try-with-resources closes ps before findNames() returns to the caller.

Try-with-resources closes declared resources automatically when the block is exited, including exits caused by return. Here, ps.executeQuery() creates a ResultSet, but then the try-with-resources block closes ps before returning the reference. JDBC specifies that closing a Statement closes its current ResultSet, so the caller receives a reference to a closed result set. Evaluating rs.next() therefore throws SQLException, assuming the close operations themselves succeed as stated.

The key takeaway is that a ResultSet should not be returned from a method after its owning Statement has been closed.

• Printed row state fails because no boolean can be printed after rs.next() is invoked on a closed ResultSet.
• Not listed as resource fails because the ResultSet is still closed indirectly when its PreparedStatement closes.
• Compilation concern fails because returning a ResultSet is legal; the problem is runtime resource lifetime.

Question 8

Topic: Accessing Databases Using JDBC

Assume the stored procedure lookup_name(IN p_id INTEGER, OUT p_name VARCHAR) sets p_name to Mia for p_id = 5 and does not return a ResultSet. What happens when run(conn) is invoked with a valid open connection?

import java.sql.*;

class Report {
    static void run(Connection conn) throws SQLException {
        try (CallableStatement cs =
                 conn.prepareCall("{call lookup_name(?, ?)}")) {
            cs.registerOutParameter(2, Types.VARCHAR);
            cs.setInt(1, 5);
            boolean first = cs.execute();
            System.out.print(cs.getString(2) + ":" + first);
        }
    }
}

Options:

• A. It throws SQLException because getString() cannot read VARCHAR.

• B. It prints Mia:true.

• C. It prints Mia:false.

• D. It throws SQLException because registerOutParameter() must follow setInt().

Best answer: C

Explanation: A CallableStatement OUT parameter must be registered before the statement is executed, then read after execution. The order of registering the OUT parameter and setting the IN parameter is not important here. Since the procedure returns no ResultSet, execute() returns false.

For JDBC callable statements, parameters are 1-based and match the procedure call placeholders. In {call lookup_name(?, ?)}, parameter 1 is the IN value and parameter 2 is the OUT value. The code correctly registers parameter 2 as Types.VARCHAR, sets parameter 1, executes the call, and then retrieves parameter 2 with getString(2). The execute() method does not indicate whether OUT parameters exist; it returns true only when the first result is a ResultSet. With no result set from this procedure, the boolean value is false.

The key takeaway is to register OUT parameters before execution and retrieve them after execution using the correct parameter index.

• Result-set flag fails because execute() reports whether the first result is a ResultSet, not whether an OUT parameter was populated.
• Registration order fails because the OUT parameter may be registered before or after setting IN values, as long as it is before execute().
• Getter choice fails because getString() is valid for reading a VARCHAR OUT parameter.

Question 9

Topic: Accessing Databases Using JDBC

An open JDBC Connection con is available. The database function lookup_score accepts one VARCHAR argument and returns an INTEGER. You prepare it with JDBC escape syntax:

CallableStatement cs = con.prepareCall("{? = call lookup_score(?)}");

Which statement is correct for using this CallableStatement?

Options:

• A. Register index 1 as OUT, set index 2, execute, then read index 1.

• B. Set index 1, register index 2 as OUT, execute, then read index 2.

• C. Execute first, then register index 1 as OUT before reading it.

• D. Use executeQuery(), then read the return value from ResultSet column 1.

Best answer: A

Explanation: For a JDBC function escape call, the return value occupies parameter index 1. The function argument starts at index 2, so the return must be registered as an OUT parameter before execution and retrieved after execution.

CallableStatement parameters are 1-based. With the escape syntax {? = call lookup_score(?)}, the first placeholder represents the function return value, not the first function argument. Therefore index 1 is registered with registerOutParameter(1, Types.INTEGER), index 2 is set with the input value, the statement is executed, and the return is retrieved with a getter such as getInt(1). Reading a ResultSet column is a different mechanism and is not how the function return placeholder is retrieved.

• Reversed indexes fails because the return placeholder is index 1, while the input argument is index 2.
• Late registration fails because OUT parameters must be registered before the statement is executed.
• ResultSet retrieval fails because the registered function return is read from the CallableStatement, not from a query result column.

Question 10

Topic: Accessing Databases Using JDBC

A report query is prepared with this SQL. department is a VARCHAR column and active is a BOOLEAN column; dept is a String local variable.

String sql = "SELECT name FROM employee WHERE department = ? AND active = ?";
PreparedStatement ps = con.prepareStatement(sql);

Which calls correctly bind the two placeholders before executing the query?

Options:

• A. ps.setBoolean(1, true); ps.setString(2, dept);

• B. ps.setString(1, dept); ps.setBoolean(2, true);

• C. ps.setString(0, dept); ps.setBoolean(1, true);

• D. ps.setString(1, dept); ps.setString(2, "true");

Best answer: B

Explanation: JDBC PreparedStatement parameter indexes start at 1, not 0. The first ? is for the VARCHAR department value, and the second ? is for the BOOLEAN active value, so the setters must match those positions and types.

For a PreparedStatement, parameter indexes are based on the position of ? placeholders in the SQL text from left to right. The first placeholder in the query is department = ?, so index 1 should receive the String department value with setString. The second placeholder is active = ?, so index 2 should receive the boolean value with setBoolean.

Using index 0 is invalid for JDBC parameters. Swapping the values binds them to the wrong SQL types, and using a string literal for a boolean relies on nonportable driver conversion rather than the compatible JDBC setter.

• Zero-based indexing fails because JDBC parameter positions start at 1.
• Swapped placeholders fails because the boolean value is bound to the VARCHAR parameter.
• String boolean value fails because setBoolean is the compatible setter for a BOOLEAN parameter.

Continue with full practice

Use the Java 17 1Z0-829 Practice Test page for the full IT Mastery route, mixed-topic practice, timed mock exams, explanations, and web/mobile app access.

Try Java 17 1Z0-829 on Web View Java 17 1Z0-829 Practice Test

Related focused pages

• Free Java 17 1Z0-829 Full-Length Practice Exam
• Core Value Types
• Controlling Program Flow
• Object-Oriented Java
• Handling Exceptions
• Working with Arrays and Collections
• Streams and Lambdas
• Packaging and Modules
• Managing Concurrent Code Execution
• Using Java I/O API
• Implementing Localization
• Logging and Annotations

Free review resource

Read the Java 17 1Z0-829 Cheat Sheet on Tech Exam Lexicon, then return to IT Mastery for timed practice.