How to get list distinct in spring boot

I have a spring boot application where I get a list of this entity:

@Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "selected_date")
    private String date;

    @Column(name = "selected_start_time")
    private String startTime;

    @Column(name = "selected_end_time")
    private String endTime;

    @Column(name = "meeting_name")
    private String meetingName;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "voter_unique_name" , referencedColumnName = "unqiue_name")
    private Voter voterUniqueName;

I want to explain my problem with an example hoping I can explain it well.
If we have a meeting with a time from 17:00-18:00 o clock on 01.08.2021. Multiple participants can click and save themselves for this time. This is alright and what I want to achieve but now I want to get for example all participants who selected the time and date above, this kinda works but with the problem:

The times pill of 17:00-18:00 are shown two times this is due to my Database looking like this right now:

Because there are different voter_unique_name it gets it multiple times. I tried working with this code:

List<VoterAvailableTime> findDistinctByMeetingNameAndDateAndStartTimeAndEndTime(String meetingName, String date, String startTime, String endTime);

Using distinct but it still gets multiple times there. How can I make it so my times will be shown not multiple but single time?

update:

controller:

@GetMapping("/filterby")
    public List<VoterAvailableTimeDTO> findByDateMeetingStartAndEndTime(@RequestParam(value = "meetingName") String meetingName,
                                                                        @RequestParam(value = "date") String date, @RequestParam(value = "startTime") String startTime,
                                                                        @RequestParam(value = "endTime") String endTime) {
        return voterAvailableTimeService.findByMeetingNameAndDateAndStartAndEndTime(meetingName, date, startTime, endTime);
    }

service:

 public List<VoterAvailableTimeDTO> findByDateAndMeetingName(String meetingName, String date){
 List<VoterAvailableTimeI> voterAvailableTimesList =  voterAvailableTimeRepository.findByVoterAvailableTimeByMeetingNameAndDate(meetingName, date);
 return voterAvailableTimesList.stream()
         .map(this::convertToDto2)
         .collect(Collectors.toList());

}

converter:

  private VoterAvailableTimeDTO convertToDto2(VoterAvailableTimeI voterAvailableTime) {
        VoterAvailableTimeDTO voterAvailableTimeDTO = new VoterAvailableTimeDTO();
        voterAvailableTimeDTO.setDate(voterAvailableTime.getDate());
        voterAvailableTimeDTO.setStartTime(voterAvailableTime.getStartTime());
        voterAvailableTimeDTO.setEndTime(voterAvailableTime.getEndTime());
        voterAvailableTimeDTO.setMeetingName(voterAvailableTime.getMeetingName());

        return voterAvailableTimeDTO;
    }

repository:

@Query(value = "SELECT DISTINCT t.meetingName, t.date FROM VoterAvailableTime t " +
                "WHERE t.meetingName = ?1 and t.date = ?2")
        List<VoterAvailableTimeI> findByVoterAvailableTimeByMeetingNameAndDate(String meetingName, String date);

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

The result you see is correct because Distinct works for all fields in Spring Query Creation.

If you want to do it according to date, startTime, endTime and meetingName fields, you need to write a special query with using @Query.

The result of the query is different from the whole object. so you need to use Projections:

public interface VoterAvailableTimeI {
    String getMeetingName();
    String getDate();
    String getStartTime();
    String getEndTime();
}

Now we write our query that makes distinct according to date, startTime, endTime and meetingName fields and then connect it to our projection interface (VoterAvailableTimeI):

@Query(value = "SELECT DISTINCT t.meetingName as meetingName, t.date as date, t.startTime as startTime, t.endTime as endTime FROM VoterAvailableTime t " +
            "WHERE t.meetingName = ?1 and t.date = ?2 and t.startTime = ?3 and t.endTime = ?4")
List<VoterAvailableTimeI> findDistinctByMeetingNameAndDateAndStartTimeAndEndTime(String meetingName, String date, String startTime, String endTime);

Method 2

Query Result is correct that is how distinct works it checked all columns to give the results

• You can try adding GROUP BY statement as following

@Query("select distinct m "
    + "from meetings m where meeting_name=:meetingName and selected_date=:selectedDate and   " 
    + "selected_start_time=:selectedStartDate "
    + "and selected_end_time=:selectedEndDate "
    + "group by meeting_name")
List<VoterAvailableTime> findBySearchCriteria(@Param("meetingName") String meetingName, @Param("selectedDate") String
    selectedDate,@Param("selectedStartDate") String  selectedStartDate,@Param("selectedEndDate") String  selectedEndDate);

• Or Else you can specified columns which needs to be distinct as follows
  For this you need to add interface which exists meeting_name, selected_date, selected_start_time, selected_end_time variables for example interface called VoterAvailableTimeSlots.
  We need to do it since resulting number of parameters differs with Entity class. That’s why we need to use projection interface.

@Query("select distinct meeting_name,selected_date,selected_start_time,selected_end_time "
    + "from meetings m where meeting_name=:meetingName and selected_date=:selectedDate and   " 
    + "selected_start_time=:selectedStartDate "
    + "and selected_end_time=:selectedEndDate "
    + "group by meeting_name")
List<VoterAvailableTimeSlots> findBySearchCriteria(@Param("meetingName") String meetingName, @Param("selectedDate") String
    selectedDate,@Param("selectedStartDate") String  selectedStartDate,@Param("selectedEndDate") String  selectedEndDate);

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0