I am new to developing REST using CrudRepository. I was wondering the proper way to handle JOINs?

I have three classes, three repo classes and three service classes. Stress, Mood, User. StressRepo, MoodRepo, UserRepo. StressService, MoodService, UserService

An example of my service class:

@Service
public class MoodService {

    @Autowired
    private MoodRepository moodRepository;

    public Mood createMood(Mood mood) {
        return moodRepository.save(mood);
    }

    public Mood getMoodById(Long id) {
        return moodRepository.findById(id).orElse(null);
    }

    public List<Mood> getAllMoods() {
        return (List<Mood>) moodRepository.findAll();
    }

    public void deleteMood(Long id) {
        moodRepository.deleteById(id);
    }

     public Mood updateMood(Long id, Mood updateMood) {
        Mood existingMood = moodRepository.findById(id).orElse(null);
        if (existingMood != null) {
            Mood newMood = new Mood(updateMood.getUserId(), updateMood.getDate(), updateMood.getMood(), updateMood.getDescription());
            return moodRepository.save(newMood);
        }
        return null;
    }

}

If I was to do this in SQL to query the DB, I would do something like this..

SELECT * 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))

I assume I would need a custom controller that would handle this request, but do I need to incorporate the Mood and Stress class inside of the User class?

@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;

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

    @ManyToMany
    Stress stress;
    
    @ManyToMany
    Mood mood;
}

I am new to developing REST using CrudRepository. I was wondering the proper way to handle JOINs?

I have three classes, three repo classes and three service classes. Stress, Mood, User. StressRepo, MoodRepo, UserRepo. StressService, MoodService, UserService

An example of my service class:

@Service
public class MoodService {

    @Autowired
    private MoodRepository moodRepository;

    public Mood createMood(Mood mood) {
        return moodRepository.save(mood);
    }

    public Mood getMoodById(Long id) {
        return moodRepository.findById(id).orElse(null);
    }

    public List<Mood> getAllMoods() {
        return (List<Mood>) moodRepository.findAll();
    }

    public void deleteMood(Long id) {
        moodRepository.deleteById(id);
    }

     public Mood updateMood(Long id, Mood updateMood) {
        Mood existingMood = moodRepository.findById(id).orElse(null);
        if (existingMood != null) {
            Mood newMood = new Mood(updateMood.getUserId(), updateMood.getDate(), updateMood.getMood(), updateMood.getDescription());
            return moodRepository.save(newMood);
        }
        return null;
    }

}

If I was to do this in SQL to query the DB, I would do something like this..

SELECT * 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))

I assume I would need a custom controller that would handle this request, but do I need to incorporate the Mood and Stress class inside of the User class?

@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;

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

    @ManyToMany
    Stress stress;
    
    @ManyToMany
    Mood mood;
}

• java
• spring-boot
• crud-repository

• Hello, definitely yes. If you want to use an ORM for JOINs, check a ref for @OneToMany and @ManyToMany annotations (e.g.: baeldung/jpa-hibernate-associations, and baeldung/hibernate-one-to-many). In this case, you can avoid a lot of queries, because ORM will JOIN and map collections for you. You still can use a native query, if performance really matters for you in this project. Check out JPQL and JPA Native Query. – Mykola Commented 2 days ago
• Also, please, check your update method. It is not the best idea to create a new object each time. You also lost an ID. You can just try to update the existing one and save it: return moodRepository.findById(id).map(existingMood -> { existingMood.setDate(updatedMood.getDate()); // etc. return moodRepository.save(existingMood); }).orElseThrow(() -> new EntityNotFoundException("Mood not found with id " + id)); And... Instead of passing the entire Mood entity object from the controller, you could use a MoodUpdateDto with only the fields that should be editable. – Mykola Commented 2 days ago

1 Answer 1

Your example query implies that Mood has a user id and that Stress also has a user id, so I assume that inside your entity models for mood and stress, there is some pointer to a particular user. Or in other words, a given mood or stress row in the database points to only one (not many) users. So your user model should be using @OneToMany on both mood and stress.

@OneToMany(mappedBy = "user", cascade = CascadeType.ALL, orphanRemoval = true)
private List<Stress> stressList;

@OneToMany(mappedBy = "user", cascade = CascadeType.ALL, orphanRemoval = true)
private List<Mood> moodList;

Conversely your mood and stress models should be using

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

on the user reference.

I assume that is causing a problem for you. That being said, if you want a custom query in a JPA repository, you can just add the query to a text block. For example, in your MoodRepository you can have something like this:

@Query("""
  SELECT DISTINCT u FROM User u
  LEFT JOIN u.moodList m
  LEFT JOIN u.stressList s
  WHERE (m.mood = 1 OR m.mood = 2)
  AND (m.mood = 3 AND (s.stress = 4 OR s.stress = 5))""")
List<User> findUsersWithMoodAndStressConditions();