[FIXED] how to Distinct in JPA Spring boot?

Issue

how to using distinct in JPA? because I want to get all phone numbers and name in my table, so i get all result by unique , my table name on a database called whatsapp_chat and I want to get by wa_id(my field database)

i tried Lits findDistincByWhatsappid() , Lits findDistincWhatsappid(); I got the error to use them, how to correct way to use Distinct? I am new in Spring boot and Java

i tried this also :

@Query("SELECT DISTINCT wa_id FROM whatsapp_chat")
    List<String> findDistinctWhatsappid();

hee is my entity/model :

Entity
@Data
@Table(name = "WHATSAPP_CHAT")
@DynamicUpdate
public class WhatsappChat extends Base {
    @Column(name = "NAME")
    private String name;

    @Column(name = "WA_ID")
    private String whatsappid;

    @Column(name = "TEXT")
    private String text;

    @Column(name = "MESSAGE_ID")
    private String messageid;

    @Temporal(TemporalType.TIMESTAMP)
    private Date timestamp;

}

Solution

You have two options:

1. Write native query:

   @Query(value = "SELECT DISTINCT w.WA_ID FROM WHATSAPP_CHAT w", nativeQuery = true)
   List<String> findDistinctWhatsappIds();
   

2. Write JPQL query (querying using objects):

   @Query("SELECT DISTINCT w.whatsappid FROM WhatsappChat AS w")
   List<String> findDistinctWhatsappIds();
   

Either way, I would change the method name to be in plural (it is a list) and Uppercase in the Id (column retrieving) to know exactly what it is coming.

NOTE: The method name has no impact in the query itself as it contains the annotation @Query specifying what to execute.

You can test with this ( a little different if not using Spring-boot):

@DataJpaTest
@RunWith(SpringRunner.class)
public class WhatsappChatRepositoryTest {

    @Autowired
    EntityManager entityManager;

    @Autowired
    WhatsappChatRepository whatsappChatRepository;

    @Before
    public void setup() {
        entityManager.persist(new WhatsappChat("whats1", "whats01", 1));
        entityManager.persist(new WhatsappChat("whats2", "whats02", 2));
        entityManager.persist(new WhatsappChat("whats3", "whats01", 3));
        entityManager.persist(new WhatsappChat("whats4", "whats04", 4));

        entityManager.flush();
    }

    @Test
    public void shouldTestSameResult() {
        assertEquals(whatsappChatRepository.findDistinctWhatsappIds(), whatsappChatRepository.findDistinctWhatsappIdsNative());

        assertEquals(Arrays.asList("whats01", "whats02", "whats04"), whatsappChatRepository.findDistinctWhatsappIds());
    }
}

Answered By - Brother