使用CrudRepository开发REST时处理JOIN的正确方式

首先要明确：你的User实体关联关系写错了，当前的@ManyToMany不适用你的场景——一个用户应该对应多条Mood、Stress记录，属于**一对多（One-to-Many）**关系，下面先修正实体关联：

一、修正实体关联映射 #

1. 修正User实体 #

@Getter
@Setter
@Entity
@NoArgsConstructor
@Table(name = "users")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    @Column(length = 100)
    private String name;

    @Column(length = 100)
    private String userName;

    @Column(length = 100)
    private String zipCode;

    public User(String userName, String name, String zipCode) {
        this.name = name;
        this.userName = userName;
        this.zipCode = zipCode;
    }

    // 一个用户对应多条Stress记录，关联字段是Stress里的user
    @OneToMany(mappedBy = "user", fetch = FetchType.LAZY)
    private List<Stress> stresses;
    
    // 一个用户对应多条Mood记录，关联字段是Mood里的user
    @OneToMany(mappedBy = "user", fetch = FetchType.LAZY)
    private List<Mood> moods;
}

2. 补充Mood实体关联 #

@Getter
@Setter
@Entity
@NoArgsConstructor
@Table(name = "moods")
public class Mood {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    // 多对一关联User
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "USER_ID")
    private User user;

    private LocalDate date;
    // 对应SQL里的MOOD字段，建议用更清晰的命名比如moodValue
    private Integer moodValue;
    private String description;

    // 保留你的构造逻辑，可添加user参数关联用户
    public Mood(User user, LocalDate date, Integer moodValue, String description) {
        this.user = user;
        this.date = date;
        this.moodValue = moodValue;
        this.description = description;
    }
}

3. 补充Stress实体关联 #

@Getter
@Setter
@Entity
@NoArgsConstructor
@Table(name = "stresses")
public class Stress {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "USER_ID")
    private User user;

    private LocalDate date;
    private Integer stressValue;
    private String description;

    public Stress(User user, LocalDate date, Integer stressValue, String description) {
        this.user = user;
        this.date = date;
        this.stressValue = stressValue;
        this.description = description;
    }
}

二、实现目标JOIN查询的三种方式 #

注意：你的原SQL WHERE条件存在逻辑矛盾——(m.MOOD =1 OR m.MOOD=2) AND (m.MOOD=3) 永远不会返回结果，下面示例保留你的条件结构，你可根据实际需求调整（比如改成(m.MOOD IN (1,2,3)) AND (s.STRESS IN (4,5))）。

1. 原生SQL查询（最贴近你写的SQL） #

在UserRepository中定义自定义查询方法：

public interface UserRepository extends CrudRepository<User, Long> {
    @Query(value = "SELECT DISTINCT u.* FROM USERS u " +
            "LEFT JOIN MOODS m ON u.id = m.USER_ID " +
            "LEFT JOIN STRESS s ON u.id = s.USER_ID " +
            "WHERE (m.MOOD = 1 OR m.MOOD = 2) AND (m.MOOD = 3 AND (s.STRESS = 4 OR s.STRESS = 5))",
            nativeQuery = true)
    List<User> findUsersWithSpecificMoodAndStress();
}

添加DISTINCT避免因JOIN产生重复的User记录。

2. JPQL查询（面向对象，符合JPA风格） #

同样在UserRepository中定义：

@Query("SELECT DISTINCT u FROM User u " +
        "LEFT JOIN u.moods m " +
        "LEFT JOIN u.stresses s " +
        "WHERE (m.moodValue = 1 OR m.moodValue = 2) AND (m.moodValue = 3 AND (s.stressValue = 4 OR s.stressValue = 5))")
List<User> findUsersViaJPQL();

JPQL直接操作实体和属性，无需写表名和数据库字段名（使用实体属性名）。

3. Specification动态查询（适合复杂/动态条件） #

如果后续需要动态调整查询条件，可使用Specification：

public interface UserRepository extends CrudRepository<User, Long>, JpaSpecificationExecutor<User> {
}

然后在Service中构建查询条件：

@Service
public class UserService {
    @Autowired
    private UserRepository userRepository;

    public List<User> findUsersWithConditions() {
        Specification<User> spec = (root, query, cb) -> {
            Join<User, Mood> moodJoin = root.join("moods", JoinType.LEFT);
            Join<User, Stress> stressJoin = root.join("stresses", JoinType.LEFT);
            
            Predicate moodCondition1 = cb.or(
                    cb.equal(moodJoin.get("moodValue"), 1),
                    cb.equal(moodJoin.get("moodValue"), 2)
            );
            Predicate moodCondition2 = cb.equal(moodJoin.get("moodValue"), 3);
            Predicate stressCondition = cb.or(
                    cb.equal(stressJoin.get("stressValue"), 4),
                    cb.equal(stressJoin.get("stressValue"), 5)
            );
            
            return cb.and(moodCondition1, cb.and(moodCondition2, stressCondition));
        };
        return userRepository.findAll(spec);
    }
}

三、在Controller中暴露REST接口 #

@RestController
@RequestMapping("/api/users")
public class UserController {
    @Autowired
    private UserService userService;

    @GetMapping("/filtered")
    public ResponseEntity<List<User>> getFilteredUsers() {
        List<User> users = userService.findUsersWithConditions();
        return ResponseEntity.ok(users);
    }
}

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