JPQL

# ── SQL (database-level): ────────────────────────────────────────────
SELECT u.id, u.name, u.email
FROM users u
WHERE u.role = 'ADMIN'
ORDER BY u.created_at DESC;

# ── Equivalent JPQL (entity-level): ──────────────────────────────────
SELECT u FROM User u
WHERE u.role = com.example.User.Role.ADMIN
ORDER BY u.createdAt DESC

# ── Key differences: ──────────────────────────────────────────────────
# SQL uses table names (users);         JPQL uses entity names (User)
# SQL uses column names (created_at);   JPQL uses field names (createdAt)
# SQL uses string literals for enums;   JPQL uses Java enum references
# SQL is dialect-specific;              JPQL compiles to any SQL dialect
# SQL selects columns;                  JPQL selects entity instances
# SQL has no concept of associations;   JPQL traverses associations with .

# ── JPQL result types: ────────────────────────────────────────────────
SELECT u FROM User u             # returns List<User> (full entity)
SELECT u.name FROM User u        # returns List<String> (scalar)
SELECT u.id, u.name FROM User u  # returns List<Object[]> (tuple)
SELECT new com.example.dto.UserDto(u.id, u.name)
FROM User u                      # returns List<UserDto> (constructor expression)

// ── Basic SELECT: ─────────────────────────────────────────────────────
@Query("SELECT u FROM User u")
List<User> findAll();

// ── WHERE with named parameters (:name syntax — preferred): ──────────
@Query("SELECT u FROM User u WHERE u.email = :email")
Optional<User> findByEmail(@Param("email") String email);

// ── WHERE with positional parameters (?1 syntax — avoid): ────────────
@Query("SELECT u FROM User u WHERE u.email = ?1 AND u.role = ?2")
Optional<User> findByEmailAndRole(String email, User.Role role);

// ── Multiple conditions: ──────────────────────────────────────────────
@Query("""
    SELECT u FROM User u
    WHERE u.role = :role
      AND u.active = true
      AND u.createdAt >= :since
    ORDER BY u.name ASC
    """)
List<User> findActiveByRoleSince(
    @Param("role") User.Role role,
    @Param("since") LocalDateTime since);

// ── Comparison operators: ─────────────────────────────────────────────
// =, <>, !=, <, >, <=, >=
// BETWEEN ... AND ...
// IN (:collection)
// NOT IN (:collection)
// IS NULL, IS NOT NULL
// LIKE (% wildcard, _ single char)
// NOT LIKE

@Query("SELECT u FROM User u WHERE u.age BETWEEN :min AND :max")
List<User> findByAgeBetween(@Param("min") int min, @Param("max") int max);

@Query("SELECT u FROM User u WHERE u.role IN :roles")
List<User> findByRoles(@Param("roles") Collection<User.Role> roles);

@Query("SELECT u FROM User u WHERE u.name LIKE %:fragment%")
List<User> searchByName(@Param("fragment") String fragment);

@Query("SELECT u FROM User u WHERE u.deletedAt IS NULL")
List<User> findActive();

// ── Case-insensitive LIKE: ────────────────────────────────────────────
@Query("SELECT u FROM User u WHERE LOWER(u.name) LIKE LOWER(CONCAT('%', :q, '%'))")
List<User> searchByNameIgnoreCase(@Param("q") String q);

// ── Implicit join via dot notation (no explicit JOIN): ────────────────
// Hibernate adds the JOIN automatically:
@Query("SELECT o FROM Order o WHERE o.customer.country = :country")
List<Order> findByCustomerCountry(@Param("country") String country);
// SQL: SELECT o.* FROM orders o JOIN customers c ON c.id = o.customer_id
//      WHERE c.country = ?

// ── Explicit INNER JOIN: ──────────────────────────────────────────────
@Query("SELECT o FROM Order o JOIN o.customer c WHERE c.email = :email")
List<Order> findByCustomerEmail(@Param("email") String email);
// INNER JOIN — excludes orders with no customer (unusual with NOT NULL FK)

// ── LEFT JOIN — include orders with no matching association: ──────────
@Query("SELECT o FROM Order o LEFT JOIN o.tags t WHERE t.name = :tag OR t IS NULL")
List<Order> findByTagOrUntagged(@Param("tag") String tag);

// ── JOIN FETCH — load association in same query (prevents N+1): ───────
@Query("SELECT o FROM Order o JOIN FETCH o.customer WHERE o.id = :id")
Optional<Order> findByIdWithCustomer(@Param("id") Long id);

@Query("SELECT DISTINCT o FROM Order o " +
       "LEFT JOIN FETCH o.items i " +
       "LEFT JOIN FETCH i.product " +
       "WHERE o.id = :id")
Optional<Order> findByIdWithItemsAndProducts(@Param("id") Long id);
// DISTINCT prevents duplicate Order objects caused by the collection JOIN

// ── Join on a collection association: ────────────────────────────────
@Query("SELECT DISTINCT u FROM User u JOIN u.roles r WHERE r.name = :roleName")
List<User> findByRoleName(@Param("roleName") String roleName);

// ── Multiple joins: ───────────────────────────────────────────────────
@Query("""
    SELECT o FROM Order o
    JOIN FETCH o.customer c
    JOIN o.items i
    JOIN i.product p
    WHERE c.tier = :tier
      AND p.category = :category
    """)
List<Order> findByCustomerTierAndProductCategory(
    @Param("tier") String tier,
    @Param("category") String category);

// ── Aggregate functions: COUNT, SUM, AVG, MIN, MAX ───────────────────
@Query("SELECT COUNT(u) FROM User u WHERE u.role = :role")
long countByRole(@Param("role") User.Role role);

@Query("SELECT SUM(i.quantity) FROM OrderItem i WHERE i.order.id = :orderId")
Long sumQuantityByOrder(@Param("orderId") Long orderId);

@Query("SELECT AVG(o.total) FROM Order o WHERE o.customer.id = :customerId")
Double avgOrderTotal(@Param("customerId") Long customerId);

@Query("SELECT MIN(o.createdAt) FROM Order o WHERE o.customer.id = :id")
Optional<LocalDateTime> firstOrderDate(@Param("id") Long id);

// ── GROUP BY — returns Object[] tuples: ──────────────────────────────
@Query("SELECT u.role, COUNT(u) FROM User u GROUP BY u.role")
List<Object[]> countByRole();

// Usage:
List<Object[]> results = userRepository.countByRole();
results.forEach(row -> {
    User.Role role  = (User.Role) row[0];
    Long count      = (Long)      row[1];
});

// ── GROUP BY with HAVING: ─────────────────────────────────────────────
@Query("""
    SELECT u.country, COUNT(u)
    FROM User u
    GROUP BY u.country
    HAVING COUNT(u) > :minCount
    ORDER BY COUNT(u) DESC
    """)
List<Object[]> findCountriesWithMinUsers(@Param("minCount") long minCount);

// ── Constructor expression — map directly to a DTO: ───────────────────
public record RoleSummary(User.Role role, long count) { }

@Query("""
    SELECT new com.example.dto.RoleSummary(u.role, COUNT(u))
    FROM User u
    GROUP BY u.role
    ORDER BY COUNT(u) DESC
    """)
List<RoleSummary> getRoleSummaries();
// Returns List<RoleSummary> — no Object[] casting needed.
// The DTO must have a matching constructor.

@Repository
public interface UserRepository extends JpaRepository<User, Long> {

    // ── Bulk UPDATE: ─────────────────────────────────────────────────
    @Modifying
    @Transactional
    @Query("UPDATE User u SET u.active = false WHERE u.lastLoginAt < :cutoff")
    int deactivateInactiveUsers(@Param("cutoff") LocalDateTime cutoff);
    // Returns the number of rows affected.

    @Modifying
    @Transactional
    @Query("UPDATE User u SET u.role = :newRole WHERE u.role = :oldRole")
    int migrateRole(
        @Param("oldRole") User.Role oldRole,
        @Param("newRole") User.Role newRole);

    // ── Bulk DELETE: ─────────────────────────────────────────────────
    @Modifying
    @Transactional
    @Query("DELETE FROM User u WHERE u.active = false AND u.createdAt < :cutoff")
    int deleteOldInactiveUsers(@Param("cutoff") LocalDateTime cutoff);

    // ── IMPORTANT: clear the persistence context after bulk operations: ─
    // Bulk UPDATE/DELETE bypass the persistence context.
    // Entities already loaded into the context may be stale after the query.
    // Use clearAutomatically = true to evict them:
    @Modifying(clearAutomatically = true, flushAutomatically = true)
    @Transactional
    @Query("UPDATE User u SET u.active = false WHERE u.lastLoginAt < :cutoff")
    int deactivateSafe(@Param("cutoff") LocalDateTime cutoff);
    // clearAutomatically: evicts all entities from the context after execution.
    // flushAutomatically: flushes pending changes before the bulk query runs.

    // ── Bulk update vs single-entity update: ─────────────────────────
    // Bulk JPQL UPDATE — 1 SQL UPDATE, no lifecycle callbacks, no dirty check:
    @Modifying @Query("UPDATE User u SET u.name = :name WHERE u.id = :id")
    int updateName(@Param("id") Long id, @Param("name") String name);

    // Single-entity update — dirty checking, @PreUpdate fires:
    @Transactional
    default void updateNameSafe(Long id, String name) {
        findById(id).ifPresent(u -> u.setName(name));
        // Hibernate generates UPDATE from dirty checking — @PreUpdate fires
    }
}

// ── Subquery — IN with subquery: ─────────────────────────────────────
@Query("""
    SELECT u FROM User u
    WHERE u.id IN (
        SELECT DISTINCT o.customer.id FROM Order o
        WHERE o.total > :amount
    )
    """)
List<User> findUsersWithLargeOrders(@Param("amount") BigDecimal amount);

// ── Subquery — EXISTS: ────────────────────────────────────────────────
@Query("""
    SELECT u FROM User u
    WHERE EXISTS (
        SELECT o FROM Order o
        WHERE o.customer = u
          AND o.status = com.example.Order.Status.PENDING
    )
    """)
List<User> findUsersWithPendingOrders();

// ── Subquery — ALL / ANY: ─────────────────────────────────────────────
@Query("""
    SELECT p FROM Product p
    WHERE p.price > ALL (
        SELECT p2.price FROM Product p2 WHERE p2.category = 'BUDGET'
    )
    """)
List<Product> findProductsMoreExpensiveThanAllBudget();

// ── CASE expression: ──────────────────────────────────────────────────
@Query("""
    SELECT u.name,
           CASE u.role
               WHEN com.example.User.Role.ADMIN THEN 'Administrator'
               WHEN com.example.User.Role.USER  THEN 'Standard User'
               ELSE 'Unknown'
           END
    FROM User u
    """)
List<Object[]> findUsersWithRoleLabel();

// ── Searched CASE (boolean conditions): ──────────────────────────────
@Query("""
    SELECT u.name,
           CASE
               WHEN u.age < 18 THEN 'Minor'
               WHEN u.age < 65 THEN 'Adult'
               ELSE 'Senior'
           END
    FROM User u
    """)
List<Object[]> findUsersWithAgeGroup();

// ── COALESCE — return first non-null: ────────────────────────────────
@Query("SELECT COALESCE(u.displayName, u.name) FROM User u WHERE u.id = :id")
String findDisplayName(@Param("id") Long id);

// ── NULLIF — return null if two values are equal: ─────────────────────
@Query("SELECT NULLIF(u.email, u.backupEmail) FROM User u WHERE u.id = :id")
String findNonDuplicateEmail(@Param("id") Long id);

// ── String functions: ─────────────────────────────────────────────────
// UPPER(x), LOWER(x), TRIM(x), LENGTH(x)
// CONCAT(x, y), SUBSTRING(x, start, length)
// LOCATE(pattern, string)

// ── Date/time functions: ──────────────────────────────────────────────
// CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP
@Query("SELECT u FROM User u WHERE u.createdAt > CURRENT_TIMESTAMP - 7")
List<User> findRecentUsers();

// ── @NamedQuery on the entity — validated at startup: ────────────────
@Entity
@Table(name = "products")
@NamedQuery(
    name = "Product.findByCategory",
    query = "SELECT p FROM Product p WHERE p.category = :category ORDER BY p.name"
)
@NamedQuery(
    name = "Product.findActiveByPriceRange",
    query = """
        SELECT p FROM Product p
        WHERE p.active = true
          AND p.price BETWEEN :min AND :max
        ORDER BY p.price ASC
        """
)
public class Product {
    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
    private String category;
    private BigDecimal price;
    private boolean active;
}

// Use named queries in Spring Data repositories:
@Repository
public interface ProductRepository extends JpaRepository<Product, Long> {

    // Spring Data resolves this to the "Product.findByCategory" named query:
    List<Product> findByCategory(@Param("category") String category);

    // Explicit named query reference:
    @Query(name = "Product.findActiveByPriceRange")
    List<Product> findActiveByPriceRange(
        @Param("min") BigDecimal min,
        @Param("max") BigDecimal max);
}

// ── EntityManager API — direct JPQL execution: ───────────────────────
@Repository
@RequiredArgsConstructor
public class ProductRepositoryCustomImpl implements ProductRepositoryCustom {

    private final EntityManager em;

    @Override
    public List<Product> searchDynamic(ProductFilter filter) {
        String jpql = "SELECT p FROM Product p WHERE 1=1";
        if (filter.getCategory() != null) jpql += " AND p.category = :category";
        if (filter.getMinPrice() != null) jpql += " AND p.price >= :minPrice";
        jpql += " ORDER BY p.name";

        TypedQuery<Product> query = em.createQuery(jpql, Product.class);
        if (filter.getCategory() != null)
            query.setParameter("category", filter.getCategory());
        if (filter.getMinPrice() != null)
            query.setParameter("minPrice", filter.getMinPrice());

        query.setFirstResult(filter.getOffset());
        query.setMaxResults(filter.getLimit());
        return query.getResultList();
    }
}