Fix: Spring Data JPA Query Not Working — @Query, Derived Methods, and N+1 Problems

The Problem

A Spring Data JPA repository query returns wrong results or throws an exception:

// Repository
@Query("SELECT u FROM User u WHERE u.email = :email")
Optional<User> findByEmail(@Param("email") String email);

// Service
Optional<User> user = userRepository.findByEmail("[email protected]");
// Returns empty — even though the user exists in the database

Or a derived method name doesn’t compile or returns unexpected results:

List<User> findByFirstNameAndLastNameOrderByCreatedAtDesc(String firstName, String lastName);
// Works — but what about:
List<User> findByAddressCityAndActiveTrue(String city);
// May fail with: No property city found for type Address

Or an @Modifying query doesn’t persist changes:

@Modifying
@Query("UPDATE User u SET u.active = false WHERE u.lastLoginAt < :cutoff")
int deactivateInactiveUsers(@Param("cutoff") LocalDateTime cutoff);
// Runs without error but database shows no changes

Or LazyInitializationException when accessing related entities:

org.hibernate.LazyInitializationException: failed to lazily initialize a collection
of role: com.example.User.orders, could not initialize proxy - no Session

Why This Happens

Spring Data JPA combines JPQL (object-oriented query language), entity mappings, and Hibernate’s session management. Errors arise from:

• JPQL vs SQL — @Query uses JPQL by default. JPQL references entity class names and field names, not table/column names. Using SQL column names in JPQL causes silent mismatches.
• Missing @Transactional on @Modifying — update/delete queries require an active transaction. Without @Transactional, the query runs but changes aren’t persisted.
• First-level cache returning stale data — after an @Modifying query, the persistence context (first-level cache) may return cached (stale) entity state. clearAutomatically = true fixes this.
• Lazy loading outside a session — fetching a lazily-loaded collection outside a @Transactional method causes LazyInitializationException. The Hibernate session is closed before the collection is accessed.
• Derived method naming rules — Spring Data parses method names like findByAddressCityAndActiveTrue. If your entity has Address address with String city, it becomes findByAddress_City (with underscore for nested properties).

Fix 1: Understand JPQL vs Native SQL

@Query uses JPQL by default — entity names and field names, not table/column names:

// Entity
@Entity
@Table(name = "users")          // Table name: users
public class User {
    @Column(name = "email_address")  // Column: email_address
    private String email;            // Field: email
}

// WRONG — using SQL syntax in JPQL
@Query("SELECT * FROM users WHERE email_address = :email")
Optional<User> findByEmail(@Param("email") String email);
// Error: unexpected token: * (JPQL doesn't support SELECT *)

// CORRECT — JPQL uses entity class name and field name
@Query("SELECT u FROM User u WHERE u.email = :email")
Optional<User> findByEmail(@Param("email") String email);
// 'User' = entity class name, 'email' = Java field name

// CORRECT — native SQL query
@Query(value = "SELECT * FROM users WHERE email_address = :email",
       nativeQuery = true)
Optional<User> findByEmailNative(@Param("email") String email);

Common JPQL vs SQL differences:

// JPQL — entity and field names
@Query("SELECT u FROM User u WHERE u.status = 'ACTIVE' ORDER BY u.createdAt DESC")

// Native SQL — table and column names
@Query(value = "SELECT * FROM users WHERE status = 'ACTIVE' ORDER BY created_at DESC",
       nativeQuery = true)

// JPQL — JOIN with entity relationship
@Query("SELECT o FROM Order o JOIN o.user u WHERE u.email = :email")

// JPQL — constructor expression (map to DTO)
@Query("SELECT new com.example.UserDto(u.id, u.name, u.email) FROM User u WHERE u.active = true")

Fix 2: Fix @Modifying Queries

Update and delete queries need @Transactional and often clearAutomatically:

// WRONG — missing @Transactional
@Modifying
@Query("UPDATE User u SET u.active = false WHERE u.lastLoginAt < :cutoff")
int deactivateInactiveUsers(@Param("cutoff") LocalDateTime cutoff);
// Throws: javax.persistence.TransactionRequiredException or silently does nothing

// CORRECT — add @Transactional
@Modifying
@Transactional
@Query("UPDATE User u SET u.active = false WHERE u.lastLoginAt < :cutoff")
int deactivateInactiveUsers(@Param("cutoff") LocalDateTime cutoff);

// BEST — also clear first-level cache to avoid stale entity reads after the update
@Modifying(clearAutomatically = true, flushAutomatically = true)
@Transactional
@Query("UPDATE User u SET u.active = false WHERE u.lastLoginAt < :cutoff")
int deactivateInactiveUsers(@Param("cutoff") LocalDateTime cutoff);

Why clearAutomatically? After a bulk update, Hibernate’s first-level cache still holds the old entity state. If you then call userRepository.findById(id), you may get the stale (pre-update) entity. clearAutomatically = true evicts all entities from the first-level cache after the modifying query.

// Service
@Transactional
public void deactivateAndRefresh(LocalDateTime cutoff, Long userId) {
    int updated = userRepository.deactivateInactiveUsers(cutoff);

    // Without clearAutomatically = true, this returns cached (stale) entity
    User user = userRepository.findById(userId).orElseThrow();
    // user.isActive() may return true even after the update above

    // With clearAutomatically = true, fresh data is fetched from DB
}

Fix 3: Fix Derived Query Method Names

Spring Data parses method names to build queries automatically. Naming conventions must be exact:

// Basic patterns
List<User> findByEmail(String email);
// → WHERE email = ?

List<User> findByEmailAndActive(String email, boolean active);
// → WHERE email = ? AND active = ?

List<User> findByAgeGreaterThan(int age);
// → WHERE age > ?

List<User> findByNameContainingIgnoreCase(String name);
// → WHERE LOWER(name) LIKE LOWER('%' + name + '%')

List<User> findByCreatedAtBetween(LocalDateTime start, LocalDateTime end);
// → WHERE created_at BETWEEN ? AND ?

List<User> findTop10ByActiveOrderByCreatedAtDesc(boolean active);
// → WHERE active = ? ORDER BY created_at DESC LIMIT 10

Nested properties — use underscore for disambiguation:

@Entity
public class User {
    private Address address;  // Nested entity/embeddable
}

@Embeddable
public class Address {
    private String city;
    private String country;
}

// Ambiguous — Spring tries to find 'addressCity' field first
List<User> findByAddressCity(String city);

// Explicit with underscore — unambiguous
List<User> findByAddress_City(String city);

// Multiple levels
List<User> findByAddress_Country_Code(String countryCode);

Reserved keywords in method names:

Fix 4: Fix LazyInitializationException

Accessing lazy-loaded collections outside a transaction causes this error:

// WRONG — accessing lazy collection outside transaction
@RestController
public class UserController {

    @GetMapping("/users/{id}")
    public UserDto getUser(@PathVariable Long id) {
        User user = userRepository.findById(id).orElseThrow();
        // Transaction from findById is already closed
        int orderCount = user.getOrders().size();  // LazyInitializationException!
        return new UserDto(user, orderCount);
    }
}

Fix 1 — use JPQL JOIN FETCH to load eagerly:

@Query("SELECT u FROM User u LEFT JOIN FETCH u.orders WHERE u.id = :id")
Optional<User> findByIdWithOrders(@Param("id") Long id);

Fix 2 — add @Transactional to keep session open:

@Service
public class UserService {

    @Transactional(readOnly = true)  // Keep session open for lazy loading
    public UserDto getUserWithOrders(Long id) {
        User user = userRepository.findById(id).orElseThrow();
        int orderCount = user.getOrders().size();  // Session still open — OK
        return new UserDto(user, orderCount);
    }
}

Fix 3 — use a DTO projection instead of lazy loading:

// DTO projection — fetches only needed fields in one query
public interface UserSummary {
    Long getId();
    String getName();
    String getEmail();

    @Value("#{target.orders.size()}")
    int getOrderCount();
}

// Repository
List<UserSummary> findAllProjectedBy();

Fix 4 — use @EntityGraph to control eager loading per query:

@EntityGraph(attributePaths = {"orders", "address"})
Optional<User> findWithGraphById(Long id);
// Fetches user + orders + address in one query
// Without making orders/address EAGER on the entity (which would affect all queries)

Fix 5: Pagination and Sorting

// Repository — paginated query
Page<User> findByActive(boolean active, Pageable pageable);

// Service — using pagination
public Page<UserDto> getActiveUsers(int page, int size) {
    Pageable pageable = PageRequest.of(
        page,
        size,
        Sort.by(Sort.Direction.DESC, "createdAt")
    );
    return userRepository.findByActive(true, pageable)
        .map(UserDto::from);
}

// Paginated @Query — requires countQuery for correct total count
@Query(value = "SELECT u FROM User u WHERE u.department.name = :dept",
       countQuery = "SELECT COUNT(u) FROM User u WHERE u.department.name = :dept")
Page<User> findByDepartment(@Param("dept") String dept, Pageable pageable);

Fix 6: Enable SQL Logging for Debugging

Log the actual SQL to diagnose query issues:

# application.yml
spring:
  jpa:
    show-sql: true
    properties:
      hibernate:
        format_sql: true         # Pretty-print SQL
        use_sql_comments: true   # Include JPQL query as comment

logging:
  level:
    org.hibernate.SQL: DEBUG
    org.hibernate.type.descriptor.sql: TRACE   # Log parameter values
    org.springframework.data.jpa.repository: DEBUG

Output shows:

-- original query: SELECT u FROM User u WHERE u.email = :email
select u.id, u.email, u.name from users u where u.email=?
-- parameter: 1: [email protected]

This reveals whether the generated SQL matches your intent and shows actual parameter values.

Still Not Working?

Optional<User> vs User return type — a @Query returning Optional<User> will throw IncorrectResultSizeDataAccessException if the query returns more than one result. Use List<User> if multiple results are possible.

Spring Data JPA @Repository not needed — JpaRepository and CrudRepository interfaces are automatically detected by Spring Boot. You don’t need @Repository on the interface (though it doesn’t hurt).

@Transactional in @Repository vs @Service — Spring Data repository methods already run in a transaction. If you call multiple repository methods from a service, wrap the service method in @Transactional to ensure they all run in the same transaction. Otherwise, each repository call is its own transaction — updates from one aren’t visible to the next in the same request.

For related Spring issues, see Fix: Spring Boot @Transactional Not Rolling Back and Fix: Spring Boot DataSource Failed to Configure.