Queries: derived, JPQL, native and @Query

The repositories lesson introduced the three ways to query — derived methods, JPQL with @Query, and native SQL. This lesson goes deeper: when each fits, how JPQL differs from SQL, parameter binding, and returning the shape you actually want. Getting queries right is most of real data-access work, and knowing the full range means you never reach for raw SQL out of not knowing the ORM. All examples run against a working Dakiya.

The three approaches, and the order to reach for them

1. Derived query methods — for simple queries, name the method and Spring Data builds it.
2. JPQL via @Query — for anything more complex, write a query over your entities.
3. Native SQL via @Query(nativeQuery = true) — for database-specific features, as a last resort.

Reach for them in that order: a derived method if the name stays readable, JPQL when it does not, native SQL only when JPQL genuinely cannot express what you need. Most queries never leave step 1 or 2.

Derived methods: the readable limit

Derived methods (from the repositories lesson) cover a surprising range:

List<Parcel> findByStatus(Parcel.Status status);                        // verified: returned the booked ones
List<Parcel> findByDestinationCityAndStatus(String city, Status status);
List<Parcel> findByStatusOrderByTrackingCodeDesc(Status status);
List<Parcel> findByDestinationCityIn(Collection<String> cities);
long countByStatus(Status status);

The vocabulary (And, Or, Between, LessThan, Like, In, IsNull, OrderBy…) handles most simple filters and sorts. The limit is readability: when the method name grows to findByStatusAndDestinationCityAndRecipientNameOrderByCreatedAtDesc, it has stopped being readable and should become a @Query. Use the derived method while the name reads as a sentence; switch when it does not.

JPQL: querying entities, not tables

JPQL (Jakarta Persistence Query Language) looks like SQL but operates on your entity model — entity names and field names — not database tables and columns:

@Query("select p from Parcel p where p.status = :status and p.destinationCity = :city")
List<Parcel> search(@Param("status") Parcel.Status status, @Param("city") String city);

Note Parcel (the entity, capital P) and p.status/p.destinationCity (the fields), not the table/column names. Hibernate translates this to SQL against the actual table. JPQL understands your relationships, so you can navigate them:

@Query("select p from Parcel p where p.hub.city = :city")     // navigate the relationship
List<Parcel> findByHubCity(@Param("city") String city);

@Query("select p from Parcel p join fetch p.hub")             // fetch join (verified: 1 query, avoids N+1)
List<Parcel> findAllWithHub();

JPQL is the workhorse for non-trivial queries: it is portable across databases (Hibernate generates the right SQL dialect), type-checked against your entities at startup (a typo in a field name fails fast), and integrates with the persistence context (results are managed entities). Verified: the join fetch query ran as a single statement. Prefer JPQL over native SQL — you keep entity-model portability and the ORM's integration.

Parameters: always bound, never concatenated

Both named (:status) and positional (?1) parameters bind values separately from the query text:

@Query("select p from Parcel p where p.status = :status")
List<Parcel> byStatus(@Param("status") Parcel.Status status);

This is not a style choice — it is what makes queries injection-safe. The value is sent to the database as a bound parameter, never spliced into the query string, so a malicious value cannot become SQL. Prefer named parameters (:status with @Param) over positional (?1) for readability. As with Spring Data's derived methods, you get injection safety by default; you would have to build a native query by string concatenation to be unsafe — so do not.

Native SQL: the last resort

When JPQL cannot express something — a database-specific function, a complex analytical query, a feature the JPA model does not cover — drop to native SQL:

@Query(value = "SELECT * FROM parcel WHERE destination_city = :city AND status = 'BOOKED'",
       nativeQuery = true)
List<Parcel> searchNative(@Param("city") String city);

nativeQuery = true runs the SQL as written (note it uses table and column names now, and is tied to your database's dialect). It is still parameterised (:city) — keep it so. Use native SQL deliberately and sparingly: you lose database portability and the entity-model type-checking, and you take on writing correct SQL yourself. The honest rule (as in every ORM course): be sure JPQL cannot do it before dropping to native — most "I need raw SQL" moments are actually "I do not know the JPQL/Hibernate feature yet", including window functions and CTEs, which modern Hibernate supports.

Returning the shape you want

Queries can return more than whole entities:

• Whole entities (List<Parcel>) — managed, part of the persistence context; the default.
• A single value or count (long, Optional<Parcel>).
• Projections (only some columns) — the next lesson, for when you do not need the whole entity.
• A Page<Parcel> — add a Pageable parameter and Spring Data paginates the query.

Match the return type to the need: a whole entity when you will modify it (so dirty checking works), a projection or specific columns when you only read a few fields (lighter — the next lesson). The progression to internalise across this and the repositories lesson: derived method → JPQL → native, and return the narrowest shape the caller needs.

Check your work

The three approaches, in order. Derived method (simple, readable names) → JPQL @Query (complex) → native SQL (database-specific, last resort).

Derived limit. Rich vocabulary (And/Or/Between/In/OrderBy…); switch to @Query when the method name stops reading as a sentence.

JPQL. Queries the entity model (entity/field names, not tables/columns), navigates relationships (p.hub.city), supports fetch joins (verified: 1 query), is portable and type-checked at startup — prefer it over native SQL.

Parameters. Named (:x + @Param) or positional (?1), always bound (injection-safe by default); prefer named for readability.

Native SQL. nativeQuery = true for database-specific needs; loses portability and type-checking — use only when JPQL genuinely cannot, still parameterised.

Return shape. Whole entities (managed, for modifying), single values/counts, projections (read a few fields — next lesson), or Page; return the narrowest shape the caller needs.

Practice

1. Write three derived methods (findByStatus, findByDestinationCityAndStatus, countByStatus) and confirm each with show-sql; then take one past the readability line and rewrite it as @Query.
2. Write a JPQL @Query navigating the relationship (p.hub.city = :city) and confirm it generates the right join.
3. Write the fetch-join JPQL and confirm (statistics) it runs one query (reconnect to N+1).
4. Introduce a typo in a JPQL field name and confirm it fails at startup (type-checked) — a native query with the same typo would fail only at runtime.
5. Write a native query for something JPQL handles, then rewrite it in JPQL; articulate what you gained (portability, type-checking).
6. Add a Pageable parameter to a @Query and return a Page; confirm pagination works.

Official documentation

• Spring Data JPA — @Query — JPQL and native queries.
• Jakarta Persistence — JPQL — The query language reference.
• Hibernate — Query language — HQL/JPQL features, including window functions and CTEs.

Next: projections — fetching only what you need.