如何通过JPA从RideTransaction表查询特定用户的指定状态记录

Hey there! Let's break down your current JPA queries and fix a critical logical flaw, plus make them more maintainable.

The Critical Issue: Operator Precedence #

Your current queries have a logic bug caused by SQL operator precedence—AND has higher priority than OR. Let's take your first query as an example:

@Query("SELECT r FROM RideTransaction r WHERE r.user=?1 AND r.status='RIDING' OR r.status='OUTSTANDING' OR r.status='COMPLETED'")

This gets parsed by SQL as:

SELECT r FROM RideTransaction r 
WHERE (r.user=?1 AND r.status='RIDING') 
OR r.status='OUTSTANDING' 
OR r.status='COMPLETED'

That means you'll get all records with status OUTSTANDING or COMPLETED—regardless of which user they belong to! The same bug applies to your second query too.

Fixed & Optimized Queries #

Here's how to fix and improve them:

1. Fix the Logic with Parentheses #

Wrap your OR conditions in parentheses to ensure the user filter applies to all status checks:

// For ride history
@Query("SELECT r FROM RideTransaction r WHERE r.user = ?1 AND (r.status = 'RIDING' OR r.status = 'OUTSTANDING' OR r.status = 'COMPLETED')")
List<RideTransaction> findRideHistoryOfUser(User user);

// For current riding/outstanding
@Query("SELECT r FROM RideTransaction r WHERE r.user = ?1 AND (r.status = 'RIDING' OR r.status = 'OUTSTANDING')")
List<RideTransaction> findAllCurrentRidingOrOutstanding(User user);

2. Use IN for Cleaner Readability #

Instead of chaining multiple OR conditions, use the IN clause—it's more concise and easier to read:

// For ride history
@Query("SELECT r FROM RideTransaction r WHERE r.user = ?1 AND r.status IN ('RIDING', 'OUTSTANDING', 'COMPLETED')")
List<RideTransaction> findRideHistoryOfUser(User user);

// For current riding/outstanding
@Query("SELECT r FROM RideTransaction r WHERE r.user = ?1 AND r.status IN ('RIDING', 'OUTSTANDING')")
List<RideTransaction> findAllCurrentRidingOrOutstanding(User user);

3. Type-Safe Optimization with Enums (Recommended) #

If you aren't already, use an enum for status instead of hardcoded strings. This eliminates typos and adds compile-time safety:
First, define the enum:

public enum RideStatus {
    RIDING, OUTSTANDING, COMPLETED, CANCELLED // Add other statuses as needed
}

Then update your entity to use the enum, and adjust the queries:

// Reusable method for flexible status filters
@Query("SELECT r FROM RideTransaction r WHERE r.user = :user AND r.status IN (:statuses)")
List<RideTransaction> findByUserAndStatusIn(@Param("user") User user, @Param("statuses") List<RideStatus> statuses);

// Specialized methods for your use cases
default List<RideTransaction> findRideHistoryOfUser(User user) {
    return findByUserAndStatusIn(user, Arrays.asList(RideStatus.RIDING, RideStatus.OUTSTANDING, RideStatus.COMPLETED));
}

default List<RideTransaction> findAllCurrentRidingOrOutstanding(User user) {
    return findByUserAndStatusIn(user, Arrays.asList(RideStatus.RIDING, RideStatus.OUTSTANDING));
}

This approach keeps your code DRY and reduces the risk of errors.

Bonus Tip: Consider Using Repository Derived Queries #

If you prefer avoiding custom @Query annotations, JPA can generate queries for you automatically:

// For ride history
List<RideTransaction> findByUserAndStatusIn(User user, List<RideStatus> statuses);

// For current riding/outstanding
List<RideTransaction> findByUserAndStatusIn(User user, RideStatus... statuses);

You can then call these with the appropriate status lists/enums, no custom SQL needed!

内容的提问来源于stack exchange，提问作者Rahul Chavan