Week 5 Day 2 — Repository Query Methods

Goal

Today I want to understand how to query data with Spring Data JPA repositories.

Main questions:

1. What are repository query methods?
2. What are derived queries?
3. How does Spring Data create queries from method names?
4. What is @Query?
5. What is JPQL?
6. What is native SQL?
7. What is the difference between JPQL and SQL?
8. How do I use query parameters?
9. How do I use Optional, List, Page, and Slice?
10. How do I use pagination?
11. How do I use sorting?
12. What are common exam traps?

---

1. Quick Review from Week 5 Day 1

In Day 1, I learned:

• JPA is the Java persistence specification.
• Hibernate is a JPA implementation.
• Spring Data JPA is a repository abstraction on top of JPA.
• An entity is a Java class mapped to a database table.
• A repository hides database access behind an interface.
• JpaRepository<Entity, Id> gives common CRUD methods.
• Spring Data creates repository proxy implementations automatically.
• EntityManager is the lower-level JPA API underneath repositories.

Memory sentence:

JPA defines.
Hibernate implements.
Spring Data JPA simplifies.

Today I learn how to write custom repository queries.

---

2. Why Repository Query Methods Matter

Basic JpaRepository gives methods like:

save()
findById()
findAll()
deleteById()
existsById()
count()

But real applications need more specific queries:

find user by email
find tasks by status
find clients by firm id
find invoices created after a date
find tasks assigned to a user
search by keyword
paginate results
sort by creation date
check if email already exists

Spring Data JPA supports these with:

derived query methods
@Query with JPQL
@Query with native SQL
pagination
sorting
projections

Memory sentence:

Repository query methods let me express database queries through repository interfaces.

---

3. Example Entity

We use this entity for examples:

@Entity
@Table(name = "tasks")
public class TaskEntity {

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

    @Column(nullable = false)
    private Long clientId;

    @Column(nullable = false)
    private String title;

    @Column(nullable = false)
    private String status;

    @Column(nullable = false)
    private String priority;

    @Column(nullable = false)
    private LocalDate dueDate;

    @Column(nullable = false)
    private Instant createdAt;

    protected TaskEntity() {
    }

    public Long getId() {
        return id;
    }

    public Long getClientId() {
        return clientId;
    }

    public String getTitle() {
        return title;
    }

    public String getStatus() {
        return status;
    }

    public String getPriority() {
        return priority;
    }

    public LocalDate getDueDate() {
        return dueDate;
    }

    public Instant getCreatedAt() {
        return createdAt;
    }
}

Repository:

public interface TaskRepository extends JpaRepository<TaskEntity, Long> {
}

---

4. What Is a Derived Query?

A derived query is a query created from a repository method name.

Example:

List<TaskEntity> findByStatus(String status);

Spring Data reads the method name:

findByStatus

and creates a query like:

select * from tasks where status = ?

Simple definition:

A derived query is a query that Spring Data derives from the repository method name.

Memory sentence:

In derived queries, the method name describes the query.

---

5. Basic Derived Query Examples

Repository:

public interface TaskRepository extends JpaRepository<TaskEntity, Long> {

    List<TaskEntity> findByStatus(String status);

    List<TaskEntity> findByPriority(String priority);

    Optional<TaskEntity> findByTitle(String title);

    boolean existsByTitle(String title);

    long countByStatus(String status);
}

Meaning:

findByStatus -> find tasks where status equals given value
findByPriority -> find tasks where priority equals given value
findByTitle -> find one task by title
existsByTitle -> check if a task with title exists
countByStatus -> count tasks with status

---

6. Method Prefixes

Common prefixes:

findBy
readBy
getBy
queryBy
searchBy
streamBy
existsBy
countBy
deleteBy
removeBy

Most common:

findByStatus(String status)
existsByEmail(String email)
countByStatus(String status)
deleteByStatus(String status)

For learning and interviews, focus on:

findBy
existsBy
countBy
deleteBy

---

7. Combining Conditions with And

Example:

List<TaskEntity> findByClientIdAndStatus(Long clientId, String status);

Meaning:

where client_id = ? and status = ?

Usage:

taskRepository.findByClientIdAndStatus(10L, "OPEN");

Spring maps:

clientId -> first parameter
status -> second parameter

Memory sentence:

And combines conditions.

---

8. Combining Conditions with Or

Example:

List<TaskEntity> findByStatusOrPriority(String status, String priority);

Meaning:

where status = ? or priority = ?

Be careful.

Or queries can become harder to read, especially with many conditions.

If the method name becomes too complex, use @Query.

---

9. Common Keywords

Spring Data method names can include keywords.

Examples:

List<TaskEntity> findByStatusNot(String status);

List<TaskEntity> findByDueDateBefore(LocalDate date);

List<TaskEntity> findByDueDateAfter(LocalDate date);

List<TaskEntity> findByDueDateBetween(LocalDate start, LocalDate end);

List<TaskEntity> findByTitleContaining(String keyword);

List<TaskEntity> findByTitleContainingIgnoreCase(String keyword);

List<TaskEntity> findByStatusIn(List<String> statuses);

List<TaskEntity> findByClientIdIsNull();

List<TaskEntity> findByClientIdIsNotNull();

Meaning:

Not -> not equal
Before -> less than date/time
After -> greater than date/time
Between -> between two values
Containing -> like %value%
IgnoreCase -> case-insensitive
In -> value in collection
IsNull -> null value
IsNotNull -> not null value

---

10. Containing, StartingWith, EndingWith

Examples:

List<TaskEntity> findByTitleContaining(String keyword);

List<TaskEntity> findByTitleStartingWith(String prefix);

List<TaskEntity> findByTitleEndingWith(String suffix);

Meaning:

Containing -> title contains keyword
StartingWith -> title starts with prefix
EndingWith -> title ends with suffix

For case-insensitive search:

List<TaskEntity> findByTitleContainingIgnoreCase(String keyword);

Memory sentence:

ContainingIgnoreCase is common for simple search fields.

---

11. Ordering in Method Names

Example:

List<TaskEntity> findByStatusOrderByCreatedAtDesc(String status);

Meaning:

where status = ?
order by created_at desc

Another example:

List<TaskEntity> findByClientIdOrderByDueDateAsc(Long clientId);

Meaning:

Find tasks for one client, ordered by due date ascending.

Memory sentence:

OrderBy...Asc/Desc adds static sorting to a derived query.

---

12. Limiting Results

Examples:

Optional<TaskEntity> findFirstByStatusOrderByCreatedAtDesc(String status);

List<TaskEntity> findTop5ByStatusOrderByCreatedAtDesc(String status);

Meaning:

findFirstBy -> first matching result
findTop5By -> first 5 matching results

Useful for:

latest item
top results
recent records

---

13. Return Types

Common return types:

Optional<TaskEntity>
TaskEntity
List<TaskEntity>
Page<TaskEntity>
Slice<TaskEntity>
boolean
long
void

Examples:

Optional<TaskEntity> findByTitle(String title);

List<TaskEntity> findByStatus(String status);

Page<TaskEntity> findByClientId(Long clientId, Pageable pageable);

boolean existsByTitle(String title);

long countByStatus(String status);

Memory sentence:

Choose return type based on how many results are expected.

---

14. Optional vs Entity Return Type

Better:

Optional<TaskEntity> findByTitle(String title);

Risky:

TaskEntity findByTitle(String title);

Why?

The result may not exist.
Optional makes absence explicit.

Use Optional when zero or one result is expected.

Use List when many results are expected.

---

15. Derived Query Name Can Become Too Long

Bad:

List<TaskEntity> findByClientIdAndStatusAndPriorityAndDueDateBeforeAndTitleContainingIgnoreCaseOrderByCreatedAtDesc(
        Long clientId,
        String status,
        String priority,
        LocalDate dueDate,
        String keyword
);

Problem:

hard to read
hard to maintain
easy to make mistakes
method name is too long

Better:

Use @Query
Use Specification
Use Querydsl
Use criteria API
Use custom repository

For certification, know:

Derived queries are good for simple queries. Use @Query or other techniques for complex queries.

---

16. @Query

@Query lets me define the query manually.

Example JPQL:

@Query("select t from TaskEntity t where t.status = :status")
List<TaskEntity> findTasksByStatus(@Param("status") String status);

Meaning:

Use this JPQL query instead of deriving the query from the method name.

Memory sentence:

Use @Query when the method name would become too complex or unclear.

---

17. What Is JPQL?

JPQL means:

Java Persistence Query Language

Simple definition:

JPQL is a query language that works with JPA entities and their fields, not directly with database tables and columns.

Example JPQL:

@Query("select t from TaskEntity t where t.status = :status")
List<TaskEntity> findByStatusWithQuery(@Param("status") String status);

Important:

TaskEntity is the entity class name.
status is the entity field name.

Not the table and column names.

Memory sentence:

JPQL talks about entities. SQL talks about tables.

---

18. JPQL vs SQL

Entity:

@Entity
@Table(name = "tasks")
public class TaskEntity {

    private String status;
}

JPQL:

select t from TaskEntity t where t.status = :status

SQL:

select * from tasks t where t.status = ?

Difference:

JPQL	SQL
uses entity names	uses table names
uses field names	uses column names
database-independent	database-specific possible
handled by JPA provider	executed directly by database

---

19. JPQL Query with Multiple Conditions

@Query("""
       select t
       from TaskEntity t
       where t.clientId = :clientId
         and t.status = :status
       order by t.createdAt desc
       """)
List<TaskEntity> findClientTasksByStatus(
        @Param("clientId") Long clientId,
        @Param("status") String status
);

This is easier to read than a very long method name.

---

20. Named Parameters

Example:

@Query("select t from TaskEntity t where t.status = :status")
List<TaskEntity> findTasks(@Param("status") String status);

:status is a named parameter.

@Param("status") binds the Java method parameter to the query parameter.

Memory sentence:

@Param connects method parameters to named query parameters.

---

21. Positional Parameters

Example:

@Query("select t from TaskEntity t where t.status = ?1 and t.priority = ?2")
List<TaskEntity> findTasks(String status, String priority);

Meaning:

?1 = first method parameter
?2 = second method parameter

This works, but named parameters are usually clearer.

Better:

@Query("""
       select t
       from TaskEntity t
       where t.status = :status
         and t.priority = :priority
       """)
List<TaskEntity> findTasks(
        @Param("status") String status,
        @Param("priority") String priority
);

Memory sentence:

Prefer named parameters for readability.

---

22. Query Returning DTO

JPQL can create DTOs directly with constructor expressions.

DTO:

public record TaskSummaryDto(
        Long id,
        String title
) {
}

Query:

@Query("""
       select new de.klarsync.tasks.TaskSummaryDto(t.id, t.title)
       from TaskEntity t
       where t.status = :status
       """)
List<TaskSummaryDto> findTaskSummariesByStatus(@Param("status") String status);

Important:

JPQL constructor expression needs the fully qualified class name.

This avoids loading full entity data when only DTO fields are needed.

---

23. Native SQL Query

A native query uses real SQL.

Example:

@Query(
        value = "select * from tasks where status = :status",
        nativeQuery = true
)
List<TaskEntity> findByStatusNative(@Param("status") String status);

This uses:

table name: tasks
column name: status

not entity name.

Memory sentence:

Native query means real SQL.

---

24. When to Use Native SQL

Use native SQL when:

database-specific features are needed
query is too complex for JPQL
performance requires specific SQL
using CTEs or window functions
calling database-specific functions

But be careful.

Native SQL is less portable across databases.

Example:

PostgreSQL-specific SQL may not work on MySQL.

Memory sentence:

Native SQL is powerful but less portable.

---

25. JPQL vs Native Query

Topic	JPQL	Native SQL
Works with	entities and fields	tables and columns
Portable	more portable	database-specific
Syntax	JPQL	actual SQL
Uses JPA mapping	yes	partly, depending on result
Good for	normal entity queries	advanced database-specific queries

---

26. Modifying Queries

For update or delete queries, use:

@Modifying

Example:

@Modifying
@Query("update TaskEntity t set t.status = :status where t.id = :id")
int updateStatus(
        @Param("id") Long id,
        @Param("status") String status
);

Important:

Modifying queries need a transaction.

Usually call them inside a @Transactional service method.

We will study transactions soon.

Memory sentence:

@Modifying is needed for update/delete queries.

---

27. Delete Derived Query

Example:

void deleteByStatus(String status);

or:

long deleteByClientId(Long clientId);

Be careful.

Delete queries can remove many rows.

Usually protect them with business logic in the service layer.

---

28. Sorting

Spring Data supports dynamic sorting with Sort.

Repository:

List<TaskEntity> findByStatus(String status, Sort sort);

Service:

List<TaskEntity> tasks = taskRepository.findByStatus(
        "OPEN",
        Sort.by(Sort.Direction.DESC, "createdAt")
);

Meaning:

Find open tasks and sort by createdAt descending.

Memory sentence:

Sort adds dynamic sorting to repository queries.

---

29. Multiple Sort Fields

Example:

Sort sort = Sort.by(
        Sort.Order.asc("priority"),
        Sort.Order.desc("createdAt")
);

List<TaskEntity> tasks = taskRepository.findByStatus("OPEN", sort);

Meaning:

order by priority asc, createdAt desc

Important:

Sort property names use entity field names, not database column names.

Memory sentence:

Sort uses entity property names.

---

30. Pagination

Pagination means loading results page by page instead of all rows.

Why?

avoid loading too much data
improve performance
support UI pages
support API paging
reduce memory usage

Repository:

Page<TaskEntity> findByClientId(Long clientId, Pageable pageable);

Service:

Pageable pageable = PageRequest.of(0, 20);

Page<TaskEntity> page = taskRepository.findByClientId(10L, pageable);

Meaning:

page 0
size 20

Important:

Page indexes are zero-based.

Memory sentence:

PageRequest.of(0, 20) means first page with 20 items.

---

31. Pagination with Sorting

Pageable pageable = PageRequest.of(
        0,
        20,
        Sort.by(Sort.Direction.DESC, "createdAt")
);

Page<TaskEntity> page = taskRepository.findByStatus("OPEN", pageable);

Repository:

Page<TaskEntity> findByStatus(String status, Pageable pageable);

Meaning:

Find first page of open tasks, 20 per page, newest first.

---

32. Page

Page<T> contains:

content
page number
page size
total elements
total pages
whether next page exists
whether previous page exists

Example:

Page<TaskEntity> page = taskRepository.findByStatus("OPEN", pageable);

List<TaskEntity> content = page.getContent();
int pageNumber = page.getNumber();
int pageSize = page.getSize();
long totalElements = page.getTotalElements();
int totalPages = page.getTotalPages();
boolean hasNext = page.hasNext();

Important:

Page usually triggers an extra count query to calculate total elements.

Memory sentence:

Page gives content plus total count information.

---

33. Slice

Slice<T> is lighter than Page<T>.

Repository:

Slice<TaskEntity> findByStatus(String status, Pageable pageable);

Slice contains:

content
page info
whether next slice exists

But does not require total count.

Use Slice when:

I only need to know if there is a next page.
I do not need total elements or total pages.

Memory sentence:

Page has totals. Slice is lighter and does not need full total count.

---

34. Mapping Page of Entities to Page of DTOs

Entity page:

Page<TaskEntity> entityPage = taskRepository.findByStatus("OPEN", pageable);

Map to DTO page:

Page<TaskDto> dtoPage = entityPage.map(this::toDto);

Example:

private TaskDto toDto(TaskEntity entity) {
    return new TaskDto(
            entity.getId(),
            entity.getTitle(),
            entity.getStatus()
    );
}

Memory sentence:

Use page.map(...) to convert entity page to DTO page.

---

35. Controller Pagination Example

Controller:

@GetMapping("/api/tasks")
public Page<TaskDto> list(
        @RequestParam(defaultValue = "OPEN") String status,
        @RequestParam(defaultValue = "0") @Min(0) int page,
        @RequestParam(defaultValue = "20") @Min(1) @Max(100) int size
) {
    return taskService.findByStatus(status, page, size);
}

Service:

public Page<TaskDto> findByStatus(String status, int page, int size) {
    Pageable pageable = PageRequest.of(
            page,
            size,
            Sort.by(Sort.Direction.DESC, "createdAt")
    );

    return taskRepository.findByStatus(status, pageable)
            .map(this::toDto);
}

Repository:

Page<TaskEntity> findByStatus(String status, Pageable pageable);

---

36. Be Careful Returning Page Directly

Returning Page<TaskDto> is easy and common.

But some teams prefer a custom response DTO.

Example:

public record PageResponse<T>(
        List<T> content,
        int page,
        int size,
        long totalElements,
        int totalPages,
        boolean hasNext
) {
}

Why?

stable API response shape
less coupling to Spring Data Page JSON structure
more control

For learning, Page<T> is fine.

For public APIs, custom page response may be better.

---

37. findAll(Pageable pageable)

JpaRepository already provides:

Page<TaskEntity> findAll(Pageable pageable);

Usage:

Pageable pageable = PageRequest.of(0, 20);
Page<TaskEntity> tasks = taskRepository.findAll(pageable);

Also sorting:

List<TaskEntity> tasks = taskRepository.findAll(
        Sort.by(Sort.Direction.DESC, "createdAt")
);

---

38. Query Methods with Pageable

Example:

Page<TaskEntity> findByClientIdAndStatus(
        Long clientId,
        String status,
        Pageable pageable
);

Usage:

Pageable pageable = PageRequest.of(0, 20);

Page<TaskEntity> tasks = taskRepository.findByClientIdAndStatus(
        10L,
        "OPEN",
        pageable
);

Important:

Pageable is usually the last parameter.

---

39. @Query with Pageable

Example:

@Query("""
       select t
       from TaskEntity t
       where t.clientId = :clientId
         and t.status = :status
       """)
Page<TaskEntity> findClientTasks(
        @Param("clientId") Long clientId,
        @Param("status") String status,
        Pageable pageable
);

Spring can apply pagination to this query.

For complex queries, count query may need customization.

Example:

@Query(
        value = """
                select t
                from TaskEntity t
                where t.clientId = :clientId
                  and t.status = :status
                """,
        countQuery = """
                select count(t)
                from TaskEntity t
                where t.clientId = :clientId
                  and t.status = :status
                """
)
Page<TaskEntity> findClientTasks(
        @Param("clientId") Long clientId,
        @Param("status") String status,
        Pageable pageable
);

Memory sentence:

Pageable can work with derived queries and @Query.

---

40. Query Method Validation at Startup

Spring Data checks repository query methods during startup.

Example mistake:

List<TaskEntity> findByStatuz(String status);

But entity field is:

status

not:

statuz

Result:

Application may fail to start.

Why?

Spring Data cannot create a query for a non-existing property.

Memory sentence:

Wrong derived query property names can fail at startup.

---

41. Common Mistake: Database Column Name in Derived Query

Entity field:

private Instant createdAt;

Column:

@Column(name = "created_at")
private Instant createdAt;

Wrong repository method:

List<TaskEntity> findByCreated_at(Instant createdAt);

Correct:

List<TaskEntity> findByCreatedAt(Instant createdAt);

Why?

Derived queries use entity property names, not database column names.

Memory sentence:

Repository method names use Java field names.

---

42. Common Mistake: Too Many Results for Single Return

Repository:

Optional<TaskEntity> findByStatus(String status);

But many tasks can have the same status.

Problem:

Query may return multiple results when only one is expected.

Better:

List<TaskEntity> findByStatus(String status);

Use Optional only when zero or one result is expected.

---

43. Common Mistake: N+1 Problem Preview

Example:

List<TaskEntity> findByStatus(String status);

If TaskEntity has lazy relationships and the code accesses them one by one, it may cause many extra queries.

This is called:

N+1 query problem

We will study this later.

For now, remember:

Repository queries can affect performance, especially with relationships.

---

44. Common Mistake: Querying Too Much Data

Bad:

List<TaskEntity> findAll();

for a large table.

Better:

Page<TaskEntity> findAll(Pageable pageable);

or a filtered query:

Page<TaskEntity> findByClientId(Long clientId, Pageable pageable);

Memory sentence:

Do not load huge tables without pagination or filtering.

---

45. Which Query Style Should I Use?

Simple condition:

List<TaskEntity> findByStatus(String status);

Multiple simple conditions:

List<TaskEntity> findByClientIdAndStatus(Long clientId, String status);

Long or complex query:

@Query(...)

Database-specific query:

@Query(nativeQuery = true, ...)

Dynamic complex filters:

Specification
Criteria API
Querydsl
custom repository

Memory sentence:

Use the simplest query style that stays readable.

---

46. Common Exam Traps

Trap 1

Spring Data derives queries from method names.

---

Trap 2

Derived queries use entity property names, not database column names.

---

Trap 3

findByStatus expects an entity field named status.

---

Trap 4

JpaRepository already gives basic CRUD methods.

---

Trap 5

Use Optional when zero or one result is expected.

---

Trap 6

Use List when many results are expected.

---

Trap 7

JPQL uses entity names and fields.

---

Trap 8

Native SQL uses table names and columns.

---

Trap 9

@Param binds method parameters to named query parameters.

---

Trap 10

@Modifying is needed for update/delete queries.

---

Trap 11

Page includes total count information.

---

Trap 12

Slice is lighter and does not include total count.

---

Trap 13

Page indexes are zero-based.

---

Trap 14

Sort uses entity property names.

---

Trap 15

Wrong repository method names can fail at startup.

---

47. Real Exam Question: Derived Query

Question:

What is a derived query in Spring Data JPA?

Answer:

A derived query is a query that Spring Data creates from the repository method name, such as findByStatus or findByEmail.

---

48. Real Exam Question: Method Name Property

Question:

In findByCreatedAt, does CreatedAt refer to the Java entity property or the database column?

Answer:

It refers to the Java entity property name, not the database column name.

---

49. Real Exam Question: @Query

Question:

When should I use @Query?

Answer:

Use @Query when a derived query method name would be too long, unclear, or when I need more control over the query.

---

50. Real Exam Question: JPQL

Question:

What is JPQL?

Answer:

JPQL is the Java Persistence Query Language. It queries JPA entities and their fields, not database tables and columns.

---

51. Real Exam Question: Native Query

Question:

What is a native query?

Answer:

A native query is a query written in real SQL using database table and column names. In Spring Data JPA, it can be declared with @Query(nativeQuery = true).

---

52. Real Exam Question: @Param

Question:

What does @Param do?

Answer:

@Param binds a repository method parameter to a named parameter in a JPQL or native query.

---

53. Real Exam Question: @Modifying

Question:

When is @Modifying needed?

Answer:

@Modifying is needed for repository queries that modify data, such as update or delete queries declared with @Query.

---

54. Real Exam Question: Page

Question:

What does Page<T> contain?

Answer:

Page<T> contains the page content plus pagination metadata such as page number, size, total elements, total pages, and whether next or previous pages exist.

---

55. Real Exam Question: Slice

Question:

What is the difference between Page and Slice?

Answer:

Page includes total count information and total pages. Slice is lighter and only indicates whether there is a next slice, without requiring total count information.

---

56. Real Exam Question: Page Index

Question:

Is PageRequest.of(0, 20) the first page or second page?

Answer:

It is the first page. Spring Data page indexes are zero-based.

---

57. Real Exam Question: Sort

Question:

Does Sort.by("createdAt") use the entity property name or database column name?

Answer:

It uses the entity property name.

---

58. Interview Answer

Question:

How do derived query methods work in Spring Data JPA?

Good answer:

Spring Data JPA can create queries from repository method names. For example, findByStatus creates a query using the entity field status, and findByClientIdAndStatus creates a query with both conditions. The method names use Java entity property names, not database column names. Derived queries are good for simple queries, but if the method name becomes too long or unclear, I use @Query.

---

59. Interview Answer

Question:

What is the difference between JPQL and native SQL?

Good answer:

JPQL queries JPA entities and their fields, so it uses entity class names and Java property names. Native SQL queries database tables and columns directly. JPQL is more portable across databases, while native SQL is useful for database-specific features or complex optimized queries.

---

60. Interview Answer

Question:

How do you implement pagination in Spring Data JPA?

Good answer:

I add a Pageable parameter to the repository method and return Page<T> or Slice<T>. In the service, I create a PageRequest, for example PageRequest.of(page, size, Sort.by("createdAt").descending()). Page gives content plus total count information, while Slice is lighter and only tells whether another slice exists.

---

61. Interview Answer

Question:

When would you use @Query instead of a derived query?

Good answer:

I use @Query when the derived method name becomes too long or hard to read, when I need a more complex query, when I want to write a DTO projection, or when I need a native SQL query for database-specific features. For simple conditions like findByStatus, derived queries are usually enough.

---

62. Interview Answer

Question:

What mistakes can happen with repository query methods?

Good answer:

Common mistakes include using database column names instead of entity property names, creating method names that are too long, returning Optional when multiple rows can match, forgetting @Param names in @Query, missing @Modifying for update/delete queries, and loading too much data without pagination.

---

63. Tiny Code Practice

Repository:

public interface TaskRepository extends JpaRepository<TaskEntity, Long> {

    List<TaskEntity> findByStatus(String status);

    List<TaskEntity> findByClientIdAndStatus(Long clientId, String status);

    List<TaskEntity> findByTitleContainingIgnoreCase(String keyword);

    Page<TaskEntity> findByClientId(Long clientId, Pageable pageable);

    boolean existsByTitle(String title);

    long countByStatus(String status);
}

Questions:

1. What query does findByStatus create?
2. What query does findByClientIdAndStatus create?
3. What does ContainingIgnoreCase mean?
4. What does Pageable do?
5. What does existsByTitle return?

Answers:

1. Finds tasks where status equals the given value.
2. Finds tasks where clientId and status match.
3. Searches title containing keyword without case sensitivity.
4. Applies pagination and possibly sorting.
5. A boolean indicating whether a task with that title exists.

---

64. Tiny Bug Practice 1

Entity field:

@Column(name = "created_at")
private Instant createdAt;

Repository:

List<TaskEntity> findByCreated_at(Instant createdAt);

Question:

What is wrong?

Answer:

Derived query methods use Java entity property names, not database column names. The correct method is:

List<TaskEntity> findByCreatedAt(Instant createdAt);

---

65. Tiny Bug Practice 2

Repository:

Optional<TaskEntity> findByStatus(String status);

Question:

What is risky?

Answer:

Many tasks can have the same status, so the query can return multiple rows. Optional is only good when zero or one result is expected. Use:

List<TaskEntity> findByStatus(String status);

---

66. Tiny Bug Practice 3

Repository:

@Query("update TaskEntity t set t.status = :status where t.id = :id")
int updateStatus(Long id, String status);

Question:

What is missing?

Answer:

For update/delete queries, @Modifying is needed. Also use @Param for named parameters.

Correct:

@Modifying
@Query("update TaskEntity t set t.status = :status where t.id = :id")
int updateStatus(
        @Param("id") Long id,
        @Param("status") String status
);

This should be called inside a transaction.

---

Practice Questions and Answers

Question 1

What is a derived query?

Answer:

A derived query is a query that Spring Data creates from the repository method name.

---

Question 2

What does findByStatus(String status) mean?

Answer:

It finds entities where the status property equals the given parameter.

---

Question 3

What does findByClientIdAndStatus(Long clientId, String status) mean?

Answer:

It finds entities where both clientId and status match the given parameters.

---

Question 4

What does findByTitleContainingIgnoreCase(String keyword) mean?

Answer:

It finds entities whose title contains the keyword, ignoring case.

---

Question 5

Do derived query methods use entity property names or database column names?

Answer:

They use entity property names, not database column names.

---

Question 6

When should I use @Query?

Answer:

Use @Query when a derived method name would be too long, unclear, or when I need more control over the query.

---

Question 7

What is JPQL?

Answer:

JPQL is Java Persistence Query Language. It queries JPA entities and their fields.

---

Question 8

What is the difference between JPQL and native SQL?

Answer:

JPQL uses entity names and fields. Native SQL uses database table and column names.

---

Question 9

What does @Param do?

Answer:

@Param binds a repository method parameter to a named query parameter.

---

Question 10

When do I need @Modifying?

Answer:

Use @Modifying for update or delete queries declared with @Query.

---

Question 11

When should I use Optional as a repository return type?

Answer:

Use Optional when zero or one result is expected.

---

Question 12

When should I use List as a repository return type?

Answer:

Use List when many results may match.

---

Question 13

What is Pageable?

Answer:

Pageable represents pagination information such as page number, page size, and sorting.

---

Question 14

What does Page<T> contain?

Answer:

Page<T> contains content plus pagination metadata such as total elements, total pages, page number, page size, and next/previous information.

---

Question 15

What is the difference between Page and Slice?

Answer:

Page includes total count information. Slice is lighter and only tells whether a next slice exists.

---

Question 16

Is the first page index 0 or 1?

Answer:

The first page index is 0.

---

Question 17

What does Sort.by("createdAt") use: entity property or database column?

Answer:

It uses the entity property name.

---

Question 18

Why should I avoid findAll() on large tables?

Answer:

Because it can load too much data into memory and hurt performance. Use filtering and pagination.

---

Question 19

How can I map Page<TaskEntity> to Page<TaskDto>?

Answer:

Use:

Page<TaskDto> dtoPage = entityPage.map(this::toDto);

---

Question 20

What can happen if a derived query method references a non-existing entity property?

Answer:

The application can fail to start because Spring Data cannot create the query.

Final Memory Sentences

• Derived queries are created from repository method names.
• Repository method names use entity property names, not database column names.
• findByStatus means query by the status field.
• And and Or combine conditions.
• ContainingIgnoreCase is useful for simple text search.
• Use @Query when derived method names become too long or unclear.
• JPQL queries entities and fields.
• Native SQL queries tables and columns.
• @Param binds named query parameters.
• @Modifying is needed for update/delete queries.
• Use Optional for zero or one result.
• Use List for many results.
• Use pagination for large result sets.
• Pageable carries page, size, and sort.
• Page includes total count information.
• Slice is lighter than Page.
• Page indexes are zero-based.
• Sort uses entity property names.
• Wrong repository property names can fail at startup.