1Z0-830 JDBC - Java SE 21 Certification Prep

JDBC (Java Database Connectivity) is the standard Java API for connecting to relational databases, executing SQL, and processing results. It defines a set of interfaces in the java.sql package; the actual database-specific behavior is provided by a driver that implements those interfaces. The 1Z0-830 exam tests your understanding of the full JDBC workflow: obtaining connections, executing statements, navigating result sets, managing transactions, and handling resources correctly.

JDBC Overview

The JDBC API is built around a small set of core interfaces. Each interface has a specific role in the query lifecycle. Understanding what each one does and how they relate to each other is the foundation for everything else on this topic.

// JDBC URL format: jdbc:subprotocol:subname
// The subprotocol identifies the driver; the subname identifies the database.

// Common JDBC URL formats:
// jdbc:mysql://localhost:3306/mydb
// jdbc:postgresql://localhost:5432/mydb
// jdbc:oracle:thin:@localhost:1521:orcl
// jdbc:h2:mem:testdb          (H2 in-memory database - useful for testing)
// jdbc:derby:mydb;create=true (Derby embedded database)

Establishing Connections

DriverManager.getConnection() is the standard way to obtain a Connection for the exam. In production applications, a DataSource with a connection pool is preferred because it avoids the cost of creating a new physical connection on every call. Both implement AutoCloseable, so both should be used with try-with-resources.

String url      = "jdbc:mysql://localhost:3306/mydb";
String user     = "root";
String password = "secret";

// Method 1: Username and password as separate arguments
Connection conn = DriverManager.getConnection(url, user, password);

// Method 2: Credentials in a Properties object
// Useful when you need to pass additional driver-specific properties
Properties props = new Properties();
props.setProperty("user", user);
props.setProperty("password", password);
props.setProperty("useSSL", "false");  // Driver-specific property example
Connection conn2 = DriverManager.getConnection(url, props);

// Method 3: Credentials embedded in the URL
// Avoid this in production - credentials appear in logs and thread dumps
String fullUrl = "jdbc:mysql://localhost:3306/mydb?user=root&password=secret";
Connection conn3 = DriverManager.getConnection(fullUrl);

// Always use try-with-resources so the connection is closed even if an exception occurs
try (Connection conn4 = DriverManager.getConnection(url, user, password)) {
    // Use the connection here
}  // conn4.close() is called automatically

// DataSource - preferred in production code
// Obtained from a JNDI registry, dependency injection framework, or connection pool library
DataSource ds = // configured externally
Connection conn5 = ds.getConnection();

Statement

Statement is the simplest way to execute SQL. Because it takes a raw SQL string at execution time, it is suitable only for static queries with no external input. Never use Statement to embed user-supplied values directly into a SQL string - use PreparedStatement instead.

try (Connection conn = DriverManager.getConnection(url, user, password);
     Statement stmt = conn.createStatement()) {

    // executeQuery() - use for SELECT; returns a ResultSet
    ResultSet rs = stmt.executeQuery("SELECT * FROM employees");

    // executeUpdate() - use for INSERT, UPDATE, DELETE, DDL; returns row count
    int rows = stmt.executeUpdate("UPDATE employees SET salary = 50000 WHERE id = 1");

    // execute() - use when you do not know whether the result is a ResultSet or a count
    // Returns true if the first result is a ResultSet, false if it is an update count
    boolean isResultSet = stmt.execute("SELECT * FROM employees");
    if (isResultSet) {
        ResultSet rs2 = stmt.getResultSet();
    } else {
        int updateCount = stmt.getUpdateCount();
    }
}

// Creating a Statement with scrollability and concurrency options
Statement scrollableStmt = conn.createStatement(
    ResultSet.TYPE_SCROLL_INSENSITIVE,  // Can navigate in both directions
    ResultSet.CONCUR_READ_ONLY          // Cannot update rows through the ResultSet
);

// ResultSet type options:
// TYPE_FORWARD_ONLY (default) - cursor moves forward only with next()
// TYPE_SCROLL_INSENSITIVE     - scrollable; reflects a static snapshot of data at query time
// TYPE_SCROLL_SENSITIVE       - scrollable; reflects changes made to the database after the query

// ResultSet concurrency options:
// CONCUR_READ_ONLY (default) - data can be read but not modified through the ResultSet
// CONCUR_UPDATABLE           - rows can be updated or deleted through the ResultSet

ResultSet

A ResultSet starts positioned before the first row. You must call next() at least once before reading any data. Column indices are 1-based. For scrollable result sets, additional navigation methods allow moving backward, jumping to a specific row, or checking the current position.

try (Connection conn = DriverManager.getConnection(url, user, password);
     Statement stmt = conn.createStatement();
     ResultSet rs = stmt.executeQuery("SELECT id, name, salary FROM employees")) {

    while (rs.next()) {  // Advances cursor; returns false when no more rows remain

        // Reading by column index - index is 1-based (not 0-based)
        int id        = rs.getInt(1);
        String name   = rs.getString(2);
        double salary = rs.getDouble(3);

        // Reading by column name - preferred; more readable and resilient to column reordering
        int idByName        = rs.getInt("id");
        String nameByName   = rs.getString("name");
        double salaryByName = rs.getDouble("salary");

        // Handling SQL NULL values
        // Primitive getters (getInt, getDouble, etc.) return 0 or false for SQL NULL
        // You must call wasNull() immediately after to distinguish 0 from NULL
        int bonus = rs.getInt("bonus");
        if (rs.wasNull()) {
            System.out.println("Bonus is NULL");
        }

        // For nullable columns, prefer getObject() which returns null for SQL NULL
        Object bonusObj = rs.getObject("bonus");  // Returns null if SQL NULL
    }
}

// Navigation methods for scrollable ResultSets (TYPE_SCROLL_INSENSITIVE or TYPE_SCROLL_SENSITIVE)
rs.next();           // Move to next row; returns false at end
rs.previous();       // Move to previous row; returns false at beginning
rs.first();          // Move to first row; returns false if ResultSet is empty
rs.last();           // Move to last row; returns false if ResultSet is empty
rs.beforeFirst();    // Move to position before first row (initial position)
rs.afterLast();      // Move to position after last row
rs.absolute(5);      // Move to row 5 (positive = from start, negative = from end)
rs.relative(2);      // Move 2 rows forward from current position
rs.relative(-1);     // Move 1 row backward from current position

// Checking current cursor position
rs.isFirst();        // true if on first row
rs.isLast();         // true if on last row
rs.isBeforeFirst();  // true if before first row (initial state)
rs.isAfterLast();    // true if after last row
rs.getRow();         // Returns current row number (1-based); 0 if not on a row

// Inspecting column structure via metadata
ResultSetMetaData meta = rs.getMetaData();
int columnCount      = meta.getColumnCount();
String columnName    = meta.getColumnName(1);    // 1-based
String columnType    = meta.getColumnTypeName(1);
boolean isNullable   = meta.isNullable(1) == ResultSetMetaData.columnNullable;

PreparedStatement

PreparedStatement precompiles the SQL when it is created, then accepts parameter values before each execution. This provides two key benefits: it prevents SQL injection by treating all parameter values as data rather than SQL syntax, and it allows the database to reuse the same query execution plan across multiple calls, which is more efficient than recompiling a new statement each time.

// The ? placeholders are filled in by set*() methods before execution
String sql = "INSERT INTO employees (name, salary, hire_date) VALUES (?, ?, ?)";

try (Connection conn = DriverManager.getConnection(url, user, password);
     PreparedStatement pstmt = conn.prepareStatement(sql)) {

    // Parameters are set by position (1-based)
    pstmt.setString(1, "Alice");
    pstmt.setDouble(2, 75000.0);
    pstmt.setDate(3, java.sql.Date.valueOf(LocalDate.now()));

    int rowsInserted = pstmt.executeUpdate();
}

// Parameterized SELECT
String querySql = "SELECT * FROM employees WHERE salary > ? AND department = ?";
try (Connection conn = DriverManager.getConnection(url, user, password);
     PreparedStatement pstmt = conn.prepareStatement(querySql)) {

    pstmt.setDouble(1, 50000.0);
    pstmt.setString(2, "Engineering");

    // executeQuery() on PreparedStatement takes no arguments - SQL was given at creation
    try (ResultSet rs = pstmt.executeQuery()) {
        while (rs.next()) {
            // Process results
        }
    }
}

// Setting SQL NULL values explicitly
pstmt.setNull(1, Types.VARCHAR);   // Set parameter 1 to SQL NULL with type hint
pstmt.setObject(2, null);         // setObject with null also inserts SQL NULL

// Batch inserts - execute multiple rows in one round trip
PreparedStatement batchStmt = conn.prepareStatement(
    "INSERT INTO employees (name, salary) VALUES (?, ?)");

for (Employee emp : employees) {
    batchStmt.setString(1, emp.getName());
    batchStmt.setDouble(2, emp.getSalary());
    batchStmt.addBatch();  // Adds current parameters as a batch item
}

int[] results = batchStmt.executeBatch();
// results[i] contains the row count for the i-th batch item
// or Statement.SUCCESS_NO_INFO if the driver cannot determine the count

// Retrieving auto-generated keys after an INSERT
PreparedStatement insertStmt = conn.prepareStatement(sql,
    Statement.RETURN_GENERATED_KEYS);  // Must request this at creation time
insertStmt.executeUpdate();

try (ResultSet keys = insertStmt.getGeneratedKeys()) {
    if (keys.next()) {
        long generatedId = keys.getLong(1);
    }
}

CallableStatement

CallableStatement is used to execute stored procedures and database functions. It uses a standardized escape syntax so that the same Java code works across different database vendors. IN parameters pass values into the procedure; OUT parameters receive values back from the procedure; INOUT parameters do both.

// Calling a stored procedure with IN and OUT parameters
// Assume: CREATE PROCEDURE get_employee(IN emp_id INT, OUT emp_name VARCHAR(100))
String call = "{call get_employee(?, ?)}";

try (Connection conn = DriverManager.getConnection(url, user, password);
     CallableStatement cstmt = conn.prepareCall(call)) {

    // Set the IN parameter just like PreparedStatement
    cstmt.setInt(1, 123);

    // Register the OUT parameter before calling execute()
    // The Types constant must match the database column type
    cstmt.registerOutParameter(2, Types.VARCHAR);

    cstmt.execute();

    // Retrieve the OUT parameter value after execution
    String name = cstmt.getString(2);
}

// Calling a stored function that returns a value
// The return value is always the first parameter (index 1) in the escape syntax
String funcCall = "{? = call calculate_bonus(?)}";

try (Connection conn = DriverManager.getConnection(url, user, password);
     CallableStatement cstmt = conn.prepareCall(funcCall)) {

    // Register the return value as an OUT parameter at index 1
    cstmt.registerOutParameter(1, Types.DOUBLE);

    // The IN parameter for the function starts at index 2
    cstmt.setInt(2, 123);

    cstmt.execute();

    double bonus = cstmt.getDouble(1);
}

// INOUT parameter - set a value in AND read a value out using the same index
// Must both set the value AND register it as an OUT parameter before executing
cstmt.setInt(1, 100);
cstmt.registerOutParameter(1, Types.INTEGER);
cstmt.execute();
int result = cstmt.getInt(1);  // Contains the value the procedure wrote to this parameter

Transactions

By default, every statement in JDBC executes in its own transaction and is committed immediately. This is called auto-commit mode. To group multiple statements into a single atomic transaction, you must disable auto-commit, execute the statements, and then explicitly commit or roll back. If you do not roll back on failure, the connection may be left in an inconsistent state when returned to a pool.

Connection conn = DriverManager.getConnection(url, user, password);

try {
    conn.setAutoCommit(false);  // Start manual transaction mode

    Statement stmt = conn.createStatement();
    stmt.executeUpdate("UPDATE accounts SET balance = balance - 100 WHERE id = 1");
    stmt.executeUpdate("UPDATE accounts SET balance = balance + 100 WHERE id = 2");

    conn.commit();  // Both updates succeed - make them permanent

} catch (SQLException e) {
    conn.rollback();  // One update failed - undo everything in this transaction
    throw e;
} finally {
    conn.setAutoCommit(true);  // Restore default so the connection is reusable
    conn.close();
}

// Savepoints - partial rollback within a transaction
// A savepoint marks a point you can roll back to without losing everything
conn.setAutoCommit(false);

Savepoint sp1 = conn.setSavepoint("point1");

try {
    stmt.executeUpdate("INSERT INTO orders ...");

    Savepoint sp2 = conn.setSavepoint("point2");

    stmt.executeUpdate("INSERT INTO order_items ...");  // This might fail

} catch (SQLException e) {
    // Roll back only to sp2 - the order INSERT is still in the transaction
    conn.rollback(sp2);
}

conn.commit();  // Commits everything up to and including the order INSERT

// Releasing a savepoint frees its resources (optional but good practice)
conn.releaseSavepoint(sp1);

// Transaction isolation levels - control visibility of uncommitted changes
// Listed from lowest to highest isolation (and lowest to highest performance cost)
conn.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED); // Dirty reads possible
conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);   // No dirty reads
conn.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);  // No non-repeatable reads
conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);     // Fully isolated; slowest

Resource Management

JDBC resources - ResultSet, Statement, and Connection - all implement AutoCloseable and must be closed after use. Failing to close them leaks database cursors and connections, which can exhaust the database server's connection limit. Try-with-resources is the correct and idiomatic way to handle this.

// All three resources are closed automatically in reverse declaration order:
// 1. ResultSet is closed first
// 2. PreparedStatement is closed second
// 3. Connection is closed last
try (Connection conn = DriverManager.getConnection(url, user, password);
     PreparedStatement pstmt = conn.prepareStatement(sql);
     ResultSet rs = pstmt.executeQuery()) {

    while (rs.next()) {
        // Process rows
    }
}

// Note: closing a Statement also closes any open ResultSet it produced.
// Closing a Connection also closes all Statements and ResultSets created from it.
// However, relying on this cascade is bad practice - close resources explicitly.

// SQLException carries database-specific diagnostic information
try {
    // JDBC operations
} catch (SQLException e) {
    // SQLState is a standardized 5-character code (e.g., "23000" = integrity constraint)
    System.err.println("SQL State:  " + e.getSQLState());
    // Error code is vendor-specific (e.g., MySQL 1062 = duplicate entry)
    System.err.println("Error Code: " + e.getErrorCode());
    System.err.println("Message:    " + e.getMessage());

    // SQLException can chain multiple exceptions (uncommon but possible)
    SQLException next = e.getNextException();
    while (next != null) {
        System.err.println("Chained: " + next.getMessage());
        next = next.getNextException();
    }

    // SQLException also implements Iterable, so you can use a for-each loop
    for (Throwable t : e) {
        System.err.println(t.getMessage());
    }
}

// Common SQLException subclasses (JDBC 4.0+)
// SQLTransientException      - may succeed if retried (e.g., timeout, lock)
// SQLNonTransientException   - will not succeed on retry (e.g., syntax error, bad data)
// SQLRecoverableException    - application can recover by closing and reopening the connection
// BatchUpdateException       - thrown by executeBatch(); contains per-item update counts

1Z0-830 Java SE 21 Certification - Table of Contents

Master all exam topics with comprehensive study guides and practice examples.

Data Types Primitives, wrappers, strings, var Control Flow If/else, switch, loops, assertions OOP Concepts Classes, inheritance, records, sealed Exception Handling Try-catch, multi-catch, try-with-resources Collections List, Set, Map, Queue, Sequenced Streams & Lambdas Functional interfaces, Stream API, Optional Modules & Packaging JPMS, services, migration Concurrency Threads, executors, virtual threads I/O & NIO.2 Files, streams, serialization JDBC Connections, statements, transactions Localization Locale, ResourceBundle, formatting New Features Java 8-21 enhancements