[FIXED] How to fetch list of objects with same phone number

Issue

I have an entity for driving_info with lot of fields but one of them is a phone number ( from which was ordered ). What I am trying to do is to fetch all drives that were ordered from that number. But when I try to pass the int of phoneNumber I get

query did not return a unique result: 5; nested exception is javax.persistence.NonUniqueResultException: query did not return a unique result: 5
org.springframework.dao.IncorrectResultSizeDataAccessException: query did not return a unique result: 5; nested exception is javax.persistence.NonUniqueResultException: query did not return a unique result: 5

I actually want the list of results so that I can get a response of list of all drives that were ordered from that phone number.

My controller method is

@GetMapping("/users/{phone}")
public List<User> getUserByPhone(@PathVariable int phone) {
   List<User> users= userService.findByPhone(phone);
   if(users == null) {
        throw new RuntimeException("User not found with "+phone+" phone number");
    }
    return users;
}

And my DAO is

@Override
@Transactional
public List<User> findByPhone(int phone) {
    Session currentSession = entityManager.unwrap(Session.class);
    Query<User> theQuery = currentSession.createQuery("from User where phone=:phone",User.class);
    List<User> users = theQuery.getResultList();
        
    return users;
}

Solution

Try to correct your query in this way:

List<User> users = currentSession.createQuery(
   "select u from User u where u.phone = :phone",
   User.class
).setParameter( "phone", phone )
.getResultList();

Please note that as it's stated in the documentation:

Even though HQL does not require the presence of a select_clause, it is generally good practice to include one. For simple queries the intent is clear and so the intended result of the select_clause is easy to infer. But on more complex queries that is not always the case.

It is usually better to explicitly specify intent. Hibernate does not actually enforce that a select_clause be present even when parsing JPQL queries, however, applications interested in JPA portability should take heed of this.

Answered By - SternK