Spring Data JPA & Database

Solution and explanation

What it is

JPA (Jakarta Persistence API, formerly Java Persistence API) is the standard specification for object-relational mapping (ORM) in Java. It defines how Java objects (entities) map to database tables and how to save, query and update them without writing most SQL by hand. JPA is only a specification; Hibernate is the most used implementation, and Spring Data JPA builds repositories on top of it.

Your code → Spring Data JPA (repositories) → JPA API (EntityManager) → Hibernate → JDBC → MySQL

Key concepts

• Entity: a class mapped to a table (@Entity).
• EntityManager: the API to persist, find, merge and remove entities.
• Persistence context: the set of managed entities in the current transaction (a first-level cache). Changes to managed entities are saved automatically at commit ("dirty checking").
• JPQL: an object-oriented query language over entities, not tables.
• Relationships: @OneToMany, @ManyToOne, @ManyToMany, @OneToOne.

Example

@Entity
@Table(name = "candidates")
public class Candidate {
    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false, length = 80)
    private String name;

    @Column(unique = true)
    private String email;

    @OneToMany(mappedBy = "candidate", cascade = CascadeType.ALL, orphanRemoval = true)
    private List<Application> applications = new ArrayList<>();
}

@PersistenceContext EntityManager em;

@Transactional
public void rename(Long id, String newName) {
    Candidate c = em.find(Candidate.class, id);   // SELECT ... WHERE id = ?
    c.setName(newName);                           // no save() call needed
}                                                 // UPDATE executed at commit (dirty checking)

List<Candidate> pune = em.createQuery(
        "SELECT c FROM Candidate c WHERE c.city = :city", Candidate.class)
    .setParameter("city", "Pune").getResultList();

Benefits and trade-offs

• Less boilerplate, database portability, caching and change tracking.
• You must understand the generated SQL: lazy loading, N+1 queries and large persistence contexts can hurt performance.

Note: in interviews, distinguish JPA (specification), Hibernate (implementation) and Spring Data JPA (repository abstraction).

Solution and explanation

What it is

Hibernate ORM is the most widely used implementation of the JPA specification. It converts entity operations into SQL, manages the persistence context, caches entities, and handles relationships and transactions. Spring Boot uses Hibernate by default with spring-boot-starter-data-jpa.

What Hibernate does for you

• Generates INSERT, UPDATE, DELETE and SELECT statements from entity mappings.
• Dirty checking: detects changed fields of managed entities and updates only those at commit.
• First-level cache: within one transaction, em.find(Job.class, 5) twice issues one query.
• Optional second-level cache (Ehcache, Redis) across transactions.
• Lazy loading of associations through proxies.
• Schema generation (ddl-auto) for development.
• Batch inserts/updates (hibernate.jdbc.batch_size).

Seeing the SQL

spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
logging.level.org.hibernate.orm.jdbc.bind=trace

select j1_0.id, j1_0.title, j1_0.location from jobs j1_0 where j1_0.id=?
binding parameter (1:BIGINT) <- [5]

Hibernate-specific features (beyond JPA)

• @BatchSize to load lazy collections in batches (fixes N+1).
• @Formula for computed columns, @Where/@SQLRestriction for soft deletes.
• Natural IDs, multi-tenancy, envers for auditing history.

ddl-auto values

• create / create-drop: rebuild the schema (tests only).
• update: alters tables – convenient in development, risky in production.
• validate: only checks mappings – good for production.
• none: do nothing. Use Flyway or Liquibase migrations for real schema changes.

Session vs EntityManager

Hibernate's native API is Session/SessionFactory; JPA's is EntityManager/EntityManagerFactory. In Spring you normally use EntityManager or Spring Data repositories, and Hibernate works underneath.

Note: always know which SQL a repository method generates – most JPA performance problems are invisible until you look at the logs.

Solution and explanation

What it is

An entity is a Java class whose instances are persisted in a database table. Each object corresponds to a row and each persistent field to a column. JPA tracks entities' lifecycle and changes.

@Entity
@Table(name = "job_applications",
       uniqueConstraints = @UniqueConstraint(columnNames = {"job_id", "candidate_id"}))
public class JobApplication {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)      // MySQL AUTO_INCREMENT
    private Long id;

    @ManyToOne(fetch = FetchType.LAZY, optional = false)
    @JoinColumn(name = "job_id")
    private Job job;

    @Column(name = "candidate_id", nullable = false)
    private Long candidateId;

    @Enumerated(EnumType.STRING)                              // store "APPLIED", not 0
    @Column(nullable = false, length = 30)
    private ApplicationStatus status = ApplicationStatus.APPLIED;

    @CreationTimestamp
    private Instant appliedAt;

    @Version                                                  // optimistic locking
    private Integer version;

    protected JobApplication() { }                            // required no-arg constructor
    public JobApplication(Job job, Long candidateId) { this.job = job; this.candidateId = candidateId; }
}

Requirements

• @Entity on the class and an @Id field.
• A no-argument constructor (public or protected).
• Not final, so Hibernate can create proxies for lazy loading.

Entity lifecycle states

• Transient: created with new, not yet known to JPA.
• Managed: persisted or loaded inside an open persistence context; changes are tracked.
• Detached: the persistence context closed; changes are no longer tracked.
• Removed: scheduled for deletion.

Best practices

• Use @Enumerated(EnumType.STRING); ordinal values break when enum order changes.
• Prefer LAZY for @ManyToOne/@OneToOne to avoid loading graphs you do not need.
• Implement equals/hashCode carefully (business key or id-based), never on lazy collections.
• Do not return entities from REST controllers; map to DTOs.

Note: @Version adds optimistic locking: if two users edit the same application, the second save fails with an OptimisticLockException instead of silently overwriting.

Solution and explanation

What it is

JpaRepository<T, ID> is a Spring Data interface that gives you a full repository implementation at runtime: CRUD, paging, sorting and batch operations. You declare an interface; Spring generates the class.

public interface JobRepository extends JpaRepository<Job, Long> { }

Inherited methods include save, saveAll, findById, findAll(Pageable), existsById, count, deleteById, flush, saveAndFlush.

Derived query methods

Spring parses the method name and builds the query.

public interface JobRepository extends JpaRepository<Job, Long> {
    List<Job> findByLocationIgnoreCase(String location);
    Page<Job> findByTitleContainingIgnoreCaseAndOpenTrue(String keyword, Pageable pageable);
    long countByCompanyId(Long companyId);
    boolean existsByCompanyIdAndTitle(Long companyId, String title);
    List<Job> findTop5ByOrderByPostedAtDesc();
}

Custom JPQL and native queries

@Query("SELECT j FROM Job j JOIN FETCH j.company WHERE j.open = true AND j.minSalary >= :salary")
List<Job> openJobsWithCompany(@Param("salary") int salary);

@Query(value = "SELECT location, COUNT(*) AS total FROM jobs GROUP BY location", nativeQuery = true)
List<Object[]> jobsPerCity();

@Modifying
@Query("UPDATE Job j SET j.open = false WHERE j.expiresAt < CURRENT_TIMESTAMP")
int closeExpired();

Projections (fetch only what you need)

public interface JobSummary { Long getId(); String getTitle(); String getLocation(); }
List<JobSummary> findByOpenTrue();

Paging and sorting

Page<Job> page = jobs.findAll(PageRequest.of(0, 20, Sort.by("postedAt").descending()));
page.getContent(); page.getTotalElements(); page.getTotalPages();

Hierarchy

Repository → CrudRepository → PagingAndSortingRepository → JpaRepository (adds flush, batch deletes, List return types).

Note: @Modifying update queries bypass the persistence context; add @Transactional and clear the context (clearAutomatically = true) if managed entities might be stale.

Solution and explanation

The difference

• save(entity): makes the entity managed (persist for new, merge for detached). The SQL INSERT/UPDATE usually runs later, when the transaction commits or Hibernate flushes before a query.
• saveAndFlush(entity): saves and immediately flushes the persistence context, sending the SQL to the database right away (still inside the same transaction – not committed yet).

What "flush" means

Flush synchronises pending changes from the persistence context to the database by executing SQL. Commit makes those changes permanent. Hibernate flushes automatically at commit and before queries that might see the pending data.

Example

@Transactional
public void register(Candidate c) {
    candidates.save(c);
    // no INSERT executed yet in many cases
    auditService.log("candidate created");
}   // INSERT happens here at commit

@Transactional
public void registerAndCheck(Candidate c) {
    try {
        candidates.saveAndFlush(c);          // INSERT now → unique constraint errors surface here
    } catch (DataIntegrityViolationException e) {
        throw new DuplicateEmailException(c.getEmail());
    }
    externalCrm.notify(c.getId());           // id and constraints are confirmed before calling out
}

When to use saveAndFlush

• You need database-generated values or triggers immediately.
• You want constraint violations (duplicate email) to be thrown at a specific point so you can handle them.
• Native SQL or JDBC in the same transaction must see the change.

When not to

In loops – flushing every row kills batching. Use saveAll and let Hibernate batch.

spring.jpa.properties.hibernate.jdbc.batch_size=50
spring.jpa.properties.hibernate.order_inserts=true

Note: with IDENTITY id generation, Hibernate must INSERT immediately on save to get the id, so save may already execute SQL; that is also why IDENTITY disables JDBC insert batching (SEQUENCE ids allow it).

Solution and explanation

What it is

With lazy loading, a related entity or collection is not fetched when the parent is loaded; JPA loads it only when your code first accesses it. Hibernate places a proxy (or an uninitialised collection) in the field and runs the extra query on access.

@Entity
public class Company {
    @Id private Long id;
    private String name;

    @OneToMany(mappedBy = "company", fetch = FetchType.LAZY)   // LAZY is the default for collections
    private List<Job> jobs;
}

@Transactional(readOnly = true)
public int openJobCount(Long companyId) {
    Company c = companies.findById(companyId).orElseThrow();   // SELECT company only
    return c.getJobs().size();                                 // now: SELECT jobs WHERE company_id = ?
}

Defaults

• @OneToMany and @ManyToMany: LAZY.
• @ManyToOne and @OneToOne: EAGER by default – most teams change these to LAZY explicitly.

LazyInitializationException

Accessing a lazy field after the transaction/persistence context has closed fails:

Company c = companyService.find(5);     // transaction ended inside the service
c.getJobs().size();                     // LazyInitializationException: no Session

Fixes:

• Load what you need inside the transaction (map to a DTO there).
• Use JOIN FETCH or an @EntityGraph for that use case.
• Avoid relying on Open Session in View (spring.jpa.open-in-view=false is recommended), which keeps the session open during view rendering and hides N+1 problems.

Benefits and risks

• Benefit: avoids loading large object graphs you do not need.
• Risk: the N+1 query problem when accessing lazy relations in a loop.

Note: lazy loading is a good default; decide per use case when to fetch eagerly with JOIN FETCH or entity graphs.

Solution and explanation

What it is

Eager loading fetches related entities together with the parent, in the same query (a join) or immediately after. The data is ready even after the transaction ends.

Two kinds of "eager"

1. Mapping-level FetchType.EAGER – the relation is always loaded, everywhere. Usually a bad idea for collections because every query pays the cost.
2. Query-level eager fetching for a specific use case – recommended.

// Query-level: JOIN FETCH
@Query("SELECT DISTINCT c FROM Company c LEFT JOIN FETCH c.jobs WHERE c.city = :city")
List<Company> companiesWithJobs(@Param("city") String city);

// Query-level: entity graph
@EntityGraph(attributePaths = {"company", "skills"})
Page<Job> findByOpenTrue(Pageable pageable);

select c.*, j.* from companies c left join jobs j on j.company_id = c.id where c.city = ?

Comparing strategies

• Lazy everywhere + fetch joins where needed: best control.
• EAGER on @ManyToOne (default): convenient but can trigger extra selects you did not expect.
• EAGER on collections: can load huge graphs and causes Cartesian products when combined.

Pitfalls

• JOIN FETCH on two collections at once multiplies rows (Cartesian product) – Hibernate throws MultipleBagFetchException for Lists; fetch one collection per query or use Set/batch fetching.
• JOIN FETCH with pagination on collections paginates in memory (HHH90003004 warning) – page the parents first, then fetch children by id.

Batch fetching alternative

spring.jpa.properties.hibernate.default_batch_fetch_size=50

Lazy collections for up to 50 parents are loaded with one IN query instead of 50 queries.

Note: decide fetching per screen or endpoint, not globally on the entity.

Solution and explanation

What it is

The N+1 problem happens when the application runs 1 query to load N parent rows, and then 1 extra query for each parent to load a lazy relation: N+1 queries in total. Each query is fast, but hundreds of round trips make the page slow.

List<Job> jobs = jobRepository.findTop50ByOrderByPostedAtDesc();   // 1 query
for (Job job : jobs) {
    System.out.println(job.getCompany().getName());                // +1 query per job (lazy company)
}

select * from jobs order by posted_at desc limit 50
select * from companies where id=?
select * from companies where id=?
... 50 times

How to detect it

• Enable SQL logging (spring.jpa.show-sql=true) or use a datasource proxy / p6spy.
• Hibernate statistics (generate_statistics=true) show query counts per request.
• APM tools (New Relic, Datadog) show many identical queries per request.
• Tests asserting the number of queries.

Fixes

1. JOIN FETCH:

@Query("SELECT j FROM Job j JOIN FETCH j.company ORDER BY j.postedAt DESC")
List<Job> latestWithCompany(Pageable pageable);

1. @EntityGraph:

@EntityGraph(attributePaths = "company")
List<Job> findTop50ByOrderByPostedAtDesc();

1. Batch fetching: hibernate.default_batch_fetch_size=50 turns N queries into about N/50 IN queries.
2. DTO projection – select only the columns you need in one query:

@Query("SELECT new com.portal.JobRow(j.id, j.title, c.name) FROM Job j JOIN j.company c")
List<JobRow> jobRows();

Result

50 jobs: 51 queries → 1 query (JOIN FETCH or projection) or 2 queries (batch fetching).

Note: the same problem exists in Laravel (Eloquent with()), Django (select_related) and any ORM – the fix is always "load relations in bulk".

Solution and explanation

What it is

Cascade makes an operation on a parent entity automatically apply to its related child entities. For example, saving a Resume also saves its Education and Experience entries; deleting it deletes them.

@Entity
public class Resume {
    @Id @GeneratedValue private Long id;

    @OneToMany(mappedBy = "resume", cascade = CascadeType.ALL, orphanRemoval = true)
    private List<Experience> experiences = new ArrayList<>();

    public void addExperience(Experience e) { experiences.add(e); e.setResume(this); }
    public void removeExperience(Experience e) { experiences.remove(e); e.setResume(null); }
}

@Entity
public class Experience {
    @Id @GeneratedValue private Long id;
    @ManyToOne(fetch = FetchType.LAZY) private Resume resume;
    private String company;
}

Resume r = new Resume();
r.addExperience(new Experience("Infosys"));
r.addExperience(new Experience("TCS"));
resumeRepository.save(r);      // inserts the resume and both experiences (PERSIST cascades)

Cascade types

• PERSIST – saving the parent saves new children.
• MERGE – merging the parent merges children.
• REMOVE – deleting the parent deletes children.
• REFRESH, DETACH – reload or detach children with the parent.
• ALL – all of the above.

orphanRemoval

orphanRemoval = true deletes a child that is removed from the collection (r.removeExperience(e) → DELETE). Cascade REMOVE only acts when the parent itself is deleted.

When to use cascade

• Use it for true composition: the child cannot exist without the parent (order → order lines, resume → experiences).
• Do not cascade to shared or independent entities: cascading REMOVE from Job to Company could delete a company used by other jobs.

Database-level alternative

ON DELETE CASCADE foreign keys delete children in the database itself; JPA cascades run in the application and fire entity callbacks.

Note: cascade = REMOVE on a large collection loads and deletes children one by one; for bulk deletes use a JPQL DELETE query.

Solution and explanation

What it is

@Transactional makes a method run inside a database transaction: all its database operations commit together, or all roll back if an exception occurs (atomicity). Spring implements it with a proxy that opens the transaction before the method and commits or rolls back after it.

Example: money transfer

@Service
public class TransferService {

    private final AccountRepository accounts;
    private final LedgerRepository ledger;

    public TransferService(AccountRepository accounts, LedgerRepository ledger) {
        this.accounts = accounts; this.ledger = ledger;
    }

    @Transactional
    public void transfer(Long fromId, Long toId, BigDecimal amount) {
        Account from = accounts.findByIdForUpdate(fromId);    // SELECT ... FOR UPDATE
        Account to = accounts.findByIdForUpdate(toId);
        if (from.getBalance().compareTo(amount) < 0) throw new InsufficientBalanceException(fromId);
        from.debit(amount);
        to.credit(amount);
        ledger.save(new LedgerEntry(fromId, toId, amount));
    }   // commit; any RuntimeException above → full rollback
}

How it works (proxy)

caller → Spring proxy → begin transaction → TransferService.transfer() → commit / rollback

Rollback rules

• Rolls back on unchecked exceptions (RuntimeException, Error) by default.
• Does not roll back on checked exceptions unless configured: @Transactional(rollbackFor = Exception.class).
• Catching an exception inside the method and not rethrowing it means the transaction commits.

Important attributes

• readOnly = true – optimisations for queries (no dirty checking flush).
• propagation – REQUIRED (default: join or create), REQUIRES_NEW (always a new, independent transaction, e.g. audit logs), MANDATORY, NESTED, etc.
• isolation – READ_COMMITTED, REPEATABLE_READ (MySQL default), SERIALIZABLE.
• timeout – seconds before rollback.

Common pitfalls

• Self-invocation: calling a @Transactional method from another method of the same class bypasses the proxy, so no transaction starts.
• Private methods are not proxied.
• Calling external APIs (payment gateway, email) inside a long transaction holds database locks; do remote calls outside, or use an outbox/event after commit.

@TransactionalEventListener(phase = TransactionPhase.AFTER_COMMIT)
public void onTransferCompleted(TransferCompleted e) { smsService.sendDebitAlert(e); }

Note: put @Transactional on service methods (business operations), not on controllers or repositories.