Databases and JDBC

Introduction

Most useful business applications must store information beyond the lifetime of a single program execution. Customer accounts, products, invoices, appointments, examination results, and audit records are commonly stored in relational database management systems (RDBMSs). Java applications communicate with these systems through the Java Database Connectivity (JDBC) API.

JDBC is a standard Java interface for connecting to databases, sending SQL statements, receiving results, and managing transactions. It does not itself store data or define a particular database engine. Instead, a database vendor supplies a JDBC driver that translates standard JDBC calls into the database’s communication protocol.

This chapter develops the practical foundations required to build safe, maintainable, data-driven Java programs. The emphasis is on relational concepts, JDBC architecture, parameterized SQL, result mapping, transactions, exception handling, and responsible data access.

Learning Objectives

By the end of this chapter, you should be able to:

1. Explain the relational database model and the role of JDBC.
2. Configure a JDBC connection and describe the principal JDBC objects.
3. Execute queries and updates using PreparedStatement.
4. Map ResultSet rows to Java objects.
5. Prevent SQL injection and reduce other security risks.
6. Use transactions, commit, rollback, and appropriate isolation concepts.
7. Manage resources correctly with try-with-resources.
8. Design a small data-access component and evaluate its limitations.

1. Relational Database Foundations

A relational database organizes data into tables. A table consists of rows, representing records, and columns, representing attributes. For example, a students table might contain student_id, name, and email.

A primary key uniquely identifies each row. A foreign key references a key in another table and expresses a relationship. A database schema defines tables, columns, constraints, indexes, views, and other database objects.

A simple design might use these tables:

The relationship between students and courses is many-to-many: one student may take many courses, and one course may have many students. The junction table enrolments represents this relationship.

SQL Operations

The principal SQL categories used by Java applications are:

• DQL: querying data with SELECT.
• DML: changing data with INSERT, UPDATE, and DELETE.
• DDL: defining structures with CREATE, ALTER, and DROP.
• Transaction control: using COMMIT and ROLLBACK.

A query should select only the columns and rows needed by the application. This improves clarity, performance, and privacy. Constraints such as NOT NULL, UNIQUE, CHECK, and foreign keys should enforce important rules close to the data.

2. JDBC Architecture

JDBC separates application code from database-specific communication.

Figure: JDBC provides a standard programming interface between Java code and a database driver.

The main JDBC components are:

• Driver: software that implements communication with a particular database.
• DriverManager: a basic mechanism for obtaining connections. Modern applications often use a DataSource instead.
• Connection: a session with a database, including transaction state.
• PreparedStatement: a compiled or precompiled SQL statement with parameters.
• Statement: a statement without parameter binding; it is less suitable for user input.
• ResultSet: a cursor over rows returned by a query.
• SQLException: an exception describing database-access failures.

A connection URL, username, and password are configuration data. They should normally be supplied through environment variables, a secrets manager, or a deployment configuration—not hard-coded in source code.

3. Establishing a Connection

A small JDBC program can obtain a connection as follows:

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

try (Connection connection =
         DriverManager.getConnection(url, user, password)) {
    System.out.println("Connected: " + !connection.isClosed());
} catch (SQLException ex) {
    System.err.println("Database access failed: " + ex.getMessage());
}

The exact URL depends on the database product and driver. The driver must be available on the application’s classpath or module path. In production, connection pooling is generally preferred to opening a new physical connection for every request. A pool reuses connections while controlling maximum concurrency and timeout behavior.

try-with-resources closes the connection automatically. Closing a connection also closes associated statements and result sets according to JDBC and driver behavior, but explicit, properly scoped resources make the intended lifetime clear.

4. Parameterized Queries and SQL Injection

A parameterized query separates SQL structure from data values:

String sql = "SELECT student_id, name, email "
           + "FROM students WHERE email = ?";

try (PreparedStatement statement = connection.prepareStatement(sql)) {
    statement.setString(1, email);
    try (ResultSet results = statement.executeQuery()) {
        while (results.next()) {
            int id = results.getInt("student_id");
            String name = results.getString("name");
            System.out.println(id + ": " + name);
        }
    }
}

The question mark is a parameter marker. JDBC parameter indexes begin at 1, not 0. The value supplied with setString is transmitted as data rather than being interpreted as SQL syntax.

Unsafe string concatenation is problematic:

String sql = "SELECT * FROM students WHERE name = '" + name + "'";

If an attacker supplies crafted text, the resulting SQL may change meaning. This is SQL injection. PreparedStatement is the primary defense for values. It does not, however, safely parameterize table names, column names, or SQL keywords. If a column must be selected dynamically, use a strict allow-list, for example mapping "name" to a known SQL fragment rather than accepting arbitrary text.

Other protections include least-privilege database accounts, input validation, authorization checks, encrypted connections where appropriate, secret rotation, and careful logging that does not expose passwords or sensitive personal data.

5. Executing Queries and Updates

Use executeQuery() for a statement expected to return a ResultSet, usually SELECT. Use executeUpdate() for INSERT, UPDATE, and DELETE; it returns the number of affected rows.

String sql = "UPDATE students SET email = ? WHERE student_id = ?";

try (PreparedStatement statement = connection.prepareStatement(sql)) {
    statement.setString(1, newEmail);
    statement.setInt(2, studentId);
    int changed = statement.executeUpdate();
    if (changed == 0) {
        System.out.println("No student was changed.");
    }
}

Checking the affected-row count helps detect an invalid identifier or an unexpectedly broad update. An update that should affect one row should often verify that exactly one row changed.

For generated keys:

String sql = "INSERT INTO courses(title) VALUES (?)";
try (PreparedStatement statement = connection.prepareStatement(
        sql, Statement.RETURN_GENERATED_KEYS)) {
    statement.setString(1, title);
    statement.executeUpdate();
    try (ResultSet keys = statement.getGeneratedKeys()) {
        if (keys.next()) {
            long courseId = keys.getLong(1);
            System.out.println("Created course " + courseId);
        }
    }
}

Generated-key behavior can vary by database and driver, so it should be tested with the chosen platform.

6. Mapping Results to Java Objects

A ResultSet initially points before its first row. Calling next() moves it to the next row and returns true if a row exists. Column labels are often clearer than numeric indexes.

A simple domain class might be:

public record Student(int id, String name, String email) { }

A repository method can map rows into objects:

public List<Student> findByName(Connection connection, String fragment)
        throws SQLException {
    String sql = "SELECT student_id, name, email "
               + "FROM students WHERE name LIKE ? ORDER BY name";
    List<Student> students = new ArrayList<>();

    try (PreparedStatement statement = connection.prepareStatement(sql)) {
        statement.setString(1, "%" + fragment + "%");
        try (ResultSet rs = statement.executeQuery()) {
            while (rs.next()) {
                students.add(new Student(
                    rs.getInt("student_id"),
                    rs.getString("name"),
                    rs.getString("email")));
            }
        }
    }
    return students;
}

The SQL query determines which fields are exposed. Avoid retrieving confidential columns merely because they exist in the table. For nullable numeric or boolean columns, primitive getters can be ambiguous because a SQL NULL may be returned as a default value. Use rs.wasNull() immediately after the getter, or use suitable wrapper types and explicit null handling.

Joins combine related data:

SELECT s.student_id, s.name, c.title
FROM students s
JOIN enrolments e ON e.student_id = s.student_id
JOIN courses c ON c.course_id = e.course_id
WHERE s.student_id = ?
ORDER BY c.title;

A join result may contain repeated student data. The application can map each row to an enrollment view, or group rows into an aggregate object. For larger systems, an object-relational mapping framework may reduce boilerplate, but understanding JDBC remains valuable for diagnosing SQL, performance, and transaction behavior.

7. Transactions and Consistency

A transaction is a logical unit of work. Its operations should be treated as one all-or-nothing action when partial completion would produce invalid data. Relational systems commonly describe transaction behavior using ACID properties:

• Atomicity: all operations succeed, or the transaction is rolled back.
• Consistency: declared constraints and application invariants are preserved.
• Isolation: concurrent transactions have controlled visibility of intermediate states.
• Durability: committed data survives subsequent failures, subject to the database’s guarantees.

By default, a JDBC connection commonly operates with auto-commit enabled. Each successful statement may then be committed separately. For a multi-step business operation, disable auto-commit and explicitly commit or roll back.

connection.setAutoCommit(false);
try {
    try (PreparedStatement debit = connection.prepareStatement(
            "UPDATE accounts SET balance = balance - ? WHERE id = ?");
         PreparedStatement credit = connection.prepareStatement(
            "UPDATE accounts SET balance = balance + ? WHERE id = ?")) {

        debit.setBigDecimal(1, amount);
        debit.setLong(2, sourceId);
        if (debit.executeUpdate() != 1) {
            throw new SQLException("Source account was not updated");
        }

        credit.setBigDecimal(1, amount);
        credit.setLong(2, destinationId);
        if (credit.executeUpdate() != 1) {
            throw new SQLException("Destination account was not updated");
        }
    }
    connection.commit();
} catch (SQLException ex) {
    connection.rollback();
    throw ex;
} finally {
    connection.setAutoCommit(true);
}

This illustrative transfer requires additional safeguards in a real system: a non-negative-balance rule, authorization, currency handling, concurrency controls, and an audit trail. Monetary values should generally use BigDecimal, not binary floating-point types, because decimal currency calculations require controlled precision and rounding.

Isolation levels represent different concurrency trade-offs. Common levels include READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, and SERIALIZABLE. The exact behavior is database-specific. Stronger isolation can reduce anomalies but may increase locking, waiting, or transaction failure. Select an isolation level based on business requirements and test it under realistic concurrency.

Savepoints allow partial rollback within a larger transaction:

Savepoint checkpoint = connection.setSavepoint("afterStudent");
// perform optional operation
connection.rollback(checkpoint);

Transactions should be short. Holding locks while performing network calls or waiting for user input can cause contention and deadlocks.

8. Error Handling and Resource Management

SQLException provides a message, SQL state, vendor error code, and sometimes a chain of related exceptions. Applications should log sufficient diagnostic context while avoiding sensitive values. User-facing messages should be understandable and should not reveal database credentials, SQL text containing private data, or internal infrastructure details.

Use narrow resource scopes:

try (Connection c = dataSource.getConnection();
     PreparedStatement p = c.prepareStatement(sql);
     ResultSet r = p.executeQuery()) {
    // process results
}

A data-access layer should define a consistent policy for translating low-level exceptions into application-level errors. It should distinguish, where possible, between validation failures, duplicate-key conflicts, unavailable services, and programming errors.

9. Performance and Maintainability

Efficient JDBC code is not simply code that runs quickly on a small dataset. Consider:

• indexes on columns frequently used for joins, filtering, or ordering;
• selecting required columns rather than using SELECT *;
• pagination for large result sets;
• batch operations for many similar changes;
• prepared-statement reuse where appropriate;
• connection-pool limits and timeouts;
• query plans and measured database performance;
• avoiding the N+1 query pattern, in which one query retrieves parent rows and a separate query is issued for every parent.

Batch insertion example:

String sql = "INSERT INTO courses(title) VALUES (?)";
try (PreparedStatement p = connection.prepareStatement(sql)) {
    for (String title : titles) {
        p.setString(1, title);
        p.addBatch();
    }
    int[] counts = p.executeBatch();
}

Batch operations may improve throughput, but batch size, memory use, generated keys, and failure semantics depend on the driver and database. Measure before and after optimization.

A repository or data-access object can isolate SQL from the rest of the application. A service layer can coordinate validation and transactions. This separation improves testing and makes it easier to replace infrastructure without spreading JDBC details throughout user-interface code.

10. Applied Case: Course Enrolment

Background

Consider an illustrative college enrolment service. A student can enroll in a course only if the student and course exist, the course has available places, and the student is not already enrolled.

Problem

A naive implementation first checks capacity, then inserts an enrollment. Two concurrent requests may both observe one remaining place and both insert, exceeding capacity. Separate auto-committed statements also make partial failure possible.

Analysis

The operation needs a transaction. The database should enforce uniqueness on (student_id, course_id). Capacity must be checked and updated under an appropriate concurrency strategy. One approach is an atomic conditional update:

UPDATE courses
SET places_taken = places_taken + 1
WHERE course_id = ? AND places_taken < capacity;

If exactly one row is updated, the service can attempt the enrollment insert. If the insert fails because of a duplicate key, the transaction rolls back. Depending on the database and workload, row locks, a serializable transaction, or optimistic retry may be appropriate.

Approach

A Java service would:

1. Validate identifiers and authorization.
2. Begin a transaction.
3. Execute the conditional capacity update with parameters.
4. Confirm that one row changed.
5. Insert the enrollment using PreparedStatement.
6. Commit both changes.
7. Roll back on any failure.
8. Return a clear result to the caller.

Outcome

The design prevents SQL injection, reduces over-enrollment risk, preserves consistency between capacity and enrollment records, and gives the application a reliable success or failure result. Monitoring should record duration, failures, and contention without exposing personal information.

Learning Points

The example demonstrates that correctness is not achieved by Java code alone. Constraints, transaction boundaries, concurrency control, parameterized statements, and meaningful error handling must work together.

11. Governance, Ethics, and Contemporary Practice

Database applications often process personal, financial, educational, or health information. Developers should collect only necessary data, restrict access by role, encrypt sensitive data in transit and at rest where required, define retention periods, and support correction or deletion obligations applicable to their jurisdiction and institution.

Audit logs should answer who performed an important action and when, but logs must not become an uncontrolled copy of sensitive data. Database accounts should have only the permissions required by the application. Backups require equivalent protection and tested restoration procedures.

Modern Java applications frequently use DataSource, connection pools, migration tools, testing containers, and higher-level persistence frameworks. AI-assisted coding can generate JDBC examples, but generated code must be reviewed for injection risks, transaction errors, secret exposure, incorrect null handling, and incompatible SQL dialects. Automated tools support engineering judgment; they do not replace security review or database testing.

Best-Practice Checklist

• Use PreparedStatement for all externally influenced values.
• Keep credentials out of source control.
• Use try-with-resources.
• Define transaction boundaries explicitly.
• Check affected-row counts.
• Enforce important rules with database constraints.
• Use BigDecimal for monetary values.
• Limit selected columns and result sizes.
• Use connection pooling in services with repeated requests.
• Test rollback, duplicate data, nulls, timeouts, and concurrent operations.
• Log useful diagnostics without recording secrets or unnecessary personal data.
• Review SQL and permissions as part of security testing.

Chapter Summary

JDBC is Java’s standard interface for relational database access. A driver connects the JDBC API to a particular database, while Connection, PreparedStatement, ResultSet, and SQLException provide the principal programming abstractions. Parameterized statements separate SQL structure from data and are the essential defense against SQL injection. Result sets must be traversed and mapped carefully, with explicit treatment of nullable values and controlled column selection.

Transactions group related operations into a reliable unit. Commit and rollback, appropriate isolation, short transaction duration, and database constraints are necessary for consistent concurrent applications. Try-with-resources prevents resource leaks, while connection pools improve service scalability. Effective JDBC design also considers performance, error translation, privacy, authorization, auditing, and maintainability. Data-driven decision making is trustworthy only when the underlying data access is secure, accurate, and governed responsibly.

Key Terms

JDBC: Java API for communicating with relational databases.

RDBMS: Relational database management system.

Driver: Database-specific software implementing JDBC communication.

Connection: A session between an application and a database.

Statement: JDBC object used to execute SQL without parameter binding.

PreparedStatement: Parameterized JDBC statement that separates SQL from values.

ResultSet: Cursor-based representation of rows returned by a query.

Primary key: Column or column set that uniquely identifies a row.

Foreign key: Column or column set referencing a key in another table.

Transaction: Logical unit of database work.

Commit: Permanently apply a transaction’s changes.

Rollback: Undo uncommitted transaction changes.

Auto-commit: Mode in which statements are committed individually.

SQL injection: Attack that alters SQL through unsafe input incorporation.

Connection pool: Managed collection of reusable database connections.

Constraint: Database rule enforcing valid data.

Isolation level: Control over the visibility of concurrent transaction effects.

Repository: Component that encapsulates data-access operations.

Review Questions

Short Answer Questions

1. What role does a JDBC driver play?
2. Why are parameterized statements safer than string concatenation?
3. What does ResultSet.next() do?
4. Why might an application disable auto-commit?
5. Name two benefits of database constraints.

Long Answer Questions

1. Explain the principal JDBC objects and the usual lifecycle of a query.
2. Describe SQL injection and give several controls that reduce its risk.
3. Explain ACID properties and their relevance to Java database applications.
4. Discuss how a Java program should handle JDBC resources and exceptions.
5. Explain how database design and JDBC code can work together to improve performance and maintainability.

Analytical / Application Questions

1. Design a transaction for transferring funds between two accounts. Identify validation, rollback, concurrency, and auditing requirements.
2. A course has one remaining place, but two students submit enrollment requests simultaneously. Explain how application logic, constraints, and transaction control could prevent over-enrollment.
3. An application retrieves every customer and then issues one query per customer to retrieve orders. Identify the performance problem and propose at least two alternatives.

Multiple Choice Questions with Answers

Question 1

Which JDBC object is most appropriate for executing SQL containing user-supplied values?

A. PreparedStatement

B. ResultSet

C. DriverManager

D. DatabaseMetaData

Correct Answer: A. PreparedStatement

Question 2

What does calling commit() do when auto-commit is disabled?

A. Closes the connection

B. Makes the transaction's changes permanent

C. Deletes the result set

D. Starts a new driver

Correct Answer: B. Makes the transaction's changes permanent

Question 3

Which method is normally used for an SQL SELECT statement?

A. executeUpdate()

B. executeQuery()

C. executeBatchOnly()

D. commitQuery()

Correct Answer: B. executeQuery()

Question 4

Why is SQL string concatenation with untrusted input dangerous?

A. It always makes queries slower

B. It prevents all indexes from being used

C. It can allow SQL injection

D. It automatically commits transactions

Correct Answer: C. It can allow SQL injection

Question 5

What does ResultSet.next() return when another row is available?

A. false

B. null

C. The row count

D. true

Correct Answer: D. true

Question 6

Which JDBC technique automatically closes declared resources at the end of a block?

A. Method overloading

B. Try-with-resources

C. Inheritance

D. Garbage collection only

Correct Answer: B. Try-with-resources

Question 7

Which Java type is generally preferable for exact decimal monetary values?

A. float

B. double

C. BigDecimal

D. char

Correct Answer: C. BigDecimal

Question 8

What is the main purpose of a foreign key?

A. To encrypt a table

B. To reference a key in another table

C. To create a Java object

D. To force auto-commit

Correct Answer: B. To reference a key in another table

Question 9

If an UPDATE intended to modify one student reports zero affected rows, what is a useful interpretation?

A. The database necessarily shut down

B. The target record may not exist or the condition matched no row

C. The SQL injection succeeded

D. The transaction is automatically committed

Correct Answer: B. The target record may not exist or the condition matched no row

Question 10

Which practice best supports least privilege?

A. Give every application user database administrator rights

B. Store passwords in SQL strings

C. Grant the application only the database permissions it requires

D. Disable all database constraints

Correct Answer: C. Grant the application only the database permissions it requires