# JAVA Springboot JPA Query Keywords

Here is the list of all the springboot JPA query keywords so that you make JPA queries easily. Save this and use whenever you build a springboot application.

# Basic query keywords

| Keyword | Purpose | Example |
| --- | --- | --- |
| `findBy` | Find matching records | `findByEmail(String email)` |
| `readBy` | Read matching records | `readByEmail(String email)` |
| `getBy` | Get matching records | `getByEmail(String email)` |
| `queryBy` | Query matching records | `queryByEmail(String email)` |
| `searchBy` | Search matching records | `searchByEmail(String email)` |
| `streamBy` | Return a stream | `streamByEmail(String email)` |
| `existsBy` | Check whether a record exists | `existsByEmail(String email)` |
| `countBy` | Count matching records | `countByUser_Id(UUID userId)` |
| `deleteBy` | Delete matching records | `deleteByUser_Id(UUID userId)` |
| `removeBy` | Remove matching records | `removeByUser_Id(UUID userId)` |

### Example

```java
public interface AppUserRepository extends JpaRepository<AppUser, UUID> {

    Optional<AppUser> findByEmail(String email);

    boolean existsByEmail(String email);

    long countByEmailContaining(String keyword);
}
```

# Comparison keywords

| Keyword | Meaning | Example |
| --- | --- | --- |
| `Is` | Equals | `findByAmountIs(BigDecimal amount)` |
| `Equals` | Equals | `findByNameEquals(String name)` |
| `IsNot` | Not equal | `findByTypeIsNot(TransactionType type)` |
| `Not` | Not equal | `findByNameNot(String name)` |
| `GreaterThan` | `>` | `findByAmountGreaterThan(BigDecimal amount)` |
| `GreaterThanEqual` | `>=` | `findByAmountGreaterThanEqual(BigDecimal amount)` |
| `LessThan` | `<` | `findByAmountLessThan(BigDecimal amount)` |
| `LessThanEqual` | `<=` | `findByAmountLessThanEqual(BigDecimal amount)` |
| `Between` | Between two values | `findByDateBetween(LocalDate from, LocalDate to)` |
| `After` | After a date/value | `findByDateAfter(LocalDate date)` |
| `Before` | Before a date/value | `findByDateBefore(LocalDate date)` |
| `IsNull` | Is NULL | `findByCategoryIsNull()` |
| `IsNotNull` | Is NOT NULL | `findByCategoryIsNotNull()` |
| `In` | Matches any value in collection | `findByTypeIn(Collection<TransactionType> types)` |
| `NotIn` | Does not match collection | `findByTypeNotIn(Collection<TransactionType> types)` |

### Example

```java
List<Transaction> findByAmountGreaterThan(BigDecimal amount);

List<Transaction> findByDateBetween(
    LocalDate from,
    LocalDate to
);

List<Transaction> findByTransactionTypeIn(
    Collection<TransactionType> types
);
```

## String matching keywords

| Keyword | Meaning | Example |
| --- | --- | --- |
| `Containing` | Contains text | `findByTitleContaining(String title)` |
| `IsContaining` | Contains text | `findByTitleIsContaining(String title)` |
| `Contains` | Contains text | `findByTitleContains(String title)` |
| `StartingWith` | Starts with | `findByTitleStartingWith(String title)` |
| `EndingWith` | Ends with | `findByTitleEndingWith(String title)` |
| `Like` | SQL LIKE | `findByTitleLike(String pattern)` |
| `NotLike` | SQL NOT LIKE | `findByTitleNotLike(String pattern)` |
| `IgnoreCase` | Case-insensitive | `findByNameIgnoreCase(String name)` |
| `IgnoringCase` | Case-insensitive | `findByNameIgnoringCase(String name)` |
| `NotContaining` | Does not contain | `findByTitleNotContaining(String title)` |

## Logical operators

| Keyword | Meaning | Example |
| --- | --- | --- |
| `And` | Both conditions must match | `findByTitleAndAmount(String title, BigDecimal amount)` |
| `Or` | Either condition can match | `findByTitleOrAmount(String title, BigDecimal amount)` |

### Example

```java
List<Transaction> findByUser_IdAndTransactionType(
    UUID userId,
    TransactionType type
);
```

## Sorting keywords

| Keyword | Meaning | Example |
| --- | --- | --- |
| `OrderBy` | Sort results | `findByUser_IdOrderByDate(UUID userId)` |
| `Asc` | Ascending | `findByUser_IdOrderByDateAsc(UUID userId)` |
| `Desc` | Descending | `findByUser_IdOrderByDateDesc(UUID userId)` |

## Limiting query results

| Keyword | Meaning | Example |
| --- | --- | --- |
| `Top` | Limit results | `findTop5ByUser_Id(UUID userId)` |
| `First` | Return first matching result | `findFirstByUser_Id(UUID userId)` |
| `Top3` | Return top three | `findTop3ByUser_IdOrderByAmountDesc(UUID userId)` |
| `First10` | Return first ten | `findFirst10ByUser_IdOrderByDateDesc(UUID userId)` |

## Boolean keywords

| Keyword | Meaning | Example |
| --- | --- | --- |
| `True` | Boolean true | `findBySystemTrue()` |
| `False` | Boolean false | `findBySystemFalse()` |
| `IsTrue` | Boolean true | `findBySystemIsTrue()` |
| `IsFalse` | Boolean false | `findBySystemIsFalse()` |

## Special keywords and return types

| Feature | Example | Purpose |
| --- | --- | --- |
| `Distinct` | `findDistinctByUser_Id(UUID id)` | Unique results |
| `IgnoreCase` | `findByEmailIgnoreCase(String email)` | Case-insensitive comparison |
| `Pageable` | `findByUser_Id(UUID id, Pageable pageable)` | Pagination |
| `Sort` | `findByUser_Id(UUID id, Sort sort)` | Dynamic sorting |
| `Optional` | `Optional<AppUser> findByEmail(String email)` | Zero or one result |
| `List` | `List<Transaction> findByUser_Id(UUID id)` | Multiple results |
| `Slice` | `Slice<Transaction> findByUser_Id(UUID id, Pageable p)` | Page navigation without total count |
| `Stream` | `Stream<Transaction> findByUser_Id(UUID id)` | Stream results |

## When should you use `@Query` instead?

Derived queries are excellent for straightforward queries, but they aren't the solution for everything. For example, imagine you want: Total monthly income, Category-wise expense breakdown. Here you have to use SQL queries.\

```java
@Query("""
    SELECT SUM(t.amount)
    FROM Transaction t
    WHERE t.user.id = :userId
    AND t.transactionType = :type
""")
BigDecimal calculateTotal(
    UUID userId,
    TransactionType type
);
```