Why jpa custom query returns more results-CodePudding

Spring boot has in memory DB H2. It has table and following data:-

    [
    {
        "id": 1,
        "firstName": "Lokesh",
        "lastName": "Gupta",
        "email": "[email protected]"
    },
    {
        "id": 2,
        "firstName": "John",
        "lastName": "Doe",
        "email": "[email protected]"
    }
]

This is my repository with custom jpa query:-

@Repository
public interface EmployeeRepository extends JpaRepository<EmployeeEntity, Long> {

    @Query("SELECT e FROM EmployeeEntity e WHERE e.firstName like firstName")
    List<EmployeeEntity> findByFirstName(@Param("firstName") String firstName);

    @Query("SELECT e FROM EmployeeEntity e WHERE e.id = id")
    EmployeeEntity findById2(@Param("id") long id);

}

Service has following 2 method:-

    public EmployeeEntity getEmployeeById2(Long id)  {
    EmployeeEntity employee = repository.findById2(id);
    return employee;
}

public List<EmployeeEntity> findByFirstName(String firstName){
    List<EmployeeEntity> employeeEntities = repository.findByFirstName(firstName);
    return employeeEntities;
}

Controller has this:-

@GetMapping("/{id}")
public ResponseEntity<EmployeeEntity> getEmployeeById(@PathVariable("id") Long id)
        throws RecordNotFoundException {
    EmployeeEntity entity = service.getEmployeeById2(id);

    return new ResponseEntity<EmployeeEntity>(entity, new HttpHeaders(), HttpStatus.OK);
}

@GetMapping(value = "/fname/{firstName}")
public List<EmployeeEntity> findByFirstName(@PathVariable("firstName") String firstName){
    return service.findByFirstName(firstName);
}

When I call findByFirstName. I got both EmployeeEntity.

When I call getEmployeeById. I got error:-

"status": 500, "error": "Internal Server Error", "message": "query did not return a unique result: 2; nested exception is javax.persistence.NonUniqueResultException: query did not return a unique result: 2",

I don't know how to write jap custom query correctly so that it returns correct results. How do I get only 1 employee by first name or only 1 employee by it's id?

CodePudding user response：

Without testing your code, try to use this,

@Repository
public interface EmployeeRepository extends JpaRepository<EmployeeEntity, Long> {

    @Query("SELECT e FROM EmployeeEntity e WHERE e.firstName like :firstName")
    List<EmployeeEntity> findByFirstName(@Param("firstName") String firstName);

    @Query("SELECT e FROM EmployeeEntity e WHERE e.id = :id")
    EmployeeEntity findById2(@Param("id") long id);

}

I added a character : before your JPQL variables.

CodePudding user response：

The named parameters in your query are missing a colon. It should be:

@Query("SELECT e FROM EmployeeEntity e WHERE e.firstName like :firstName")
List<EmployeeEntity> findByFirstName(@Param("firstName") String firstName);

@Query("SELECT e FROM EmployeeEntity e WHERE e.id = :id")
EmployeeEntity findById2(@Param("id") long id);