JDBC Queries, Updates, and Result Processing

Introduction

Java Database Connectivity (JDBC) is the standard Java API for communicating with relational databases. It provides classes and interfaces for opening database connections, executing SQL statements, binding parameters, processing returned rows, managing transactions, discovering database capabilities, and invoking stored procedures.

JDBC is deliberately database-independent. A Java program communicates with a JDBC driver, and the driver translates JDBC calls into the protocol understood by a particular database system such as PostgreSQL, MySQL, Oracle, or Microsoft SQL Server. This separation allows application code to remain largely stable when the underlying database changes.

JDBC is important in professional Java development because many business systems store durable data in relational databases. Typical applications include customer management, banking, inventory, payroll, education, health care, and online commerce systems. Although modern applications frequently use object-relational mapping frameworks, knowledge of JDBC remains essential for understanding database behavior, diagnosing performance problems, writing efficient data-access code, and handling SQL operations that frameworks may not express conveniently.

Learning Objectives

After completing this chapter, the learner should be able to:

• explain the JDBC architecture and resource-management model;
• distinguish among Statement, PreparedStatement, and CallableStatement;
• implement create, read, update, and delete operations;
• bind Java values safely to SQL parameters;
• process ResultSet objects, including nullable values and generated keys;
• invoke stored procedures and functions;
• use metadata to inspect result structures and database capabilities;
• explain common SQL-to-Java type mappings;
• identify transaction, security, portability, and performance considerations.

1. JDBC Architecture and Execution Model

A typical JDBC operation follows this sequence:

1. Obtain a Connection from a DataSource or DriverManager.
2. Create a statement object.
3. Bind parameters if required.
4. Execute SQL.
5. Process the result or update count.
6. Commit or roll back the transaction when appropriate.
7. Close resources.

The primary interfaces are:

Modern JDBC drivers are normally discovered automatically when they are placed on the application classpath. Applications should generally obtain connections through a connection pool exposed as a DataSource, rather than creating a new physical connection for every request. A pooled connection is logically borrowed and returned; closing it usually returns it to the pool.

A connection is commonly created as follows:

String url = "jdbc:postgresql://localhost:5432/training";
String user = "app_user";
String password = System.getenv("DB_PASSWORD");

try (Connection connection = DriverManager.getConnection(url, user, password)) {
    System.out.println("Connected");
}

In production, credentials should be supplied through a secure configuration service, environment variables, or a secrets manager. Passwords should not be committed to source code.

Automatic resource management

JDBC objects implement AutoCloseable. A try-with-resources statement closes resources in reverse order, even when an exception occurs:

String sql = "SELECT id, name FROM department ORDER BY name";

try (Connection connection = dataSource.getConnection();
     PreparedStatement statement = connection.prepareStatement(sql);
     ResultSet results = statement.executeQuery()) {

    while (results.next()) {
        int id = results.getInt("id");
        String name = results.getString("name");
        System.out.println(id + ": " + name);
    }
} catch (SQLException ex) {
    // Log diagnostic information without exposing credentials.
    throw new DataAccessException("Unable to read departments", ex);
}

The order is significant: a ResultSet depends on its statement, and a statement depends on its connection.

2. Statement Types and Parameter Binding

Statement

Statement is suitable for SQL that contains no user-supplied values and is constructed from trusted, fixed text:

try (Statement statement = connection.createStatement();
     ResultSet results = statement.executeQuery(
         "SELECT id, name FROM department ORDER BY name")) {
    while (results.next()) {
        System.out.println(results.getString("name"));
    }
}

It should not be used by concatenating user input into SQL. The following is unsafe:

String sql = "SELECT * FROM account WHERE username = '" + username + "'";

An attacker could supply SQL syntax as part of username. This is SQL injection.

PreparedStatement

PreparedStatement uses placeholders represented by question marks. Values are supplied separately from SQL syntax:

String sql = "SELECT id, username, balance FROM account "
           + "WHERE username = ? AND status = ?";

try (PreparedStatement statement = connection.prepareStatement(sql)) {
    statement.setString(1, username);
    statement.setString(2, "ACTIVE");

    try (ResultSet results = statement.executeQuery()) {
        while (results.next()) {
            System.out.println(results.getBigDecimal("balance"));
        }
    }
}

Parameter indexes begin at 1, not 0. A parameter marker represents a value, not an SQL identifier. Thus it cannot normally replace a table name, column name, or SQL keyword. Dynamic identifiers must be selected from a trusted allow-list.

Important binding methods include:

For modern Java time classes, setObject and getObject are often the clearest choice, but exact support depends on the JDBC driver and database.

CallableStatement

CallableStatement invokes stored procedures or functions. Its syntax is database-dependent, but JDBC escape syntax is commonly used:

String call = "{call calculate_discount(?, ?, ?)}";

try (CallableStatement statement = connection.prepareCall(call)) {
    statement.setLong(1, customerId);
    statement.setBigDecimal(2, orderTotal);
    statement.registerOutParameter(3, Types.DECIMAL);
    statement.execute();

    BigDecimal discount = statement.getBigDecimal(3);
}

For a function returning a value, a common form is:

String call = "{? = call get_account_balance(?)}";
try (CallableStatement statement = connection.prepareCall(call)) {
    statement.registerOutParameter(1, Types.DECIMAL);
    statement.setLong(2, accountId);
    statement.execute();
    BigDecimal balance = statement.getBigDecimal(1);
}

The exact procedure declaration and call syntax must be verified against the target database.

3. CRUD Operations

CRUD means create, read, update, and delete. In a relational system, these operations correspond primarily to INSERT, SELECT, UPDATE, and DELETE.

Create: inserting rows

String sql = "INSERT INTO student (student_no, full_name, email) "
           + "VALUES (?, ?, ?)";

try (PreparedStatement statement = connection.prepareStatement(sql)) {
    statement.setString(1, "ST-2041");
    statement.setString(2, "Amina Rahman");
    statement.setString(3, "[email protected]");

    int affected = statement.executeUpdate();
    if (affected != 1) {
        throw new SQLException("Expected one inserted row, got " + affected);
    }
}

executeUpdate() returns the number of affected rows for an insert, update, or delete. A successful operation does not necessarily affect exactly one row; the expected count depends on the business rule.

Read: selecting rows

String sql = "SELECT id, student_no, full_name, email "
           + "FROM student WHERE id = ?";

try (PreparedStatement statement = connection.prepareStatement(sql)) {
    statement.setLong(1, studentId);
    try (ResultSet results = statement.executeQuery()) {
        if (results.next()) {
            Student student = new Student(
                results.getLong("id"),
                results.getString("student_no"),
                results.getString("full_name"),
                results.getString("email")
            );
        } else {
            // No matching row exists.
        }
    }
}

A ResultSet cursor initially points before the first row. next() advances it and returns true when a row is available. A query expected to return one row should check both the presence of the row and, where required, the absence of a second row.

Update

String sql = "UPDATE student SET email = ? WHERE id = ?";
try (PreparedStatement statement = connection.prepareStatement(sql)) {
    statement.setString(1, newEmail);
    statement.setLong(2, studentId);

    int affected = statement.executeUpdate();
    if (affected == 0) {
        throw new StudentNotFoundException(studentId);
    }
}

An update count of zero can mean that the row does not exist or that a condition prevented modification. Applications should interpret this result according to the SQL predicate and business semantics.

Delete

String sql = "DELETE FROM student WHERE id = ?";
try (PreparedStatement statement = connection.prepareStatement(sql)) {
    statement.setLong(1, studentId);
    int affected = statement.executeUpdate();
    if (affected != 1) {
        throw new SQLException("Unexpected delete count: " + affected);
    }
}

Deletion is irreversible unless a transaction is rolled back or the system maintains an archival strategy. Many enterprise systems use a status column for logical deletion when auditability is required.

4. Generated Keys

Databases often generate primary keys using identity columns, sequences, or other mechanisms. JDBC can request generated keys when an insert is prepared:

String sql = "INSERT INTO student (student_no, full_name) VALUES (?, ?)";

try (PreparedStatement statement = connection.prepareStatement(
        sql, Statement.RETURN_GENERATED_KEYS)) {

    statement.setString(1, "ST-2042");
    statement.setString(2, "Daniel Okoro");
    statement.executeUpdate();

    try (ResultSet keys = statement.getGeneratedKeys()) {
        if (!keys.next()) {
            throw new SQLException("Database did not return a generated key");
        }
        long generatedId = keys.getLong(1);
        System.out.println("New student ID: " + generatedId);
    }
}

Some databases or drivers require the generated column names to be supplied instead:

connection.prepareStatement(sql, new String[] { "id" });

Generated-key behavior should be tested with the actual database and driver. When a parent row and child rows must be inserted together, retrieve the parent key before inserting dependent records and use one transaction.

5. ResultSet Processing

A ResultSet represents a tabular result. Its cursor may be forward-only or scrollable, and it may be read-only or updatable. The default mode is commonly forward-only and read-only, which is efficient for ordinary queries.

try (PreparedStatement statement = connection.prepareStatement(
        "SELECT id, full_name, birth_date FROM student")) {
    try (ResultSet results = statement.executeQuery()) {
        while (results.next()) {
            long id = results.getLong(1);
            String name = results.getString(2);
            LocalDate birthDate = results.getObject(3, LocalDate.class);
        }
    }
}

Column labels are usually clearer than numeric indexes. If a query contains aliases, use the alias:

SELECT s.full_name AS student_name FROM student s

String name = results.getString("student_name");

Null values

SQL NULL means unknown, missing, or inapplicable; it is not the same as Java zero, false, or an empty string. Primitive getters can obscure nullity. For example, getInt returns 0 for both an actual zero and a SQL NULL. Test wasNull() immediately after the getter:

int value = results.getInt("credits");
Integer credits = results.wasNull() ? null : value;

Alternatively, use a nullable Java type with getObject:

Integer credits = results.getObject("credits", Integer.class);

Large values and streams

Large text and binary values can be accessed through Reader and InputStream objects. Streaming may reduce memory usage, but the stream normally remains dependent on the open result set and connection. It must be consumed before those resources are closed.

Multiple results and update counts

Some procedures and compound statements produce multiple results. JDBC provides execute() followed by getResultSet(), getUpdateCount(), and getMoreResults():

boolean hasResult = statement.execute();
while (true) {
    if (hasResult) {
        try (ResultSet results = statement.getResultSet()) {
            while (results.next()) {
                // Process rows.
            }
        }
    } else {
        int count = statement.getUpdateCount();
        if (count == -1) {
            break;
        }
        System.out.println("Updated rows: " + count);
    }
    hasResult = statement.getMoreResults();
}

Driver behavior for multiple results varies, so this pattern should be used only when the database contract requires it.

6. SQL-to-Java Type Mapping

JDBC maps SQL types to Java types, but exact behavior depends on the database and driver.

For financial values, BigDecimal is normally preferred because it represents decimal arithmetic more predictably than double. A database column defined as DECIMAL(p, s) has precision p total decimal digits and scale s digits to the right of the decimal point. The application must still apply appropriate rounding and validation rules.

Date and time handling requires special care. A date such as 2025-04-10 is not necessarily an instant in time. An instant requires a time zone or offset. Systems should define whether timestamps are stored and displayed in UTC, a fixed offset, or a user-specific zone.

7. Transactions and Consistency

A transaction groups operations into an atomic unit. By default, JDBC connections commonly use auto-commit mode, in which each statement is committed automatically. Multi-step business operations should usually disable auto-commit:

try (Connection connection = dataSource.getConnection()) {
    connection.setAutoCommit(false);
    try {
        // Insert order header.
        // Insert order lines.
        // Reduce inventory.
        connection.commit();
    } catch (SQLException | RuntimeException ex) {
        connection.rollback();
        throw ex;
    } finally {
        connection.setAutoCommit(true);
    }
}

The transaction should be short. Holding a connection while performing network calls or user interaction can exhaust a connection pool and increase locking. Isolation levels, including READ_COMMITTED, REPEATABLE_READ, and SERIALIZABLE, affect visibility and concurrency. The available behavior is database-specific.

Savepoints provide partial rollback:

Savepoint savepoint = connection.setSavepoint("before_optional_step");
try {
    // Optional operation.
} catch (SQLException ex) {
    connection.rollback(savepoint);
}

Transactions enforce database consistency, but application code must still validate input and handle conflicts such as duplicate keys or optimistic-locking failures.

8. Metadata

Metadata supports tools, diagnostics, migration utilities, and generic data-access components. DatabaseMetaData describes the database and driver:

DatabaseMetaData metadata = connection.getMetaData();
System.out.println(metadata.getDatabaseProductName());
System.out.println(metadata.getDatabaseProductVersion());
System.out.println(metadata.getDriverName());
System.out.println(metadata.supportsTransactions());

It can also expose tables, columns, keys, and supported SQL features:

try (ResultSet tables = metadata.getTables(
        null, null, "%", new String[] { "TABLE" })) {
    while (tables.next()) {
        System.out.println(tables.getString("TABLE_NAME"));
    }
}

ResultSetMetaData describes a query result:

try (PreparedStatement statement = connection.prepareStatement(
        "SELECT id, full_name FROM student");
     ResultSet results = statement.executeQuery()) {

    ResultSetMetaData resultMetadata = results.getMetaData();
    for (int i = 1; i <= resultMetadata.getColumnCount(); i++) {
        System.out.println(resultMetadata.getColumnLabel(i)
                + ": " + resultMetadata.getColumnTypeName(i));
    }
}

Metadata should not automatically be treated as a security boundary. Database permissions, schemas, and driver behavior determine what is visible. Generic metadata-driven applications must also defend against unsafe identifier generation and unexpected schema changes.

9. Performance and Reliability Practices

Prepared statements improve safety and may improve performance when the database or driver reuses execution plans. They do not automatically make every query efficient. Performance depends on indexes, predicates, join strategy, cardinality, network transfer, and transaction behavior.

Professional JDBC code should follow these practices:

• select only required columns rather than using SELECT * in stable application queries;
• use indexes that support frequent search and join predicates;
• process large results incrementally rather than storing every row unnecessarily;
• configure fetch size only after testing with the target driver;
• close statements, result sets, and connections promptly;
• use connection pools with bounded capacity and timeouts;
• log SQL timing and safe diagnostic identifiers, but never passwords or sensitive parameter values;
• translate low-level exceptions into meaningful application-level errors;
• distinguish transient failures from constraint violations and programming errors;
• use batch operations for many similar writes.

Example batch insertion:

String sql = "INSERT INTO tag (name) VALUES (?)";
try (PreparedStatement statement = connection.prepareStatement(sql)) {
    for (String tag : tags) {
        statement.setString(1, tag);
        statement.addBatch();
    }
    int[] counts = statement.executeBatch();
}

Batch behavior, generated keys, and partial failure semantics vary by driver. A transaction is often appropriate when the batch must succeed as a unit.

10. Security, Governance, and Ethical Considerations

SQL injection prevention begins with parameter binding, but secure data access requires more. Applications should apply least-privilege database accounts, encrypt connections where appropriate, validate authorization independently of input validation, and protect personally identifiable information.

Access should be auditable. Logs should record enough information to investigate failures without exposing passwords, tokens, health information, or complete financial records. Retention and deletion should comply with applicable law and institutional policy.

Stored procedures can centralize authorization and business rules, but they are not automatically secure. Dynamic SQL inside a procedure can still be injectable. Similarly, an ORM does not eliminate the need to understand SQL or review generated queries.

AI-assisted programming can help generate JDBC boilerplate, but generated code must be reviewed for injection vulnerabilities, incorrect transaction boundaries, resource leaks, invalid type assumptions, and accidental disclosure of credentials. AI tools should not receive production secrets or unredacted personal data.

11. Applied Case: Course Registration Service

Background

A college maintains student, course, and registration tables. A registration request must insert a registration row, reject duplicate registrations, and avoid exceeding the course capacity.

Problem

A naïve implementation checks capacity with one query and inserts with a separate auto-committed statement. Two simultaneous requests may both observe one remaining seat and both insert, exceeding capacity.

Analysis

The availability check and insert form one business unit. The service must use a transaction and an appropriate concurrency strategy. A database constraint should prevent duplicate (student_id, course_id) pairs. Capacity can be protected by locking the course row during the transaction or by using an atomic conditional update.

Approach

One approach is:

1. begin a transaction;
2. lock the selected course row using database-supported row locking;
3. verify that the course is not full;
4. insert the registration with a PreparedStatement;
5. update the registered count or use a database-maintained count;
6. commit;
7. roll back on any exception.

Illustrative code:

String courseSql = "SELECT capacity, registered_count FROM course "
                 + "WHERE id = ? FOR UPDATE";
String insertSql = "INSERT INTO registration(student_id, course_id) VALUES (?, ?)";

try (Connection connection = dataSource.getConnection()) {
    connection.setAutoCommit(false);
    try {
        int capacity;
        int registered;
        try (PreparedStatement statement = connection.prepareStatement(courseSql)) {
            statement.setLong(1, courseId);
            try (ResultSet results = statement.executeQuery()) {
                if (!results.next()) {
                    throw new CourseNotFoundException(courseId);
                }
                capacity = results.getInt("capacity");
                registered = results.getInt("registered_count");
            }
        }

        if (registered >= capacity) {
            throw new CourseFullException(courseId);
        }

        try (PreparedStatement statement = connection.prepareStatement(insertSql)) {
            statement.setLong(1, studentId);
            statement.setLong(2, courseId);
            statement.executeUpdate();
        }

        try (PreparedStatement statement = connection.prepareStatement(
                "UPDATE course SET registered_count = registered_count + 1 WHERE id = ?")) {
            statement.setLong(1, courseId);
            statement.executeUpdate();
        }
        connection.commit();
    } catch (Exception ex) {
        connection.rollback();
        throw ex;
    }
}

Outcome

The transaction prevents another transaction from changing the locked course row until the decision is complete. The unique constraint prevents duplicate registration. In production, the service should also handle deadlocks, retries, cancellation rules, and reconciliation of the stored count.

Learning points

This case demonstrates that JDBC syntax alone does not guarantee correctness. Correct database programming combines parameter binding, result processing, constraints, transaction design, concurrency control, and clear error handling.

12. Limitations and Contemporary Practice

JDBC is low-level and verbose. It exposes database-specific differences and requires developers to manage mapping, transactions, and resource lifecycles. Frameworks such as Jakarta Persistence, Spring JDBC, and jOOQ can reduce boilerplate, but they ultimately depend on relational database principles and often expose JDBC concepts directly.

Reactive database APIs and asynchronous application architectures introduce different resource and back-pressure models, but they do not remove the need for safe SQL, correct transactions, and type-aware result processing. Cloud databases and serverless deployments make connection pooling, short transactions, retry policies, and observability even more important.

The most maintainable design usually isolates JDBC code in a repository or data-access layer. Domain services should express business operations, while repositories manage SQL and mapping. This separation supports testing, migration, and controlled database-specific behavior.

Chapter Summary

JDBC provides Java applications with a standard interface to relational databases. Statement is appropriate for fixed trusted SQL, while PreparedStatement is the normal choice for parameterized operations because it separates values from SQL syntax and helps prevent injection. CallableStatement invokes stored procedures and functions.

CRUD operations use executeQuery() for queries and executeUpdate() for inserts, updates, and deletes. Generated keys can be retrieved after inserts. ResultSet processing requires correct cursor movement, careful handling of SQL NULL, and suitable SQL-to-Java type choices. BigDecimal is generally preferable for exact decimal values, and date-time mappings require explicit consideration of time zones.

Transactions combine related operations into consistent units. Metadata describes database capabilities and result structures. Professional JDBC applications use connection pooling, try-with-resources, least-privilege credentials, parameter binding, constraints, logging, and tested performance strategies. Reliable database programming requires both API knowledge and sound concurrency, security, and data-governance practices.

Key Terms

JDBC: Java API for communicating with relational databases.

Driver: Software that translates JDBC calls into a database-specific protocol.

Connection: A logical session between a Java program and a database.

DataSource: A connection factory commonly used with pooling and managed environments.

Statement: JDBC object for executing static SQL text.

PreparedStatement: Parameterized JDBC statement using bound values.

CallableStatement: JDBC statement for stored procedures and functions.

Parameter binding: Supplying values separately from SQL syntax.

ResultSet: Cursor-based representation of query results.

Generated key: Database-created value, commonly an identity primary key.

Update count: Number of rows affected by an update, insert, or delete.

Transaction: Atomic unit of database work.

Auto-commit: Mode in which each statement is committed automatically.

Savepoint: Named position to which a transaction can partially roll back.

SQL NULL: Marker for an unknown, missing, or inapplicable value.

DatabaseMetaData: Information about database and driver capabilities.

ResultSetMetaData: Information about columns in a query result.

SQL injection: Attack caused by treating untrusted input as SQL syntax.

Connection pool: Managed collection of reusable database connections.

Type mapping: Representation of SQL values using Java types.

Review Questions

Short Answer Questions

1. What is the principal difference between Statement and PreparedStatement?
2. Why do JDBC parameter indexes begin at 1?
3. What does ResultSet.next() return and why is it necessary?
4. How can an application distinguish SQL NULL from integer zero when using getInt()?
5. What information is provided by DatabaseMetaData?

Long Answer Questions

1. Explain the complete JDBC execution lifecycle, including resource management and exception handling.
2. Describe how to implement create, read, update, and delete operations using PreparedStatement.
3. Explain generated-key retrieval and discuss situations in which generated keys are important.
4. Compare JDBC transactions, auto-commit mode, savepoints, and isolation considerations.
5. Discuss SQL-to-Java type mapping, including decimal, binary, text, and date-time values.

Analytical / Application Questions

1. Design a transaction for transferring money between two accounts. Identify the statements, failure points, locking concerns, and rollback behavior.
2. A query returns 10 million rows. Recommend a JDBC result-processing strategy and justify choices involving fetch size, resource lifetime, selected columns, and memory use.
3. A registration system occasionally exceeds course capacity under concurrent requests. Diagnose the flaw and propose a JDBC and database solution using transactions, constraints, or row locking.

Multiple Choice Questions with Answers

Question 1

Which JDBC method is normally used to execute a parameterized SELECT statement?

A. executeQuery()

B. executeUpdate()

C. executeBatchOnly()

D. commitQuery()

Correct Answer: A. executeQuery()

Question 2

What is the first valid parameter index in a PreparedStatement?

A. 0

B. 1

C. -1

D. It depends on the SQL dialect

Correct Answer: B. 1

Question 3

Which practice most directly prevents SQL injection in a JDBC query?

A. Concatenating user input after trimming it

B. Using a larger database connection pool

C. Binding user values through PreparedStatement parameters

D. Converting all input to uppercase

Correct Answer: C. Binding user values through PreparedStatement parameters

Question 4

What does executeUpdate() return for an UPDATE, INSERT, or DELETE operation?

A. The generated primary key only

B. The number of affected rows

C. A ResultSet containing all database tables

D. The transaction isolation level

Correct Answer: B. The number of affected rows

Question 5

How can a JDBC program request generated keys from an inserted row?

A. Use Statement.RETURN_GENERATED_KEYS when preparing the statement

B. Call getGeneratedKeys() before executing the insert

C. Use ResultSet.TYPE_SCROLL_INSENSITIVE only

D. Set auto-commit to true after the insert

Correct Answer: A. Use Statement.RETURN_GENERATED_KEYS when preparing the statement

Question 6

What does a ResultSet cursor initially do before next() is called?

A. It points to the last row

B. It points before the first row

C. It points to the first column of the first row

D. It points to the database schema

Correct Answer: B. It points before the first row

Question 7

Which Java type is generally most appropriate for exact decimal monetary values?

A. double

B. float

C. BigDecimal

D. char[]

Correct Answer: C. BigDecimal

Question 8

Which JDBC object is designed to invoke stored procedures?

A. ResultSet

B. DatabaseMetaData

C. CallableStatement

D. Savepoint

Correct Answer: C. CallableStatement

Question 9

What is the purpose of connection.rollback()?

A. To close the JDBC driver permanently

B. To undo uncommitted transaction work

C. To retrieve generated keys

D. To convert SQL NULL into zero

Correct Answer: B. To undo uncommitted transaction work

Question 10

Which object describes the columns returned by a particular query?

A. ResultSetMetaData

B. DatabaseMetaData only

C. DriverManager

D. SQLWarning

Correct Answer: A. ResultSetMetaData