11. Database Programming and Persistence

Introduction and Context

Most useful software systems must remember information beyond the lifetime of a single program execution. Banking transactions, examination records, inventory levels, customer profiles, audit events, and configuration data all require persistence: the ability to store state reliably and retrieve it later. In Java applications, persistence is not merely a matter of issuing SQL statements. It involves selecting an appropriate data model, managing database connections, controlling transactions, mapping objects to records, handling failures, and preserving consistency under concurrent access.

Java provides several complementary approaches to database programming. The Java Database Connectivity (JDBC) API offers direct, explicit control over SQL and database resources. Connection pools improve the efficiency of applications that repeatedly access a database. Prepared statements reduce injection risks and improve statement reuse. Transactions provide atomicity and define how concurrent operations interact. Object-relational mapping (ORM), particularly Jakarta Persistence (JPA), reduces repetitive mapping code while introducing its own concepts, such as persistence contexts, entity states, lazy loading, and flushing. Beyond relational databases, document, key-value, column-family, and graph databases offer alternative persistence models for specific workloads.

The central engineering challenge is to align application requirements with a suitable persistence strategy. Strong consistency, flexible schema evolution, horizontal scalability, analytical performance, and development productivity may not all be maximized simultaneously. This chapter develops the concepts and practices needed to make these choices deliberately.

Learning Objectives

After studying this chapter, a student should be able to:

1. Explain the role of JDBC, connection pooling, prepared statements, and transactions in Java applications.

2. Design transaction boundaries and select appropriate isolation levels.

3. Describe JPA entity mapping and the lifecycle of a persistent entity.

4. Identify common ORM performance problems and apply query optimization techniques.

5. Explain caching, schema migration, and persistence testing strategies.

6. Compare relational and NoSQL persistence according to consistency, structure, scalability, and access patterns.

7. Evaluate technical, security, governance, and professional implications of database design decisions.

Persistence as an Application Responsibility

A persistence layer separates domain logic from the mechanisms used to store and retrieve data. This separation may be implemented through repositories, data access objects, or service components. The separation is valuable because business rules should not be scattered across SQL strings, transaction code, and user-interface controllers. At the same time, excessive abstraction can conceal important database behavior. A well-designed persistence layer hides accidental complexity without hiding performance characteristics or consistency requirements.

A relational database represents data through tables, rows, columns, keys, and relationships. A primary key identifies a row, while foreign keys express relationships between tables. Constraints such as `NOT NULL`, `UNIQUE`, `CHECK`, and referential integrity rules allow the database to enforce invariants independently of application code. Normalization reduces undesirable duplication, although carefully controlled denormalization may improve read performance.

The ACID properties provide a useful model for transactional behavior. Atomicity means that a transaction is treated as an indivisible unit. Consistency means that committed transactions preserve declared rules and application invariants. Isolation concerns the interaction between concurrent transactions. Durability means that committed changes survive failures according to the database's durability guarantees. ACID does not eliminate all design problems; for example, an application can commit an incorrect business decision perfectly atomically.

JDBC: Explicit Control over Relational Access

JDBC is Java's standard API for communicating with relational databases. A typical JDBC operation obtains a connection, creates a statement, binds parameters, executes SQL, processes the result, and closes resources. Modern Java code should use try-with-resources so that connections, statements, and result sets are closed even when an exception occurs.

A simplified example is:

```java

String sql = "SELECT id, name, balance FROM account WHERE id = ?";

try (Connection connection = dataSource.getConnection();

PreparedStatement statement = connection.prepareStatement(sql)) {

statement.setLong(1, accountId);

try (ResultSet result = statement.executeQuery()) {

if (result.next()) {

long id = result.getLong("id");

String name = result.getString("name");

BigDecimal balance = result.getBigDecimal("balance");

}

}

}

```

The `DataSource` abstraction is generally preferable to directly managing a JDBC driver or using `DriverManager` throughout an enterprise application. A data source can provide pooling, configuration, credentials, and operational integration.

`PreparedStatement` represents parameterized SQL. Parameters are transmitted separately from the SQL structure, which prevents ordinary user input from being interpreted as SQL syntax. String concatenation such as `"... WHERE name = '" + input + "'"` is unsafe and can produce SQL injection vulnerabilities. Prepared statements also make type binding explicit and often permit database or driver-level statement reuse.

JDBC distinguishes several result-handling concerns. `executeQuery()` is normally used for `SELECT` operations, `executeUpdate()` for inserts, updates, and deletes, and `execute()` when the statement type is variable or multiple results may be returned. Generated keys can be retrieved when an insert creates an identity value. Batch updates can reduce network round trips when many independent changes must be made, but batch size and transaction duration should be controlled to avoid excessive memory use or lock retention.

Connection Pooling and Resource Management

Opening a database connection may require authentication, network setup, and session initialization. Creating a new physical connection for every request is therefore expensive. A connection pool maintains a bounded collection of established connections. The application borrows a connection, uses it, and returns it to the pool when the logical operation finishes. Returning a connection does not normally close the physical database connection.

Pooling improves throughput, but it is not an unlimited resource. A pool that is too small causes waiting; a pool that is too large can overload the database with concurrent work. Configuration should consider database capacity, transaction duration, application concurrency, and the number of application instances. Timeouts, leak detection, validation, and metrics are important operational features.

Applications must restore connection state before returning a connection. Uncommitted work, altered isolation levels, read-only flags, session variables, or an unexpected schema can contaminate the next user's operation. A pool library commonly performs some cleanup, but application code must still commit or roll back explicitly and close borrowed resources.

Transactions and Isolation

A transaction boundary should correspond to a meaningful unit of business work. For example, transferring money requires debiting one account and crediting another within the same transaction. If the debit succeeds but the credit fails, the entire operation must be rolled back. In JDBC, auto-commit is often disabled for multi-step business operations:

```java

try (Connection connection = dataSource.getConnection()) {

connection.setAutoCommit(false);

try {

debit(connection, sourceId, amount);

credit(connection, destinationId, amount);

connection.commit();

} catch (Exception failure) {

connection.rollback();

throw failure;

}

}

```

Isolation levels determine which effects of concurrent transactions are visible. The commonly recognized levels are:

• Read uncommitted: may permit dirty reads, in which one transaction observes data another has not committed.
• Read committed: prevents dirty reads, but repeated reads may see different committed values.
• Repeatable read: strengthens stability of rows already read, although exact behavior varies by database implementation and may not prevent every form of phantom result.
• Serializable: aims to produce an effect equivalent to serial execution, generally at a cost in concurrency.

The anomalies are not merely vocabulary. A dirty read can cause a decision based on data that later disappears. A non-repeatable read occurs when a row changes between two reads in one transaction. A phantom read occurs when a repeated predicate query returns a different set of rows because another transaction inserted or removed matching records. Modern databases may use locks, multiversion concurrency control, or hybrid techniques, so the selected isolation level must be verified against the specific database system.

Higher isolation is not automatically better. Serializable execution can reduce throughput and create deadlocks or long waits. A practical design chooses the lowest level that preserves the business invariant, then uses explicit locking, optimistic version checks, constraints, or retry logic where necessary. Deadlocks are normal possibilities in concurrent systems. Transactions should be short, access resources in a consistent order, and retry only when the operation is safe to repeat.

JPA and the Object-Relational Boundary

JPA, now part of the Jakarta EE ecosystem, defines a standard for managing persistent Java objects. An ORM provider, such as Hibernate, implements the standard and translates entity operations into SQL. JPA can reduce repetitive mapping code and allow developers to work with domain-oriented abstractions, but it does not remove the need to understand relational design or SQL.

An entity is a class whose instances are managed by a persistence context. A typical mapping might be expressed as follows:

```java

@Entity

public class Account {

@Id

@GeneratedValue

private Long id;

@Version

private long version;

@Column(nullable = false, precision = 19, scale = 2)

private BigDecimal balance;

protected Account() { }

}

```

The no-argument constructor is required by JPA, usually with protected or package visibility. `@Id` identifies the primary key, `@Version` supports optimistic locking, and column metadata communicates constraints to the mapping or schema-generation process. Production systems commonly manage schemas through migration tools rather than relying on automatic schema creation.

The entity lifecycle is central to correct JPA use. A new object has not yet been associated with a persistence context. A managed entity is tracked; changes to it can be detected and synchronized with the database. A detached entity was once managed but is no longer tracked, often because the context was closed. A removed entity is scheduled for deletion. The `EntityManager` operations `persist`, `find`, `merge`, and `remove` have different semantics. In particular, `merge` copies state into a managed instance and does not make the supplied object itself managed.

A persistence context acts as a first-level cache and identity map. Within one context, repeated retrieval of the same entity identity generally yields the same managed object. Changes may not be sent immediately. The process of synchronizing in-memory state with the database is called flushing. Flush may occur before commit or before a query whose result could be affected by pending changes. Consequently, an error may appear at flush or commit rather than at the line that initially changed an object.

Associations require deliberate choices. One-to-many and many-to-one relationships can be mapped with foreign keys, join tables, and cascading rules. Cascade operations can simplify aggregate persistence, but broad cascading can accidentally delete or update large object graphs. Lazy loading postpones retrieval until an association is accessed; eager loading retrieves it earlier. Lazy loading is usually safer for controlling query volume, but accessing a lazy association after the persistence context has closed can produce a lazy initialization failure.

Query Performance and the N+1 Problem

ORM convenience can conceal expensive SQL. The N+1 query problem occurs when one query retrieves N parent records and then an additional query is executed for each parent to obtain related data. It can be addressed through fetch joins, entity graphs, batch fetching, carefully designed projections, or a dedicated query. A fetch join is not universally appropriate: joining multiple collections can create a Cartesian multiplication of rows and duplicate data in the result.

Query optimization begins with measurement. SQL logs, execution plans, database statistics, and application metrics reveal whether the problem is excessive query count, poor selectivity, missing indexes, large result sets, lock contention, or network transfer. Indexes can accelerate filtering and joins, but they consume storage and slow writes. Composite indexes must consider the order and selectivity of columns. An index should support an actual access pattern rather than being added speculatively.

Applications should retrieve only the columns needed by a use case. DTO projections can avoid materializing large entities. Pagination limits result size, although offset pagination may become expensive for deep pages; keyset or seek pagination can be more efficient for ordered datasets. Sorting and filtering should be expressed with controlled parameters, and dynamic query construction must avoid injection vulnerabilities.

Caching has several levels. The persistence context is a transaction-scoped first-level cache. A second-level cache may be shared across contexts, while a query cache stores query-result information in some implementations. Application-level caches may provide fast access to selected data. Caching introduces invalidation, staleness, memory pressure, and distributed consistency problems. It is most suitable for data that is read frequently, changes predictably, and can tolerate the defined freshness policy.

Schema Evolution and Migration

Database schemas evolve as applications acquire new features. A migration is a versioned, ordered change such as adding a nullable column, creating an index, or transforming data. Migration tools allow changes to be reviewed, applied consistently across environments, and associated with application versions. A production migration should be observable and reversible where practical, although rollback of destructive data transformations is not always simple.

A common safe-evolution pattern is expand and contract. First, add the new structure without breaking the old application. Next, deploy code capable of writing or reading both representations, backfill existing data, and verify the result. Finally, switch all clients to the new representation and remove obsolete structures in a later migration. This approach is particularly important in rolling deployments, where old and new application instances coexist.

Schema ownership also has organizational implications. Teams need rules for migration review, access permissions, data retention, backup verification, and compatibility testing. Automatic production schema changes during application startup can create race conditions and uncontrolled privileges; controlled deployment pipelines are generally safer.

Relational and NoSQL Alternatives

Relational databases are strong choices when data has well-defined relationships, transactions span multiple records, and constraints are important. NoSQL systems are not one uniform category. Document stores organize semi-structured documents; key-value stores emphasize simple lookup; wide-column systems support distributed, high-volume access patterns; graph databases model relationships and traversals directly.

A NoSQL design often begins with query patterns rather than a normalized universal schema. Data may be embedded to make an aggregate available in one read, or duplicated to avoid distributed joins. Duplication improves read performance but creates update and consistency responsibilities. A system may offer eventual consistency, meaning replicas converge after a delay, or stronger consistency for selected operations. The appropriate choice depends on whether stale data can cause harm, how much availability is required during partitions, and whether the business operation can be compensated later.

Polyglot persistence uses more than one storage technology in the same system. For example, a relational database may be the system of record, a search engine may support full-text queries, and a key-value cache may reduce latency. This architecture increases operational complexity, data synchronization requirements, and failure modes. It should be adopted for a demonstrated workload need rather than technological fashion.

Illustrative Case Study: Reservation and Inventory Platform

Background

Consider an illustrative online platform that reserves equipment for university laboratories. Users search inventory, select time periods, submit reservations, and receive confirmation. The platform must prevent two users from reserving the same scarce item for overlapping periods.

Situation and Problem

An initial implementation reads the available quantity, checks it in Java, and then inserts a reservation. Under concurrent requests, two transactions can read the same available quantity before either writes. Both may succeed, producing an overbooking. The problem is a race condition, not simply a faulty user interface.

Analysis

The invariant is that the number of confirmed reservations for an item and time interval must not exceed capacity. A transaction must perform the availability decision and reservation change as one atomic operation. Depending on the database design, the solution may use a unique or exclusion constraint, a row-level lock on an inventory record, a serializable transaction, or an optimistic version check. The correct option depends on the interval model and expected contention.

Approach and Solution

The platform stores inventory and reservations in a relational database. A service method begins a transaction, locks the relevant inventory row, verifies the requested interval, inserts the reservation, and commits. The lock is held only for the necessary duration. A database constraint provides an additional line of defense where the overlap rule can be represented. The application catches a constraint or serialization failure and returns a controlled “no longer available” response rather than exposing an internal error.

Search results are served through indexed queries and may be cached briefly because a search result is advisory, not a reservation guarantee. The confirmation endpoint never relies solely on cached availability. A migration pipeline manages schema changes, and metrics record transaction duration, lock waits, failed attempts, and reservation conflicts.

Outcome

The design preserves the business invariant under concurrent requests while allowing search traffic to scale independently. It also distinguishes data that may be stale from data that must be authoritative at commit time.

Learning Points

The case demonstrates that consistency requirements should be expressed as invariants, enforced as close to the data as practical, and supported by transaction design. Caching is appropriate for discovery but not for the final allocation decision. Performance optimization must not weaken the correctness condition that defines the business operation.

Risks, Governance, and Professional Practice

Security begins with parameterized queries, least-privilege database accounts, protected credentials, encrypted connections, and careful handling of error messages. Sensitive data should be minimized, classified, retained only as long as justified, and protected in backups and logs. Personally identifiable information and financial records may be subject to legal or institutional requirements concerning access, retention, and deletion.

A database is a shared organizational asset, so auditability matters. Changes to important records may require an audit trail that records the actor, time, operation, and relevant reason. Audit data itself must be protected from unauthorized alteration. Developers should avoid logging passwords, tokens, full payment details, or unredacted personal information.

Common technical mistakes include leaving auto-commit enabled for multi-step work, failing to roll back before returning a pooled connection, constructing SQL through string concatenation, loading entire tables into memory, using unbounded eager relationships, ignoring indexes and execution plans, and assuming that ORM code always produces efficient SQL. Another mistake is treating database constraints as optional because validation exists in the application. Application validation improves user experience; database constraints protect integrity when multiple applications or faulty code access the same data.

Testing should include unit tests for mapping and repository behavior, integration tests against a real or compatible database engine, migration tests, transaction rollback tests, and concurrency tests for critical invariants. An in-memory substitute may be useful for rapid tests but can conceal differences in SQL dialect, locking, indexing, and transaction behavior. Production-like performance testing is necessary before making claims about scalability.

Emerging Directions

Modern Java applications increasingly use observability tools to correlate application requests with SQL statements, transaction latency, connection-pool wait time, and database resource consumption. Reactive database access can support non-blocking workflows, but it does not remove database limits or make blocking database engines magically scalable. Cloud-managed databases simplify operations while introducing cost, network-latency, portability, and vendor-dependency considerations.

Distributed architectures also use event-driven patterns. A committed database transaction may publish an event through an outbox table, which is later delivered to other services. This avoids the unreliable practice of updating a database and sending a message as two unrelated operations. Consumers should be idempotent because delivery may be repeated. Such patterns provide eventual propagation rather than a single instantaneous transaction across all services.

Artificial intelligence and analytics workloads add further persistence demands, including vector search, feature storage, lineage, and governance. These capabilities should be integrated only with clear requirements for accuracy, privacy, explainability, and operational cost. The persistence principle remains unchanged: storage technology must serve a defined access pattern and consistency contract.

Best-Practice Design Checklist

A sound Java persistence design generally follows these principles:

• Define business invariants and transaction boundaries before selecting APIs.
• Use `DataSource`, connection pooling, try-with-resources, and explicit failure handling.
• Use prepared statements or safely parameterized ORM queries.
• Keep transactions short and design for deadlock and transient-failure handling.
• Choose isolation levels according to actual correctness requirements.
• Treat ORM-generated SQL as something to inspect and measure.
• Use indexes, projections, pagination, and batch operations based on evidence.
• Control entity relationships, cascading, lazy loading, and serialization boundaries.
• Version schema changes through reviewed migration scripts.
• Separate authoritative writes from caches and search indexes.
• Protect credentials and sensitive data, and apply least privilege.
• Test with the real database behavior that matters to the application.

Chapter Summary

Java persistence spans direct JDBC programming, pooled connections, parameterized statements, transaction management, ORM, schema evolution, and alternative database models. JDBC offers precise SQL and resource control, while JPA offers object-oriented mapping through managed entity lifecycles and persistence contexts. Transactions protect atomic business operations, and isolation levels govern the visibility of concurrent work. Connection pooling improves efficiency but requires disciplined cleanup.

ORM does not eliminate database reasoning. Lazy loading, cascading, flushing, N+1 queries, indexes, projections, pagination, and caching all influence correctness and performance. Versioned migrations support safe schema evolution, while relational and NoSQL systems represent different trade-offs involving structure, consistency, scalability, and access patterns. Professional persistence engineering combines technical design with security, governance, testing, observability, and explicit decision-making about consistency.

Key Terms

ACID: A model of transaction properties comprising atomicity, consistency, isolation, and durability.

JDBC: Java's standard API for connecting to and operating on relational databases.

DataSource: An abstraction that supplies database connections and can integrate pooling and configuration.

Prepared statement: A parameterized SQL statement whose values are bound separately from its structure.

Connection pool: A managed collection of reusable physical database connections.

Transaction: A unit of database work that is committed or rolled back as a whole.

Isolation level: A rule governing which concurrent transaction effects are visible.

Dirty read: Observation of data written by another transaction before that transaction commits.

ORM: Object-relational mapping between programming-language objects and relational data.

JPA: The Jakarta persistence standard for managing entities and object-relational mappings.

Persistence context: A managed set of entity instances tracked by an entity manager.

Entity lifecycle: The transitions of an entity among new, managed, detached, and removed states.

Flush: Synchronization of pending in-memory persistence changes with the database.

Optimistic locking: Concurrency control that detects conflicting updates, commonly with a version field.

N+1 query problem: Excessive querying caused by one initial query followed by a query for each returned object.

Migration: A versioned and controlled change to a database schema or stored data.

Eventual consistency: A model in which replicas may temporarily differ but converge over time.

Polyglot persistence: Use of multiple data-storage technologies within one application or organization.

Idempotency: A property whereby repeating an operation produces the same intended effect as performing it once.

Review Questions

Short Answer Questions

1. Why are prepared statements preferred over concatenating user input into SQL?

2. What is the purpose of a connection pool?

3. Distinguish a managed entity from a detached entity in JPA.

4. What is a phantom read?

5. Why should database schema changes be versioned through migrations?

Long Answer Questions

1. Explain JDBC resource management and transaction handling in a multi-step business operation.

2. Compare the major transaction isolation levels and discuss the anomalies they address.

3. Describe the JPA entity lifecycle, persistence context, flushing, and optimistic locking.

4. Explain the N+1 query problem and evaluate several techniques for resolving it.

5. Compare relational and NoSQL persistence using consistency, schema, scalability, and access patterns as criteria.

Analytical / Application Questions

1. A payment service debits an account, updates an audit record, and sends a notification. Analyze which actions should belong to the database transaction and propose a reliable design for notification delivery.

2. An ORM-based report executes 1,001 SQL queries for 1,000 orders. Diagnose the likely cause and design a measurement-based optimization plan.

3. A reservation system experiences overbooking during peak demand. Formulate the invariant, transaction strategy, database protections, and failure response needed to preserve correctness.

22. Multiple Choice Questions with Answers

Question 1

Which JDBC practice most directly prevents ordinary user input from being interpreted as SQL syntax?

A. Using a larger connection pool

B. Using prepared statements with bound parameters

C. Increasing the transaction timeout

D. Enabling auto-commit

Correct Answer: B. Using prepared statements with bound parameters

Question 2

What is the primary purpose of a connection pool?

A. To permanently store query results

B. To replace database indexes

C. To reuse established physical database connections

D. To enforce serializable isolation for every query

Correct Answer: C. To reuse established physical database connections

Question 3

A transaction reads a value written by another transaction that has not committed. Which anomaly is this?

A. Dirty read

B. Non-repeatable read

C. Phantom read

D. Lost schema migration

Correct Answer: A. Dirty read

Question 4

Which statement correctly describes JPA's merge operation?

A. It always makes the supplied object itself managed

B. It copies state into a managed instance

C. It immediately commits all changes

D. It permanently removes the entity

Correct Answer: B. It copies state into a managed instance

Question 5

What commonly causes the N+1 query problem?

A. A database having too many constraints

B. One query for parent records followed by one query per parent

C. Using a prepared statement

D. Disabling second-level caching

Correct Answer: B. One query for parent records followed by one query per parent

Question 6

Why can a broad cascade configuration in JPA be dangerous?

A. It prevents all inserts

B. It can unintentionally update or delete a large object graph

C. It disables primary keys

D. It forces every query to be serializable

Correct Answer: B. It can unintentionally update or delete a large object graph

Question 7

Which schema-evolution strategy is most suitable when old and new application versions coexist during deployment?

A. Drop the old columns before deploying new code

B. Use an expand-and-contract sequence

C. Allow every application instance to alter the schema independently

D. Disable database constraints permanently

Correct Answer: B. Use an expand-and-contract sequence

Question 8

A cached availability result is used to finalize a scarce-item reservation. What is the principal design flaw?

A. Caches cannot store strings

B. A cache may be stale and should not replace authoritative transactional validation

C. Transactions cannot contain reads

D. Indexes make caches inconsistent

Correct Answer: B. A cache may be stale and should not replace authoritative transactional validation

Question 9

Which choice best supports detection of concurrent updates to the same JPA entity without holding a long database lock?

A. Optimistic locking with a version field

B. String concatenation in SQL

C. Unbounded eager loading

D. Removing the primary key

Correct Answer: A. Optimistic locking with a version field

Question 10

Why should consumers of an outbox-produced event generally be idempotent?

A. Events are guaranteed to arrive exactly once

B. Repeated delivery can occur and should not duplicate the intended effect

C. Idempotency increases database normalization

D. It eliminates the need for transactions

Correct Answer: B. Repeated delivery can occur and should not duplicate the intended effect