Loading...
Loading...
Loading...
Backend interview guide
Entities, relationships, repositories, queries and transactions. Every answer is short enough to say aloud, and every question links to the lesson that teaches it.
60 questionsLast updated
Spring Data derives the COUNT by rewriting the main query. For native SQL, GROUP BY or complex joins that rewrite can be invalid (I have seen count(p.*) fail on MySQL) or slower than needed. A hand-written countQuery keeps only the WHERE and the joins that change the row count.
Move the filter into the query and add a Pageable, so the database returns only one page. findAll() reads the whole table into memory on every request, which gets slower as the table grows and can run the JVM out of heap.
With Sort.by("price").descending().and(Sort.by("name")), or Sort.by(Sort.Order.desc("price"), Sort.Order.asc("name")). The second order only matters when the first values are equal. For a page, pass the sort to PageRequest.of(page, size, sort).
Sort.sort(Product.class).by(Product::getName) builds the sort from a getter, so typos fail at compile time and renames are safe. It records the call through a proxy, and in Spring Data 4.1.1 that fails for final return types such as BigDecimal, so for those I use Sort.by("price").
The SQL has no ORDER BY, so the database returns rows in whatever order is fastest. That order is not guaranteed and can change between pages or after an index change, so a paged list must always have a sort, ending with a unique column.
Lesson: Sorting by one or more fieldsIt caps the size a client can ask for; larger sizes are cut to the maximum (Boot's default is 2000). It protects the server, but silently: the client gets a 200 with a different size than it asked for. When the API contract wants a 400, I validate size myself.
An unknown field makes the query fail, which shows up as a 500 for what is really a client mistake. It can also let clients sort by columns they should not see or that have no index, which is slow on big tables. I keep an allow-list of API sort names mapped to entity properties and answer 400 for anything else.
Lesson: Exposing page and sort in REST endpointsA reusable WHERE condition written as (root, query, cb) -> predicate with the JPA Criteria API. A repository that extends JpaSpecificationExecutor can run any combination of them with findAll(spec, pageable) or count(spec).
A join to a collection returns one row per matching child, so a customer with two matching addresses is counted twice and can show up on two pages. I use an EXISTS subquery, or query.distinct(true), which makes both the SELECT and the COUNT distinct.
For simple AND searches on the entity's own fields, like an admin filter on name and email, with exact, starts-with or contains matching. It cannot express OR, ranges or conditions on collections, so I switch to Specifications for those.
Lesson: Optional filters with SpecificationsThe database must find and order all the skipped rows before returning the page, so the cost grows with the offset. On 5 lakh orders, offset 400,000 took about a second versus 1 ms for the first page, for the same 20 rows.
Lesson: Keyset pagination for large tablesYou cannot jump to page N and usually do not show a total. The sort must be fixed during scrolling and end with a unique column, and you need an index that matches the filter and the sort. Going backwards needs the reversed condition.
Lesson: Keyset pagination for large tablesReturn the page with a nextCursor: the last row's key encoded as an opaque string, for example Base64 of orderedAt|id. The client sends ?after=<cursor> for the next page and never sees or builds SQL; a missing cursor means the first page.
OFFSET is simple and lets users jump to any page, but the database reads and discards every skipped row, so deep pages get slow on big tables. Keyset continues from the last row's sort values with an index, so every page is fast, but there is no jumping to page N and the sort must end with a unique key.
Lesson: Practice: pagination, sorting and searchingOne Specification per filter in a small class, then start from Specification.unrestricted() and and only the filters that have a value. The repository extends JpaSpecificationExecutor, so the same search pages and sorts with findAll(spec, pageable).
For a public contract I prefer a 400 with a clear message, as ShopLite does, because a silent change hides client bugs. Spring's max-page-size clamps silently, which is acceptable as a safety net for internal tools but not as the API's rule.
@ManyToOne and @OneToOne are EAGER; @OneToMany and @ManyToMany are LAZY. The surprise is the to-one side: a plain @ManyToOne silently loads the parent row on every query, so I always write fetch = FetchType.LAZY.
It is a generated subclass of the entity that holds only the id, put into a LAZY to-one field. getId() returns the id without SQL; the first other getter runs one SELECT and fills it. After the session closes, that first getter throws LazyInitializationException instead.
equals for an entity that may be a proxy?Compare with o instanceof Category other and the id through other.getId(), never getClass(), because the proxy's class is a subclass. getId() on a proxy does not touch the database, and hashCode should be stable, for example a constant per class.
One query loads N rows, then code reads a lazy link on each row and Hibernate sends one more query per row (per distinct linked row). For an order list with customer names that is 1 + N statements instead of 1. It is invisible in the Java code and shows up in the SQL log.
Lesson: The N+1 query problemEach statement is a network round trip, plus parsing and planning in the database, and the request holds a pool connection the whole time. 2000 tiny queries cost far more than one join: in my lab 3260 ms against 75 ms for 2000 orders, and more over a real network.
Lesson: The N+1 query problemThe persistence context is a per-session cache keyed by id, so a customer already loaded is reused. N is the number of distinct customers, 7 here. With real data most orders have different customers, so N grows almost one-to-one with rows.
Lesson: The N+1 query problemLoad the link with the list: join fetch in JPQL or @EntityGraph on the repository method, which gives one statement. As a global safety net I set hibernate.default_batch_fetch_size so any missed lazy load becomes an IN query per batch. For read-only screens a DTO query is often best of all.
Not since Hibernate 6: duplicate root entities are removed from fetch-join results automatically. In Hibernate 5 select distinct was needed, so older code has it; it is harmless but no longer required.
LIMIT counts SQL rows, not parent entities. Hibernate 5 and 6 load everything and page in memory with warning HHH90003004; Hibernate 7.4 pushes the limit into a subquery but the count can still disagree. I page the ids first and then fetch those ids with their collection.
Lesson: Fixes: JOIN FETCH, @EntityGraph and batch fetchingProbably a collection fetch that multiplies rows, for example two collections or a big one-to-many, so far more data travels than the page shows. I would check the row count of the SQL, split into two queries, or switch to a DTO query with only the needed columns.
Lesson: Fixes: JOIN FETCH, @EntityGraph and batch fetchingEntities when the use case changes data or needs entity logic, like cancelling an order. DTOs for read-only lists, searches and reports. My rule is: if the method changes nothing, start with a DTO query.
Lesson: Returning DTOs instead of entities"Could not resolve class" when the class after new is not fully qualified, and "Missing constructor for type" when the selected values do not match a constructor in order and type. Spring Data validates @Query at startup, so both stop the app early.
One DTO query that joins orders and items, groups by order and computes sum(quantity * unitPrice) in SQL, paged by order. The database returns one small row per order with its total; no items are loaded into Java at all.
It lets lazy loading happen in the web layer, outside transactions, so N+1 queries hide in controllers and JSON serialisation. And it holds the database connection until the response is written, so slow work after the query can exhaust the connection pool under load.
Lesson: Open Session in View and why to turn it offWith OSIV the connection stays with the request until the end. If the request then calls a slow API or writes a big response, the connection is idle but not free. In my lab, a pool of 2 with four requests that waited 3 seconds after their query failed two of them with Hikari's "Connection is not available"; with OSIV off and a DTO, all four passed.
Lesson: Open Session in View and why to turn it offIn the shared application.properties or application.yml, so every profile has spring.jpa.open-in-view=false. If dev had it on and prod off, the LazyInitializationExceptions would appear first in production.
In dev I wrap the DataSource with datasource-proxy and a filter that logs one line per request with the statement count. It sees statements from Hibernate, JdbcClient and plain JDBC, and I switch it on with a property only where I need it.
Lesson: Counting queries in tests and logsCopy each statement from the Hibernate SQL log and run EXPLAIN on it in MySQL, or catch it in the slow query log. I look for type ALL, a large rows estimate and Using filesort, and add an index on the filter columns followed by the sort column, then check the plan again.
With a tiny threshold it logs almost every statement, which costs disk and I/O on the busiest server. In production I keep a sensible threshold, such as one second, and use low thresholds on test or staging servers while profiling.
Lesson: Counting queries in tests and logsOne query for N rows plus one per row for a lazy link. Four ways: join fetch, @EntityGraph, batch fetching (default_batch_fetch_size or @BatchSize), and a DTO query that selects the needed columns with a join.
Lazy for every association, because EAGER cannot be turned off per query and adds SELECTs to every list. Each use case decides what to fetch in its own query.
Lesson: Practice: JPA performance trapsFor read-only screens and reports: fewer columns, no managed entities, no snapshots, and aggregates done by the database. When the use case changes data, I load entities with an entity graph or fetch join instead.
Lesson: Practice: JPA performance trapsddl-auto=update in production?It only adds and alters: it cannot rename or drop, and it never moves data, so a renamed field leaves the data in an unused column. Failed changes are only logged as warnings, so databases drift apart. And nobody reviews the SQL it sends to the live database.
Lesson: Why ddl-auto=update is unsafevalidate and none?Both leave the schema alone. validate additionally checks every mapped table and column at startup and stops the app on a mismatch, so a missing migration is caught immediately. I prefer validate because it fails fast.
A versioned SQL script, such as V2__rename_phone_to_mobile.sql, that moves the schema one step. A tool runs each script once per database, in order, and records it. The scripts live in Git, so changes are reviewed and every environment ends up the same.
Flyway is a migration tool that runs versioned SQL scripts in order. It keeps a flyway_schema_history table in each database with every applied script, its checksum and whether it succeeded, and at startup it runs only the scripts that are not in that table.
V<version>__<description>.sql: a capital V, the version, two underscores and a description. V1_1__x.sql is version 1.1. A file that does not match is skipped silently by default, which is an easy mistake to miss.
baseline-on-migrate=true in application.yml forever?On every non-empty database without a history table it would silently baseline. If someone points the app at the wrong database, Flyway would mark it as migrated instead of stopping. I use it once, on purpose, from the command line.
Lesson: Adding Flyway and writing V1flyway repair the right tool?In two cases: after a failed migration, once I have undone its half-applied changes, to delete the failed history row; and after a harmless edit, such as a comment, to store the new checksum. It never changes tables, so it is not a way to undo a migration.
Lesson: Changing a live schema with new migrationsThe first two stay applied, because MySQL commits DDL immediately, and Flyway records the migration as failed. The app will not start until I undo those changes by hand, run repair, fix the script and run it again. That is why I keep one DDL statement per migration where possible.
Flyway refuses to start with "Found more than one migration with version 7". Before deploying, the second developer renames theirs to the next free number. If one V7 is already deployed somewhere, that file must not change, and the other one moves up.
Lesson: Changing a live schema with new migrationsA versioned file (V3__x.sql) runs exactly once and must never change afterwards. A repeatable file (R__x.sql) has no version, runs after all versioned ones and runs again whenever its checksum changes. I use repeatables for views and procedures that I always rewrite completely.
It lives only in db/dev-data, and only application-dev.yml and the test settings list that folder; the shared and prod settings keep classpath:db/migration. A test or a quick mvn flyway:info against production would also show no version 1.1.
They run code around Flyway commands. For example afterMigrate__refresh_statistics.sql runs ANALYZE TABLE after every migrate, or a Java Callback bean logs row counts in dev. They are not recorded in the history, so they must be safe to repeat.
Hibernate compares every entity with the real tables at startup and stops the app on a missing table, missing column or wrong type, without changing anything. Flyway owns the schema, validate makes sure the code agrees with it. Mismatches show up at startup, not on the first request in production.
Lesson: Validating entities against the schemaThe entity uses Long for an INT column, usually an id. The fix is in Java: Integer for INT. Changing the column to BIGINT would also work but is a migration on every table and foreign key, so I only do it on purpose, for example when ids may pass two billion.
No. It checks the tables, columns and types that entities map. It ignores extra columns, indexes, CHECK constraints, defaults and the exact nullability in many cases. That is why the migrations themselves are reviewed and the fresh-database test also checks things like the order history index.
Lesson: Validating entities against the schemaupdate guesses changes from the entities: it never drops or renames, cannot move data and gives different schemas on databases with different histories. With Flyway every change is a reviewed SQL file with a version, applied the same way everywhere, and Hibernate only validates.
Lesson: Practice: Flyway migrationsIt keeps a flyway_schema_history table in the database with one row per applied migration: version, description, script name, checksum, who ran it, when, and whether it succeeded. At startup it compares that table with the files and runs only the new ones.
Expand and contract. First add the new column and copy the data, and release code that reads the new one and writes both. When no old version runs any more, copy leftovers, drop the old column and remove it from the entity. Each step keeps both running versions working.
Lesson: Practice: Flyway migrationsAfter the merge Flyway refuses to start with "Found more than one migration with version 12". The one not yet deployed is renamed to the next number. Teams avoid it by pulling main before adding a migration, and some use timestamps as versions, like V20261016_1130__x.sql.
@Transactional service method changes an entity.The proxy starts a transaction and an EntityManager. Loaded entities are managed and Hibernate keeps a snapshot of each. At commit it flushes: it compares each entity with its snapshot, sends one UPDATE per changed entity, then commits the JDBC transaction. An unchecked exception rolls it all back instead.
Optimistic locking (@Version) suits rare conflicts such as two admins editing the same product: the loser gets an error and retries. For stock, where many buyers hit the same row at once, I lock the rows with PESSIMISTIC_WRITE in a fixed order or use an atomic UPDATE.
With Flyway: every change is a new versioned SQL file, applied in order at startup and recorded in flyway_schema_history. Hibernate only validates (ddl-auto=validate). I never edit a migration that has run; sample data lives in a dev-only location.
Each lesson has quizzes, practice and more interview questions on the same topic.
Open the first lesson