Spring JPA - underscore on column name, failed to create query, property not found
Issue
I have an Employee class with a company_id field. I’m using spring-data-jpa and I want to create a query with JPA to find a class
@Entity
public class Employee {
...
@Column(name = "company_id")
private Long company_id;
...
}
@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Long> {
Employee findByCompany_Id(Long companyId);
}
During startup, the application crashes because of this error:
org.springframework.data.repository.query.QueryCreationException: Could not create
query for public abstract com.example.jpa.Employee com.example.jpa.EmployeeRepository.findByCompany_Id(java.lang.Long)
Caused by: java.lang.IllegalArgumentException: Failed to create query for method
public abstract com.example.jpa.Employee com.example.employee.jpa.EmployeeRepository.findByCompany_Id(java.lang.Long);
No property 'company' found for type 'Employee'
I tried to change the method name to findByCompany_id but id didn’t help. This seems to happen only for parameters that have an underscore in the name. Why is this happening and how can I fix it?
Solution
In Spring Data JPA, the underscore is a special character, it is used to search into nested fields. So by defining the query findByCompany_Id, JPA expects to find a Company field with an Id in it. For example, this query would work fine if your Employee entity was defined like this:
@Entity
public class Employee {
...
@JoinColumn
private Company company;
...
}
In any case, to fix it I highly suggest changing all your entity parameters and the method query definition to camelCase, as that is what works best with jpa. Spring also automatically converts the field names to columns converting camelCase to snake_case out of the box, so you don’t need to delcare all the column names
@Entity
public class Employee {
...
private Long companyId;
...
}
@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Long> {
Employee findByCompanyId(Long companyId);
}
This is also mentioned in spring documentation:
Because we treat underscores (_) as a reserved character, we strongly advise to follow standard Java naming conventions (that is, not using underscores in property names but applying camel case instead).
If you don’t want to change to camel case, then you will have to write down your queries as you cannot rely on JPA:
@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Long> {
@Query("Select e from Employee e where e.company_id = :companyId")
Employee findByCompany_Id(Long companyId);
}