After the previous lesson, CicloUrbana knows how to save, look up by identifier, count and paginate. It knows how to do the basics. But the Ribalta network needs a great deal more: the stations with free space, the bikes with battery below the threshold, a user's rentals between two dates, the stations nearest to a coordinate, the occupancy report in a single query. All of those are questions, and Spring Data offers five different ways to ask them.

This is the most practical lesson in the module and the one that changes day-to-day work the most. Here is the complete arsenal: queries derived from the method name —the mechanism that surprises newcomers to Spring Data the most—, @Query with JPQL, native PostgreSQL queries, bulk modifications with @Modifying, projections that avoid loading whole entities, @EntityGraph to settle the N+1 of 04-04 and Specifications for dynamic filters. And, cutting across all of it, the judgement to choose: every mechanism has a point where it stops being the right tool.

1. Queries derived from the method name
2. An exhaustive keyword table
3. Nested properties and ambiguities
4. When to abandon the method name
5. @Query with JPQL
6. DTO projections in the query
7. JOIN FETCH and solving N+1
8. Native queries
9. @Modifying: UPDATE and DELETE
10. Projections by interface and by record
11. @EntityGraph
12. Specification and the Criteria API
13. @NamedQuery, Streamable and Stream
14. Verifying the SQL actually executed
15. Common Mistakes and Tips
16. Exercises

The mechanism is simple to state and surprising the first time: you declare a method whose name describes the query and Spring Data generates it.

None of them has an implementation and all five work. At startup, PartTreeJpaQuery parses each name and builds the query.

How the name is parsed. It splits into two parts: the subject (findByStatus... → find) says what is returned —find, read, get, query, search are equivalent; plus count, exists, delete, with Distinct and limiters such as Top10 or First—, and the predicate, which starts at By and describes the filter with property names, operators and connectors.

The parsing is strict. findByBatteryLevel works because Bike has a batteryLevel field; findByBattery fails at startup:

That is a great advantage: the error shows up at startup, not on the first request. Renaming an entity field breaks startup and forces you to fix the methods that used it: the early validation that SQL strings never give you.

CicloUrbana's repositories with genuinely useful queries:

Notice findByUserIdAndEndedAtIsNull: this is the query that answers "does this user have a rental in progress?", CicloUrbana's central business rule, and it fits in a method name without writing SQL or JPQL. A practical detail: the order of the parameters must match the order in which they appear in the name; swapping them does not compile if the types differ, but with two parameters of the same type the error moves to run time and silently produces wrong results. A good reason not to chain too many conditions.

You can navigate to properties of related entities:

Spring Data generates a JOIN automatically:

The ambiguity problem. The parser resolves findByStationName greedily: first it looks for a stationName property on Bike and, failing to find it, splits at the last capital letter and looks for station.name. If both existed the first would win, and the query would be a different one from the one you intended with no error at all. To disambiguate, an underscore is used: findByStation_Name(String name) is unequivocal. It is ugly and it breaks Java naming conventions, but it is explicit.

Beware of deep navigation: findByBikeStationLocationLatitude(...) generates three chained JOINs in an unreadable name. When you get there, move to @Query.

Derived queries are excellent up to a point, and that point is recognisable without ambiguity:

The alternatives, in order of preference: @Query with JPQL for complex but portable queries; a native query when PostgreSQL-specific SQL is needed; Specification for dynamic, combinable filters; and a repository of your own (Impl) for logic that does not fit into a single query.

JPQL (Jakarta Persistence Query Language) is SQL over the object model: it queries entities and their properties, not tables and columns.

Notice Station capitalised and s.capacity: they are the Java class name and its field name, not stations and not the column. That is what makes JPQL portable across engines and verifiable against the model. And Java text blocks (""") are the right way to write multi-line queries, with no concatenation and no lost spaces.

Positional versus named parameters:

Always use named parameters with @Param. Positional ones depend on argument order and give no hint at all when read. And a warning inherited from the JDBC world: never concatenate values into the query text, because that is SQL injection. JPA parameters travel in prepared statements, with the values separated from the text: that is their protection.

Paginated queries with @Query and their countQuery:

Spring Data can derive the countQuery automatically, but with complex queries —especially with JOIN FETCH or DISTINCT— the derivation either fails or generates a count far more expensive than necessary. Declaring it is the safe option.

One of the highest-impact techniques in the whole lesson: fetch only the columns you need and build the DTO directly, without going through managed entities.

Requirements of select new: a fully qualified class name and a constructor whose types match exactly those of the expressions. A record satisfies this naturally. Compare with the alternative of loading entities:

It is the solution we chose in exercise 2 of 04-04, now written out in full. The rule: if the endpoint is not going to modify anything, ask yourself whether it needs entities at all.

When you do need entities with their relationships loaded, JOIN FETCH brings them in a single query.

join fetch versus a plain join. A normal join serves to filter: you can place conditions on the joined entity, but it is not loaded, so accessing it afterwards fires lazy queries. join fetch filters and loads. The distinction is subtle and critical.

Why distinct. With a JOIN to a collection, the database returns one row per bike, that is, the station repeated N times; without distinct, the list would contain duplicates. In Hibernate 6 it is applied in memory without being added to the SQL, so it costs nothing. You can chain several levels:

Four entities in a single query, instead of 1 + 3N.

The two limits of JOIN FETCH. The first: you cannot paginate with collections; we already saw this in 04-04, Hibernate warns with HHH90003004 and paginates in memory, so with collections and pagination you have to use @BatchSize or two queries (one for paginated ids and another with where id in). The second: only one collection fetch per query, because doing join fetch on two different collections generates a cartesian product —30 bikes × 20 incidents = 600 rows for one station—; Hibernate 6 allows it, but it is almost always a mistake.

When JPQL cannot reach, nativeQuery = true runs SQL straight from the engine.

That expression is the haversine formula, which computes the distance over the Earth's surface between two coordinates. JPQL has no trigonometric functions, so there is no portable alternative. The projection that collects the result (section 10):

When a native query is justified: engine-specific functions (geospatial, JSONB, full text); window functions (ROW_NUMBER, LAG, RANK), which JPQL does not support; CTEs (WITH ... AS) and recursive queries; specific optimisations such as PostgreSQL's ON CONFLICT; and bulk operations where performance rules.

Its risks, to be taken on knowingly:

The second risk is the most treacherous: a native query references battery_level, somebody renames the field in the entity and updates the schema, and the query compiles, starts up and fails in production. CicloUrbana's rule: JPQL by default, native only when JPQL cannot; and when you do use native, cover it with an integration test.

By default, @Query assumes a read query. To modify, @Modifying is required:

It returns the number of rows affected. Three things are mandatory or nearly so:

@Transactional is indispensable. Without it, TransactionRequiredException: Executing an update/delete query. Normally the transaction comes from the service (04-07).

flushAutomatically = true pushes the context's pending changes out before running the query; without it, a bike modified in memory and not yet flushed would fail to match the WHERE it ought to match.

clearAutomatically = true is the most important and the least understood. A modifying query runs straight against the database, bypassing the persistence context, so the entities already loaded are left with stale values:

Worse still: if bike is then modified and committed, dirty checking would write the old status, undoing the bulk update. clearAutomatically = true empties the context after the query, forcing a re-read.

When to use @Modifying and when not to. It is the right tool for bulk operations —marking a hundred bikes, closing the night's abandoned rentals— and it is not the tool for modifying one specific entity: there, loading it and changing it is enough, letting dirty checking do its work. A final warning: a modifying query bypasses cascades, @EntityListeners and optimistic locking, so updated_at is not refreshed and version is not incremented; if that matters, do it explicitly in the query itself.

A projection returns a subset of the data instead of the complete entity. There are three ways.

Closed projection by interface. You declare an interface with getX() methods matching properties:

Spring Data generates a proxy and, most importantly, restricts the SELECT to the necessary columns: select s1_0.id, s1_0.name, s1_0.capacity from stations s1_0 where s1_0.active = true. With a thirty-column table, the difference in data transferred is enormous.

Open projection with @Value and SpEL. It allows computation:

It has a cost worth knowing about: with @Value, Spring Data loads the complete entity in order to evaluate the expression, losing the SELECT optimisation. Use it only when you need the computation.

Projection to a record. The cleanest option with modern Java: just declare public record StationSummary(Long id, String name, Integer capacity) { } and use it as the return type. Spring Data recognises the record and uses its canonical constructor; the component names must match the property names.

Dynamic projections. The same method can return different shapes depending on what you ask for:

@EntityGraph declares, per method, which associations to load, without writing JPQL:

It generates the same LEFT JOIN FETCHes you would write by hand and works over derived queries as well as over @Query.

And with @NamedEntityGraph(name = "Station.withBikes", attributeNodes = @NamedAttributeNode("bikes")) on the entity, the graph is defined once and referenced by name from any method with @EntityGraph("Station.withBikes").

Remember that the pagination limit with collections (section 7) applies just the same: @EntityGraph over a collection with Pageable also paginates in memory.

The case no previous technique solves well: the station search with combinable optional filters. The council wants to filter by name, minimum capacity, active status and bike availability, in any combination; with derived queries that would take 16 methods and with @Query an unreadable where full of (:param is null or ...).

First, the repository also extends JpaSpecificationExecutor<Station>. Then, a class holding the predicates:

And the service combines them, with Specification.where(...) and and:

The key is returning null. Spring Data discards null predicates, so an absent filter never appears in the WHERE: no SQL concatenation and no nested ifs, and any combination of filters works without writing extra code. Specification is also compatible with Pageable and Sort.

Its trade-off is the verbosity of the Criteria API —cb.greaterThanOrEqualTo(root.get("capacity"), minimum) reads worse than capacity >= :minimum— and the fact that root.get("capacity") is a string no compiler verifies. The mitigation is JPA's static metamodel, which generates Station_ classes with typed constants: root.get(Station_.capacity).

Named queries. They are declared on the entity with @NamedQuery(name = "Station.findWithoutBikes", query = "select s from Station s where s.bikes is empty") and invoked by declaring a findWithoutBikes() method in the repository, which Spring Data resolves by the name Station.<method>. Their historical advantage was validation at startup, but @Query is validated today too, and their drawback is that they move the query away from the repository that uses it and clutter the entity. In CicloUrbana we do not use them.

Streamable<T> is an Iterable enriched with map, filter and and, useful for composing results without resorting to Stream. And Stream<T> processes large results without loading them all into memory, with one strict rule:

Three mandatory conditions: try-with-resources, because the Stream keeps a JDBC cursor open; an active transaction for the whole consumption; and emptying the context periodically with entityManager.clear() if you traverse hundreds of thousands of rows, or all of them stay managed and memory runs out. Without the close, the connection never returns to the pool: the leak from 04-02.

With so many mechanisms, the only way to know what is going on is to look. The configuration is the one from 04-02:

And the three questions to ask of every new endpoint: how many queries does it run? —it must be a constant number, not proportional to the results (04-04)—; does it fetch columns it does not use? —if the DTO has 4 fields and the SELECT brings 20, a projection is missing—; and are they the ones you expected?, because a query you do not recognise is usually a lazy load fired by accident.

For hard cases, PostgreSQL's EXPLAIN ANALYZE shows the real execution plan: if a Seq Scan over a large table appears, an index is missing. That analysis belongs to 09-01.

Mile-long method names. Once you go past three or four conditions, move to @Query. Readability matters more than brevity of code.

Using positional parameters (?1, ?2). Reordering the arguments breaks the query without the compiler saying a word. Use @Param.

Forgetting clearAutomatically in @Modifying. The context is left out of sync and dirty checking can undo the bulk update.

Doing JOIN FETCH on two collections. A cartesian product. One collection per query.

Paginating with a collection JOIN FETCH. Hibernate warns with HHH90003004 and loads everything into memory. Use @BatchSize or two queries.

Overusing native queries. They break portability and are not validated at startup. Only when JPQL cannot.

Consuming a Stream without try-with-resources. It leaves a cursor and a connection open: a pool leak.

Tip: project whenever you are not going to modify. If the endpoint only reads, a projection record or a select new avoids loading entities, reduces memory and removes the risk of LazyInitializationException.

Tip: declare the countQuery on complex paginated queries. Automatic derivation fails with JOIN FETCH and DISTINCT.

Tip: use Specification for search screens. Any combination of optional filters without a single concatenating if.

Tip: look at each new endpoint's SQL log before calling it done. It costs a minute and finds 90% of the problems in this lesson.

For each CicloUrbana need, choose the mechanism (derived query, @Query JPQL, native, projection, Specification) and write the method signature.

1. Bikes at a station with status AVAILABLE.
2. The 10 stations nearest to a coordinate, with the distance in kilometres.
3. A search with four combinable optional filters and pagination.
4. A count of rentals per origin station for the last month, for a report.
5. Every bike with battery below the threshold, moved to MAINTENANCE in a single operation.

Write the query that produces CicloUrbana's monthly report: for each user with at least one finished rental in the month, their name, email, number of rentals, total minutes and total amount billed, ordered by amount descending and paginated. Define the DTO and the repository method.

This repository has four problems. Identify them and fix them.

Solution 1.

1. A derived query. Two simple conditions over direct properties: List<Bike> findByStationIdAndStatus(Long stationId, BikeStatus status);

2. A native query with an interface projection. JPQL has no trigonometric functions, so the haversine formula is only expressible in SQL: List<NearbyStationProjection> findNearest(...) with nativeQuery = true.

3. Specification. Four optional filters are 16 combinations, and no other technique covers it without duplicating code: the Page<Station> findAll(Specification<Station>, Pageable) inherited from JpaSpecificationExecutor is enough.

4. @Query JPQL with select new. It is an aggregation with group by, impossible in a derived query, and it needs no entities:

5. @Modifying. A bulk operation over many rows: loading them all to modify them would be absurd:

Solution 2.

Decisions and why:

• select new with a record: the result is directly the response DTO, without loading a single User or Rental as an entity. With thousands of rentals, the difference in memory is orders of magnitude.
• A declared countQuery: with group by, automatic derivation would count grouped rows and give an incorrect number of pages. count(distinct r.user.id) is the real one.
• join r.user u instead of navigating r.user.name repeatedly: a single explicit JOIN, more readable and with a single alias in the group by.
• >= :from and < :to instead of between, which is inclusive at both ends and would duplicate the last instant between two consecutive months: the half-open interval is the correct one for date ranges.
• function('extract', ...) invokes an engine function from JPQL, with the dependency on PostgreSQL that entails; the portable alternative would be to store the duration in minutes as a column when the rental finishes, which would also avoid the calculation on every report.

Solution 3. The four problems:

1. Page with JOIN FETCH and no countQuery. Although here they are @ManyToOne associations and not collections —which avoids the HHH90003004 warning—, Spring Data will try to derive the count from a query with fetch and will either fail or generate a count with unnecessary JOINs. It has to be declared.

2. Positional parameters. ?1 and ?2 are fragile: swapping from and to in the signature raises no compile error and produces a query that never returns anything. Use @Param.

3. The native query returns entities with SELECT *. It works while the columns match, breaks silently as soon as one is added or renamed, and is not validated at startup. Besides, it does not need to be native: totalAmount > :minimum is pure JPQL.

4. @Modifying without clearAutomatically or flushAutomatically, and with an enum literal. The context is left with stale rentals, and 'EXPIRED' in quotes is a string literal that Hibernate may not convert to the enum type. @Transactional is missing as well. Corrected version:

CicloUrbana now knows how to ask questions. You have mastered queries derived from the method name, understanding how the name is parsed into subject and predicate, the complete keyword table and —most valuable of all— that the error shows up at startup with a PropertyReferenceException that even suggests the right name. You know how to navigate nested properties, disambiguate with _ when necessary and recognise the signals that the method name has run out of road: more than three conditions, a mix of And and Or, aggregations, subqueries or optional filters. You write JPQL with @Query over entities and properties instead of tables and columns, with named parameters and text blocks, declaring the countQuery when the paginated query is complex. And you project to a DTO with select new, the highest-impact technique in the lesson: a single query, four columns, no managed entities and the response DTO built directly.

You solve the N+1 of 04-04 with JOIN FETCH, knowing its two limits —no paginating collections, one collection per query— and its declarative alternative @EntityGraph, with or without @NamedEntityGraph. You resort to native queries only when JPQL cannot reach, as with the haversine formula for the nearest stations, taking on its portability and late-validation risks knowingly. You handle @Modifying for bulk operations knowing why clearAutomatically and flushAutomatically are not optional: a modifying query bypasses the persistence context, auditing, cascades and optimistic locking. You choose among projections by closed interface, open with SpEL, by record and dynamic with generics, knowing which one optimises the SELECT and which does not. And you build Ribalta's station search with Specification, where returning null for an absent filter lets any combination work without a single if.

One piece remains that has appeared in every example without being fully explained: @Transactional. We have put it on the services, we have needed it in @Modifying, we have said that the persistence context lives as long as the transaction, that dirty checking saves without calling save() and that readOnly = true optimises something. All of those are claims we have not yet justified. Lesson 04-07, Transactions and Persistence Management, justifies them: what a transaction is and what the ACID properties mean when starting a rental requires marking the bike and creating the record together or not at all; where to put @Transactional and why; how it works underneath and the two traps that sometimes make it not work at all; the seven values of propagation and the four of isolation; the rollback rule and the classic mistake of catching the exception and losing it; pessimistic locking versus the optimistic @Version in the race for a station's last bike; and the transactional events that let the rental be charged after the commit without holding a connection from the pool.