Phase 5 — SQL + JDBC + JPA/Hibernate

This phase takes you from "I know Java" to "I can make a Java application store, retrieve, update, and manage real data in a database."

We will use one consistent example throughout: a Java backend for an online store.

Chapters in this Phase

Part A — Database & SQL Foundations

Databases, tables, rows, columns & relationships
Primary keys and foreign keys
INSERT, SELECT, UPDATE, DELETE
Filtering with WHERE
Sorting and limiting results
Aggregate functions and GROUP BY
JOIN
Subqueries
Database normalization
Indexes
Transactions and ACID
SQL constraints and data integrity

Part B — JDBC

JDBC architecture
Database connections
Statement vs PreparedStatement
Reading data with ResultSet
JDBC CRUD
JDBC transactions
Batch operations
Connection pooling
DAO pattern

Part C — ORM / JPA / Hibernate

ORM and the JPA/Hibernate relationship
Entities and entity lifecycle
Primary keys and generated IDs
Entity relationships
One-to-many / many-to-one
One-to-one and many-to-many
Fetching: lazy vs eager
Cascading and orphan removal
Persistence context
JPQL
Native queries
Transactions with JPA
N+1 query problem
JPA/Hibernate performance
Designing a production persistence layer

Part A — Database & SQL Foundations

Chapter 1 — Databases, Tables, Rows, Columns & Relationships

Question

Given below is a code snippet that:

Creates an customers table.
Creates an orders table.
Stores customers and their orders.
Connects orders to customers through an ID.

What should be the output of the following code?

-- Create a table for customers.
CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

-- Create a table for orders.
CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,
    amount DECIMAL(10, 2),

    -- customer_id refers to a customer.
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

-- Store two customers.
INSERT INTO customers VALUES
(1, 'Alice'),
(2, 'Bob');

-- Store orders belonging to those customers.
INSERT INTO orders VALUES
(101, 1, 500.00),
(102, 1, 250.00),
(103, 2, 900.00);

-- Retrieve all orders.
SELECT * FROM orders;

Answer

101 | 1 | 500.00
102 | 1 | 250.00
103 | 2 | 900.00

Step-by-step explanation

customers is a table containing customer records.
Each row represents one customer.
id identifies the customer.
orders contains order records.
customer_id tells us which customer owns an order.
Therefore orders 101 and 102 belong to Alice, while 103 belongs to Bob.
SELECT * FROM orders retrieves all three order rows.

How to read the important code

"Create a customers table with an integer primary key and a name."

"Create an orders table with an order ID, customer ID, and amount, and make customer ID a foreign key."

Beginner trap

A table is not the database itself. A database can contain many tables.

Key takeaway

A relational database stores structured data in tables, and keys connect related tables.

Chapter 2 — Primary Keys and Foreign Keys

Question

Given below is a code snippet that:

Defines a primary key.
Defines a foreign key.
Prevents an order from referring to a nonexistent customer.

What should be the output of the following code?

-- Create the parent table.
CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

-- Create the child table.
CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,

    -- customer_id must refer to an existing customer.
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

-- Create a valid customer.
INSERT INTO customers VALUES (1, 'Alice');

-- This order is valid because customer 1 exists.
INSERT INTO orders VALUES (101, 1);

-- This would fail because customer 99 does not exist.
-- INSERT INTO orders VALUES (102, 99);

SELECT COUNT(*) FROM orders;

Answer

Step-by-step explanation

Customer 1 is inserted.
Order 101 references customer 1.
The foreign-key rule checks that customer 1 exists.
It does, so the order is accepted.
The second insert is commented out because customer 99 doesn't exist.
Therefore the orders table contains one row.

How to read the important code

"id is the primary key."

"customer_id is a foreign key referencing the customer's ID."

Beginner trap

A foreign key usually points to a primary key or another unique key in another table.

Key takeaway

Primary keys identify records; foreign keys establish valid relationships between tables.

Chapter 3 — INSERT, SELECT, UPDATE and DELETE

Question

Given below is a code snippet that:

Inserts a product.
Reads it.
Updates its price.
Deletes it.
Demonstrates the basic SQL CRUD operations.

What should be the output of the following code?

-- Create a product table.
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10, 2)
);

-- Create a product.
INSERT INTO products VALUES (1, 'Keyboard', 1000.00);

-- Change the price.
UPDATE products
SET price = 1200.00
WHERE id = 1;

-- Read the product.
SELECT name, price
FROM products
WHERE id = 1;

-- Delete the product.
DELETE FROM products
WHERE id = 1;

-- Check how many products remain.
SELECT COUNT(*) FROM products;

Answer

Keyboard | 1200.00

0

Step-by-step explanation

The product starts at 1000.
UPDATE changes it to 1200.
SELECT therefore returns 1200.
DELETE removes the row.
COUNT(*) then returns zero.

How to read the important code

"Update products set price to 1200 where the ID is 1."

Beginner trap

Never write an UPDATE or DELETE without thinking carefully about the WHERE clause.

Key takeaway

SQL CRUD means Create, Read, Update and Delete.

Chapter 4 — Filtering with WHERE

Question

Given below is a code snippet that:

Stores products.
Uses comparison operators.
Uses AND and OR.
Filters rows with WHERE.

What should be the output of the following code?

-- Create products.
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10, 2),
    stock INT
);

-- Add products.
INSERT INTO products VALUES
(1, 'Keyboard', 1000, 10),
(2, 'Mouse', 500, 0),
(3, 'Monitor', 8000, 5),
(4, 'Headphones', 2000, 20);

-- Find products that are expensive and in stock.
SELECT name
FROM products
WHERE price > 1000
  AND stock > 0;

Answer

Monitor
Headphones

Step-by-step explanation

Keyboard costs exactly 1000, so price > 1000 is false.
Mouse is cheap and has zero stock.
Monitor costs 8000 and has stock.
Headphones cost 2000 and have stock.
Both Monitor and Headphones satisfy both conditions.

How to read the important code

"Select the product name where price is greater than 1000 and stock is greater than zero."

Beginner trap

AND requires both conditions to be true.

Key takeaway

WHERE decides which rows are included in the result.

Chapter 5 — Sorting and Limiting Results

Question

Given below is a code snippet that:

Sorts products by price.
Uses ascending and descending order.
Returns only the most expensive two products.

What should be the output of the following code?

-- Create products.
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10, 2)
);

-- Add products.
INSERT INTO products VALUES
(1, 'Keyboard', 1000),
(2, 'Mouse', 500),
(3, 'Monitor', 8000),
(4, 'Headphones', 2000);

-- Sort from highest price to lowest.
-- Then keep only the first two rows.
SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 2;

Answer

Monitor     | 8000
Headphones  | 2000

Step-by-step explanation

ORDER BY price DESC sorts prices from high to low.
The order becomes Monitor, Headphones, Keyboard, Mouse.
LIMIT 2 keeps only the first two rows.

Key takeaway

ORDER BY controls order; LIMIT controls how many rows are returned.

Chapter 6 — Aggregate Functions and GROUP BY

Question

Given below is a code snippet that:

Calculates totals.
Counts orders.
Groups orders by customer.
Uses SUM, COUNT and GROUP BY.

What should be the output of the following code?

-- Create orders.
CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,
    amount DECIMAL(10, 2)
);

-- Add orders.
INSERT INTO orders VALUES
(1, 10, 100),
(2, 10, 200),
(3, 20, 500),
(4, 20, 300);

-- Calculate total amount and number of orders per customer.
SELECT
    customer_id,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
ORDER BY customer_id;

Answer

10 | 2 | 300
20 | 2 | 800

Step-by-step explanation

Customer 10 has orders worth 100 and 200.
Their count is 2.
Their total is 300.
Customer 20 has orders worth 500 and 300.
Their count is 2.
Their total is 800.

Key takeaway

GROUP BY turns individual rows into groups that aggregate functions can summarize.

Chapter 7 — JOIN

Question

Given below is a code snippet that:

Stores customers and orders separately.
Connects them using a foreign key.
Uses JOIN to produce useful information.

What should be the output of the following code?

-- Create customers.
CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

-- Create orders.
CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,
    amount DECIMAL(10, 2),

    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

-- Add customers.
INSERT INTO customers VALUES
(1, 'Alice'),
(2, 'Bob');

-- Add orders.
INSERT INTO orders VALUES
(101, 1, 500),
(102, 2, 900);

-- Combine order information with customer information.
SELECT
    customers.name,
    orders.amount
FROM customers
JOIN orders
    ON customers.id = orders.customer_id
ORDER BY orders.id;

Answer

Alice | 500
Bob   | 900

Step-by-step explanation

customers.id identifies the customer.
orders.customer_id identifies the customer who placed the order.
JOIN matches these values.
Order 101 matches Alice.
Order 102 matches Bob.

Key takeaway

A JOIN combines related information stored in different tables.

Chapter 8 — Subqueries

Question

Given below is a code snippet that:

Calculates the average product price.
Uses that result inside another query.
Finds products above the average.

What should be the output of the following code?

-- Create products.
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10, 2)
);

-- Add products.
INSERT INTO products VALUES
(1, 'Mouse', 500),
(2, 'Keyboard', 1000),
(3, 'Monitor', 3000);

-- Find products whose price is above the average price.
SELECT name
FROM products
WHERE price > (
    -- This inner query calculates the average.
    SELECT AVG(price)
    FROM products
)
ORDER BY price;

Answer

Keyboard
Monitor

Step-by-step explanation

The average is (500 + 1000 + 3000) / 3 = 1500.
Mouse costs 500, so it is excluded.
Keyboard costs 1000, so it is also below the average.

Wait — this means the correct result is actually:

Monitor

This illustrates an important habit: always calculate the inner query before predicting the outer query.

Key takeaway

A subquery is a query used inside another SQL statement.

Chapter 9 — Database Normalization

Question

Given below is a code snippet that:

Separates customer data from order data.
Avoids repeatedly storing the same customer name.
Uses a foreign key to connect the tables.

What should be the output of the following code?

-- Store customer information once.
CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

-- Store order information separately.
CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,
    amount DECIMAL(10, 2),

    -- Connect the order to the customer.
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

-- Alice is stored only once.
INSERT INTO customers VALUES (1, 'Alice');

-- Multiple orders reference Alice.
INSERT INTO orders VALUES
(101, 1, 500),
(102, 1, 700);

-- Count Alice's orders.
SELECT COUNT(*)
FROM orders
WHERE customer_id = 1;

Answer

Step-by-step explanation

Alice exists once in customers.
Orders don't repeat the name "Alice".
They store Alice's ID instead.
Both orders contain customer_id = 1.
Therefore Alice has two orders.

Key takeaway

Normalization organizes data to reduce unnecessary duplication and update problems.

Chapter 10 — Indexes

Question

Given below is a code snippet that:

Creates an index.
Uses the indexed column for searching.
Demonstrates the basic purpose of an index.

What should be the output of the following code?

-- Create a customer table.
CREATE TABLE customers (
    id INT PRIMARY KEY,
    email VARCHAR(255),
    name VARCHAR(100)
);

-- Create an index for faster email lookups.
CREATE INDEX idx_customer_email
ON customers(email);

-- Add a customer.
INSERT INTO customers VALUES
(1, 'alice@example.com', 'Alice');

-- Search by email.
SELECT name
FROM customers
WHERE email = 'alice@example.com';

Answer

Alice

Step-by-step explanation

The index is created on email.
The query searches for a specific email.
The database can use the index to locate matching rows efficiently.
The result is Alice.

Beginner trap

An index is not free. It consumes storage and can make writes more expensive because the index also has to be maintained.

Key takeaway

Indexes are data structures that can make suitable queries much faster.

Chapter 11 — Transactions and ACID

Question

Given below is a code snippet that:

Starts a transaction.
Updates two accounts.
Commits both changes together.

What should be the output of the following code?

-- Create bank accounts.
CREATE TABLE accounts (
    id INT PRIMARY KEY,
    balance DECIMAL(10, 2)
);

-- Add two accounts.
INSERT INTO accounts VALUES
(1, 1000),
(2, 500);

-- Begin one logical transaction.
START TRANSACTION;

-- Remove money from Alice.
UPDATE accounts
SET balance = balance - 200
WHERE id = 1;

-- Add the same money to Bob.
UPDATE accounts
SET balance = balance + 200
WHERE id = 2;

-- Make both changes permanent.
COMMIT;

-- Display final balances.
SELECT id, balance
FROM accounts
ORDER BY id;

Answer

1 | 800
2 | 700

Step-by-step explanation

Account 1 starts at 1000.
200 is removed, leaving 800.
Account 2 starts at 500.
200 is added, leaving 700.
COMMIT makes both changes permanent.
A transaction treats the operations as one logical unit.

Key takeaway

Transactions protect operations that must succeed or fail together.

Chapter 12 — SQL Constraints and Data Integrity

Question

Given below is a code snippet that:

Uses PRIMARY KEY.
Uses NOT NULL.
Uses UNIQUE.
Uses CHECK.
Prevents invalid data.

What should be the output of the following code?

-- Create a user table with data rules.
CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    age INT CHECK (age >= 18)
);

-- Insert valid data.
INSERT INTO users VALUES
(1, 'alice@example.com', 25);

-- Count valid users.
SELECT COUNT(*)
FROM users;

Answer

Step-by-step explanation

id must uniquely identify the row.
email cannot be missing.
email must also be unique.
age must be at least 18.
Alice satisfies all rules.
Therefore one row exists.

Key takeaway

Database constraints make the database itself help protect data quality.

Part B — JDBC

Chapter 13 — JDBC Architecture

Question

Given below is a code snippet that:

Uses Java's JDBC API.
Connects Java application code to a database.
Executes SQL.
Reads a result.

What should be the output of the following code?

import java.sql.*;

// The Java application asks JDBC to connect to the database.
String url = "jdbc:mysql://localhost:3306/shop";

// Open a database connection.
try (Connection connection = DriverManager.getConnection(
        url,
        "app_user",
        "password")) {

    // Create a SQL statement.
    Statement statement = connection.createStatement();

    // Execute a query and receive the rows.
    ResultSet result = statement.executeQuery(
        "SELECT name FROM products WHERE id = 1"
    );

    // Move to the first returned row.
    if (result.next()) {
        // Read the name from the current row.
        System.out.println(result.getString("name"));
    }
}

Answer

Assuming the database contains product 1 named Keyboard:

Keyboard

Step-by-step explanation

Connection represents a connection between Java and the database.
Statement represents SQL that Java wants the database to execute.
executeQuery() sends a SELECT.
The database returns rows through ResultSet.
result.next() moves to the next row.
getString("name") reads the name column.
The try block automatically closes the connection.

How to read the important code

"Open a database connection, create a statement, execute a query, and read the result."

Key takeaway

JDBC is Java's standard API for communicating directly with relational databases.

Chapter 14 — Database Connections

Question

Given below is a code snippet that:

Opens a JDBC connection.
Uses the connection.
Automatically closes it.

What should be the output of the following code?

import java.sql.*;

// Database connection information.
String url = "jdbc:mysql://localhost:3306/shop";

// Open the connection.
try (Connection connection = DriverManager.getConnection(
        url,
        "app_user",
        "password")) {

    // Check whether the connection is currently open.
    System.out.println(!connection.isClosed());
}

Answer

true

Step-by-step explanation

DriverManager.getConnection() opens a database connection.
The Connection object represents that connection.
Inside the try block, it is open.
Therefore isClosed() returns false.
!false becomes true.
After the block, Java closes the connection automatically.

Key takeaway

A JDBC Connection represents a live communication channel between Java and the database.

Chapter 15 — Statement vs PreparedStatement

Question

Given below is a code snippet that:

Uses PreparedStatement.
Sends values separately from SQL.
Avoids constructing SQL by concatenating user input.

What should be the output of the following code?

import java.sql.*;

// SQL contains a placeholder instead of directly inserting the email.
String sql = "SELECT name FROM users WHERE email = ?";

// Open the database connection.
try (Connection connection = DriverManager.getConnection(
        "jdbc:mysql://localhost:3306/shop",
        "app_user",
        "password")) {

    // Prepare the SQL statement.
    try (PreparedStatement statement =
             connection.prepareStatement(sql)) {

        // Safely provide the value for the ? placeholder.
        statement.setString(1, "alice@example.com");

        // Execute the query.
        try (ResultSet result = statement.executeQuery()) {

            // Read the matching user.
            if (result.next()) {
                System.out.println(result.getString("name"));
            }
        }
    }
}

Answer

Assuming Alice has that email:

Alice

Step-by-step explanation

? is a parameter placeholder.
setString(1, ...) supplies the first parameter.
JDBC handles the value separately from the SQL structure.
This is safer than constructing SQL with string concatenation.
The matching row is returned.

Beginner trap

Avoid code like:

"SELECT * FROM users WHERE email = '" + email + "'"

when user input is involved.

Key takeaway

Use PreparedStatement for parameterized SQL.

Chapter 16 — ResultSet

Question

Given below is a code snippet that:

Reads multiple database rows.
Uses ResultSet.
Moves through rows with next().

What should be the output of the following code?

import java.sql.*;

// SQL retrieves multiple products.
String sql = "SELECT name, price FROM products ORDER BY id";

// Open the connection.
try (Connection connection = DriverManager.getConnection(
        "jdbc:mysql://localhost:3306/shop",
        "app_user",
        "password");
     
     // Prepare the query.
     PreparedStatement statement = connection.prepareStatement(sql);
     
     // Execute the query.
     ResultSet result = statement.executeQuery()) {

    // Move through every returned row.
    while (result.next()) {

        // Read columns from the current row.
        String name = result.getString("name");
        double price = result.getDouble("price");

        // Display the product.
        System.out.println(name + " = " + price);
    }
}

Answer

For:

Keyboard = 1000
Mouse = 500

the output is:

Keyboard = 1000.0
Mouse = 500.0

Key takeaway

ResultSet represents the rows returned by a database query.

Chapter 17 — JDBC CRUD

Question

Given below is a code snippet that:

Inserts a product.
Reads it.
Updates it.
Demonstrates JDBC CRUD.

What should be the output of the following code?

import java.sql.*;

// SQL for inserting a product.
String insertSql =
    "INSERT INTO products(id, name, price) VALUES (?, ?, ?)";

// SQL for changing the product price.
String updateSql =
    "UPDATE products SET price = ? WHERE id = ?";

// Open one database connection.
try (Connection connection = DriverManager.getConnection(
        "jdbc:mysql://localhost:3306/shop",
        "app_user",
        "password")) {

    // Insert the product.
    try (PreparedStatement insert =
             connection.prepareStatement(insertSql)) {

        // Fill the three placeholders.
        insert.setInt(1, 10);
        insert.setString(2, "Keyboard");
        insert.setDouble(3, 1000);

        // Execute the INSERT.
        insert.executeUpdate();
    }

    // Update the price.
    try (PreparedStatement update =
             connection.prepareStatement(updateSql)) {

        // Set the new price and product ID.
        update.setDouble(1, 1200);
        update.setInt(2, 10);

        // Execute the UPDATE.
        update.executeUpdate();
    }

    // Read the final price.
    try (PreparedStatement select =
             connection.prepareStatement(
                 "SELECT price FROM products WHERE id = ?")) {

        // Specify which product to read.
        select.setInt(1, 10);

        // Execute the SELECT.
        try (ResultSet result = select.executeQuery()) {

            // Print the updated price.
            if (result.next()) {
                System.out.println(result.getDouble("price"));
            }
        }
    }
}

Answer

1200.0

Step-by-step explanation

Product 10 is inserted at 1000.
Its price is updated to 1200.
The final SELECT retrieves that price.
Therefore Java prints 1200.0.

Key takeaway

JDBC allows Java to perform complete database CRUD operations.

Chapter 18 — JDBC Transactions

Question

Given below is a code snippet that:

Starts a transaction.
Performs two database updates.
Commits them together.

What should be the output?

import java.sql.*;

// Open a database connection.
try (Connection connection = DriverManager.getConnection(
        "jdbc:mysql://localhost:3306/shop",
        "app_user",
        "password")) {

    // Disable automatic commit so we control the transaction.
    connection.setAutoCommit(false);

    try {
        // Remove money from account 1.
        try (PreparedStatement debit =
                 connection.prepareStatement(
                     "UPDATE accounts SET balance = balance - ? WHERE id = ?")) {

            debit.setDouble(1, 100);
            debit.setInt(2, 1);
            debit.executeUpdate();
        }

        // Add money to account 2.
        try (PreparedStatement credit =
                 connection.prepareStatement(
                     "UPDATE accounts SET balance = balance + ? WHERE id = ?")) {

            credit.setDouble(1, 100);
            credit.setInt(2, 2);
            credit.executeUpdate();
        }

        // Permanently apply both operations.
        connection.commit();

        System.out.println("Transfer complete");

    } catch (Exception e) {

        // Undo all changes if something fails.
        connection.rollback();

        System.out.println("Transfer failed");
    }
}

Answer

Transfer complete

Step-by-step explanation

Automatic commit is disabled.
The debit happens.
The credit happens.
commit() makes both changes permanent.
The success message is printed.
If an exception occurred before commit, rollback() would undo the transaction.

Key takeaway

JDBC transactions let Java control whether a group of database changes becomes permanent.

Chapter 19 — JDBC Batch Operations

Question

Given below is a code snippet that:

Uses one prepared SQL statement.
Adds multiple operations to a batch.
Executes them together.

What should be the output?

import java.sql.*;

// SQL used repeatedly for inserting products.
String sql =
    "INSERT INTO products(id, name, price) VALUES (?, ?, ?)";

// Open the database connection.
try (Connection connection = DriverManager.getConnection(
        "jdbc:mysql://localhost:3306/shop",
        "app_user",
        "password");
     
     // Prepare one reusable statement.
     PreparedStatement statement = connection.prepareStatement(sql)) {

    // Add the first product to the batch.
    statement.setInt(1, 1);
    statement.setString(2, "Keyboard");
    statement.setDouble(3, 1000);
    statement.addBatch();

    // Add the second product.
    statement.setInt(1, 2);
    statement.setString(2, "Mouse");
    statement.setDouble(3, 500);
    statement.addBatch();

    // Send the batch for execution.
    int[] results = statement.executeBatch();

    // Two operations were submitted.
    System.out.println(results.length);
}

Answer

Step-by-step explanation

The first product is added to the batch.
The second product is added.
executeBatch() executes the queued operations.
The returned array contains a result for each operation.
There were two operations, so its length is 2.

Key takeaway

Batch operations allow multiple similar database operations to be submitted efficiently.

Chapter 20 — Connection Pooling

Question

Given below is a code snippet that:

Uses a connection pool concept.
Borrows a connection.
Returns it to the pool when finished.

What should be the output?

// Imagine a connection pool containing reusable database connections.
ConnectionPool pool = new ConnectionPool();

// Borrow one existing connection from the pool.
try (Connection connection = pool.getConnection()) {

    // Use the database connection.
    System.out.println("Using database");
}

// Closing this pooled connection returns it to the pool.
System.out.println("Connection returned");

Answer

Using database
Connection returned

Step-by-step explanation

Creating a database connection can be relatively expensive.
A connection pool keeps reusable connections available.
The application borrows one.
The application uses it.
Closing it returns it to the pool rather than necessarily destroying the physical database connection.

Beginner trap

A connection pool is not the database. It manages reusable connections to the database.

Key takeaway

Production applications generally reuse database connections instead of opening a brand-new physical connection for every query.

Chapter 21 — DAO Pattern

Question

Given below is a code snippet that:

Separates database code from business logic.
Uses a DAO.
Lets the service ask for data without knowing SQL details.

What should be the output?

// DAO = Data Access Object.
// Its job is database access.
class ProductDao {

    // Pretend this method reads the product from the database.
    String findNameById(int id) {
        return "Keyboard";
    }
}

// Service contains business-level logic.
class ProductService {

    // The service depends on the DAO.
    private final ProductDao dao = new ProductDao();

    String getProductName(int id) {

        // Ask the DAO for database data.
        return dao.findNameById(id);
    }
}

// Application code.
ProductService service = new ProductService();

// Business code asks the service for the product.
System.out.println(service.getProductName(1));

Answer

Keyboard

Step-by-step explanation

ProductDao handles data access.
ProductService handles application/business logic.
The service doesn't contain SQL.
It asks the DAO for the product.
The DAO returns the data.
The application prints it.

Key takeaway

DAO separates database access from the rest of the application.

Part C — JPA & Hibernate

Chapter 22 — ORM, JPA and Hibernate

Question

Given below is a code snippet that:

Defines a Java class representing database data.
Uses JPA annotations.
Lets an ORM map Java objects to database rows.

What should be the output?

import jakarta.persistence.*;

// Tell JPA that this class represents a database entity.
@Entity
class Product {

    // This field represents the database primary key.
    @Id
    private Long id;

    // This field represents a database column.
    private String name;

    // Constructor for creating a product.
    Product(Long id, String name) {
        this.id = id;
        this.name = name;
    }

    // Return the product name.
    String getName() {
        return name;
    }
}

// Application code creates a normal Java object.
Product product = new Product(1L, "Keyboard");

// The object behaves like a normal Java object.
System.out.println(product.getName());

Answer

Keyboard

Step-by-step explanation

@Entity tells JPA that Product is intended to be persisted.
@Id identifies the entity's primary key.
JPA can map the object's fields to database columns.
Hibernate is a popular implementation of the JPA specification.
The Java application can work primarily with objects instead of manually writing SQL for every operation.

Key takeaway

JPA defines ORM APIs; Hibernate is a widely used implementation of those APIs.

Chapter 23 — Entities and Entity Lifecycle

Question

Given below is a code snippet that:

Creates an entity.
Makes it managed by an EntityManager.
Changes the managed object.
Commits the transaction.

What should be the output?

// Create a new Java object.
Product product = new Product(null, "Keyboard");

// Begin a database transaction.
entityManager.getTransaction().begin();

// Tell JPA to manage this new entity.
entityManager.persist(product);

// Change the managed object's state.
product.setName("Mechanical Keyboard");

// Commit the transaction.
entityManager.getTransaction().commit();

Answer

Conceptually, the database receives:

Product name = Mechanical Keyboard

Step-by-step explanation

product starts as a normal Java object.
persist() makes it a managed entity.
JPA tracks changes to managed entities.
The name changes.
When the transaction commits, JPA can generate the necessary SQL.
The final database state contains "Mechanical Keyboard".

Key takeaway

A managed entity is tracked by JPA, allowing changes to be synchronized with the database.

Chapter 24 — Primary Keys and Generated IDs

Question

Given below is a code snippet that:

Defines an entity ID.
Tells JPA to generate the ID.
Creates an entity without manually assigning its ID.

What should be the output?

@Entity
class Product {

    // JPA identifies this field as the primary key.
    @Id

    // The database/provider generates the ID.
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    // Store the product name.
    private String name;

    Product(String name) {
        this.name = name;
    }

    Long getId() {
        return id;
    }
}

// Create a product without specifying an ID.
Product product = new Product("Keyboard");

// After persistence, the database/provider can assign an ID.
entityManager.persist(product);

// Print the generated ID.
System.out.println(product.getId());

Answer

The exact number depends on the database's generated sequence.

For example:

Step-by-step explanation

The application creates the product without an ID.
@GeneratedValue tells JPA that the ID should be generated.
When persisted, the database/provider generates an ID.
The entity can then contain that generated ID.

Key takeaway

Generated IDs let the database or persistence provider assign unique identifiers.

Chapter 25 — Entity Relationships

Question

Given below is a code snippet that:

Creates Customer and Order entities.
Connects them.
Represents a database relationship in Java.

What should be the output?

@Entity
class Customer {

    // Customer's database ID.
    @Id
    Long id;

    // Customer's name.
    String name;
}

@Entity
class Order {

    // Order's database ID.
    @Id
    Long id;

    // Reference the customer who owns this order.
    @ManyToOne
    Customer customer;
}

// Create a customer object.
Customer customer = new Customer();
customer.name = "Alice";

// Create an order.
Order order = new Order();

// Connect the order to Alice.
order.customer = customer;

// Read the relationship.
System.out.println(order.customer.name);

Answer

Alice

Step-by-step explanation

A Customer object is created.
An Order object is created.
order.customer stores a reference to the customer.
Therefore order.customer.name reaches Alice.

Key takeaway

Entity relationships allow Java objects to represent relationships that exist between database records.

Chapter 26 — One-to-Many and Many-to-One

Question

Given below is a code snippet that:

Gives one customer multiple orders.
Uses @OneToMany.
Uses @ManyToOne.
Traverses the relationship.

What should be the output?

@Entity
class Customer {

    // Customer's ID.
    @Id
    Long id;

    // One customer can have many orders.
    @OneToMany(mappedBy = "customer")
    List<Order> orders = new ArrayList<>();
}

@Entity
class Order {

    // Order's ID.
    @Id
    Long id;

    // Each order belongs to one customer.
    @ManyToOne
    Customer customer;
}

// Create one customer.
Customer customer = new Customer();

// Create two orders.
Order first = new Order();
Order second = new Order();

// Connect both orders to the customer.
first.customer = customer;
second.customer = customer;

// Keep the two orders in the customer's collection.
customer.orders.add(first);
customer.orders.add(second);

// Count the customer's orders.
System.out.println(customer.orders.size());

Answer

Step-by-step explanation

One customer is created.
Two orders are created.
Each order points to the customer.
The customer's orders collection contains both orders.
Therefore its size is 2.

Key takeaway

One-to-many means one entity can be associated with many other entities.

Chapter 27 — One-to-One and Many-to-Many

Question

Given below is a code snippet that:

Demonstrates one-to-one.
Demonstrates many-to-many.
Shows how relationships can be represented between entities.

What should be the output?

// A user can have one profile.
@Entity
class User {

    // User ID.
    @Id
    Long id;

    // One user has one profile.
    @OneToOne
    Profile profile;
}

// A user's profile.
@Entity
class Profile {

    // Profile ID.
    @Id
    Long id;
}

// A product can belong to many categories.
@Entity
class Product {

    // Product ID.
    @Id
    Long id;

    // A product can have many categories,
    // and a category can contain many products.
    @ManyToMany
    Set<Category> categories = new HashSet<>();
}

@Entity
class Category {

    // Category ID.
    @Id
    Long id;

    // Categories can contain many products.
    @ManyToMany(mappedBy = "categories")
    Set<Product> products = new HashSet<>();
}

Answer

There is no console output.

The code defines relationships; it doesn't print anything.

Step-by-step explanation

User → Profile demonstrates one-to-one.
A user has one profile.
Product ↔ Category demonstrates many-to-many.
A product can belong to many categories.
A category can contain many products.
These relationships usually require corresponding database structures.

Key takeaway

JPA provides relationship mappings that connect Java objects to relational database relationships.

Chapter 28 — Lazy vs Eager Fetching

Question

Given below is a code snippet that:

Defines a lazy relationship.
Loads the customer first.
Delays loading orders until they are accessed.

What should be the output?

@Entity
class Customer {

    // Customer's ID.
    @Id
    Long id;

    // Orders are not necessarily loaded immediately.
    @OneToMany(
        mappedBy = "customer",
        fetch = FetchType.LAZY
    )
    List<Order> orders;
}

// Load the customer.
Customer customer = entityManager.find(Customer.class, 1L);

// The customer itself has been loaded.
System.out.println("Customer loaded");

// Accessing the collection may cause Hibernate
// to load the orders from the database.
System.out.println(customer.orders.size());

Answer

Customer loaded
2

assuming the customer has two orders.

Step-by-step explanation

The customer is loaded.
The orders use LAZY fetching.
Hibernate can postpone loading the collection.
When customer.orders is accessed, Hibernate may issue another query.
The collection contains two orders.

Beginner trap

Lazy loading does not simply mean "never load it." It means loading can be deferred until needed.

Key takeaway

Lazy fetching can avoid loading related data until the application actually needs it.

Chapter 29 — Cascading and Orphan Removal

Question

Given below is a code snippet that:

Connects a customer to orders.
Uses cascading.
Uses orphan removal.
Shows the intended ownership relationship.

What should be the output?

@Entity
class Customer {

    // Customer ID.
    @Id
    Long id;

    // Saving the customer can also persist its orders.
    // Removing an order from this collection can remove
    // the corresponding child entity when orphanRemoval is enabled.
    @OneToMany(
        mappedBy = "customer",
        cascade = CascadeType.ALL,
        orphanRemoval = true
    )
    List<Order> orders = new ArrayList<>();
}

// Create a customer.
Customer customer = new Customer();

// Create an order.
Order order = new Order();

// Add the order to the customer.
customer.orders.add(order);

// Save the customer.
entityManager.persist(customer);

// Remove the order from the customer's collection.
customer.orders.remove(order);

Answer

There is no console output.

Step-by-step explanation

cascade = ALL tells JPA to propagate certain persistence operations.
The customer can therefore propagate persistence operations to its orders.
orphanRemoval = true tells JPA that an order removed from the managed collection is considered an orphan.
Depending on the transaction/state, JPA can remove that orphan from the database.

Key takeaway

Cascading controls propagation of persistence operations; orphan removal controls child entities that are removed from an owning relationship.

Chapter 30 — Persistence Context

Question

Given below is a code snippet that:

Loads an entity.
Changes it without explicitly calling update().
Relies on dirty checking.

What should be the output?

// Begin a transaction.
entityManager.getTransaction().begin();

// Load the managed entity.
Product product =
    entityManager.find(Product.class, 1L);

// Change its state.
product.setPrice(1200);

// No explicit UPDATE command is written here.

// Commit the transaction.
entityManager.getTransaction().commit();

Answer

Conceptually:

UPDATE products SET price = 1200 WHERE id = 1

Step-by-step explanation

find() loads the entity into the persistence context.
The entity becomes managed.
Java changes its price.
JPA tracks the managed entity.
At flush/commit, Hibernate detects the change.
Hibernate can generate an SQL UPDATE.

This is called dirty checking.

Key takeaway

JPA can detect changes to managed entities and synchronize them with the database automatically.

Chapter 31 — JPQL

Question

Given below is a code snippet that:

Uses JPQL.
Queries entities rather than database tables directly.
Filters products by price.

What should be the output?

// JPQL refers to the entity class and its Java field.
String jpql =
    "SELECT p FROM Product p WHERE p.price > :minimum";

// Create the JPQL query.
TypedQuery<Product> query =
    entityManager.createQuery(jpql, Product.class);

// Provide the named parameter.
query.setParameter("minimum", 1000);

// Execute the query.
List<Product> products = query.getResultList();

// Print each matching product.
for (Product product : products) {
    System.out.println(product.getName());
}

For products:

Mouse = 500
Keyboard = 1200
Monitor = 5000

Answer

Keyboard
Monitor

Step-by-step explanation

JPQL uses the Product entity.
p.price refers to the Java entity field.
:minimum is a named parameter.
The parameter is set to 1000.
Products above 1000 are returned.
Keyboard and Monitor qualify.

Key takeaway

JPQL queries the application's entity model rather than writing SQL directly against tables.

Chapter 32 — Native Queries

Question

Given below is a code snippet that:

Uses native SQL.
Executes SQL directly against database tables.
Retrieves a result.

What should be the output?

// This is real SQL, so it refers to the database table.
String sql =
    "SELECT name FROM products WHERE price > ?";

// Create a native query.
Query query =
    entityManager.createNativeQuery(sql);

// Supply the SQL parameter.
query.setParameter(1, 1000);

// Execute the database query.
List<?> names = query.getResultList();

// Print the first result.
System.out.println(names.get(0));

Assuming the first matching product is "Keyboard":

Answer

Keyboard

Step-by-step explanation

Unlike JPQL, the query directly refers to the database table.
products is the table.
price is the database column.
The parameter is 1000.
Matching rows are returned.

Key takeaway

Native queries let you use database-specific SQL when JPQL is insufficient or inappropriate.

Chapter 33 — Transactions with JPA

Question

Given below is a code snippet that:

Starts a transaction.
Changes two entities.
Commits both changes.

What should be the output?

// Begin one transaction.
entityManager.getTransaction().begin();

// Load the first product.
Product first =
    entityManager.find(Product.class, 1L);

// Load the second product.
Product second =
    entityManager.find(Product.class, 2L);

// Change both managed entities.
first.setPrice(1200);
second.setPrice(800);

// Commit both changes together.
entityManager.getTransaction().commit();

System.out.println("Saved");

Answer

Saved

Step-by-step explanation

A transaction starts.
Two products are loaded.
Both become managed.
Their prices change.
JPA tracks those changes.
Commit makes the transaction permanent.
If the transaction fails and is rolled back, the changes should not be committed as a successful transaction.

Key takeaway

Database changes should be grouped into transactions according to the business operation they represent.

Chapter 34 — The N+1 Query Problem

Question

Given below is a code snippet that:

Loads multiple customers.
Accesses each customer's orders.
Demonstrates how one query can unexpectedly become many queries.

What should be the output?

// Load all customers.
List<Customer> customers =
    entityManager.createQuery(
        "SELECT c FROM Customer c",
        Customer.class
    ).getResultList();

// Access orders for every customer.
for (Customer customer : customers) {

    // With lazy loading, this access may trigger
    // another database query for each customer.
    System.out.println(customer.orders.size());
}

Assume there are 100 customers.

Answer

Conceptually, the database may receive:

1 query for customers
+
100 queries for orders
=
101 queries

Step-by-step explanation

The first query loads all 100 customers.
The loop processes each customer.
Accessing customer.orders may trigger a separate query.
That can happen 100 times.
Therefore one query becomes 101 queries.

This is the N+1 query problem.

Key takeaway

ORM convenience can accidentally create excessive database queries.

Chapter 35 — JPA/Hibernate Performance

Question

Given below is a code snippet that:

Loads only required fields.
Uses pagination.
Avoids retrieving an unnecessarily large dataset.

What should be the output?

// Retrieve only the fields needed by the screen.
TypedQuery<Object[]> query =
    entityManager.createQuery(
        "SELECT p.id, p.name FROM Product p ORDER BY p.id",
        Object[].class
    );

// Start at the first result.
query.setFirstResult(0);

// Retrieve only 20 rows.
query.setMaxResults(20);

// Execute the optimized query.
List<Object[]> products = query.getResultList();

// Print how many records were returned.
System.out.println(products.size());

Answer

assuming at least 20 products exist.

Step-by-step explanation

The query doesn't retrieve every product.
It selects only id and name.
setFirstResult(0) starts from the beginning.
setMaxResults(20) limits the result.
Therefore only 20 rows are returned.

Important performance principles

In real applications, watch for:

N+1 queries
unnecessary eager fetching
huge result sets
missing indexes
inefficient joins
unnecessary entity loading
excessive database round trips
poorly designed transactions

Key takeaway

ORM does not eliminate database performance concerns—you still need to understand the SQL being generated.

Chapter 36 — Designing a Production Persistence Layer

Question

Given below is a code snippet that:

Separates Controller, Service and Repository responsibilities.
Uses a repository for persistence.
Keeps business logic in the service.
Demonstrates the structure of a production-style backend.

What should be the output?

// Repository is responsible for persistence.
interface ProductRepository {

    // Find a product using its ID.
    Product findById(Long id);
}

// Service contains business logic.
class ProductService {

    // Depend on the persistence abstraction.
    private final ProductRepository repository;

    // Receive the repository through the constructor.
    ProductService(ProductRepository repository) {
        this.repository = repository;
    }

    // Business operation.
    String getProductName(Long id) {

        // Ask persistence layer for the product.
        Product product = repository.findById(id);

        // Convert the entity into business-level output.
        return product.getName();
    }
}

// Pretend repository implementation.
class InMemoryProductRepository
        implements ProductRepository {

    public Product findById(Long id) {

        // Simulate data retrieved from a database.
        return new Product(id, "Keyboard");
    }
}

// Build the application layers.
ProductRepository repository =
    new InMemoryProductRepository();

// Inject repository into the service.
ProductService service =
    new ProductService(repository);

// Perform the business operation.
System.out.println(service.getProductName(1L));

Answer

Keyboard

Step-by-step explanation

The repository represents the persistence layer.
The service represents business logic.
The service doesn't need to know how the database works.
It depends on the ProductRepository abstraction.
A repository implementation supplies the data.
The service returns the product name.
The application prints Keyboard.

How to read the important code

"The repository handles persistence, the service handles business logic, and the service receives the repository as a dependency."

This separation becomes extremely important when we reach Spring Boot in Phase 6.

Key takeaway

A good persistence architecture keeps database concerns separated from business logic.

Phase 5 — What You Should Now Understand

By the end of this phase, you should be able to mentally follow this entire chain:

Java Application
       ↓
Service / Business Logic
       ↓
Repository / DAO
       ↓
JPA / Hibernate OR JDBC
       ↓
SQL
       ↓
Database
       ↓
Tables
       ↓
Rows

And you should understand the two major approaches:

Direct database programming

Java
 ↓
JDBC
 ↓
SQL
 ↓
Database

You explicitly write SQL and process ResultSet objects.

ORM-based programming

Java
 ↓
JPA
 ↓
Hibernate
 ↓
SQL generated by Hibernate
 ↓
Database

You primarily work with Java entities, relationships, persistence contexts and queries.

The most important concepts from Phase 5

If you remember only the high-value concepts initially, remember these:

DATABASE
├── Tables
├── Rows
├── Columns
├── Primary keys
├── Foreign keys
├── Relationships
├── Constraints
├── Indexes
└── Transactions

SQL
├── SELECT
├── INSERT
├── UPDATE
├── DELETE
├── WHERE
├── ORDER BY
├── GROUP BY
├── JOIN
└── Subqueries

JDBC
├── Connection
├── PreparedStatement
├── ResultSet
├── CRUD
├── Transactions
├── Batch operations
├── Connection pooling
└── DAO

JPA / HIBERNATE
├── Entity
├── @Id
├── Relationships
├── Persistence Context
├── Dirty Checking
├── JPQL
├── Native SQL
├── Lazy Loading
├── Eager Loading
├── Cascades
├── Transactions
└── N+1 Problem

The professional mental model

The biggest thing I want you to take from this phase is:

Java objects are not automatically the database.

There is a translation layer between them.

Java object
   ↓
JPA/Hibernate
   ↓
SQL
   ↓
Database row

Understanding that translation is what separates someone who merely knows Spring Data JPA syntax from someone who can actually debug and build Java backend applications professionally.