[FIXED] Custom Native Query in JpaRepository with a custom name throwing QueryCreationException

Issue

I am stuck in a very trivial issue for 2 days . I am creating a spring boot project with Rest endpoints . There are 2 entity classes - Employee and Address with OneToMany mapping with 1 employee having a list of addresses. I have created an EmployeeRepository class that implements JpaRepository for performing employee related transactions. So there is a requirement of fetching an employee by the address id , so as per my understanding , if we have to create our own custom query which is not provided by JpaRepository , we need to use @Query annotation above our query method. That method can be named anything - I have named it - getByAddress. I am facing problem here. Employee class has List of Addresses like -

@OneToMany
    @JoinColumn(name = "eId", referencedColumnName = "empId")
    List<Address> address;

Now whenever I am trying to fetch the employee by id by passing Integer address id in the Url , it says, expecting an address in URL, I am suspecting this is because I have named the method - getByAddress. So I changed the name to getByAddrId.

This throws exception -

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'employeeRepository' defined in com.shweta.rest.repository.EmployeeRepository defined in @EnableJpaRepositories declared on SpringRestApplication: Invocation of init method failed; nested exception is org.springframework.data.repository.query.QueryCreationException: Could not create query for public abstract com.shweta.rest.entity.Employee com.shweta.rest.repository.EmployeeRepository.getByAddrId(int)! Reason: Failed to create query for method public abstract com.shweta.rest.entity.Employee com.shweta.rest.repository.EmployeeRepository.getByAddrId(int)! No property 'addrId' found for type 'Employee'!; nested exception is java.lang.IllegalArgumentException: Failed to create query for method public abstract com.shweta.rest.entity.Employee com.shweta.rest.repository.EmployeeRepository.getByAddrId(int)! No property 'addrId' found for type 'Employee'!

Caused by: org.springframework.data.repository.query.QueryCreationException: Could not create query for public abstract com.shweta.rest.entity.Employee com.shweta.rest.repository.EmployeeRepository.getByAddrId(int)! Reason: Failed to create query for method public abstract com.shweta.rest.entity.Employee com.shweta.rest.repository.EmployeeRepository.getByAddrId(int)! No property 'addrId' found for type 'Employee'!; nested exception is java.lang.IllegalArgumentException: Failed to create query for method public abstract com.shweta.rest.entity.Employee com.shweta.rest.repository.EmployeeRepository.getByAddrId(int)! No property 'addrId' found for type 'Employee'!

I am highly confused now , we can give custom query method names then why is it treating my method name as a field in Employee class which its of course unable to find as there is no field like getByAddrId in my Employee class.

Look at my classes below :

My employee class:

My Address class:

Controller snippet :

/**
 To get Employee by address id
 */
@GetMapping("/getEmployeeByAddressId/{id}")
public ResponseEntity<Employee> getEmployeebyAddressId(@PathVariable("id") int id){
    return new ResponseEntity<Employee>(empService.getEmployeebyAddressId(id), HttpStatus.FOUND);
}

My Service class method that's making call to custom query method from EmployeeRepository:

 @Override 
  public Employee getEmployeebyAddressId(int id) 
  { 
      Employee emp = employeeRepository.getByAddrId(id); 
      return emp; 
  }

My JpaRepository class that has the custom method with custom query (query works fine in MySQL):

@Repository

public interface EmployeeRepository extends JpaRepository<Employee, Integer> {

  //Native Query
  @Query(
  name="select * from tb_employee where emp_id = (select e_id from tb_address where addr_id=?1 ", nativeQuery = true) 
  Employee getByAddrId(int id);
  

Please help me , I am not able to move ahead because of this blocker, my requirement is to create a custom query method to run any complex query

Solution

I found the issue:

In EmployeeRepository.java , instead of giving 'name' in @Query , 'value' attribute was to be given which was causing all the issues. After I fixed it to value , it worked fine, so below snippet is correct one

@Query(
  value="select * from tb_employee where emp_id = (select e_id from tb_address where addr_id=?1) ", nativeQuery = true) 
  Employee getByAddrId(int id);

Answered By - Shweta Priyadarshani
Answer Checked By - Clifford M. (JavaFixing Volunteer)