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).

The hardest class of JPA failure is the runtime-only one: code that compiles fine, passes unit tests with mocked repositories, and then throws at runtime against the real database. JPQL is a string. Spring Data verifies its syntax at startup, but not its semantics against your schema. A typo in a field name, a JOIN on the wrong relationship, or a missing @Transactional on an @Modifying query all sail through the compiler and surface only when production traffic hits that code path.

The blast radius is per-endpoint, which sounds limited, but for a public API this is severe. A broken @Query on the /users/search endpoint returns 500s for every search request — that single endpoint’s error rate goes from 0% to 100% the moment the bug ships. Worse are the silent failure modes: @Modifying queries without @Transactional log no error but persist nothing, so a “deactivate inactive users” batch job appears to succeed and reports a row count, while the database stays unchanged. The same applies to first-level cache staleness: after a bulk update, reads return the pre-update state, so a UI that refreshes after the update shows wrong data. These bugs are typically caught by integration tests against a real database — never by unit tests with mocked repositories.

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.

Fix 7: Defend Against Runtime-Only Query Failures with Integration Tests

Unit tests with mocked repositories prove your service layer compiles. They prove nothing about whether your JPQL is valid or your @Modifying query actually modifies. Every repository method needs an integration test that hits a real database (Testcontainers, an embedded H2, or a dedicated test schema).

@SpringBootTest
@Testcontainers
class UserRepositoryTest {

    @Container
    static PostgreSQLContainer<?> postgres = new PostgreSQLContainer<>("postgres:16");

    @DynamicPropertySource
    static void props(DynamicPropertyRegistry registry) {
        registry.add("spring.datasource.url", postgres::getJdbcUrl);
        registry.add("spring.datasource.username", postgres::getUsername);
        registry.add("spring.datasource.password", postgres::getPassword);
    }

    @Autowired
    UserRepository userRepository;

    @Test
    void deactivateInactiveUsers_actuallyUpdatesTheRow() {
        User u = userRepository.save(new User("[email protected]", true, oldDate()));

        int updated = userRepository.deactivateInactiveUsers(LocalDateTime.now().minusDays(30));

        assertThat(updated).isEqualTo(1);
        // CRITICAL: re-fetch from DB, do not rely on cached entity
        User reloaded = userRepository.findById(u.getId()).orElseThrow();
        assertThat(reloaded.isActive()).isFalse();
    }
}

Pair this with a data audit job. For business-critical bulk updates (deactivations, billing recomputations, GDPR deletions), run a daily query that asserts the invariant: “no active users with last_login_at < 30 days ago.” If the invariant breaks, the bulk update is silently failing — alert and investigate.

Pro Tip: Add @DataJpaTest slice tests for query verification, and full @SpringBootTest integration tests for transactional behavior. The slice tests are fast and catch JPQL syntax errors at startup. The integration tests catch missing @Transactional annotations on @Modifying queries — a class of bug that slice tests miss because they wrap every test in a transaction by default.

Production Incident Playbook: Query Returns Wrong Results

Scenario: Operations reports that the “deactivate inactive users” nightly batch claims to have updated 12,000 rows, but the database still shows those users as active. The job exits successfully, the logs say Updated 12000 inactive users, and yet the data is unchanged.

Blast radius: One job, but its downstream impact is everything that depends on users.active. Login flows, billing prorates, abandoned-cart emails, and security audits all branch on this flag. Wrong data here propagates everywhere.

Detection: The data audit job (see Fix 7) catches it the next day. Without that audit, you find out from a user who was supposed to be deactivated but still received your nightly email. Detection can lag by weeks.

Diagnosis checklist:

1. Connect to the production database and run the same query manually. Confirm whether the update actually changed rows when run as raw SQL.
2. Check the repository method for @Modifying and @Transactional. Missing @Transactional is the most common cause of silent no-ops.
3. Enable SQL logging (Fix 6) on a staging environment and re-run the job. Watch whether the UPDATE statement actually fires and check the parameter values.
4. Look for @Modifying(clearAutomatically = false) (the default). If the service reads the same entities after the update, it may be working from stale cache and reporting incorrect “before” state.
5. Check whether the calling service method is marked @Transactional. If the batch wraps every update in its own transaction and one fails, partial completion can produce a misleading log line.

Recovery: Re-run the job after fixing the missing @Transactional. For data that was supposed to be deactivated weeks ago, you may need a one-off script that walks the user table and re-applies the rule. Recovery time: minutes to hours depending on how long the bug shipped.

Prevention: Integration test every @Modifying query with a real database (Fix 7). Add a data audit job for every bulk operation that asserts the post-condition invariant. Never trust a “row count” log line as confirmation that work was done — verify with a follow-up read after clearing the persistence context. See Fix: Spring Boot @Transactional Not Rolling Back for the broader transactional pitfalls.

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.

Native query result mapping fails — when using @Query(nativeQuery = true) that returns columns not in your entity, you get ConverterNotFoundException or ResultSetMapping errors. Either map the result to a projection interface explicitly or use a @SqlResultSetMapping annotation on the entity.

N+1 query bursts in production — a method that works fine in tests but generates 1000 queries in production usually has a lazy collection being iterated. Enable SQL logging (Fix 6) in staging and look for repeated SELECT statements with different IDs. Switch to JOIN FETCH or @EntityGraph per Fix 4.

Connection pool exhaustion under load — long-running queries hold a connection from the pool. Under traffic, you exhaust HikariCP and new requests time out with Connection is not available. Cap query duration with @QueryHints({@QueryHint(name = "javax.persistence.query.timeout", value = "5000")}) and tune hikari.maximumPoolSize. See Fix: Spring Boot DataSource Failed to Configure for pool sizing.

null parameter handled differently than expected — passing null to a derived findByName(String name) generates WHERE name = NULL, which never matches any row in SQL. Use findByNameIsNull or build a Specification with conditional clauses.

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